1     public class SQLHelper  2     {  3         // 超时时间  4         private static int Timeout = 1000;  5         // 数据库名称  6         public const String BestNet = "BestNet";  7         //存储过程名称  8         public const String UserInfoCURD = "UserInfoCURD";  9         // 数据库连接字符串 10         private static Dictionary<String, String> ConnStrs = new Dictionary<String, String>(); 11  12         /// <summary> 13         /// SQLServer操作类(静态构造函数) 14         /// </summary> 15         static SQLHelper() 16         { 17             ConnectionStringSettingsCollection configs = WebConfigurationManager.ConnectionStrings; 18             foreach (ConnectionStringSettings config in configs) 19             { 20                 ConnStrs.Add(config.Name, config.ConnectionString); 21             } 22         } 23  24         /// <summary> 25         /// 获取数据库连接 26         /// </summary> 27         /// <param name="database">数据库(配置文件内connectionStrings的name)</param> 28         /// <returns>数据库连接</returns> 29         private static SqlConnection GetConnection(string database) 30         { 31             if (string.IsNullOrEmpty(database)) 32             { 33                 throw new Exception("未设置参数：database"); 34             } 35             if (!ConnStrs.ContainsKey(database)) 36             { 37                 throw new Exception("未找到数据库：" + database); 38             } 39             return new SqlConnection(ConnStrs[database]); 40         } 41  42         /// <summary> 43         /// 获取SqlCommand 44         /// </summary> 45         /// <param name="conn">SqlConnection</param> 46         /// <param name="transaction">SqlTransaction</param> 47         /// <param name="cmdType">CommandType</param> 48         /// <param name="sql">SQL</param> 49         /// <param name="parms">SqlParameter数组</param> 50         /// <returns></returns> 51         private static SqlCommand GetCommand(SqlConnection conn, SqlTransaction transaction, CommandType cmdType, string sql, SqlParameter[] parms) 52         { 53             SqlCommand cmd = new SqlCommand(sql, conn); 54             cmd.CommandType = cmdType; 55             cmd.CommandTimeout = Timeout; 56             if (transaction != null) 57                 cmd.Transaction = transaction; 58             if (parms != null && parms.Length != 0) 59                 cmd.Parameters.AddRange(parms); 60             return cmd; 61         } 62  63         /// <summary> 64         /// 查询数据，返回DataTable 65         /// </summary> 66         /// <param name="database">数据库</param> 67         /// <param name="sql">SQL语句或存储过程名</param> 68         /// <param name="parms">参数</param> 69         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param> 70         /// <returns>DataTable</returns> 71         public static DataTable QueryDataTable(string database, string sql, SqlParameter[] parms, CommandType cmdType) 72         { 73             if (string.IsNullOrEmpty(database)) 74             { 75                 throw new Exception("未设置参数：database"); 76             } 77             if (string.IsNullOrEmpty(sql)) 78             { 79                 throw new Exception("未设置参数：sql"); 80             } 81  82             try 83             { 84                 using (SqlConnection conn = GetConnection(database)) 85                 { 86                     conn.Open(); 87  88                     using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms)) 89                     { 90                         using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 91                         { 92                             DataTable dt = new DataTable(); 93                             da.Fill(dt); 94                             return dt; 95                         } 96                     } 97                 } 98             } 99             catch (SqlException ex)100             {101                 System.Text.StringBuilder log = new System.Text.StringBuilder();102                 log.Append("查询数据出错：");103                 log.Append(ex);104                 throw new Exception(log.ToString());105             }106         }107 108         /// <summary>109         /// 查询数据，返回DataSet110         /// </summary>111         /// <param name="database">数据库</param>112         /// <param name="sql">SQL语句或存储过程名</param>113         /// <param name="parms">参数</param>114         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>115         /// <returns>DataSet</returns>116         public static DataSet QueryDataSet(string database, string sql, SqlParameter[] parms, CommandType cmdType)117         {118             if (string.IsNullOrEmpty(database))119             {120                 throw new Exception("未设置参数：database");121             }122             if (string.IsNullOrEmpty(sql))123             {124                 throw new Exception("未设置参数：sql");125             }126 127             try128             {129                 using (SqlConnection conn = GetConnection(database))130                 {131                     conn.Open();132 133                     using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))134                     {135                         using (SqlDataAdapter da = new SqlDataAdapter(cmd))136                         {137                             DataSet ds = new DataSet();138                             da.Fill(ds);139                             return ds;140                         }141                     }142                 }143             }144             catch (SqlException ex)145             {146                 System.Text.StringBuilder log = new System.Text.StringBuilder();147                 log.Append("查询数据出错：");148                 log.Append(ex);149                 throw new Exception(log.ToString());150             }151         }152 153         /// <summary>154         /// 执行命令获取唯一值(第一行第一列)155         /// </summary>156         /// <param name="database">数据库</param>157         /// <param name="sql">SQL语句或存储过程名</param>158         /// <param name="parms">参数</param>159         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>160         /// <returns>获取值</returns>161         public static object QueryScalar(string database, string sql, SqlParameter[] parms, CommandType cmdType)162         {163             if (string.IsNullOrEmpty(database))164             {165                 throw new Exception("未设置参数：database");166             }167             if (string.IsNullOrEmpty(sql))168             {169                 throw new Exception("未设置参数：sql");170             }171             try172             {173                 using (SqlConnection conn = GetConnection(database))174                 {175                     conn.Open();176 177                     using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))178                     {179                         return cmd.ExecuteScalar();180                     }181                 }182             }183             catch (SqlException ex)184             {185                 System.Text.StringBuilder log = new System.Text.StringBuilder();186                 log.Append("处理出错：");187                 log.Append(ex);188                 throw new Exception(log.ToString());189             }190         }191 192         /// <summary>193         /// 执行命令更新数据194         /// </summary>195         /// <param name="database">数据库</param>196         /// <param name="sql">SQL语句或存储过程名</param>197         /// <param name="parms">参数</param>198         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>199         /// <returns>更新的行数</returns>200         public static int Execute(string database, string sql, SqlParameter[] parms, CommandType cmdType)201         {202             if (string.IsNullOrEmpty(database))203             {204                 throw new Exception("未设置参数：database");205             }206             if (string.IsNullOrEmpty(sql))207             {208                 throw new Exception("未设置参数：sql");209             }210 211             //返回(增删改)的更新行数212             int count = 0;213 214             try215             {216                 using (SqlConnection conn = GetConnection(database))217                 {218                     conn.Open();219 220                     using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))221                     {222                         if (cmdType == CommandType.StoredProcedure)223                             cmd.Parameters.AddWithValue("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;224 225                         count = cmd.ExecuteNonQuery();226 227                         if (count <= 0)228                             if (cmdType == CommandType.StoredProcedure)229                                 count = (int)cmd.Parameters["@RETURN_VALUE"].Value;230                     }231                 }232             }233             catch (SqlException ex)234             {235                 System.Text.StringBuilder log = new System.Text.StringBuilder();236                 log.Append("处理出错：");237                 log.Append(ex);238                 throw new Exception(log.ToString());239             }240             return count;241         }242 243         /// <summary>244         /// 查询数据，返回DataTable245         /// </summary>246         /// <param name="database">数据库</param>247         /// <param name="sql">SQL语句或存储过程名</param>248         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>249         /// <param name="values">参数</param>250         /// <returns>DataTable</returns>251         public static DataTable QueryDataTable(string database, string sql, CommandType cmdType, IDictionary<string, object> values)252         {253             SqlParameter[] parms = DicToParams(values);254             return QueryDataTable(database, sql, parms, cmdType);255         }256 257         /// <summary>258         /// 执行存储过程查询数据，返回DataSet259         /// </summary>260         /// <param name="database">数据库</param>261         /// <param name="sql">SQL语句或存储过程名</param>262         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>263         /// <param name="values">参数264         /// <returns>DataSet</returns>265         public static DataSet QueryDataSet(string database, string sql, CommandType cmdType, IDictionary<string, object> values)266         {267             SqlParameter[] parms = DicToParams(values);268             return QueryDataSet(database, sql, parms, cmdType);269         }270 271         /// <summary>272         /// 执行命令获取唯一值(第一行第一列)273         /// </summary>274         /// <param name="database">数据库</param>275         /// <param name="sql">SQL语句或存储过程名</param>276         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>277         /// <param name="values">参数</param>278         /// <returns>唯一值</returns>279         public static object QueryScalar(string database, string sql, CommandType cmdType, IDictionary<string, object> values)280         {281             SqlParameter[] parms = DicToParams(values);282             return QueryScalar(database, sql, parms, cmdType);283         }284 285         /// <summary>286         /// 执行命令更新数据287         /// </summary>288         /// <param name="database">数据库</param>289         /// <param name="sql">SQL语句或存储过程名</param>290         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>291         /// <param name="values">参数</param>292         /// <returns>更新的行数</returns>293         public static int Execute(string database, string sql, CommandType cmdType, IDictionary<string, object> values)294         {295             SqlParameter[] parms = DicToParams(values);296             return Execute(database, sql, parms, cmdType);297         }298 299         /// <summary>300         /// 创建参数301         /// </summary>302         /// <param name="name">参数名</param>303         /// <param name="type">参数类型</param>304         /// <param name="size">参数大小</param>305         /// <param name="direction">参数方向(输入/输出)</param>306         /// <param name="value">参数值</param>307         /// <returns>新参数对象</returns>308         public static SqlParameter[] DicToParams(IDictionary<string, object> values)309         {310             if (values == null) return null;311 312             SqlParameter[] parms = new SqlParameter[values.Count];313             int index = 0;314             foreach (KeyValuePair<string, object> kv in values)315             {316                 SqlParameter parm = null;317                 if (kv.Value =http://www.mamicode.com/= null)318                 {319                     parm = new SqlParameter(kv.Key, DBNull.Value);320                 }321                 else322                 {323                     Type t = kv.Value.GetType();324                     parm = new SqlParameter(kv.Key, NetToSql(kv.Value.GetType()));325                     parm.Value =http://www.mamicode.com/ kv.Value;326                 }327 328                 parms[index++] = parm;329             }330             return parms;331         }332 333 334         /// <summary>335         /// .net类型转换为Sql类型336         /// </summary>337         /// <param name="t">.net类型</param>338         /// <returns>Sql类型</returns>339         public static SqlDbType NetToSql(Type t)340         {341             SqlDbType dbType = SqlDbType.Variant;342             switch (t.Name)343             {344                 case "Int16":345                     dbType = SqlDbType.SmallInt;346                     break;347                 case "Int32":348                     dbType = SqlDbType.Int;349                     break;350                 case "Int64":351                     dbType = SqlDbType.BigInt;352                     break;353                 case "Single":354                     dbType = SqlDbType.Real;355                     break;356                 case "Decimal":357                     dbType = SqlDbType.Decimal;358                     break;359 360                 case "Byte[]":361                     dbType = SqlDbType.VarBinary;362                     break;363                 case "Boolean":364                     dbType = SqlDbType.Bit;365                     break;366                 case "String":367                     dbType = SqlDbType.NVarChar;368                     break;369                 case "Char[]":370                     dbType = SqlDbType.Char;371                     break;372                 case "DateTime":373                     dbType = SqlDbType.DateTime;374                     break;375                 case "DateTime2":376                     dbType = SqlDbType.DateTime2;377                     break;378                 case "DateTimeOffset":379                     dbType = SqlDbType.DateTimeOffset;380                     break;381                 case "TimeSpan":382                     dbType = SqlDbType.Time;383                     break;384                 case "Guid":385                     dbType = SqlDbType.UniqueIdentifier;386                     break;387                 case "Xml":388                     dbType = SqlDbType.Xml;389                     break;390                 case "Object":391                     dbType = SqlDbType.Variant;392                     break;393             }394             return dbType;395         }396 397     }

1 IDictionary<string, object> values = new Dictionary<string, object>();2 values.Add("@UserName", UserName);            3 values.Add("@PassWord", passWord);4 object Scalar = SQLHelper.QueryScalar(SQLHelper.BestNet, SQLHelper.UserInfoCURD, CommandType.StoredProcedure, values);