第一次写博客，想不到写什么好b（￣▽￣）d ，考虑的半天决定从sqlhelper开始，sqlhelper对程序员来说就像helloworld一样，很简单却又很重要，helloworld代表着程序员萌新第一次写代码，而sqlhelper则是初次接触数据库（不知道这种说法对不对）。
好了不废话了，下面直接上代码（无话可说了）：
我要评论
public class sqlhelper
{
// 超时时间
private static int timeout = 1000;
// 数据库名称
public const string bestnet = "bestnet";
//存储过程名称
public const string userinfocurd = "userinfocurd";
// 数据库连接字符串
private static dictionary<string, string> connstrs = new dictionary<string, string>();
/// <summary>
/// sqlserver操作类(静态构造函数)
/// </summary>
static sqlhelper()
{
connectionstringsettingscollection configs = webconfigurationmanager.connectionstrings;
foreach (connectionstringsettings config in configs)
{
connstrs.add(config.name, config.connectionstring);
}
}
/// <summary>
/// 获取数据库连接
/// </summary>
/// <param name="database">数据库(配置文件内connectionstrings的name)</param>
/// <returns>数据库连接</returns>
private static sqlconnection getconnection(string database)
{
if (string.isnullorempty(database))
{
throw new exception("未设置参数：database");
}
if (!connstrs.containskey(database))
{
throw new exception("未找到数据库：" + database);
}
return new sqlconnection(connstrs[database]);
}
/// <summary>
/// 获取sqlcommand
/// </summary>
/// <param name="conn">sqlconnection</param>
/// <param name="transaction">sqltransaction</param>
/// <param name="cmdtype">commandtype</param>
/// <param name="sql">sql</param>
/// <param name="parms">sqlparameter数组</param>
/// <returns></returns>
private static sqlcommand getcommand(sqlconnection conn, sqltransaction transaction, commandtype cmdtype, string sql, sqlparameter[] parms)
{
sqlcommand cmd = new sqlcommand(sql, conn);
cmd.commandtype = cmdtype;
cmd.commandtimeout = timeout;
if (transaction != null)
cmd.transaction = transaction;
if (parms != null && parms.length != 0)
cmd.parameters.addrange(parms);
return cmd;
}
/// <summary>
/// 查询数据，返回datatable
/// </summary>
/// <param name="database">数据库</param>
/// <param name="sql">sql语句或存储过程名</param>
/// <param name="parms">参数</param>
/// <param name="cmdtype">查询类型(sql语句/存储过程名)</param>
/// <returns>datatable</returns>
public static datatable querydatatable(string database, string sql, sqlparameter[] parms, commandtype cmdtype)
{
if (string.isnullorempty(database))
{
throw new exception("未设置参数：database");
}
if (string.isnullorempty(sql))
{
throw new exception("未设置参数：sql");
}
try
{
using (sqlconnection conn = getconnection(database))
{
conn.open();
using (sqlcommand cmd = getcommand(conn, null, cmdtype, sql, parms))
{
using (sqldataadapter da = new sqldataadapter(cmd))
{
datatable dt = new datatable();
da.fill(dt);
return dt;
}
}
}
}
catch (sqlexception ex)
{
system.text.stringbuilder log = new system.text.stringbuilder();
log.append("查询数据出错：");
log.append(ex);
throw new exception(log.tostring());
}
}
/// <summary>
/// 查询数据，返回dataset
/// </summary>
/// <param name="database">数据库</param>
/// <param name="sql">sql语句或存储过程名</param>
/// <param name="parms">参数</param>
/// <param name="cmdtype">查询类型(sql语句/存储过程名)</param>
/// <returns>dataset</returns>
public static dataset querydataset(string database, string sql, sqlparameter[] parms, commandtype cmdtype)
{
if (string.isnullorempty(database))
{
throw new exception("未设置参数：database");
}
if (string.isnullorempty(sql))
{
throw new exception("未设置参数：sql");
}
try
{
using (sqlconnection conn = getconnection(database))
{
conn.open();
using (sqlcommand cmd = getcommand(conn, null, cmdtype, sql, parms))
{
using (sqldataadapter da = new sqldataadapter(cmd))
{
dataset ds = new dataset();
da.fill(ds);
return ds;
}
}
}
}
catch (sqlexception ex)
{
system.text.stringbuilder log = new system.text.stringbuilder();
log.append("查询数据出错：");
log.append(ex);
throw new exception(log.tostring());
}
}
/// <summary>
/// 执行命令获取唯一值(第一行第一列)
/// </summary>
/// <param name="database">数据库</param>
/// <param name="sql">sql语句或存储过程名</param>
/// <param name="parms">参数</param>
/// <param name="cmdtype">查询类型(sql语句/存储过程名)</param>
/// <returns>获取值</returns>
public static object queryscalar(string database, string sql, sqlparameter[] parms, commandtype cmdtype)
{
if (string.isnullorempty(database))
{
throw new exception("未设置参数：database");
}
if (string.isnullorempty(sql))
{
throw new exception("未设置参数：sql");
}
try
{
using (sqlconnection conn = getconnection(database))
{
conn.open();
using (sqlcommand cmd = getcommand(conn, null, cmdtype, sql, parms))
{
return cmd.executescalar();
}
}
}
catch (sqlexception ex)
{
system.text.stringbuilder log = new system.text.stringbuilder();
log.append("处理出错：");
log.append(ex);
throw new exception(log.tostring());
}
}
/// <summary>
/// 执行命令更新数据
/// </summary>
/// <param name="database">数据库</param>
/// <param name="sql">sql语句或存储过程名</param>
/// <param name="parms">参数</param>
/// <param name="cmdtype">查询类型(sql语句/存储过程名)</param>
/// <returns>更新的行数</returns>
public static int execute(string database, string sql, sqlparameter[] parms, commandtype cmdtype)
{
if (string.isnullorempty(database))
{
throw new exception("未设置参数：database");
}
if (string.isnullorempty(sql))
{
throw new exception("未设置参数：sql");
}
//返回(增删改)的更新行数
int count = 0;
try
{
using (sqlconnection conn = getconnection(database))
{
conn.open();
using (sqlcommand cmd = getcommand(conn, null, cmdtype, sql, parms))
{
if (cmdtype == commandtype.storedprocedure)
cmd.parameters.addwithvalue("@return_value", "").direction = parameterdirection.returnvalue;
count = cmd.executenonquery();
if (count <= 0)
if (cmdtype == commandtype.storedprocedure)
count = (int)cmd.parameters["@return_value"].value;
}
}
}
catch (sqlexception ex)
{
system.text.stringbuilder log = new system.text.stringbuilder();
log.append("处理出错：");
log.append(ex);
throw new exception(log.tostring());
}
return count;
}
/// <summary>
/// 查询数据，返回datatable
/// </summary>
/// <param name="database">数据库</param>
/// <param name="sql">sql语句或存储过程名</param>
/// <param name="cmdtype">查询类型(sql语句/存储过程名)</param>
/// <param name="values">参数</param>
/// <returns>datatable</returns>
public static datatable querydatatable(string database, string sql, commandtype cmdtype, idictionary<string, object> values)
{
sqlparameter[] parms = dictoparams(values);
return querydatatable(database, sql, parms, cmdtype);
}
/// <summary>
/// 执行存储过程查询数据，返回dataset
/// </summary>
/// <param name="database">数据库</param>
/// <param name="sql">sql语句或存储过程名</param>
/// <param name="cmdtype">查询类型(sql语句/存储过程名)</param>
/// <param name="values">参数
/// <returns>dataset</returns>
public static dataset querydataset(string database, string sql, commandtype cmdtype, idictionary<string, object> values)
{
sqlparameter[] parms = dictoparams(values);
return querydataset(database, sql, parms, cmdtype);
}
/// <summary>
/// 执行命令获取唯一值(第一行第一列)
/// </summary>
/// <param name="database">数据库</param>
/// <param name="sql">sql语句或存储过程名</param>
/// <param name="cmdtype">查询类型(sql语句/存储过程名)</param>
/// <param name="values">参数</param>
/// <returns>唯一值</returns>
public static object queryscalar(string database, string sql, commandtype cmdtype, idictionary<string, object> values)
{
sqlparameter[] parms = dictoparams(values);
return queryscalar(database, sql, parms, cmdtype);
}
/// <summary>
/// 执行命令更新数据
/// </summary>
/// <param name="database">数据库</param>
/// <param name="sql">sql语句或存储过程名</param>
/// <param name="cmdtype">查询类型(sql语句/存储过程名)</param>
/// <param name="values">参数</param>
/// <returns>更新的行数</returns>
public static int execute(string database, string sql, commandtype cmdtype, idictionary<string, object> values)
{
sqlparameter[] parms = dictoparams(values);
return execute(database, sql, parms, cmdtype);
}
/// <summary>
/// 创建参数
/// </summary>
/// <param name="name">参数名</param>
/// <param name="type">参数类型</param>
/// <param name="size">参数大小</param>
/// <param name="direction">参数方向(输入/输出)</param>
/// <param name="value">参数值</param>
/// <returns>新参数对象</returns>
public static sqlparameter[] dictoparams(idictionary<string, object> values)
{
if (values == null) return null;
sqlparameter[] parms = new sqlparameter[values.count];
int index = 0;
foreach (keyvaluepair<string, object> kv in values)
{
sqlparameter parm = null;
if (kv.value == null)
{
parm = new sqlparameter(kv.key, dbnull.value);
}
else
{
type t = kv.value.gettype();
parm = new sqlparameter(kv.key, nettosql(kv.value.gettype()));
parm.value = kv.value;
}
parms[index++] = parm;
}
return parms;
}
/// <summary>
/// .net类型转换为sql类型
/// </summary>
/// <param name="t">.net类型</param>
/// <returns>sql类型</returns>
public static sqldbtype nettosql(type t)
{
sqldbtype dbtype = sqldbtype.variant;
switch (t.name)
{
case "int16":
dbtype = sqldbtype.smallint;
break;
case "int32":
dbtype = sqldbtype.int;
break;
case "int64":
dbtype = sqldbtype.bigint;
break;
case "single":
dbtype = sqldbtype.real;
break;
case "decimal":
dbtype = sqldbtype.decimal;
break;
case "byte[]":
dbtype = sqldbtype.varbinary;
break;
case "boolean":
dbtype = sqldbtype.bit;
break;
case "string":
dbtype = sqldbtype.nvarchar;
break;
case "char[]":
dbtype = sqldbtype.char;
break;
case "datetime":
dbtype = sqldbtype.datetime;
break;
case "datetime2":
dbtype = sqldbtype.datetime2;
break;
case "datetimeoffset":
dbtype = sqldbtype.datetimeoffset;
break;
case "timespan":
dbtype = sqldbtype.time;
break;
case "guid":
dbtype = sqldbtype.uniqueidentifier;
break;
case "xml":
dbtype = sqldbtype.xml;
break;
case "object":
dbtype = sqldbtype.variant;
break;
}
return dbtype;
}
}
可以直接这样调用：
idictionary<string, object> values = new dictionary<string, object>();
values.add("@username", username);
values.add("@password", password);
object scalar = sqlhelper.queryscalar(sqlhelper.bestnet, sqlhelper.userinfocurd, commandtype.storedprocedure, values);
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持移动技术网。
如对本文有疑问， 点击进行留言回复！！
使用Visual Studio2019创建C#项目(窗体应用程序、控制台应用程序、Web应用程序)
C#实现获取本地内网(局域网)和外网(公网)IP地址的方法分析
网友评论