正文

C#实现较为实用的SQLhelper

︶ㄣ米米素材网 メ
米米素材网V管理员 / 阅读
0708

这篇文章主要为大家详细介绍了C#实现较为实用SQLhelper的相关资料,具有一定的参考价值，感兴趣的小伙伴们可以参考一下

第一次写博客，想不到写什么好b（￣▽￣）d　，考虑的半天决定从sqlhelper开始，sqlhelper对程序员来说就像helloworld一样，很简单却又很重要，helloworld代表着程序员萌新第一次写代码，而sqlhelper则是初次接触数据库（不知道这种说法对不对）。

好了不废话了，下面直接上代码（无话可说了）：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
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;
    }
 
  }

可以直接这样调用： 

?
1
2
3
4
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);

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持米米素材网。