C# 数据库通用操作类DBHelper

企业信息系统开发主要是围绕数据库进行CRUD ，收藏一个通用的DBHELP操作类
SQLHelper、DBHelper是ADO.NET的改造版
  1 using System;
  2 using System.Collections;
  3 using System.Collections.Specialized;
  4 using System.Data;
  5 using System.Data.SqlClient;
  6 using System.Configuration;
  7 using System.Data.Common;
  8 using System.Collections.Generic;
  9 namespace lrdbhelpSQL
 10 {
 11     /// <summary>
 12     /// 数据访问抽象基础类
 13     /// Copyright (C) Maticsoft 
 14     /// </summary>
 15     public abstract class DbHelperSQL
 16     {
 17         ////数据库连接字符串(web.config来配置)，多数据库可使用DbHelperSQLP来实现.
 18         //public static string connectionString = GetConfigInfo();
 19 
 20         //public static string GetConfigInfo()
 21         //{
 22         //    string configInfo = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
 23         //    return configInfo;
 24         //}
 25 
 26         public static string connectionString = AppConst.DBConnstring;
 27 
 28         public DbHelperSQL()
 29         {
 30         }
 31 
 32 
 33         #region 公用方法
 34         /// <summary>
 35         /// 判断是否存在某表的某个字段
 36         /// </summary>
 37         /// <param name="tableName">表名称</param>
 38         /// <param name="columnName">列名称</param>
 39         /// <returns>是否存在</returns>
 40         public static bool ColumnExists(string tableName, string columnName)
 41         {
 42             string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
 43             object res = GetSingle(sql);
 44             if (res == null)
 45             {
 46                 return false;
 47             }
 48             return Convert.ToInt32(res) > 0;
 49         }
 50         public static int GetMaxID(string FieldName, string TableName)
 51         {
 52             string strsql = "select max(" + FieldName + ")+1 from " + TableName;
 53             object obj = GetSingle(strsql);
 54             if (obj == null)
 55             {
 56                 return 1;
 57             }
 58             else
 59             {
 60                 return int.Parse(obj.ToString());
 61             }
 62         }
 63         public static bool Exists(string strSql)
 64         {
 65             object obj = GetSingle(strSql);
 66             int cmdresult;
 67             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 68             {
 69                 cmdresult = 0;
 70             }
 71             else
 72             {
 73                 cmdresult = int.Parse(obj.ToString()); //也可能=0
 74             }
 75             if (cmdresult == 0)
 76             {
 77                 return false;
 78             }
 79             else
 80             {
 81                 return true;
 82             }
 83         }
 84         /// <summary>
 85         /// 表是否存在
 86         /// </summary>
 87         /// <param name="TableName"></param>
 88         /// <returns></returns>
 89         public static bool TabExists(string TableName)
 90         {
 91             string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
 92             //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
 93             object obj = 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 = 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 
136         #region  执行简单SQL语句
137 
138 
139         /// <summary>
140         /// 执行SQL语句，返回影响的记录数
141         /// </summary>
142         /// <param name="SQLString">SQL语句</param>
143         /// <returns>影响的记录数</returns>
144         public static int ExecuteSql(string SQLString)
145         {
146             using (SqlConnection connection = new SqlConnection(connectionString))
147             {
148                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
149                 {
150                     try
151                     {
152                         connection.Open();
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                     return 0;
162                 }
163             }
164         }
165 
166 
167 
168         public static int ExecuteSqlByTime(string SQLString, int Times)
169         {
170             using (SqlConnection connection = new SqlConnection(connectionString))
171             {
172                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
173                 {
174                     try
175                     {
176                         connection.Open();
177                         cmd.CommandTimeout = Times;
178                         int rows = cmd.ExecuteNonQuery();
179                         return rows;
180                     }
181                     catch (System.Data.SqlClient.SqlException e)
182                     {
183                         connection.Close();
184                         throw e;
185                     }
186                 }
187             }
188         }
189 
190 
191         /// <summary>
192         /// 执行多条SQL语句，实现数据库事务。
193         /// </summary>
194         /// <param name="SQLStringList">多条SQL语句</param>
195         public static int ExecuteSqlTran(List<String> SQLStringList)
196         {
197             using (SqlConnection conn = new SqlConnection(connectionString))
198             {
199                 conn.Open();
200                 SqlCommand cmd = new SqlCommand();
201                 cmd.Connection = conn;
202                 SqlTransaction tx = conn.BeginTransaction();
203                 cmd.Transaction = tx;
204                 try
205                 {
206                     int count = 0;
207                     for (int n = 0; n < SQLStringList.Count; n++)
208                     {
209                         string strsql = SQLStringList[n];
210                         if (strsql.Trim().Length > 1)
211                         {
212                             cmd.CommandText = strsql;
213                             count += cmd.ExecuteNonQuery();
214                         }
215                     }
216                     tx.Commit();
217                     return count;
218                 }
219                 catch
220                 {
221                     tx.Rollback();
222                     return 0;
223                 }
224             }
225         }
226         /// <summary>
227         /// 执行带一个存储过程参数的的SQL语句。
228         /// </summary>
229         /// <param name="SQLString">SQL语句</param>
230         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
231         /// <returns>影响的记录数</returns>
232         public static int ExecuteSql(string SQLString, string content)
233         {
234             using (SqlConnection connection = new SqlConnection(content))
235             {
236                 SqlCommand cmd = new SqlCommand(SQLString, connection);
237                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
238                 myParameter.Value = content;
239                 cmd.Parameters.Add(myParameter);
240                 try
241                 {
242                     connection.Open();
243                     int rows = cmd.ExecuteNonQuery();
244                     return rows;
245                 }
246                 catch (System.Data.SqlClient.SqlException e)
247                 {
248                     //throw e;
249                 }
250                 finally
251                 {
252                     cmd.Dispose();
253                     connection.Close();
254                 }
255                 return 0;
256             }
257         }
258         /// <summary>
259         /// 执行带一个存储过程参数的的SQL语句。
260         /// </summary>
261         /// <param name="SQLString">SQL语句</param>
262         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
263         /// <returns>影响的记录数</returns>
264         public static object ExecuteSqlGet(string SQLString, string content)
265         {
266             using (SqlConnection connection = new SqlConnection(connectionString))
267             {
268                 SqlCommand cmd = new SqlCommand(SQLString, connection);
269                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
270                 myParameter.Value = content;
271                 cmd.Parameters.Add(myParameter);
272                 try
273                 {
274                     connection.Open();
275                     object obj = cmd.ExecuteScalar();
276                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
277                     {
278                         return null;
279                     }
280                     else
281                     {
282                         return obj;
283                     }
284                 }
285                 catch (System.Data.SqlClient.SqlException e)
286                 {
287                     throw e;
288                 }
289                 finally
290                 {
291                     cmd.Dispose();
292                     connection.Close();
293                 }
294             }
295         }
296         /// <summary>
297         /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
298         /// </summary>
299         /// <param name="strSQL">SQL语句</param>
300         /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
301         /// <returns>影响的记录数</returns>
302         public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
303         {
304             using (SqlConnection connection = new SqlConnection(connectionString))
305             {
306                 SqlCommand cmd = new SqlCommand(strSQL, connection);
307                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
308                 myParameter.Value = fs;
309                 cmd.Parameters.Add(myParameter);
310                 try
311                 {
312                     connection.Open();
313                     int rows = cmd.ExecuteNonQuery();
314                     return rows;
315                 }
316                 catch (System.Data.SqlClient.SqlException e)
317                 {
318                     throw e;
319                 }
320                 finally
321                 {
322                     cmd.Dispose();
323                     connection.Close();
324                 }
325             }
326         }
327 
328 
329         /// <summary>
330         /// 执行一条计算查询结果语句，返回查询结果（object）。
331         /// </summary>
332         /// <param name="SQLString">计算查询结果语句</param>
333         /// <returns>查询结果（object）</returns>
334         public static object GetSingle(string SQLString)
335         {
336             using (SqlConnection connection = new SqlConnection(connectionString))
337             {
338                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
339                 {
340                     try
341                     {
342                         connection.Open();
343                         object obj = cmd.ExecuteScalar();
344                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
345                         {
346                             return null;
347                         }
348                         else
349                         {
350                             return obj;
351                         }
352                     }
353                     catch (System.Data.SqlClient.SqlException e)
354                     {
355                         connection.Close();
356                         throw e;
357                     }
358                 }
359             }
360         }
361         public static object GetSingle(string SQLString, int Times)
362         {
363             using (SqlConnection connection = new SqlConnection(connectionString))
364             {
365                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
366                 {
367                     try
368                     {
369                         connection.Open();
370                         cmd.CommandTimeout = Times;
371                         object obj = cmd.ExecuteScalar();
372                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
373                         {
374                             return null;
375                         }
376                         else
377                         {
378                             return obj;
379                         }
380                     }
381                     catch (System.Data.SqlClient.SqlException e)
382                     {
383                         connection.Close();
384                         throw e;
385                     }
386                 }
387             }
388         }
389         /// <summary>
390         /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
391         /// </summary>
392         /// <param name="strSQL">查询语句</param>
393         /// <returns>SqlDataReader</returns>
394         public static SqlDataReader ExecuteReader(string strSQL)
395         {
396             SqlConnection connection = new SqlConnection(connectionString);
397             SqlCommand cmd = new SqlCommand(strSQL, connection);
398             try
399             {
400                 connection.Open();
401                 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
402                 return myReader;
403             }
404             catch (System.Data.SqlClient.SqlException e)
405             {
406                 throw e;
407             }
408 
409 
410         }
411         /// <summary>
412         /// 执行查询语句，返回DataSet
413         /// </summary>
414         /// <param name="SQLString">查询语句</param>
415         /// <returns>DataSet</returns>
416         public static DataSet Query(string SQLString)
417         {
418             using (SqlConnection connection = new SqlConnection(connectionString))
419             {
420                 DataSet ds = new DataSet();
421                 try
422                 {
423                     connection.Open();
424                     SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
425                     command.Fill(ds, "ds");
426                 }
427                 catch (System.Data.SqlClient.SqlException ex)
428                 {
429                     //throw new Exception(ex.Message);
430                 }
431                 return ds;
432             }
433         }
434         public static DataSet Query(string SQLString, int Times)
435         {
436             using (SqlConnection connection = new SqlConnection(connectionString))
437             {
438                 DataSet ds = new DataSet();
439                 try
440                 {
441                     connection.Open();
442                     SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
443                     command.SelectCommand.CommandTimeout = Times;
444                     command.Fill(ds, "ds");
445                 }
446                 catch (System.Data.SqlClient.SqlException ex)
447                 {
448                     throw new Exception(ex.Message);
449                 }
450                 return ds;
451             }
452         }
453 
454 
455 
456 
457 
458 
459         #endregion
460 
461 
462         #region 执行带参数的SQL语句
463 
464 
465         /// <summary>
466         /// 执行SQL语句，返回影响的记录数
467         /// </summary>
468         /// <param name="SQLString">SQL语句</param>
469         /// <returns>影响的记录数</returns>
470         public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
471         {
472             using (SqlConnection connection = new SqlConnection(connectionString))
473             {
474                 using (SqlCommand cmd = new SqlCommand())
475                 {
476                     try
477                     {
478                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
479                         int rows = cmd.ExecuteNonQuery();
480                         cmd.Parameters.Clear();
481                         return rows;
482                     }
483                     catch (System.Data.SqlClient.SqlException e)
484                     {
485                         throw e;
486                     }
487                 }
488             }
489         }
490 
491 
492 
493 
494         /// <summary>
495         /// 执行多条SQL语句，实现数据库事务。
496         /// </summary>
497         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
498         public static void ExecuteSqlTran(Hashtable SQLStringList)
499         {
500             using (SqlConnection conn = new SqlConnection(connectionString))
501             {
502                 conn.Open();
503                 using (SqlTransaction trans = conn.BeginTransaction())
504                 {
505                     SqlCommand cmd = new SqlCommand();
506                     try
507                     {
508                         //循环
509                         foreach (DictionaryEntry myDE in SQLStringList)
510                         {
511                             string cmdText = myDE.Key.ToString();
512                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
513                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
514                             int val = cmd.ExecuteNonQuery();
515                             cmd.Parameters.Clear();
516                         }
517                         trans.Commit();
518                     }
519                     catch
520                     {
521                         trans.Rollback();
522                         throw;
523                     }
524                 }
525             }
526         }
527 
528 
529         /// <summary>
530         /// 执行多条SQL语句，实现数据库事务。
531         /// </summary>
532         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
533         public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
534         {
535             using (SqlConnection conn = new SqlConnection(connectionString))
536             {
537                 conn.Open();
538                 using (SqlTransaction trans = conn.BeginTransaction())
539                 {
540                     SqlCommand cmd = new SqlCommand();
541                     try
542                     {
543                         int indentity = 0;
544                         //循环
545                         foreach (DictionaryEntry myDE in SQLStringList)
546                         {
547                             string cmdText = myDE.Key.ToString();
548                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
549                             foreach (SqlParameter q in cmdParms)
550                             {
551                                 if (q.Direction == ParameterDirection.InputOutput)
552                                 {
553                                     q.Value = indentity;
554                                 }
555                             }
556                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
557                             int val = cmd.ExecuteNonQuery();
558                             foreach (SqlParameter q in cmdParms)
559                             {
560                                 if (q.Direction == ParameterDirection.Output)
561                                 {
562                                     indentity = Convert.ToInt32(q.Value);
563                                 }
564                             }
565                             cmd.Parameters.Clear();
566                         }
567                         trans.Commit();
568                     }
569                     catch
570                     {
571                         trans.Rollback();
572                         throw;
573                     }
574                 }
575             }
576         }
577         /// <summary>
578         /// 执行一条计算查询结果语句，返回查询结果（object）。
579         /// </summary>
580         /// <param name="SQLString">计算查询结果语句</param>
581         /// <returns>查询结果（object）</returns>
582         public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
583         {
584             using (SqlConnection connection = new SqlConnection(connectionString))
585             {
586                 using (SqlCommand cmd = new SqlCommand())
587                 {
588                     try
589                     {
590                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
591                         object obj = cmd.ExecuteScalar();
592                         cmd.Parameters.Clear();
593                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
594                         {
595                             return null;
596                         }
597                         else
598                         {
599                             return obj;
600                         }
601                     }
602                     catch (System.Data.SqlClient.SqlException e)
603                     {
604                         throw e;
605                     }
606                 }
607             }
608         }
609 
610 
611         /// <summary>
612         /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
613         /// </summary>
614         /// <param name="strSQL">查询语句</param>
615         /// <returns>SqlDataReader</returns>
616         public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
617         {
618             SqlConnection connection = new SqlConnection(connectionString);
619             SqlCommand cmd = new SqlCommand();
620             try
621             {
622                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
623                 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
624                 cmd.Parameters.Clear();
625                 return myReader;
626             }
627             catch (System.Data.SqlClient.SqlException e)
628             {
629                 throw e;
630             }
631             //  finally
632             //  {
633             //  cmd.Dispose();
634             //  connection.Close();
635             //  }
636 
637 
638         }
639 
640 
641         /// <summary>
642         /// 执行查询语句，返回DataSet
643         /// </summary>
644         /// <param name="SQLString">查询语句</param>
645         /// <returns>DataSet</returns>
646         public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
647         {
648             using (SqlConnection connection = new SqlConnection(connectionString))
649             {
650                 SqlCommand cmd = new SqlCommand();
651                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
652                 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
653                 {
654                     DataSet ds = new DataSet();
655                     try
656                     {
657                         da.Fill(ds, "ds");
658                         cmd.Parameters.Clear();
659                     }
660                     catch (System.Data.SqlClient.SqlException ex)
661                     {
662                         throw new Exception(ex.Message);
663                     }
664                     return ds;
665                 }
666             }
667         }
668 
669 
670 
671 
672         private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
673         {
674             if (conn.State != ConnectionState.Open)
675                 conn.Open();
676             cmd.Connection = conn;
677             cmd.CommandText = cmdText;
678             if (trans != null)
679                 cmd.Transaction = trans;
680             cmd.CommandType = CommandType.Text;//cmdType;
681             if (cmdParms != null)
682             {
683                 foreach (SqlParameter parameter in cmdParms)
684                 {
685                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
686                         (parameter.Value == null))
687                     {
688                         parameter.Value = DBNull.Value;
689                     }
690                     cmd.Parameters.Add(parameter);
691                 }
692             }
693         }
694 
695 
696         #endregion
697 
698 
699         #region 存储过程操作
700 
701 
702         /// <summary>
703         /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
704         /// </summary>
705         /// <param name="storedProcName">存储过程名</param>
706         /// <param name="parameters">存储过程参数</param>
707         /// <returns>SqlDataReader</returns>
708         public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
709         {
710             SqlConnection connection = new SqlConnection(connectionString);
711             SqlDataReader returnReader;
712             connection.Open();
713             SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
714             command.CommandType = CommandType.StoredProcedure;
715             returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
716             return returnReader;
717 
718         }
719 
720 
721 
722 
723         /// <summary>
724         /// 执行存储过程
725         /// </summary>
726         /// <param name="storedProcName">存储过程名</param>
727         /// <param name="parameters">存储过程参数</param>
728         /// <param name="tableName">DataSet结果中的表名</param>
729         /// <returns>DataSet</returns>
730         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
731         {
732             using (SqlConnection connection = new SqlConnection(connectionString))
733             {
734                 DataSet dataSet = new DataSet();
735                 connection.Open();
736                 SqlDataAdapter sqlDA = new SqlDataAdapter();
737                 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
738                 sqlDA.Fill(dataSet, tableName);
739                 connection.Close();
740                 return dataSet;
741             }
742         }
743         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
744         {
745             using (SqlConnection connection = new SqlConnection(connectionString))
746             {
747                 DataSet dataSet = new DataSet();
748                 connection.Open();
749                 SqlDataAdapter sqlDA = new SqlDataAdapter();
750                 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
751                 sqlDA.SelectCommand.CommandTimeout = Times;
752                 sqlDA.Fill(dataSet, tableName);
753                 connection.Close();
754                 return dataSet;
755             }
756         }
757 
758 
759 
760 
761         /// <summary>
762         /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
763         /// </summary>
764         /// <param name="connection">数据库连接</param>
765         /// <param name="storedProcName">存储过程名</param>
766         /// <param name="parameters">存储过程参数</param>
767         /// <returns>SqlCommand</returns>
768         private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
769         {
770             SqlCommand command = new SqlCommand(storedProcName, connection);
771             command.CommandType = CommandType.StoredProcedure;
772             foreach (SqlParameter parameter in parameters)
773             {
774                 if (parameter != null)
775                 {
776                     // 检查未分配值的输出参数,将其分配以DBNull.Value.
777                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
778                         (parameter.Value == null))
779                     {
780                         parameter.Value = DBNull.Value;
781                     }
782                     command.Parameters.Add(parameter);
783                 }
784             }
785 
786 
787             return command;
788         }
789 
790 
791         /// <summary>
792         /// 执行存储过程，返回影响的行数 
793         /// </summary>
794         /// <param name="storedProcName">存储过程名</param>
795         /// <param name="parameters">存储过程参数</param>
796         /// <param name="rowsAffected">影响的行数</param>
797         /// <returns></returns>
798         public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
799         {
800             using (SqlConnection connection = new SqlConnection(connectionString))
801             {
802                 int result;
803                 connection.Open();
804                 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
805                 rowsAffected = command.ExecuteNonQuery();
806                 result = (int)command.Parameters["ReturnValue"].Value;
807                 //Connection.Close();
808                 return result;
809             }
810         }
811 
812 
813         /// <summary>
814         /// 创建 SqlCommand 对象实例(用来返回一个整数值) 
815         /// </summary>
816         /// <param name="storedProcName">存储过程名</param>
817         /// <param name="parameters">存储过程参数</param>
818         /// <returns>SqlCommand 对象实例</returns>
819         private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
820         {
821             SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
822             command.Parameters.Add(new SqlParameter("ReturnValue",
823                 SqlDbType.Int, 4, ParameterDirection.ReturnValue,
824                 false, 0, 0, string.Empty, DataRowVersion.Default, null));
825             return command;
826         }
827         #endregion
828 
829 
830     }
831 
832 
833 }