声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did119134
基于公司级平台封装的SqlserverHelper
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>
查看更多关于基于公司级平台封装的SqlserverHelper的详细内容...
阅读：33次