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);
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持毛票票。