SQLServer数据库访问帮助类
这里有一个Mysql帮助类的使用例子可以参考（大同小异）
写了一个Mysql的例子，SQLServer的差不多，一样的
C#简单三层结构设计UI、BLL、DAL
1.web.config配置数据库连接字符串
1 <?xml version="1.0"?>
2 <configuration>
3 <appSettings>
4 <!-- 连接字符串是否加密 -->
5 <add key="ConStringEncrypt" value="false"/>
6 <!-- 数据库连接字符串,（如果采用加密方式，上面一项要设置为true；加密工具，可在官方下载，
7 如果使用明文这样server=127.0.0.1;database=.....,上面则设置为false。 -->
8 <add key="ConnectionString" value="server=127.0.0.1;database=BenXHCMS;uid=sa;pwd=jyf"/>
9 </appSettings>
10 </configuration> 2.数据库字符串读取类（多一个加密算法，可以自己添加）
1 public class PubConstant
2 {
3 /// <summary>
4 /// 获取连接字符串
5 /// </summary>
6 public static string ConnectionString
7 {
8 get
9 {
10 string _connectionString = ConfigurationManager.AppSettings["ConnectionString"];
11 string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
12 if (ConStringEncrypt == "true")
13 {
14 _connectionString = DESEncrypt.Decrypt(_connectionString);
15 }
16 return _connectionString;
17 }
18 }
19
20 /// <summary>
21 /// 得到web.config里配置项的数据库连接字符串。
22 /// </summary>
23 /// <param name="configName"></param>
24 /// <returns></returns>
25 public static string GetConnectionString(string configName)
26 {
27 string connectionString = ConfigurationManager.AppSettings[configName];
28 string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
29 if (ConStringEncrypt == "true")
30 {
31 connectionString = DESEncrypt.Decrypt(connectionString);
32 }
33 return connectionString;
34 }
35
36
37 } 3.SQLServer数据库访问类
1 using System;
2 using System.Collections;
3 using System.Collections.Specialized;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Configuration;
7 using System.Data.Common;
8 using System.Collections.Generic;
9 namespace Maticsoft.DBUtility
10 {
11 /// <summary>
12 /// 数据访问抽象基础类
13 /// Copyright (C) Maticsoft
14 /// </summary>
15 public abstract class DbHelperSQL
16 {
17 //数据库连接字符串(web.config来配置)，多数据库可使用DbHelperSQLP来实现.
18 public static string connectionString = PubConstant.ConnectionString;
19 public DbHelperSQL()
20 {
21 }
22
23 #region 公用方法
24 /// <summary>
25 /// 判断是否存在某表的某个字段
26 /// </summary>
27 /// <param name="tableName">表名称</param>
28 /// <param name="columnName">列名称</param>
29 /// <returns>是否存在</returns>
30 public static bool ColumnExists(string tableName, string columnName)
31 {
32 string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
33 object res = GetSingle(sql);
34 if (res == null)
35 {
36 return false;
37 }
38 return Convert.ToInt32(res) > 0;
39 }
40 public static int GetMaxID(string FieldName, string TableName)
41 {
42 string strsql = "select max(" + FieldName + ")+1 from " + TableName;
43 object obj = GetSingle(strsql);
44 if (obj == null)
45 {
46 return 1;
47 }
48 else
49 {
50 return int.Parse(obj.ToString());
51 }
52 }
53 public static bool Exists(string strSql)
54 {
55 object obj = GetSingle(strSql);
56 int cmdresult;
57 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
58 {
59 cmdresult = 0;
60 }
61 else
62 {
63 cmdresult = int.Parse(obj.ToString()); //也可能=0
64 }
65 if (cmdresult == 0)
66 {
67 return false;
68 }
69 else
70 {
71 return true;
72 }
73 }
74 /// <summary>
75 /// 表是否存在
76 /// </summary>
77 /// <param name="TableName"></param>
78 /// <returns></returns>
79 public static bool TabExists(string TableName)
80 {
81 string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
82 //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
83 object obj = GetSingle(strsql);
84 int cmdresult;
85 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
86 {
87 cmdresult = 0;
88 }
89 else
90 {
91 cmdresult = int.Parse(obj.ToString());
92 }
93 if (cmdresult == 0)
94 {
95 return false;
96 }
97 else
98 {
99 return true;
100 }
101 }
102 public static bool Exists(string strSql, params SqlParameter[] cmdParms)
103 {
104 object obj = GetSingle(strSql, cmdParms);
105 int cmdresult;
106 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
107 {
108 cmdresult = 0;
109 }
110 else
111 {
112 cmdresult = int.Parse(obj.ToString());
113 }
114 if (cmdresult == 0)
115 {
116 return false;
117 }
118 else
119 {
120 return true;
121 }
122 }
123 #endregion
124
125 #region 执行简单SQL语句
126
127 /// <summary>
128 /// 执行SQL语句，返回影响的记录数
129 /// </summary>
130 /// <param name="SQLString">SQL语句</param>
131 /// <returns>影响的记录数</returns>
132 public static int ExecuteSql(string SQLString)
133 {
134 using (SqlConnection connection = new SqlConnection(connectionString))
135 {
136 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
137 {
138 try
139 {
140 connection.Open();
141 int rows = cmd.ExecuteNonQuery();
142 return rows;
143 }
144 catch (System.Data.SqlClient.SqlException e)
145 {
146 connection.Close();
147 throw e;
148 }
149 }
150 }
151 }
152
153 public static int ExecuteSqlByTime(string SQLString, int Times)
154 {
155 using (SqlConnection connection = new SqlConnection(connectionString))
156 {
157 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
158 {
159 try
160 {
161 connection.Open();
162 cmd.CommandTimeout = Times;
163 int rows = cmd.ExecuteNonQuery();
164 return rows;
165 }
166 catch (System.Data.SqlClient.SqlException e)
167 {
168 connection.Close();
169 throw e;
170 }
171 }
172 }
173 }
174
175 /// <summary>
176 /// 执行Sql和Oracle滴混合事务
177 /// </summary>
178 /// <param name="list">SQL命令行列表</param>
179 /// <param name="oracleCmdSqlList">Oracle命令行列表</param>
180 /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
181 public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)
182 {
183 using (SqlConnection conn = new SqlConnection(connectionString))
184 {
185 conn.Open();
186 SqlCommand cmd = new SqlCommand();
187 cmd.Connection = conn;
188 SqlTransaction tx = conn.BeginTransaction();
189 cmd.Transaction = tx;
190 try
191 {
192 foreach (CommandInfo myDE in list)
193 {
194 string cmdText = myDE.CommandText;
195 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
196 PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
197 if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
198 {
199 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
200 {
201 tx.Rollback();
202 throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式");
203 //return 0;
204 }
205
206 object obj = cmd.ExecuteScalar();
207 bool isHave = false;
208 if (obj == null && obj == DBNull.Value)
209 {
210 isHave = false;
211 }
212 isHave = Convert.ToInt32(obj) > 0;
213 if (isHave)
214 {
215 //引发事件
216 myDE.OnSolicitationEvent();
217 }
218 }
219 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
220 {
221 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
222 {
223 tx.Rollback();
224 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
225 //return 0;
226 }
227
228 object obj = cmd.ExecuteScalar();
229 bool isHave = false;
230 if (obj == null && obj == DBNull.Value)
231 {
232 isHave = false;
233 }
234 isHave = Convert.ToInt32(obj) > 0;
235
236 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
237 {
238 tx.Rollback();
239 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
240 //return 0;
241 }
242 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
243 {
244 tx.Rollback();
245 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
246 //return 0;
247 }
248 continue;
249 }
250 int val = cmd.ExecuteNonQuery();
251 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
252 {
253 tx.Rollback();
254 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
255 //return 0;
256 }
257 cmd.Parameters.Clear();
258 }
259 string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
260 bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
261 if (!res)
262 {
263 tx.Rollback();
264 throw new Exception("Oracle执行失败");
265 // return -1;
266 }
267 tx.Commit();
268 return 1;
269 }
270 catch (System.Data.SqlClient.SqlException e)
271 {
272 tx.Rollback();
273 throw e;
274 }
275 catch (Exception e)
276 {
277 tx.Rollback();
278 throw e;
279 }
280 }
281 }
282 /// <summary>
283 /// 执行多条SQL语句，实现数据库事务。
284 /// </summary>
285 /// <param name="SQLStringList">多条SQL语句</param>
286 public static int ExecuteSqlTran(List<String> SQLStringList)
287 {
288 using (SqlConnection conn = new SqlConnection(connectionString))
289 {
290 conn.Open();
291 SqlCommand cmd = new SqlCommand();
292 cmd.Connection = conn;
293 SqlTransaction tx = conn.BeginTransaction();
294 cmd.Transaction = tx;
295 try
296 {
297 int count = 0;
298 for (int n = 0; n < SQLStringList.Count; n++)
299 {
300 string strsql = SQLStringList[n];
301 if (strsql.Trim().Length > 1)
302 {
303 cmd.CommandText = strsql;
304 count += cmd.ExecuteNonQuery();
305 }
306 }
307 tx.Commit();
308 return count;
309 }
310 catch
311 {
312 tx.Rollback();
313 return 0;
314 }
315 }
316 }
317 /// <summary>
318 /// 执行带一个存储过程参数的的SQL语句。
319 /// </summary>
320 /// <param name="SQLString">SQL语句</param>
321 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
322 /// <returns>影响的记录数</returns>
323 public static int ExecuteSql(string SQLString, string content)
324 {
325 using (SqlConnection connection = new SqlConnection(connectionString))
326 {
327 SqlCommand cmd = new SqlCommand(SQLString, connection);
328 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
329 myParameter.Value = content;
330 cmd.Parameters.Add(myParameter);
331 try
332 {
333 connection.Open();
334 int rows = cmd.ExecuteNonQuery();
335 return rows;
336 }
337 catch (System.Data.SqlClient.SqlException e)
338 {
339 throw e;
340 }
341 finally
342 {
343 cmd.Dispose();
344 connection.Close();
345 }
346 }
347 }
348 /// <summary>
349 /// 执行带一个存储过程参数的的SQL语句。
350 /// </summary>
351 /// <param name="SQLString">SQL语句</param>
352 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
353 /// <returns>影响的记录数</returns>
354 public static object ExecuteSqlGet(string SQLString, string content)
355 {
356 using (SqlConnection connection = new SqlConnection(connectionString))
357 {
358 SqlCommand cmd = new SqlCommand(SQLString, connection);
359 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
360 myParameter.Value = content;
361 cmd.Parameters.Add(myParameter);
362 try
363 {
364 connection.Open();
365 object obj = cmd.ExecuteScalar();
366 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
367 {
368 return null;
369 }
370 else
371 {
372 return obj;
373 }
374 }
375 catch (System.Data.SqlClient.SqlException e)
376 {
377 throw e;
378 }
379 finally
380 {
381 cmd.Dispose();
382 connection.Close();
383 }
384 }
385 }
386 /// <summary>
387 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
388 /// </summary>
389 /// <param name="strSQL">SQL语句</param>
390 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
391 /// <returns>影响的记录数</returns>
392 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
393 {
394 using (SqlConnection connection = new SqlConnection(connectionString))
395 {
396 SqlCommand cmd = new SqlCommand(strSQL, connection);
397 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
398 myParameter.Value = fs;
399 cmd.Parameters.Add(myParameter);
400 try
401 {
402 connection.Open();
403 int rows = cmd.ExecuteNonQuery();
404 return rows;
405 }
406 catch (System.Data.SqlClient.SqlException e)
407 {
408 throw e;
409 }
410 finally
411 {
412 cmd.Dispose();
413 connection.Close();
414 }
415 }
416 }
417
418 /// <summary>
419 /// 执行一条计算查询结果语句，返回查询结果（object）。
420 /// </summary>
421 /// <param name="SQLString">计算查询结果语句</param>
422 /// <returns>查询结果（object）</returns>
423 public static object GetSingle(string SQLString)
424 {
425 using (SqlConnection connection = new SqlConnection(connectionString))
426 {
427 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
428 {
429 try
430 {
431 connection.Open();
432 object obj = cmd.ExecuteScalar();
433 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
434 {
435 return null;
436 }
437 else
438 {
439 return obj;
440 }
441 }
442 catch (System.Data.SqlClient.SqlException e)
443 {
444 connection.Close();
445 throw e;
446 }
447 }
448 }
449 }
450 public static object GetSingle(string SQLString, int Times)
451 {
452 using (SqlConnection connection = new SqlConnection(connectionString))
453 {
454 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
455 {
456 try
457 {
458 connection.Open();
459 cmd.CommandTimeout = Times;
460 object obj = cmd.ExecuteScalar();
461 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
462 {
463 return null;
464 }
465 else
466 {
467 return obj;
468 }
469 }
470 catch (System.Data.SqlClient.SqlException e)
471 {
472 connection.Close();
473 throw e;
474 }
475 }
476 }
477 }
478 /// <summary>
479 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
480 /// </summary>
481 /// <param name="strSQL">查询语句</param>
482 /// <returns>SqlDataReader</returns>
483 public static SqlDataReader ExecuteReader(string strSQL)
484 {
485 SqlConnection connection = new SqlConnection(connectionString);
486 SqlCommand cmd = new SqlCommand(strSQL, connection);
487 try
488 {
489 connection.Open();
490 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
491 return myReader;
492 }
493 catch (System.Data.SqlClient.SqlException e)
494 {
495 throw e;
496 }
497
498 }
499 /// <summary>
500 /// 执行查询语句，返回DataSet
501 /// </summary>
502 /// <param name="SQLString">查询语句</param>
503 /// <returns>DataSet</returns>
504 public static DataSet Query(string SQLString)
505 {
506 using (SqlConnection connection = new SqlConnection(connectionString))
507 {
508 DataSet ds = new DataSet();
509 try
510 {
511 connection.Open();
512 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
513 command.Fill(ds, "ds");
514 }
515 catch (System.Data.SqlClient.SqlException ex)
516 {
517 throw new Exception(ex.Message);
518 }
519 return ds;
520 }
521 }
522 public static DataSet Query(string SQLString, int Times)
523 {
524 using (SqlConnection connection = new SqlConnection(connectionString))
525 {
526 DataSet ds = new DataSet();
527 try
528 {
529 connection.Open();
530 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
531 command.SelectCommand.CommandTimeout = Times;
532 command.Fill(ds, "ds");
533 }
534 catch (System.Data.SqlClient.SqlException ex)
535 {
536 throw new Exception(ex.Message);
537 }
538 return ds;
539 }
540 }
541
542
543
544 #endregion
545
546 #region 执行带参数的SQL语句
547
548 /// <summary>
549 /// 执行SQL语句，返回影响的记录数
550 /// </summary>
551 /// <param name="SQLString">SQL语句</param>
552 /// <returns>影响的记录数</returns>
553 public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
554 {
555 using (SqlConnection connection = new SqlConnection(connectionString))
556 {
557 using (SqlCommand cmd = new SqlCommand())
558 {
559 try
560 {
561 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
562 int rows = cmd.ExecuteNonQuery();
563 cmd.Parameters.Clear();
564 return rows;
565 }
566 catch (System.Data.SqlClient.SqlException e)
567 {
568 throw e;
569 }
570 }
571 }
572 }
573
574
575 /// <summary>
576 /// 执行多条SQL语句，实现数据库事务。
577 /// </summary>
578 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
579 public static void ExecuteSqlTran(Hashtable SQLStringList)
580 {
581 using (SqlConnection conn = new SqlConnection(connectionString))
582 {
583 conn.Open();
584 using (SqlTransaction trans = conn.BeginTransaction())
585 {
586 SqlCommand cmd = new SqlCommand();
587 try
588 {
589 //循环
590 foreach (DictionaryEntry myDE in SQLStringList)
591 {
592 string cmdText = myDE.Key.ToString();
593 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
594 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
595 int val = cmd.ExecuteNonQuery();
596 cmd.Parameters.Clear();
597 }
598 trans.Commit();
599 }
600 catch
601 {
602 trans.Rollback();
603 throw;
604 }
605 }
606 }
607 }
608 /// <summary>
609 /// 执行多条SQL语句，实现数据库事务。
610 /// </summary>
611 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
612 public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
613 {
614 using (SqlConnection conn = new SqlConnection(connectionString))
615 {
616 conn.Open();
617 using (SqlTransaction trans = conn.BeginTransaction())
618 {
619 SqlCommand cmd = new SqlCommand();
620 try
621 { int count = 0;
622 //循环
623 foreach (CommandInfo myDE in cmdList)
624 {
625 string cmdText = myDE.CommandText;
626 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
627 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
628
629 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
630 {
631 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
632 {
633 trans.Rollback();
634 return 0;
635 }
636
637 object obj = cmd.ExecuteScalar();
638 bool isHave = false;
639 if (obj == null && obj == DBNull.Value)
640 {
641 isHave = false;
642 }
643 isHave = Convert.ToInt32(obj) > 0;
644
645 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
646 {
647 trans.Rollback();
648 return 0;
649 }
650 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
651 {
652 trans.Rollback();
653 return 0;
654 }
655 continue;
656 }
657 int val = cmd.ExecuteNonQuery();
658 count += val;
659 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
660 {
661 trans.Rollback();
662 return 0;
663 }
664 cmd.Parameters.Clear();
665 }
666 trans.Commit();
667 return count;
668 }
669 catch
670 {
671 trans.Rollback();
672 throw;
673 }
674 }
675 }
676 }
677 /// <summary>
678 /// 执行多条SQL语句，实现数据库事务。
679 /// </summary>
680 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
681 public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
682 {
683 using (SqlConnection conn = new SqlConnection(connectionString))
684 {
685 conn.Open();
686 using (SqlTransaction trans = conn.BeginTransaction())
687 {
688 SqlCommand cmd = new SqlCommand();
689 try
690 {
691 int indentity = 0;
692 //循环
693 foreach (CommandInfo myDE in SQLStringList)
694 {
695 string cmdText = myDE.CommandText;
696 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
697 foreach (SqlParameter q in cmdParms)
698 {
699 if (q.Direction == ParameterDirection.InputOutput)
700 {
701 q.Value = indentity;
702 }
703 }
704 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
705 int val = cmd.ExecuteNonQuery();
706 foreach (SqlParameter q in cmdParms)
707 {
708 if (q.Direction == ParameterDirection.Output)
709 {
710 indentity = Convert.ToInt32(q.Value);
711 }
712 }
713 cmd.Parameters.Clear();
714 }
715 trans.Commit();
716 }
717 catch
718 {
719 trans.Rollback();
720 throw;
721 }
722 }
723 }
724 }
725 /// <summary>
726 /// 执行多条SQL语句，实现数据库事务。
727 /// </summary>
728 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
729 public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
730 {
731 using (SqlConnection conn = new SqlConnection(connectionString))
732 {
733 conn.Open();
734 using (SqlTransaction trans = conn.BeginTransaction())
735 {
736 SqlCommand cmd = new SqlCommand();
737 try
738 {
739 int indentity = 0;
740 //循环
741 foreach (DictionaryEntry myDE in SQLStringList)
742 {
743 string cmdText = myDE.Key.ToString();
744 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
745 foreach (SqlParameter q in cmdParms)
746 {
747 if (q.Direction == ParameterDirection.InputOutput)
748 {
749 q.Value = indentity;
750 }
751 }
752 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
753 int val = cmd.ExecuteNonQuery();
754 foreach (SqlParameter q in cmdParms)
755 {
756 if (q.Direction == ParameterDirection.Output)
757 {
758 indentity = Convert.ToInt32(q.Value);
759 }
760 }
761 cmd.Parameters.Clear();
762 }
763 trans.Commit();
764 }
765 catch
766 {
767 trans.Rollback();
768 throw;
769 }
770 }
771 }
772 }
773 /// <summary>
774 /// 执行一条计算查询结果语句，返回查询结果（object）。
775 /// </summary>
776 /// <param name="SQLString">计算查询结果语句</param>
777 /// <returns>查询结果（object）</returns>
778 public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
779 {
780 using (SqlConnection connection = new SqlConnection(connectionString))
781 {
782 using (SqlCommand cmd = new SqlCommand())
783 {
784 try
785 {
786 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
787 object obj = cmd.ExecuteScalar();
788 cmd.Parameters.Clear();
789 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
790 {
791 return null;
792 }
793 else
794 {
795 return obj;
796 }
797 }
798 catch (System.Data.SqlClient.SqlException e)
799 {
800 throw e;
801 }
802 }
803 }
804 }
805
806 /// <summary>
807 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
808 /// </summary>
809 /// <param name="strSQL">查询语句</param>
810 /// <returns>SqlDataReader</returns>
811 public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
812 {
813 SqlConnection connection = new SqlConnection(connectionString);
814 SqlCommand cmd = new SqlCommand();
815 try
816 {
817 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
818 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
819 cmd.Parameters.Clear();
820 return myReader;
821 }
822 catch (System.Data.SqlClient.SqlException e)
823 {
824 throw e;
825 }
826 // finally
827 // {
828 // cmd.Dispose();
829 // connection.Close();
830 // }
831
832 }
833
834 /// <summary>
835 /// 执行查询语句，返回DataSet
836 /// </summary>
837 /// <param name="SQLString">查询语句</param>
838 /// <returns>DataSet</returns>
839 public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
840 {
841 using (SqlConnection connection = new SqlConnection(connectionString))
842 {
843 SqlCommand cmd = new SqlCommand();
844 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
845 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
846 {
847 DataSet ds = new DataSet();
848 try
849 {
850 da.Fill(ds, "ds");
851 cmd.Parameters.Clear();
852 }
853 catch (System.Data.SqlClient.SqlException ex)
854 {
855 throw new Exception(ex.Message);
856 }
857 return ds;
858 }
859 }
860 }
861
862
863 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
864 {
865 if (conn.State != ConnectionState.Open)
866 conn.Open();
867 cmd.Connection = conn;
868 cmd.CommandText = cmdText;
869 if (trans != null)
870 cmd.Transaction = trans;
871 cmd.CommandType = CommandType.Text;//cmdType;
872 if (cmdParms != null)
873 {
874
875
876 foreach (SqlParameter parameter in cmdParms)
877 {
878 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
879 (parameter.Value == null))
880 {
881 parameter.Value = DBNull.Value;
882 }
883 cmd.Parameters.Add(parameter);
884 }
885 }
886 }
887
888 #endregion
889
890 #region 存储过程操作
891
892 /// <summary>
893 /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
894 /// </summary>
895 /// <param name="storedProcName">存储过程名</param>
896 /// <param name="parameters">存储过程参数</param>
897 /// <returns>SqlDataReader</returns>
898 public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
899 {
900 SqlConnection connection = new SqlConnection(connectionString);
901 SqlDataReader returnReader;
902 connection.Open();
903 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
904 command.CommandType = CommandType.StoredProcedure;
905 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
906 return returnReader;
907
908 }
909
910
911 /// <summary>
912 /// 执行存储过程
913 /// </summary>
914 /// <param name="storedProcName">存储过程名</param>
915 /// <param name="parameters">存储过程参数</param>
916 /// <param name="tableName">DataSet结果中的表名</param>
917 /// <returns>DataSet</returns>
918 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
919 {
920 using (SqlConnection connection = new SqlConnection(connectionString))
921 {
922 DataSet dataSet = new DataSet();
923 connection.Open();
924 SqlDataAdapter sqlDA = new SqlDataAdapter();
925 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
926 sqlDA.Fill(dataSet, tableName);
927 connection.Close();
928 return dataSet;
929 }
930 }
931 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
932 {
933 using (SqlConnection connection = new SqlConnection(connectionString))
934 {
935 DataSet dataSet = new DataSet();
936 connection.Open();
937 SqlDataAdapter sqlDA = new SqlDataAdapter();
938 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
939 sqlDA.SelectCommand.CommandTimeout = Times;
940 sqlDA.Fill(dataSet, tableName);
941 connection.Close();
942 return dataSet;
943 }
944 }
945
946
947 /// <summary>
948 /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
949 /// </summary>
950 /// <param name="connection">数据库连接</param>
951 /// <param name="storedProcName">存储过程名</param>
952 /// <param name="parameters">存储过程参数</param>
953 /// <returns>SqlCommand</returns>
954 private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
955 {
956 SqlCommand command = new SqlCommand(storedProcName, connection);
957 command.CommandType = CommandType.StoredProcedure;
958 foreach (SqlParameter parameter in parameters)
959 {
960 if (parameter != null)
961 {
962 // 检查未分配值的输出参数,将其分配以DBNull.Value.
963 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
964 (parameter.Value == null))
965 {
966 parameter.Value = DBNull.Value;
967 }
968 command.Parameters.Add(parameter);
969 }
970 }
971
972 return command;
973 }
974
975 /// <summary>
976 /// 执行存储过程，返回影响的行数
977 /// </summary>
978 /// <param name="storedProcName">存储过程名</param>
979 /// <param name="parameters">存储过程参数</param>
980 /// <param name="rowsAffected">影响的行数</param>
981 /// <returns></returns>
982 public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
983 {
984 using (SqlConnection connection = new SqlConnection(connectionString))
985 {
986 int result;
987 connection.Open();
988 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
989 rowsAffected = command.ExecuteNonQuery();
990 result = (int)command.Parameters["ReturnValue"].Value;
991 //Connection.Close();
992 return result;
993 }
994 }
995
996 /// <summary>
997 /// 创建 SqlCommand 对象实例(用来返回一个整数值)
998 /// </summary>
999 /// <param name="storedProcName">存储过程名</param>
1000 /// <param name="parameters">存储过程参数</param>
1001 /// <returns>SqlCommand 对象实例</returns>
1002 private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
1003 {
1004 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
1005 command.Parameters.Add(new SqlParameter("ReturnValue",
1006 SqlDbType.Int, 4, ParameterDirection.ReturnValue,
1007 false, 0, 0, string.Empty, DataRowVersion.Default, null));
1008 return command;
1009 }
1010 #endregion
1011
1012 }
1013
1014 }