C#实现较为实用的SQLhelper
第一次写博客，想不到写什么好b（￣▽￣）d，考虑的半天决定从sqlhelper开始，sqlhelper对程序员来说就像helloworld一样，很简单却又很重要，helloworld代表着程序员萌新第一次写代码，而sqlhelper则是初次接触数据库（不知道这种说法对不对）。
好了不废话了，下面直接上代码（无话可说了）：
publicclassSQLHelper
{
//超时时间
privatestaticintTimeout=1000;
//数据库名称
publicconstStringBestNet="BestNet";
//存储过程名称
publicconstStringUserInfoCURD="UserInfoCURD";
//数据库连接字符串
privatestaticDictionary<String,String>ConnStrs=newDictionary<String,String>();
///<summary>
///SQLServer操作类(静态构造函数)
///</summary>
staticSQLHelper()
{
ConnectionStringSettingsCollectionconfigs=WebConfigurationManager.ConnectionStrings;
foreach(ConnectionStringSettingsconfiginconfigs)
{
ConnStrs.Add(config.Name,config.ConnectionString);
}
}
///<summary>
///获取数据库连接
///</summary>
///<paramname="database">数据库(配置文件内connectionStrings的name)</param>
///<returns>数据库连接</returns>
privatestaticSqlConnectionGetConnection(stringdatabase)
{
if(string.IsNullOrEmpty(database))
{
thrownewException("未设置参数：database");
}
if(!ConnStrs.ContainsKey(database))
{
thrownewException("未找到数据库："+database);
}
returnnewSqlConnection(ConnStrs[database]);
}
///<summary>
///获取SqlCommand
///</summary>
///<paramname="conn">SqlConnection</param>
///<paramname="transaction">SqlTransaction</param>
///<paramname="cmdType">CommandType</param>
///<paramname="sql">SQL</param>
///<paramname="parms">SqlParameter数组</param>
///<returns></returns>
privatestaticSqlCommandGetCommand(SqlConnectionconn,SqlTransactiontransaction,CommandTypecmdType,stringsql,SqlParameter[]parms)
{
SqlCommandcmd=newSqlCommand(sql,conn);
cmd.CommandType=cmdType;
cmd.CommandTimeout=Timeout;
if(transaction!=null)
cmd.Transaction=transaction;
if(parms!=null&&parms.Length!=0)
cmd.Parameters.AddRange(parms);
returncmd;
}
///<summary>
///查询数据，返回DataTable
///</summary>
///<paramname="database">数据库</param>
///<paramname="sql">SQL语句或存储过程名</param>
///<paramname="parms">参数</param>
///<paramname="cmdType">查询类型(SQL语句/存储过程名)</param>
///<returns>DataTable</returns>
publicstaticDataTableQueryDataTable(stringdatabase,stringsql,SqlParameter[]parms,CommandTypecmdType)
{
if(string.IsNullOrEmpty(database))
{
thrownewException("未设置参数：database");
}
if(string.IsNullOrEmpty(sql))
{
thrownewException("未设置参数：sql");
}
try
{
using(SqlConnectionconn=GetConnection(database))
{
conn.Open();
using(SqlCommandcmd=GetCommand(conn,null,cmdType,sql,parms))
{
using(SqlDataAdapterda=newSqlDataAdapter(cmd))
{
DataTabledt=newDataTable();
da.Fill(dt);
returndt;
}
}
}
}
catch(SqlExceptionex)
{
System.Text.StringBuilderlog=newSystem.Text.StringBuilder();
log.Append("查询数据出错：");
log.Append(ex);
thrownewException(log.ToString());
}
}
///<summary>
///查询数据，返回DataSet
///</summary>
///<paramname="database">数据库</param>
///<paramname="sql">SQL语句或存储过程名</param>
///<paramname="parms">参数</param>
///<paramname="cmdType">查询类型(SQL语句/存储过程名)</param>
///<returns>DataSet</returns>
publicstaticDataSetQueryDataSet(stringdatabase,stringsql,SqlParameter[]parms,CommandTypecmdType)
{
if(string.IsNullOrEmpty(database))
{
thrownewException("未设置参数：database");
}
if(string.IsNullOrEmpty(sql))
{
thrownewException("未设置参数：sql");
}
try
{
using(SqlConnectionconn=GetConnection(database))
{
conn.Open();
using(SqlCommandcmd=GetCommand(conn,null,cmdType,sql,parms))
{
using(SqlDataAdapterda=newSqlDataAdapter(cmd))
{
DataSetds=newDataSet();
da.Fill(ds);
returnds;
}
}
}
}
catch(SqlExceptionex)
{
System.Text.StringBuilderlog=newSystem.Text.StringBuilder();
log.Append("查询数据出错：");
log.Append(ex);
thrownewException(log.ToString());
}
}
///<summary>
///执行命令获取唯一值(第一行第一列)
///</summary>
///<paramname="database">数据库</param>
///<paramname="sql">SQL语句或存储过程名</param>
///<paramname="parms">参数</param>
///<paramname="cmdType">查询类型(SQL语句/存储过程名)</param>
///<returns>获取值</returns>
publicstaticobjectQueryScalar(stringdatabase,stringsql,SqlParameter[]parms,CommandTypecmdType)
{
if(string.IsNullOrEmpty(database))
{
thrownewException("未设置参数：database");
}
if(string.IsNullOrEmpty(sql))
{
thrownewException("未设置参数：sql");
}
try
{
using(SqlConnectionconn=GetConnection(database))
{
conn.Open();
using(SqlCommandcmd=GetCommand(conn,null,cmdType,sql,parms))
{
returncmd.ExecuteScalar();
}
}
}
catch(SqlExceptionex)
{
System.Text.StringBuilderlog=newSystem.Text.StringBuilder();
log.Append("处理出错：");
log.Append(ex);
thrownewException(log.ToString());
}
}
///<summary>
///执行命令更新数据
///</summary>
///<paramname="database">数据库</param>
///<paramname="sql">SQL语句或存储过程名</param>
///<paramname="parms">参数</param>
///<paramname="cmdType">查询类型(SQL语句/存储过程名)</param>
///<returns>更新的行数</returns>
publicstaticintExecute(stringdatabase,stringsql,SqlParameter[]parms,CommandTypecmdType)
{
if(string.IsNullOrEmpty(database))
{
thrownewException("未设置参数：database");
}
if(string.IsNullOrEmpty(sql))
{
thrownewException("未设置参数：sql");
}
//返回(增删改)的更新行数
intcount=0;
try
{
using(SqlConnectionconn=GetConnection(database))
{
conn.Open();
using(SqlCommandcmd=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(SqlExceptionex)
{
System.Text.StringBuilderlog=newSystem.Text.StringBuilder();
log.Append("处理出错：");
log.Append(ex);
thrownewException(log.ToString());
}
returncount;
}
///<summary>
///查询数据，返回DataTable
///</summary>
///<paramname="database">数据库</param>
///<paramname="sql">SQL语句或存储过程名</param>
///<paramname="cmdType">查询类型(SQL语句/存储过程名)</param>
///<paramname="values">参数</param>
///<returns>DataTable</returns>
publicstaticDataTableQueryDataTable(stringdatabase,stringsql,CommandTypecmdType,IDictionary<string,object>values)
{
SqlParameter[]parms=DicToParams(values);
returnQueryDataTable(database,sql,parms,cmdType);
}
///<summary>
///执行存储过程查询数据，返回DataSet
///</summary>
///<paramname="database">数据库</param>
///<paramname="sql">SQL语句或存储过程名</param>
///<paramname="cmdType">查询类型(SQL语句/存储过程名)</param>
///<paramname="values">参数
///<returns>DataSet</returns>
publicstaticDataSetQueryDataSet(stringdatabase,stringsql,CommandTypecmdType,IDictionary<string,object>values)
{
SqlParameter[]parms=DicToParams(values);
returnQueryDataSet(database,sql,parms,cmdType);
}
///<summary>
///执行命令获取唯一值(第一行第一列)
///</summary>
///<paramname="database">数据库</param>
///<paramname="sql">SQL语句或存储过程名</param>
///<paramname="cmdType">查询类型(SQL语句/存储过程名)</param>
///<paramname="values">参数</param>
///<returns>唯一值</returns>
publicstaticobjectQueryScalar(stringdatabase,stringsql,CommandTypecmdType,IDictionary<string,object>values)
{
SqlParameter[]parms=DicToParams(values);
returnQueryScalar(database,sql,parms,cmdType);
}
///<summary>
///执行命令更新数据
///</summary>
///<paramname="database">数据库</param>
///<paramname="sql">SQL语句或存储过程名</param>
///<paramname="cmdType">查询类型(SQL语句/存储过程名)</param>
///<paramname="values">参数</param>
///<returns>更新的行数</returns>
publicstaticintExecute(stringdatabase,stringsql,CommandTypecmdType,IDictionary<string,object>values)
{
SqlParameter[]parms=DicToParams(values);
returnExecute(database,sql,parms,cmdType);
}
///<summary>
///创建参数
///</summary>
///<paramname="name">参数名</param>
///<paramname="type">参数类型</param>
///<paramname="size">参数大小</param>
///<paramname="direction">参数方向(输入/输出)</param>
///<paramname="value">参数值</param>
///<returns>新参数对象</returns>
publicstaticSqlParameter[]DicToParams(IDictionary<string,object>values)
{
if(values==null)returnnull;
SqlParameter[]parms=newSqlParameter[values.Count];
intindex=0;
foreach(KeyValuePair<string,object>kvinvalues)
{
SqlParameterparm=null;
if(kv.Value==null)
{
parm=newSqlParameter(kv.Key,DBNull.Value);
}
else
{
Typet=kv.Value.GetType();
parm=newSqlParameter(kv.Key,NetToSql(kv.Value.GetType()));
parm.Value=kv.Value;
}
parms[index++]=parm;
}
returnparms;
}
///<summary>
///.net类型转换为Sql类型
///</summary>
///<paramname="t">.net类型</param>
///<returns>Sql类型</returns>
publicstaticSqlDbTypeNetToSql(Typet)
{
SqlDbTypedbType=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;
}
returndbType;
}
}
可以直接这样调用：
IDictionary<string,object>values=newDictionary<string,object>();
values.Add("@UserName",UserName);
values.Add("@PassWord",passWord);
objectScalar=SQLHelper.QueryScalar(SQLHelper.BestNet,SQLHelper.UserInfoCURD,CommandType.StoredProcedure,values);
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。