SqlHelper个人完善版本介绍
分类:
IT文章
•
2023-11-02 09:07:10
虽然现在有层出不穷的各种ORM框架来与数据库交互，但是其底层也无外乎调用ADO.NET来处理，所以ADO.NET是根本，而且对于传统的老三层项目，大家想都不用想就能说出数据库操作有各种完善的SqlHelper。
别人的自己用了总是有不爽的地方，所以花时间把现有的SqlHelper全部改写了一下，充分利用泛型的以及委托的特性。
先来个代码截图
主要代码如下：
1.批量SQL语句处理类创建
![]()
1 /// <summary>
2 /// 批量SQL定义
3 /// </summary>
4 public sealed class BatchCmdEntity
5 {
6 //参数列表
7 private readonly List<SqlParameter> _mParas;
8
9 public BatchCmdEntity()
10 {
11 _mParas = new List<SqlParameter>();
12 }
13
14 /// <summary>
15 /// SQL语句
16 /// </summary>
17 public string Sql { get; set; }
18
19 /// <summary>
20 /// 参数数组属性
21 /// </summary>
22 public SqlParameter[] Parameter => _mParas.ToArray();
23
24 /// <summary>
25 /// 添加参数
26 /// </summary>
27 /// <param name="name">参数名称</param>
28 /// <param name="value">参数值</param>
29 public void AddParameter(string name, object value)
30 {
31 _mParas.Add(new SqlParameter(name, value));
32 }
33
34 /// <summary>
35 /// 添加参数数组
36 /// </summary>
37 /// <param name="paras">参数数组</param>
38 public void AddParameter(SqlParameter[] paras)
39 {
40 _mParas.AddRange(paras);
41 }
42 }
View Code
有些场景比如ExecuteNonQuery需要返回多条语句叠加的数量结果是可以采用此类来封装
2.处理数据库空值方法
![]()
1 #region 【处理数据库空值方法】
2
3 /// <summary>
4 /// 处理数据库空值方法
5 /// </summary>
6 /// <typeparam name="T">泛型类型</typeparam>
7 /// <param name="value">读取出来的值</param>
8 /// <param name="defaultValue">泛型类型的默认值</param>
9 /// <returns></returns>
10 private static T ConvertData<T>(object value, T defaultValue)
11 {
12 if (Convert.IsDBNull(value) || value == null)
13 {
14 return defaultValue;
15 }
16 if (value is T)
17 {
18 return (T)value;
19 }
20 return (T)Convert.ChangeType(value, typeof(T));
21 }
22
23 #endregion
View Code
有些场景比如ExecuteScalar需要返回首行首列的值，该值可能为空，如果不为空则为object类型，需要返回我们需要的类型如int,string,double等，可以调用此方法
3.生成分页SQL语句，采用row_number函数
![]()
1 #region【BuildPagingSql】
2
3 /// <summary>
4 /// 生成分页语句
5 /// </summary>
6 /// <param name="orderByField">分页字段</param>
7 /// <param name="querySql">查询SQL语句</param>
8 /// <param name="pageSize">每页数量</param>
9 /// <param name="pageIndex">页索引</param>
10 /// <returns></returns>
11 public static string BuildPagingSql(string orderByField, string querySql, int pageSize, int pageIndex)
12 {
13 //开始记录数
14 int beginRowNum = (pageIndex - 1) * pageSize + 1;
15 //结束记录数
16 int endRowNum = pageIndex * pageSize;
17
18 StringBuilder sb = new StringBuilder();
19 sb.Append(@"SELECT * ");
20 sb.AppendFormat($" FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY {orderByField} ) ROWNUMBER, *");
21 sb.AppendFormat($" FROM ({querySql}) a) b ");
22 sb.AppendFormat($" WHERE ROWNUMBER BETWEEN {beginRowNum} AND {endRowNum}");
23
24 return sb.ToString();
25 }
View Code
![]()
1 #region
2
3 using System;
4 using System.Collections.Generic;
5 using System.Configuration;
6 using System.Data;
7 using System.Data.SqlClient;
8 using System.Linq;
9 using System.Text;
10
11 #endregion
12
13 namespace OAO2O.BusinessService.DAL
14 {
15 /// <summary>
16 /// DbHelper 的摘要说明
17 /// </summary>
18 public sealed class SqlHelper
19 {
20 //连接字符串从配置文件中读取
21 public static readonly string ConnString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
22
23 #region【ExecuteNonQuery】
24
25 /// <summary>
26 /// 生成使用默认连接的ExecuteNonQuery
27 /// </summary>
28 /// <param name="commandText">语句</param>
29 /// <param name="cmdType">类型</param>
30 /// <param name="paras">参数数组</param>
31 /// <returns></returns>
32 public static int ExecuteNonQuery(string commandText, CommandType cmdType, params SqlParameter[] paras)
33 {
34 return ExecuteNonQuery(ConnString, commandText, cmdType, paras);
35 }
36
37 /// <summary>
38 /// 生成使用自定义连接的ExecuteNonQuery
39 /// </summary>
40 /// <param name="connectionString">自定义连接字符串</param>
41 /// <param name="commandText">语句</param>
42 /// <param name="cmdType">类型</param>
43 /// <param name="parms">参数数组</param>
44 /// <returns></returns>
45 public static int ExecuteNonQuery(string connectionString, string commandText, CommandType cmdType,
46 params SqlParameter[] parms)
47 {
48 using (SqlConnection connection = new SqlConnection(connectionString))
49 {
50 using (SqlCommand command = new SqlCommand())
51 {
52 PrepareCommand(command, connection, null, cmdType, commandText, parms);
53 int retval = command.ExecuteNonQuery();
54 command.Parameters.Clear();
55 return retval;
56 }
57 }
58 }
59
60 /// <summary>
61 /// 生成使用默认连接的ExecuteNonQuery
62 /// </summary>
63 /// <param name="cmdList">批量SQL语句</param>
64 /// <returns></returns>
65 public static int ExecuteNonQuery(List<BatchCmdEntity> cmdList)
66 {
67 return ExecuteNonQuery(ConnString, cmdList);
68 }
69
70 /// <summary>
71 /// 生成使用自定义连接的ExecuteNonQuery
72 /// </summary>
73 /// <param name="connectionString">自定义连接字符串</param>
74 /// <param name="cmdList">批量SQL语句</param>
75 /// <returns></returns>
76 public static int ExecuteNonQuery(string connectionString, List<BatchCmdEntity> cmdList)
77 {
78 return ExecuteNonQuery(connectionString, command =>
79 {
80 int retval = 0;
81 foreach (BatchCmdEntity cmd in cmdList)
82 {
83 //因为是批量语句，所以进来之前先清空
84 command.CommandText = "";
85 command.Parameters.Clear();
86 //重新赋值
87 command.CommandText = cmd.Sql;
88 command.Parameters.AddRange(cmd.Parameter);
89 retval += command.ExecuteNonQuery();
90 }
91
92 return retval;
93 });
94 }
95
96 /// <summary>
97 /// 生成使用默认连接的ExecuteNonQuery
98 /// </summary>
99 /// <param name="fun">泛型委托方法</param>
100 /// <returns></returns>
101 public static int ExecuteNonQuery(Func<SqlCommand, int> fun)
102 {
103 return ExecuteNonQuery(ConnString, fun);
104 }
105
106 /// <summary>
107 /// 生成使用自定义连接的ExecuteNonQuery
108 /// </summary>
109 /// <param name="connectionString">自定义连接字符串</param>
110 /// <param name="fun">泛型委托方法</param>
111 /// <returns></returns>
112 public static int ExecuteNonQuery(string connectionString, Func<SqlCommand, int> fun)
113 {
114 using (SqlConnection connection = new SqlConnection(connectionString))
115 {
116 if (connection.State != ConnectionState.Open) connection.Open();
117
118 SqlTransaction trans = connection.BeginTransaction();
119 using (SqlCommand command = new SqlCommand())
120 {
121 PrepareCommand(command, connection, trans);
122 int retval = 0;
123 try
124 {
125 retval = fun(command);
126 trans.Commit();
127 }
128 catch
129 {
130 trans.Rollback();
131
132 }
133 return retval;
134 }
135 }
136 }
137
138 #endregion
139
140 #region 【ExecuteScalar】
141
142 /// <summary>
143 /// 生成使用默认连接的ExecuteScalar
144 /// </summary>
145 /// <typeparam name="T">泛型类型</typeparam>
146 /// <param name="commandText">语句</param>
147 /// <param name="cmdType">类型</param>
148 /// <param name="parms">参数数组</param>
149 /// <returns></returns>
150 public static T ExecuteScalar<T>(string commandText, CommandType cmdType, params SqlParameter[] parms)
151 {
152 return ExecuteScalar<T>(ConnString, commandText, cmdType, parms);
153 }
154
155 /// <summary>
156 /// 生成使用自定义连接的ExecuteScalar
157 /// </summary>
158 /// <typeparam name="T">泛型类型</typeparam>
159 /// <param name="connectionString">自定义连接字符串</param>
160 /// <param name="commandText">语句</param>
161 /// <param name="cmdType">类型</param>
162 /// <param name="parms">参数数组</param>
163 /// <returns></returns>
164 public static T ExecuteScalar<T>(string connectionString, string commandText, CommandType cmdType,
165 params SqlParameter[] parms)
166 {
167 using (SqlConnection connection = new SqlConnection(connectionString))
168 {
169 using (SqlCommand command = new SqlCommand())
170 {
171 PrepareCommand(command, connection, null, cmdType, commandText, parms);
172 object retval = command.ExecuteScalar();
173 command.Parameters.Clear();
174 return ConvertData(retval, default(T));
175 }
176 }
177 }
178
179 #endregion
180
181 #region 【ExecuteDataRow】
182
183 public static DataRow ExecuteDataRow(string commandText, CommandType cmdType, params SqlParameter[] parms)
184 {
185 return ExecuteDataRow(ConnString, commandText, cmdType, parms);
186 }
187
188 /// <summary>
189 /// 生成数据行DataRow
190 /// </summary>
191 /// <param name="connectionString"></param>
192 /// <param name="commandText"></param>
193 /// <param name="cmdType"></param>
194 /// <param name="parms"></param>
195 /// <returns></returns>
196 public static DataRow ExecuteDataRow(string connectionString, string commandText, CommandType cmdType,
197 params SqlParameter[] parms)
198 {
199 DataTable data = ExecuteDataTable(connectionString, commandText, cmdType, parms);
200 return data?.Rows[0];
201 }
202
203 #endregion
204
205 #region 【ExecuteDataTable】
206
207 public static DataTable ExecuteDataTable(string commandText, CommandType cmdType, params SqlParameter[] paras)
208 {
209 return ExecuteDataTable(ConnString, commandText, cmdType, paras);
210 }
211
212 /// <summary>
213 /// 生成数据表DataTable
214 /// </summary>
215 /// <param name="connectionString">连接字符串</param>
216 /// <param name="commandText">语句</param>
217 /// <param name="cmdType">类型</param>
218 /// <param name="parms">参数数组</param>
219 /// <returns></returns>
220 public static DataTable ExecuteDataTable(string connectionString, string commandText, CommandType cmdType,
221 params SqlParameter[] parms)
222 {
223 using (SqlConnection connection = new SqlConnection(connectionString))
224 {
225 using (SqlCommand command = new SqlCommand())
226 {
227 PrepareCommand(command, connection, null, cmdType, commandText, parms);
228 SqlDataAdapter adapter = new SqlDataAdapter(command);
229
230 DataTable data = new DataTable();
231 adapter.Fill(data);
232 command.Parameters.Clear();
233
234 return data;
235 }
236 }
237 }
238
239 #endregion
240
241 #region【ExecuteDataSet】
242
243 public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, params SqlParameter[] parms)
244 {
245 return ExecuteDataSet(ConnString, cmdText, cmdType, parms);
246 }
247
248 /// <summary>
249 /// 生成数据集合DataSet
250 /// </summary>
251 /// <param name="connectionString">连接字符串</param>
252 /// <param name="commandText">语句</param>
253 /// <param name="cmdType">类型</param>
254 /// <param name="parms">参数数组</param>
255 /// <returns></returns>
256 public static DataSet ExecuteDataSet(string connectionString, string commandText, CommandType cmdType,
257 params SqlParameter[] parms)
258 {
259 using (SqlConnection connection = new SqlConnection(connectionString))
260 {
261 using (SqlCommand command = new SqlCommand())
262 {
263 PrepareCommand(command, connection, null, cmdType, commandText, parms);
264 SqlDataAdapter adapter = new SqlDataAdapter(command);
265
266 DataSet data = new DataSet();
267 adapter.Fill(data);
268 command.Parameters.Clear();
269
270 return data;
271 }
272 }
273 }
274
275 #endregion
276
277 #region 【PrepareCommand】
278
279 private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction)
280 {
281 PrepareCommand(command, connection, transaction, CommandType.Text, string.Empty, null);
282 }
283
284 /// <summary>
285 /// 生成cmd
286 /// </summary>
287 /// <param name="command">cmd对象</param>
288 /// <param name="connection">连接</param>
289 /// <param name="transaction">事务</param>
290 /// <param name="commandType">类型</param>
291 /// <param name="commandText">语句</param>
292 /// <param name="parms">参数数组</param>
293 private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction,
294 CommandType commandType, string commandText, params SqlParameter[] parms)
295 {
296 if (connection.State != ConnectionState.Open) connection.Open();
297
298 command.Connection = connection;
299 command.CommandTimeout = 600;
300 command.CommandText = commandText;
301 command.CommandType = commandType;
302
303 if (transaction != null) command.Transaction = transaction;
304
305 if (parms == null || parms.Length == 0) return;
306 //循环设置参数值为null的参数，设置其值为数据库制定类型DBNull.Value
307 foreach (SqlParameter parameter in parms.Where(parameter => (parameter.Direction == ParameterDirection.InputOutput ||
308 parameter.Direction == ParameterDirection.Input) &&
309 (parameter.Value == null)))
310 {
311 parameter.Value = DBNull.Value;
312 }
313 //添加到参数数组中
314 command.Parameters.AddRange(parms);
315 }
316
317 #endregion
318
319 #region 【BuildPara】
320 /// <summary>
321 /// 生成普通参数
322 /// </summary>
323 /// <param name="name">参数名</param>
324 /// <param name="value">参数值</param>
325 /// <returns></returns>
326 public static SqlParameter BuildPara(string name, object value)
327 {
328 return new SqlParameter(name, value);
329 }
330
331 /// <summary>
332 /// 生成普通参数
333 /// </summary>
334 /// <param name="name">参数名</param>
335 /// <param name="dbType">参数类型</param>
336 /// <param name="value">参数值</param>
337 /// <returns></returns>
338 public static SqlParameter BuildPara(string name, SqlDbType dbType, object value)
339 {
340 return new SqlParameter(name, dbType)
341 {
342 Value = value
343 };
344 }
345
346 /// <summary>
347 /// 生成输出参数
348 /// </summary>
349 /// <param name="name">参数名</param>
350 /// <param name="direction">输出方向</param>
351 /// <param name="value">参数值</param>
352 /// <returns></returns>
353 public static SqlParameter BuildPara(string name, ParameterDirection direction, object value)
354 {
355 return new SqlParameter
356 {
357 ParameterName = name,
358 Direction = direction,
359 Value = value
360 };
361 }
362
363 /// <summary>
364 /// 生成输出参数
365 /// </summary>
366 /// <param name="name">参数名</param>
367 /// <param name="dbType">参数类型</param>
368 /// <param name="direction">输出方向</param>
369 /// <returns></returns>
370 public static SqlParameter BuildPara(string name, SqlDbType dbType, ParameterDirection direction)
371 {
372 return new SqlParameter(name, dbType)
373 {
374 Direction = direction
375 };
376 }
377
378 /// <summary>
379 /// 生成带长度的输出参数
380 /// </summary>
381 /// <param name="name">参数名</param>
382 /// <param name="dbType">参数类型</param>
383 /// <param name="size">长度</param>
384 /// <param name="direction">输出方向</param>
385 /// <returns></returns>
386 public static SqlParameter BuildPara(string name, SqlDbType dbType, int size, ParameterDirection direction)
387 {
388 return new SqlParameter(name, dbType, size)
389 {
390 Direction = direction
391 };
392 }
393
394 /// <summary>
395 /// 生成输出参数
396 /// </summary>
397 /// <param name="name">参数名</param>
398 /// <param name="dbType">参数类型</param>
399 /// <param name="direction">输出方向</param>
400 /// <param name="value">参数值</param>
401 /// <returns></returns>
402 public static SqlParameter BuildPara(string name, SqlDbType dbType, ParameterDirection direction, object value)
403 {
404 return new SqlParameter(name, dbType)
405 {
406 Direction = direction,
407 Value = value
408 };
409 }
410
411 #endregion
412
413 #region【BuildPagingSql】
414
415 /// <summary>
416 /// 生成分页语句
417 /// </summary>
418 /// <param name="orderByField">分页字段</param>
419 /// <param name="querySql">查询SQL语句</param>
420 /// <param name="pageSize">每页数量</param>
421 /// <param name="pageIndex">页索引</param>
422 /// <returns></returns>
423 public static string BuildPagingSql(string orderByField, string querySql, int pageSize, int pageIndex)
424 {
425 //开始记录数
426 int beginRowNum = (pageIndex - 1) * pageSize + 1;
427 //结束记录数
428 int endRowNum = pageIndex * pageSize;
429
430 StringBuilder sb = new StringBuilder();
431 sb.Append(@"SELECT * ");
432 sb.AppendFormat($" FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY {orderByField} ) ROWNUMBER, *");
433 sb.AppendFormat($" FROM ({querySql}) a) b ");
434 sb.AppendFormat($" WHERE ROWNUMBER BETWEEN {beginRowNum} AND {endRowNum}");
435
436 return sb.ToString();
437 }
438
439 #endregion
440
441 #region 【处理数据库空值方法】
442
443 /// <summary>
444 /// 处理数据库空值方法
445 /// </summary>
446 /// <typeparam name="T">泛型类型</typeparam>
447 /// <param name="value">读取出来的值</param>
448 /// <param name="defaultValue">泛型类型的默认值</param>
449 /// <returns></returns>
450 private static T ConvertData<T>(object value, T defaultValue)
451 {
452 if (Convert.IsDBNull(value) || value == null)
453 {
454 return defaultValue;
455 }
456 if (value is T)
457 {
458 return (T)value;
459 }
460 return (T)Convert.ChangeType(value, typeof(T));
461 }
462
463 #endregion
464 }
465
466
467 /// <summary>
468 /// 批量SQL定义
469 /// </summary>
470 public sealed class BatchCmdEntity
471 {
472 //参数列表
473 private readonly List<SqlParameter> _mParas;
474
475 public BatchCmdEntity()
476 {
477 _mParas = new List<SqlParameter>();
478 }
479
480 /// <summary>
481 /// SQL语句
482 /// </summary>
483 public string Sql { get; set; }
484
485 /// <summary>
486 /// 参数数组属性
487 /// </summary>
488 public SqlParameter[] Parameter => _mParas.ToArray();
489
490 /// <summary>
491 /// 添加参数
492 /// </summary>
493 /// <param name="name">参数名称</param>
494 /// <param name="value">参数值</param>
495 public void AddParameter(string name, object value)
496 {
497 _mParas.Add(new SqlParameter(name, value));
498 }
499
500 /// <summary>
501 /// 添加参数数组
502 /// </summary>
503 /// <param name="paras">参数数组</param>
504 public void AddParameter(SqlParameter[] paras)
505 {
506 _mParas.AddRange(paras);
507 }
508 }
509 }
