我这里面所用的DBHelper

  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.Text;
  8 
  9 namespace IFACE.DBUtility
 10 {
 11 /// <summary>
 12 /// 数据访问抽象基础类
 13 /// Copyright (C) 2004-2008 LiTianPing 
 14 /// Enterprise Library 2.0 数据访问进一步封装类
 15 /// All rights reserved
 16 /// </summary>
 17 public abstract class DbHelperSQL
 18 {
 19 //数据库连接字符串(web.config来配置)
 20 public static string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
 21 
 22 public DbHelperSQL()
 23 {
 24 }
 25 private static SqlConnection GetConn()
 26 {
 27 SqlConnection conn = new SqlConnection(connectionString);
 28 return conn;
 29 }
 30 /// <summary>
 31 /// 执行多次提交的sql数组对象
 32 /// </summary>
 33 /// <param name="sqlStr">数组对象</param>
 34 /// <param name="len">有效长度</param>
 35 /// <returns>bool</returns>
 36 public static bool ExecuteOrcSqlStr(ArrayList arrSql, out string strErr)
 37 {
 38 strErr = "";
 39 bool result = false;
 40 SqlConnection con = GetConn();
 41 if (con.State != ConnectionState.Open)
 42 {
 43 con.Open();
 44 }
 45 SqlCommand ocmd;
 46 ocmd = con.CreateCommand();
 47 
 48 SqlTransaction trans;
 49 trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
 50 ocmd.Transaction = trans;
 51 
 52 try
 53 {
 54 for (int i = 0; i < arrSql.Count; i++)
 55 {
 56 if (arrSql[i].ToString().Trim() != "")
 57 {
 58 ocmd.CommandText = arrSql[i].ToString();
 59 ocmd.ExecuteNonQuery();
 60 }
 61 }
 62 trans.Commit();
 63 result = true;
 64 }
 65 catch (Exception err)
 66 {
 67 strErr = err.ToString() + ocmd.CommandText;
 68 trans.Rollback();
 69 }
 70 finally
 71 {
 72 con.Close();
 73 con.Dispose();
 74 }
 75 return result;
 76 }
 77 #region 公用方法
 78 public static int GetMaxID(string FieldName, string TableName)
 79 {
 80 string strsql = "select max(" + FieldName + ")+1 from " + TableName;
 81 object obj = DbHelperSQL.GetSingle(strsql);
 82 if (obj == null)
 83 {
 84 return 1;
 85 }
 86 else
 87 {
 88 return int.Parse(obj.ToString());
 89 }
 90 }
 91 public static bool Exists(string strSql)
 92 {
 93 object obj = DbHelperSQL.GetSingle(strSql);
 94 int cmdresult;
 95 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 96 {
 97 cmdresult = 0;
 98 }
 99 else
100 {
101 cmdresult = int.Parse(obj.ToString());
102 }
103 if (cmdresult == 0)
104 {
105 return false;
106 }
107 else
108 {
109 return true;
110 }
111 }
112 public static bool Exists(string strSql, params SqlParameter[] cmdParms)
113 {
114 object obj = DbHelperSQL.GetSingle(strSql, cmdParms);
115 int cmdresult;
116 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
117 {
118 cmdresult = 0;
119 }
120 else
121 {
122 cmdresult = int.Parse(obj.ToString());
123 }
124 if (cmdresult == 0)
125 {
126 return false;
127 }
128 else
129 {
130 return true;
131 }
132 }
133 #endregion
134 
135 #region 执行简单SQL语句
136 /// <summary>
137 /// 执行SQL语句，返回影响的记录数
138 /// </summary>
139 /// <param name="SQLString">SQL语句</param>
140 /// <returns>影响的记录数</returns>
141 public static int ExecuteSql(string SQLString)
142 {
143 using (SqlConnection connection = new SqlConnection(connectionString))
144 {
145 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
146 {
147 try
148 {
149 connection.Open();
150 int rows = cmd.ExecuteNonQuery();
151 return rows;
152 }
153 catch (System.Data.SqlClient.SqlException E)
154 {
155 
156 throw new Exception(E.Message);
157 }
158 finally
159 {
160 connection.Close();
161 }
162 }
163 }
164 }
165 /// <summary>
166 /// 执行SQL语句，设置命令的执行等待时间
167 /// </summary>
168 /// <param name="SQLString"></param>
169 /// <param name="Times"></param>
170 /// <returns></returns>
171 public static int ExecuteSqlByTime(string SQLString, int Times)
172 {
173 using (SqlConnection connection = new SqlConnection(connectionString))
174 {
175 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
176 {
177 try
178 {
179 connection.Open();
180 cmd.CommandTimeout = Times;
181 int rows = cmd.ExecuteNonQuery();
182 return rows;
183 }
184 catch (System.Data.SqlClient.SqlException E)
185 {
186 
187 throw new Exception(E.Message);
188 }
189 finally
190 {
191 connection.Close();
192 }
193 }
194 }
195 }
196 
197 /// <summary>
198 /// 执行多条SQL语句，实现数据库事务。
199 /// </summary>
200 /// <param name="SQLStringList">多条SQL语句</param>    
201 public static void ExecuteSqlTran(ArrayList SQLStringList)
202 {
203 using (SqlConnection conn = new SqlConnection(connectionString))
204 {
205 conn.Open();
206 SqlCommand cmd = new SqlCommand();
207 cmd.Connection = conn;
208 SqlTransaction tx = conn.BeginTransaction();
209 cmd.Transaction = tx;
210 string strsql = "";
211 try
212 {
213 for (int n = 0; n < SQLStringList.Count; n++)
214 {
215 strsql = SQLStringList[n].ToString();
216 if (strsql.Trim().Length > 1)
217 {
218 cmd.CommandText = strsql;
219 cmd.ExecuteNonQuery();
220 }
221 }
222 tx.Commit();
223 }
224 catch (System.Data.SqlClient.SqlException E)
225 {
226 tx.Rollback();
227 throw new Exception(E.Message + " " + strsql);
228 }
229 finally
230 {
231 conn.Close();
232 }
233 }
234 }
235 
236 /// <summary>
237 /// 执行多条SQL语句，实现数据库事务。
238 /// </summary>
239 /// <param name="SQLString">多条SQL语句组成的字符串</param>    
240 public static void ExecuteSqlTran(StringBuilder SQLString)
241 {
242 using (SqlConnection conn = new SqlConnection(connectionString))
243 {
244 conn.Open();
245 SqlCommand cmd = new SqlCommand();
246 cmd.Connection = conn;
247 SqlTransaction tx = conn.BeginTransaction();
248 cmd.Transaction = tx;
249 string strsql = "";
250 try
251 {
252 
253 strsql = SQLString.ToString();
254 if (strsql.Trim().Length > 1)
255 {
256 cmd.CommandText = strsql;
257 cmd.ExecuteNonQuery();
258 }
259 tx.Commit();
260 }
261 catch (System.Data.SqlClient.SqlException E)
262 {
263 tx.Rollback();
264 throw new Exception(E.Message + " " + strsql);
265 }
266 finally
267 {
268 conn.Close();
269 }
270 }
271 }
272 
273 
274 /// <summary>
275 /// 执行带一个存储过程参数的的SQL语句。
276 /// </summary>
277 /// <param name="SQLString">SQL语句</param>
278 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式新增</param>
279 /// <returns>影响的记录数</returns>
280 public static int ExecuteSql(string SQLString, string content)
281 {
282 using (SqlConnection connection = new SqlConnection(connectionString))
283 {
284 SqlCommand cmd = new SqlCommand(SQLString, connection);
285 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
286 myParameter.Value = content;
287 cmd.Parameters.Add(myParameter);
288 try
289 {
290 connection.Open();
291 int rows = cmd.ExecuteNonQuery();
292 return rows;
293 }
294 catch (System.Data.SqlClient.SqlException E)
295 {
296 throw new Exception(E.Message);
297 }
298 finally
299 {
300 cmd.Dispose();
301 connection.Close();
302 }
303 }
304 }
305 /// <summary>
306 /// 执行带一个存储过程参数的的SQL语句。
307 /// </summary>
308 /// <param name="SQLString">SQL语句</param>
309 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式新增</param>
310 /// <returns>影响的记录数</returns>
311 public static object ExecuteSqlGet(string SQLString, string content)
312 {
313 using (SqlConnection connection = new SqlConnection(connectionString))
314 {
315 SqlCommand cmd = new SqlCommand(SQLString, connection);
316 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
317 myParameter.Value = content;
318 cmd.Parameters.Add(myParameter);
319 try
320 {
321 connection.Open();
322 object obj = cmd.ExecuteScalar();
323 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
324 {
325 return null;
326 }
327 else
328 {
329 return obj;
330 }
331 }
332 catch (System.Data.SqlClient.SqlException E)
333 {
334 throw new Exception(E.Message);
335 }
336 finally
337 {
338 cmd.Dispose();
339 connection.Close();
340 }
341 }
342 }
343 /// <summary>
344 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
345 /// </summary>
346 /// <param name="strSQL">SQL语句</param>
347 /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
348 /// <returns>影响的记录数</returns>
349 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
350 {
351 using (SqlConnection connection = new SqlConnection(connectionString))
352 {
353 SqlCommand cmd = new SqlCommand(strSQL, connection);
354 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
355 myParameter.Value = fs;
356 cmd.Parameters.Add(myParameter);
357 try
358 {
359 connection.Open();
360 int rows = cmd.ExecuteNonQuery();
361 return rows;
362 }
363 catch (System.Data.SqlClient.SqlException E)
364 {
365 throw new Exception(E.Message);
366 }
367 finally
368 {
369 cmd.Dispose();
370 connection.Close();
371 }
372 }
373 }
374 
375 /// <summary>
376 /// 执行一条计算查询结果语句，返回查询结果（object）。
377 /// </summary>
378 /// <param name="SQLString">计算查询结果语句</param>
379 /// <returns>查询结果（object）</returns>
380 public static object GetSingle(string SQLString)
381 {
382 using (SqlConnection connection = new SqlConnection(connectionString))
383 {
384 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
385 {
386 try
387 {
388 connection.Open();
389 object obj = cmd.ExecuteScalar();
390 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
391 {
392 return null;
393 }
394 else
395 {
396 return obj;
397 }
398 }
399 catch (System.Data.SqlClient.SqlException e)
400 {
401 
402 throw new Exception(e.Message);
403 }
404 finally
405 {
406 connection.Close();
407 }
408 }
409 }
410 }
411 
412 
413 /// <summary>
414 /// 执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)
415 /// </summary>
416 /// <param name="strSQL">查询语句</param>
417 /// <returns>SqlDataReader</returns>
418 public static SqlDataReader ExecuteReader(string strSQL)
419 {
420 SqlConnection connection = new SqlConnection(connectionString);
421 SqlCommand cmd = new SqlCommand(strSQL, connection);
422 try
423 {
424 connection.Open();
425 SqlDataReader myReader = cmd.ExecuteReader();
426 return myReader;
427 }
428 catch (System.Data.SqlClient.SqlException e)
429 {
430 throw new Exception(e.Message);
431 }
432 finally
433 {
434 connection.Close();
435 }
436 }
437 /// <summary>
438 /// 执行查询语句，返回DataSet
439 /// </summary>
440 /// <param name="SQLString">查询语句</param>
441 /// <returns>DataSet</returns>
442 public static DataSet Query(string SQLString)
443 {
444 using (SqlConnection connection = new SqlConnection(connectionString))
445 {
446 DataSet ds = new DataSet();
447 try
448 {
449 connection.Open();
450 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
451 command.Fill(ds, "ds");
452 }
453 catch (System.Data.SqlClient.SqlException ex)
454 {
455 throw new Exception(ex.Message);
456 }
457 finally
458 {
459 connection.Close();
460 }
461 return ds;
462 }
463 }
464 /// <summary>
465 /// 执行查询语句，返回DataSet
466 /// </summary>
467 /// <param name="SQLString">查询语句</param>
468 /// <returns>DataSet</returns>
469 public static DataTable QueryDT(string SQLString, out string msg)
470 {
471 msg = "";
472 using (SqlConnection connection = new SqlConnection(connectionString))
473 {
474 DataTable dt = new DataTable();
475 try
476 {
477 connection.Open();
478 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
479 command.Fill(dt);
480 }
481 catch (System.Data.SqlClient.SqlException ex)
482 {
483 msg = ex.Message;
484 //throw new Exception(ex.Message);
485 }
486 finally
487 {
488 connection.Close();
489 }
490 return dt;
491 }
492 }
493 /// <summary>
494 /// 执行查询语句，返回DataSet
495 /// </summary>
496 /// <param name="SQLString">查询语句</param>
497 /// <returns>DataSet</returns>
498 public static DataTable QueryDT(string SQLString)
499 {
500 using (SqlConnection connection = new SqlConnection(connectionString))
501 {
502 DataTable dt = new DataTable();
503 try
504 {
505 connection.Open();
506 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
507 command.Fill(dt);
508 }
509 catch (System.Data.SqlClient.SqlException ex)
510 {
511 throw new Exception(ex.Message);
512 }
513 finally
514 {
515 connection.Close();
516 }
517 return dt;
518 }
519 }
520 /// <summary>
521 /// 执行查询语句，返回DataSet,设置命令的执行等待时间
522 /// </summary>
523 /// <param name="SQLString"></param>
524 /// <param name="Times"></param>
525 /// <returns></returns>
526 public static DataSet Query(string SQLString, int Times)
527 {
528 using (SqlConnection connection = new SqlConnection(connectionString))
529 {
530 DataSet ds = new DataSet();
531 try
532 {
533 connection.Open();
534 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
535 command.SelectCommand.CommandTimeout = Times;
536 command.Fill(ds, "ds");
537 }
538 catch (System.Data.SqlClient.SqlException ex)
539 {
540 throw new Exception(ex.Message);
541 }
542 finally
543 {
544 connection.Close();
545 }
546 return ds;
547 }
548 }
549 
550 #endregion
551 
552 #region 执行带参数的SQL语句
553 
554 /// <summary>
555 /// 执行SQL语句，返回影响的记录数
556 /// </summary>
557 /// <param name="SQLString">SQL语句</param>
558 /// <returns>影响的记录数</returns>
559 public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
560 {
561 using (SqlConnection connection = new SqlConnection(connectionString))
562 {
563 using (SqlCommand cmd = new SqlCommand())
564 {
565 try
566 {
567 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
568 int rows = cmd.ExecuteNonQuery();
569 cmd.Parameters.Clear();
570 if (connection.State != ConnectionState.Closed)
571 {
572 connection.Close();
573 connection.Dispose();
574 }
575 return rows;
576 }
577 catch (System.Data.SqlClient.SqlException E)
578 {
579 throw new Exception(E.Message);
580 }
581 finally
582 {
583 if (connection.State != ConnectionState.Closed)
584 {
585 connection.Close();
586 connection.Dispose();
587 }
588 }
589 }
590 }
591 }
592 /// <summary>
593 /// 执行多条SQL语句，实现数据库事务。
594 /// </summary>
595 /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
596 public static void ExecuteSqlTran(Hashtable SQLStringList)
597 {
598 using (SqlConnection conn = new SqlConnection(connectionString))
599 {
600 conn.Open();
601 using (SqlTransaction trans = conn.BeginTransaction())
602 {
603 SqlCommand cmd = new SqlCommand();
604 try
605 {
606 //循环
607 foreach (DictionaryEntry myDE in SQLStringList)
608 {
609 string cmdText = myDE.Key.ToString();
610 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
611 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
612 int val = cmd.ExecuteNonQuery();
613 cmd.Parameters.Clear();
614 
615 trans.Commit();
616 }
617 
618 }
619 catch
620 {
621 trans.Rollback();
622 throw;
623 }
624 finally
625 {
626 if (conn.State != ConnectionState.Closed)
627 {
628 conn.Close();
629 conn.Dispose();
630 }
631 }
632 }
633 }
634 }
635 
636 /// <summary>
637 /// 执行一条计算查询结果语句，返回查询结果（object）。
638 /// </summary>
639 /// <param name="SQLString">计算查询结果语句</param>
640 /// <returns>查询结果（object）</returns>
641 public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
642 {
643 using (SqlConnection connection = new SqlConnection(connectionString))
644 {
645 using (SqlCommand cmd = new SqlCommand())
646 {
647 try
648 {
649 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
650 object obj = cmd.ExecuteScalar();
651 cmd.Parameters.Clear();
652 if (connection.State != ConnectionState.Closed)
653 {
654 connection.Close();
655 }
656 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
657 {
658 return null;
659 }
660 else
661 {
662 return obj;
663 }
664 }
665 catch (System.Data.SqlClient.SqlException e)
666 {
667 throw new Exception(e.Message);
668 }
669 finally
670 {
671 if (connection.State != ConnectionState.Closed)
672 {
673 connection.Close();
674 connection.Dispose();
675 }
676 }
677 }
678 }
679 }
680 
681 /// <summary>
682 /// 执行查询语句，返回SqlDataReader (使用该方法切记要手工关闭SqlDataReader和连接)
683 /// </summary>
684 /// <param name="strSQL">查询语句</param>
685 /// <returns>SqlDataReader</returns>
686 public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
687 {
688 SqlConnection connection = new SqlConnection(connectionString);
689 SqlCommand cmd = new SqlCommand();
690 try
691 {
692 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
693 SqlDataReader myReader = cmd.ExecuteReader();
694 cmd.Parameters.Clear();
695 
696 if (connection.State != ConnectionState.Closed)
697 {
698 connection.Close();
699 connection.Dispose();
700 }
701 return myReader;
702 }
703 catch (System.Data.SqlClient.SqlException e)
704 {
705 throw new Exception(e.Message);
706 }
707 finally
708 {
709 if (connection.State != ConnectionState.Closed)
710 {
711 connection.Close();
712 connection.Dispose();
713 }
714 }
715 }
716 
717 /// <summary>
718 /// 执行查询语句，返回DataSet
719 /// </summary>
720 /// <param name="SQLString">查询语句</param>
721 /// <returns>DataSet</returns>
722 public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
723 {
724 using (SqlConnection connection = new SqlConnection(connectionString))
725 {
726 SqlCommand cmd = new SqlCommand();
727 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
728 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
729 {
730 DataSet ds = new DataSet();
731 try
732 {
733 da.Fill(ds, "ds");
734 cmd.Parameters.Clear();
735 }
736 catch (System.Data.SqlClient.SqlException ex)
737 {
738 throw new Exception(ex.Message);
739 }
740 finally
741 {
742 if (connection.State != ConnectionState.Closed)
743 {
744 connection.Close();
745 connection.Dispose();
746 }
747 }
748 return ds;
749 }
750 }
751 }
752 
753 
754 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
755 {
756 if (conn.State != ConnectionState.Open)
757 conn.Open();
758 cmd.Connection = conn;
759 cmd.CommandText = cmdText;
760 if (trans != null)
761 cmd.Transaction = trans;
762 cmd.CommandType = CommandType.Text;//cmdType;
763 if (cmdParms != null)
764 {
765 
766 
767 foreach (SqlParameter parameter in cmdParms)
768 {
769 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
770 (parameter.Value == null))
771 {
772 parameter.Value = DBNull.Value;
773 }
774 cmd.Parameters.Add(parameter);
775 }
776 }
777 if (conn.State != ConnectionState.Closed)
778 {
779 conn.Close();
780 conn.Dispose();
781 }
782 }
783 
784 #endregion
785 
786 #region 存储过程操作
787 
788 /// <summary>
789 /// 执行存储过程 (使用该方法切记要手工关闭SqlDataReader和连接)
790 /// </summary>
791 /// <param name="storedProcName">存储过程名</param>
792 /// <param name="parameters">存储过程参数</param>
793 /// <returns>SqlDataReader</returns>
794 public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
795 {
796 SqlConnection connection = new SqlConnection(connectionString);
797 SqlDataReader returnReader;
798 connection.Open();
799 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
800 command.CommandType = CommandType.StoredProcedure;
801 returnReader = command.ExecuteReader();
802 connection.Close();
803 connection.Dispose();
804 return returnReader;
805 
806 }
807 
808 
809 /// <summary>
810 /// 执行存储过程
811 /// </summary>
812 /// <param name="storedProcName">存储过程名</param>
813 /// <param name="parameters">存储过程参数</param>
814 /// <param name="tableName">DataSet结果中的表名</param>
815 /// <returns>DataSet</returns>
816 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
817 {
818 using (SqlConnection connection = new SqlConnection(connectionString))
819 {
820 DataSet dataSet = new DataSet();
821 connection.Open();
822 SqlDataAdapter sqlDA = new SqlDataAdapter();
823 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
824 sqlDA.Fill(dataSet, tableName);
825 connection.Close();
826 connection.Dispose();
827 return dataSet;
828 }
829 }
830 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
831 {
832 using (SqlConnection connection = new SqlConnection(connectionString))
833 {
834 DataSet dataSet = new DataSet();
835 connection.Open();
836 SqlDataAdapter sqlDA = new SqlDataAdapter();
837 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
838 sqlDA.SelectCommand.CommandTimeout = Times;
839 sqlDA.Fill(dataSet, tableName);
840 connection.Close();
841 connection.Dispose();
842 return dataSet;
843 }
844 }
845 
846 
847 /// <summary>
848 /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
849 /// </summary>
850 /// <param name="connection">数据库连接</param>
851 /// <param name="storedProcName">存储过程名</param>
852 /// <param name="parameters">存储过程参数</param>
853 /// <returns>SqlCommand</returns>
854 private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
855 {
856 SqlCommand command = new SqlCommand(storedProcName, connection);
857 command.CommandType = CommandType.StoredProcedure;
858 foreach (SqlParameter parameter in parameters)
859 {
860 if (parameter != null)
861 {
862 // 检查未分配值的输出参数,将其分配以DBNull.Value.
863 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
864 (parameter.Value == null))
865 {
866 parameter.Value = DBNull.Value;
867 }
868 command.Parameters.Add(parameter);
869 }
870 }
871 
872 return command;
873 }
874 
875 /// <summary>
876 /// 执行存储过程，返回影响的行数    
877 /// </summary>
878 /// <param name="storedProcName">存储过程名</param>
879 /// <param name="parameters">存储过程参数</param>
880 /// <param name="rowsAffected">影响的行数</param>
881 /// <returns></returns>
882 public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
883 {
884 using (SqlConnection connection = new SqlConnection(connectionString))
885 {
886 int result;
887 connection.Open();
888 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
889 rowsAffected = command.ExecuteNonQuery();
890 result = (int)command.Parameters["ReturnValue"].Value;
891 connection.Close();
892 connection.Dispose();
893 return result;
894 }
895 }
896 
897 /// <summary>
898 /// 创建 SqlCommand 对象实例(用来返回一个整数值)    
899 /// </summary>
900 /// <param name="storedProcName">存储过程名</param>
901 /// <param name="parameters">存储过程参数</param>
902 /// <returns>SqlCommand 对象实例</returns>
903 private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
904 {
905 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
906 command.Parameters.Add(new SqlParameter("ReturnValue",
907 SqlDbType.Int, 4, ParameterDirection.ReturnValue,
908 false, 0, 0, string.Empty, DataRowVersion.Default, null));
909 return command;
910 }
911 #endregion
912 
913 }
914 
915 }