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