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