基于公司级平台封装的SqlserverHelper
时间：2021-07-01 10:21:17
帮助过：29人阅读
public class DBHelper
2 {
3 /// <summary>
4 /// 数据库帮助
5 /// </summary>
6 protected static DbHelper db =
null;
7
8
9 /// <summary>
10 /// 增删改
11 /// </summary>
12 /// <param name="sql">sql语句</param>
13 /// <param name="param">参数</param>
14 /// <returns></returns>
15 public int ExecuteCommand(
string sql, DbParameter[] param)
16 {
17 if (
string.IsNullOrWhiteSpace(sql))
18 {
19 throw new ArgumentNullException(
"参数异常");
20 }
21 try
22 {
23 using (db =
Config.CreateDbHelper())
24 {
25 //循环添加参数;
26 if (param !=
null)
27 {
28 foreach (
var pa
in param)
29 {
30 db.AddParameter(pa.ParameterName, pa.Value);
31 }
32 }
33 return db.ExecuteNonQuerySQL(sql);
34 }
35 }
36 catch (Exception e)
37 {
38 throw e;
39 }
40 }
41
42 /// <summary>
43 /// 获取集合
44 /// </summary>
45 /// <param name="sql">sql语句</param>
46 /// <param name="param">参数</param>
47 /// <returns></returns>
48 public IHashObjectList ExecuteScalar(
string sql, DbParameter[] param)
49 {
50 if (
string.IsNullOrWhiteSpace(sql))
51 {
52 throw new ArgumentNullException(
"参数异常");
53 }
54 try
55 {
56 using (db =
Config.CreateDbHelper())
57 {
58 //循环添加参数;
59 if (param !=
null)
60 {
61 foreach (
var pa
in param)
62 {
63 db.AddParameter(pa.ParameterName, pa.Value);
64 }
65 }
66 return db.Select(sql);
67 }
68 }
69 catch (Exception e)
70 {
71 throw e;
72 }
73 }
74
75 /// <summary>
76 /// 获取第一行数据
77 /// </summary>
78 /// <param name="sql">sql语句</param>
79 /// <param name="param">参数</param>
80 /// <returns></returns>
81 public IHashObject SelectFirstRow(
string sql, DbParameter[] param)
82 {
83 if (
string.IsNullOrWhiteSpace(sql))
84 {
85 throw new ArgumentNullException(
"参数异常");
86 }
87 try
88 {
89 using (db =
Config.CreateDbHelper())
90 {
91 //循环添加参数;
92 if (param !=
null)
93 {
94 foreach (
var pa
in param)
95 {
96 db.AddParameter(pa.ParameterName, pa.Value);
97 }
98 }
99 return db.SelectFirstRow(sql) ??
new HashObject();
100 }
101 }
102 catch (Exception e)
103 {
104 throw e;
105 }
106 }
107
108 /// <summary>
109 /// 获取第一行数据
110 /// </summary>
111 /// <param name="sql">sql语句</param>
112 /// <param name="param">参数</param>
113 /// <returns></returns>
114 public IHashObject SelectSingleRow(
string sql, DbParameter[] param)
115 {
116 if (
string.IsNullOrWhiteSpace(sql))
117 {
118 throw new ArgumentNullException(
"参数异常");
119 }
120 try
121 {
122 using (db =
Config.CreateDbHelper())
123 {
124 //循环添加参数;
125 if (param !=
null)
126 {
127 foreach (
var pa
in param)
128 {
129 db.AddParameter(pa.ParameterName, pa.Value);
130 }
131 }
132 return db.SelectSingleRow(sql) ??
new HashObject();
133 }
134 }
135 catch (Exception e)
136 {
137 throw e;
138 }
139 }
140
141 /// <summary>
142 /// 分页获取
143 /// </summary>
144 /// <param name="pageIndex"></param>
145 /// <param name="pageCount"></param>
146 /// <param name="totalCount"></param>
147 /// <param name="tableName"></param>
148 /// <param name="order"></param>
149 /// <param name="whereData"></param>
150 /// <returns></returns>
151 public IHashObjectList GetByPage(
int pageIndex,
int pageCount,
out int totalCount,
string tableName,
string order,
bool isAsc,
string[] fieldNames, IDictionary<
string,
object> whereData=
null)
152 {
153 totalCount =
0;
154 if (
string.IsNullOrWhiteSpace(tableName) ||
string.IsNullOrWhiteSpace(order)|| fieldNames.Length==
0)
155 {
156 throw new ArgumentNullException(
"参数异常");
157 }
158 try
159 {
160 using (db =
Config.CreateDbHelper())
161 {
162 string strWhere =
BuildSelectWhereSql(whereData);
163 this.BuildParameters(whereData);
164 if (!
string.IsNullOrWhiteSpace(strWhere))
165 {
166 totalCount = (
int)db.ExecuteScalerSQL(
string.Format(
"select count(0) from {0} where ", tableName) +
strWhere);
167 }
168 else
169 {
170 totalCount = (
int)db.ExecuteScalerSQL(
string.Format(
"select count(0) from {0}", tableName));
171 }
172 StringBuilder strSql =
new StringBuilder();
173 strSql.Append(
"SELECT * FROM ( ");
174 strSql.Append(
" SELECT ROW_NUMBER() OVER (");
175 if (isAsc)
176 {
177 strSql.Append(
"order by T." +
order);
178 }
179 else
180 {
181 strSql.Append(
"order by T." + order+
" desc");
182 }
183 StringBuilder strColumns =
new StringBuilder();
184 if (fieldNames.Length >
0)
185 {
186 foreach (
var item
in fieldNames)
187 {
188 if (strColumns.Length !=
0)
189 {
190 strColumns.Append(
" , ");
191 }
192 strColumns.Append(
"T." +
item);
193 }
194 }
195 strSql.Append(
")AS Row, " + strColumns +
" from " + tableName +
" T ");
196 if (!
string.IsNullOrWhiteSpace(strWhere))
197 {
198 strSql.Append(
" WHERE " +
strWhere);
199 }
200 strSql.Append(
" ) TT");
201 strSql.AppendFormat(
" WHERE TT.Row between (({0}*{1})+1) and ((({0}+1)*{1}))", pageIndex, pageCount);
202 this.BuildParameters(whereData);
203 return db.Select(strSql.ToString());
204 }
205 }
206 catch (Exception e)
207 {
208 throw e;
209 }
210 }
211
212 /// <summary>
213 /// 事务提交数据;
214 /// </summary>
215 /// <param name="sql">sql语句</param>
216 /// <param name="param">参数</param>
217 /// <returns></returns>
218 public bool ExecuteSQLByTransaction(
string sql, DbParameter[] param)
219 {
220 if (
string.IsNullOrWhiteSpace(sql))
221 {
222 throw new ArgumentNullException(
"参数异常");
223 }
224 try
225 {
226 using (db =
Config.CreateDbHelper())
227 {
228 int result =
0;
229 if (!
db.HasBegunTransaction)
230 {
231 //循环添加参数;
232 if (param !=
null)
233 {
234 foreach (
var pa
in param)
235 {
236 db.AddParameter(pa.ParameterName, pa.Value);
237 }
238 }
239 try
240 {
241 db.BeginTransaction();
242 result =
db.ExecuteNonQuerySQL(sql);
243 db.CommitTransaction();
244 }
245 catch (Exception ex)
246 {
247 db.RollbackTransaction();
248 throw ex;
249 }
250 }
251 return result >
0 ?
true :
false;
252 }
253 }
254 catch (Exception e)
255 {
256 throw e;
257 }
258 }
259
260 /// <summary>
261 /// 执行sql语句得到返回结果
262 /// </summary>
263 /// <param name="sql">sql语句</param>
264 /// <returns></returns>
265 public object ExecuteScalerSQL(
string sql)
266 {
267 if (
string.IsNullOrWhiteSpace(sql))
268 {
269 throw new ArgumentNullException(
"参数异常");
270 }
271 try
272 {
273 using (db =
Config.CreateDbHelper())
274 {
275 return db.ExecuteScalerSQL(sql);
276 }
277 }
278 catch (Exception e)
279 {
280 throw e;
281 }
282 }
283
284 /// <summary>
285 /// 获取数据集数组
286 /// </summary>
287 /// <param name="sql">sql语句</param>
288 /// <returns></returns>
289 public DataTable[] ExecuteSQLEx(
string sql,
string[] tableNames)
290 {
291 if (
string.IsNullOrWhiteSpace(sql))
292 {
293 throw new ArgumentNullException(
"参数异常");
294 }
295 try
296 {
297 using (db =
Config.CreateDbHelper())
298 {
299 return db.ExecuteSQLEx(sql, tableNames);
300 }
301 }
302 catch (Exception e)
303 {
304
305 throw e;
306 }
307 }
308
309 /// <summary>
310 /// 获取数据集数组
311 /// </summary>
312 /// <param name="sql">sql语句</param>
313 /// <param name="tableNames">表明</param>
314 /// <returns></returns>
315 public DataTable[] ExecuteSQLEx(
string sql)
316 {
317 if (
string.IsNullOrWhiteSpace(sql))
318 {
319 throw new ArgumentNullException(
"参数异常");
320 }
321 try
322 {
323 using (db =
Config.CreateDbHelper())
324 {
325 return db.ExecuteSQLEx(sql);
326 }
327 }
328 catch (Exception e)
329 {
330 throw e;
331 }
332 }
333
334 /// <summary>
335 /// 新增
336 /// </summary>
337 /// <param name="tableName"></param>
338 /// <param name="fieldNames"></param>
339 /// <param name="data"></param>
340 /// <returns></returns>
341 public int Insert(
string tableName,
string[] fieldNames, IDictionary<
string,
object>
data)
342 {
343 if (
string.IsNullOrWhiteSpace(tableName) || data==
null)
344 {
345 throw new ArgumentNullException(
"参数异常");
346 }
347 try
348 {
349 using (db =
Config.CreateDbHelper())
350 {
351 if (fieldNames.Length ==
0)
352 {
353 return db.Insert(tableName, data);
354 }
355 else
356 {
357 return db.Insert(tableName, fieldNames,data);
358 }
359 }
360 }
361 catch (Exception e)
362 {
363 throw e;
364 }
365 }
366
367 /// <summary>
368 /// 修改
369 /// </summary>
370 /// <param name="tableName"></param>
371 /// <param name="fieldNames"></param>
372 /// <param name="data"></param>
373 /// <returns></returns>
374 public int Update(
string tableName,
string[] fieldNames, IDictionary<
string,
object>
data)
375 {
376 if (
string.IsNullOrWhiteSpace(tableName) || data ==
null || fieldNames.Length==
0)
377 {
378 throw new ArgumentNullException(
"参数异常");
379 }
380 try
381 {
382 using (db =
Config.CreateDbHelper())
383 {
384 return db.Update(tableName, fieldNames, data);
385 }
386 }
387 catch (Exception e)
388 {
389 throw e;
390 }
391 }
392
393 /// <summary>
394 /// 删除
395 /// </summary>
396 /// <param name="tableName"></param>
397 /// <param name="keyField"></param>
398 /// <param name="keyValue"></param>
399 /// <returns></returns>
400 public int Delete(
string tableName,
string keyField,
object keyValue)
401 {
402 if (
string.IsNullOrWhiteSpace(tableName) ||
string.IsNullOrWhiteSpace(keyField) || keyValue ==
null)
403 {
404 throw new ArgumentNullException(
"参数异常");
405 }
406 try
407 {
408 using (db =
Config.CreateDbHelper())
409 {
410 return db.Delete(tableName, keyField, keyValue);
411 }
412 }
413 catch (Exception e)
414 {
415 throw e;
416 }
417 }
418
419 /// <summary>
420 /// 获取集合
421 /// </summary>
422 /// <param name="tableName"></param>
423 /// <param name="fieldNames"></param>
424 /// <param name="data"></param>
425 /// <returns></returns>
426 public IHashObjectList GetList(
string tableName,
string[] fieldNames, IDictionary<
string,
object>
data)
427 {
428 if (
string.IsNullOrWhiteSpace(tableName) || fieldNames==
null)
429 {
430 throw new ArgumentNullException(
"参数异常");
431 }
432 try
433 {
434 string sql =
BuildSelectWhereSql(tableName, fieldNames, data);
435 this.BuildParameters(data);
436 return db.Select(sql);
437 }
438 catch (Exception e)
439 {
440 throw e;
441 }
442 }
443
444 #region 辅助方法
445
446 /// <summary>
