C# SqlHerper

1、C# SqlHelper

  1 public static class SqlHelper
  2     {
  3         private static readonly string conStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
  4 
  5         /// <summary>
  6         /// 执行增删改的
  7         /// </summary>
  8         /// <param name="sql"></param>
  9         /// <param name="cmdType"></param>
 10         /// <param name="pms"></param>
 11         /// <returns></returns>
 12         public static int ExecuteNonQuery(string sql, CommandType cmdType = CommandType.Text, params SqlParameter[] pms)
 13         {
 14             using (SqlConnection con = new SqlConnection(conStr))
 15             {
 16                 using (SqlCommand cmd = new SqlCommand(sql, con))
 17                 {
 18                     cmd.CommandType = cmdType;
 19                     if (pms != null)
 20                     {
 21                         cmd.Parameters.AddRange(pms);
 22                     }
 23                     con.Open();
 24                     return cmd.ExecuteNonQuery();
 25                 }
 26             }
 27         }
 28 
 29         /// <summary>
 30         /// 封装一个执行返回单个值的方法
 31         /// </summary>
 32         /// <param name="sql"></param>
 33         /// <param name="cmdType"></param>
 34         /// <param name="pms"></param>
 35         /// <returns></returns>
 36         public static object ExecuteScalar(string sql, CommandType cmdType = CommandType.Text, params SqlParameter[] pms)
 37         {
 38             using (SqlConnection con = new SqlConnection(conStr))
 39             {
 40                 using (SqlCommand cmd = new SqlCommand(sql, con))
 41                 {
 42                     cmd.CommandType = cmdType;
 43                     if (pms != null)
 44                     {
 45                         cmd.Parameters.AddRange(pms);
 46                     }
 47                     con.Open();
 48                     return cmd.ExecuteScalar();
 49                 }
 50             }
 51         }
 52 
 53         /// <summary>
 54         /// 返回SqlDataReader对象的方法
 55         /// </summary>
 56         /// <param name="sql"></param>
 57         /// <param name="cmdType"></param>
 58         /// <param name="pms"></param>
 59         /// <returns></returns>
 60         public static SqlDataReader ExecuteReader(string sql, CommandType cmdType = CommandType.Text, params SqlParameter[] pms)
 61         {
 62             SqlConnection con = new SqlConnection(conStr);
 63             using (SqlCommand cmd = new SqlCommand(sql, con))
 64             {
 65                 cmd.CommandType = cmdType;
 66                 if (pms != null)
 67                 {
 68                     cmd.Parameters.AddRange(pms);
 69                 }
 70                 try
 71                 {
 72                     con.Open();
 73                     //这里第二个参数代表在函数外部,如果掉了close()方法,则con会随之一起销毁
 74                     return cmd.ExecuteReader(CommandBehavior.CloseConnection);
 75                 }
 76                 catch (Exception)
 77                 {
 78                     con.Close();
 79                     con.Dispose();
 80                     throw;
 81                 }
 82             }
 83         }
 84 
 85 
 86         /// <summary>
 87         /// //执行操作SQL语句,返回DataTable
 88         /// </summary>
 89         /// <param name="sql"></param>
 90         /// <param name="cmdType"></param>
 91         /// <param name="pms"></param>
 92         /// <returns></returns>
 93         public static DataTable ExecuteToDataTable(string sql,CommandType cmdType = CommandType.Text,params SqlParameter[] pms)
 94         {
 95             DataTable dt = new DataTable();
 96             using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr))
 97             {
 98                 adapter.SelectCommand.CommandType = cmdType;
 99                 if (pms != null)
100                 {
101                     adapter.SelectCommand.Parameters.AddRange(pms);
102                 }
103                 adapter.Fill(dt);
104             }
105 
106             return dt;
107         }
108 
109         /// <summary>
110         /// 执行操作SQL语句,返回DataSet
111         /// </summary>
112         /// <param name="sql"></param>
113         public static DataSet ExecuteToDataSet(string sql, CommandType cmdType = CommandType.Text, params IDataParameter[] pms)
114         {
115             DataSet ds = new DataSet();
116             using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr))
117             {
118                 adapter.SelectCommand.CommandType = cmdType;
119                 if (pms != null)
120                 {
121                     adapter.SelectCommand.Parameters.AddRange(pms);
122                 }
123                 adapter.Fill(ds);
124             }
125             return ds;
126         }
127 
128         /// <summary>
129         /// DataTable 转换为List 集合
130         /// </summary>
131         /// <typeparam name="T">类型</typeparam>
132         /// <param name="dt">DataTable</param>
133         /// <returns></returns>
134         public static IList<T> DataTableToList<T>(DataTable dt) where T : class,new()
135         {
136             //创建一个属性的列表
137             List<PropertyInfo> prlist = new List<PropertyInfo>();
138             //获取T的类型实例  反射的入口
139             Type t = typeof(T);
140             //获得T 的所有的Public 属性 并找出T属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表 
141             Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); });
142             //创建返回的集合
143             List<T> oblist = new List<T>();
144 
145             foreach (DataRow row in dt.Rows)
146             {
147                 //创建T的实例
148                 T ob = new T();
149                 //找到对应的数据  并赋值
150                 prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });
151                 //放入到返回的集合中.
152                 oblist.Add(ob);
153             }
154             return oblist;
155         }
156 
157         /// <summary>
158         /// List集合 转换为一个DataTable
159         /// </summary>
160         /// <typeparam name="T"></typeparam>
161         /// <param name="value"></param>
162         /// <returns></returns>
163         public static DataTable ListToDataTable<T>(IEnumerable<T> value) where T : class
164         {
165             //创建属性的集合
166             List<PropertyInfo> pList = new List<PropertyInfo>();
167             //获得反射的入口
168             Type type = typeof(T);
169             DataTable dt = new DataTable();
170             //把所有的public属性加入到集合 并添加DataTable的列
171             Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name); });
172             foreach (var item in value)
173             {
174                 //创建一个DataRow实例
175                 DataRow row = dt.NewRow();
176                 //给row 赋值
177                 pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
178                 //加入到DataTable
179                 dt.Rows.Add(row);
180             }
181             return dt;
182         }
183 
184         //IDataReder转实体类列表
185         public static List<T> ReaderToList<T>(IDataReader DataReader)
186         {
187             using (DataReader)
188             {
189                 List<string> field = new List<string>(DataReader.FieldCount);
190                 for (int i = 0; i < DataReader.FieldCount; i++)
191                 {
192                     field.Add(DataReader.GetName(i).ToLower());
193                 }
194                 List<T> list = new List<T>();
195                 while (DataReader.Read())
196                 {
197                     T model = Activator.CreateInstance<T>();
198                     foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
199                     {
200                         if (field.Contains(property.Name.ToLower()))
201                         {
202                             //if (!IsNullOrDBNull(DataReader[property.Name]))
203                             if (DataReader[property.Name] != DBNull.Value)
204                             {
205                                 property.SetValue(model, HackType(DataReader[property.Name], property.PropertyType), null);
206                             }
207                         }
208                     }
209                     list.Add(model);
210                 }
211                 return list;
212             }
213         }
214 
215         /// <summary>
216         /// IDataReder转实体类列表 空字符串和dbnull都返回null
217         /// </summary>
218         /// <typeparam name="T"></typeparam>
219         /// <param name="DataReader"></param>
220         /// <returns></returns>
221         public static List<T> ReaderToListNull<T>(IDataReader DataReader)
222         {
223             using (DataReader)
224             {
225                 List<string> field = new List<string>(DataReader.FieldCount);
226                 for (int i = 0; i < DataReader.FieldCount; i++)
227                 {
228                     field.Add(DataReader.GetName(i).ToLower());
229                 }
230                 List<T> list = new List<T>();
231                 while (DataReader.Read())
232                 {
233                     T model = Activator.CreateInstance<T>();
234                     foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
235                     {
236                         if (field.Contains(property.Name.ToLower()))
237                         {
238                             if (!IsNullOrDBNull(DataReader[property.Name]))
239                             //if (DataReader[property.Name] != DBNull.Value)
240                             {
241                                 property.SetValue(model, HackType(DataReader[property.Name], property.PropertyType), null);
242                             }
243                         }
244                     }
245                     list.Add(model);
246                 }
247                 return list;
248             }
249         }
250 
251         //IDataReder转实体类列表
252         public static List<dynamic> ReaderToDynamicList(IDataReader DataReader)
253         {
254             using (DataReader)
255             {
256                 List<dynamic> list = new List<dynamic>();
257                 while (DataReader.Read())
258                 {
259                     dynamic DataObject = new ExpandoObject();
260                     var DataRow = DataObject as IDictionary<string, object>;
261 
262                     for (int i = 0; i < DataReader.FieldCount; i++)
263                     {
264                         DataRow.Add(DataReader.GetName(i).ToUpper(), DataReader[i]);
265                     }
266                     list.Add(DataRow);
267                 }
268                 return list;
269             }
270         }
271 
272         //这个类对可空类型进行判断转换,要不然会报错
273         private static object HackType(object value, Type conversionType)
274         {
275             if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
276             {
277                 if (value == null)
278                     return null;
279 
280                 System.ComponentModel.NullableConverter nullableConverter = new System.ComponentModel.NullableConverter(conversionType);
281                 conversionType = nullableConverter.UnderlyingType;
282             }
283             return Convert.ChangeType(value, conversionType);
284         }
285 
286         private static bool IsNullOrDBNull(object obj)
287         {
288             return ((obj is DBNull) || string.IsNullOrEmpty(obj.ToString())) ? true : false;
289         }
290     }
View Code

 2、c# SqlHelper微软版本

   1 using System;
   2 using System.Collections;
   3 using System.Collections.Generic;
   4 using System.Configuration;
   5 using System.Data;
   6 using System.Data.SqlClient;
   7 using System.Linq;
   8 using System.Text;
   9 using System.Threading.Tasks;
  10 using System.Xml;
  11 
  12 namespace ConsoleApplication1
  13 {
  14     /// <summary>   
  15     /// SqlServer数据访问帮助类   
  16     /// </summary>   
  17     public sealed class SqlHelper
  18     {
  19         #region 私有构造函数和方法
  20 
  21         private SqlHelper() { }
  22 
  23         /// <summary>   
  24         /// 将SqlParameter参数数组(参数值)分配给SqlCommand命令.   
  25         /// 这个方法将给任何一个参数分配DBNull.Value;   
  26         /// 该操作将阻止默认值的使用.   
  27         /// </summary>   
  28         /// <param name="command">命令名</param>   
  29         /// <param name="commandParameters">SqlParameters数组</param>   
  30         private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
  31         {
  32             if (command == null) throw new ArgumentNullException("command");
  33             if (commandParameters != null)
  34             {
  35                 foreach (SqlParameter p in commandParameters)
  36                 {
  37                     if (p != null)
  38                     {
  39                         // 检查未分配值的输出参数,将其分配以DBNull.Value.   
  40                         if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
  41                             (p.Value == null))
  42                         {
  43                             p.Value = DBNull.Value;
  44                         }
  45                         command.Parameters.Add(p);
  46                     }
  47                 }
  48             }
  49         }
  50 
  51         /// <summary>   
  52         /// 将DataRow类型的列值分配到SqlParameter参数数组.   
  53         /// </summary>   
  54         /// <param name="commandParameters">要分配值的SqlParameter参数数组</param>   
  55         /// <param name="dataRow">将要分配给存储过程参数的DataRow</param>   
  56         private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
  57         {
  58             if ((commandParameters == null) || (dataRow == null))
  59             {
  60                 return;
  61             }
  62 
  63             int i = 0;
  64             // 设置参数值   
  65             foreach (SqlParameter commandParameter in commandParameters)
  66             {
  67                 // 创建参数名称,如果不存在,只抛出一个异常.   
  68                 if (commandParameter.ParameterName == null ||
  69                     commandParameter.ParameterName.Length <= 1)
  70                     throw new Exception(
  71                         string.Format("请提供参数{0}一个有效的名称{1}.", i, commandParameter.ParameterName));
  72                 // 从dataRow的表中获取为参数数组中数组名称的列的索引.   
  73                 // 如果存在和参数名称相同的列,则将列值赋给当前名称的参数.   
  74                 if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
  75                     commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
  76                 i++;
  77             }
  78         }
  79 
  80         /// <summary>   
  81         /// 将一个对象数组分配给SqlParameter参数数组.   
  82         /// </summary>   
  83         /// <param name="commandParameters">要分配值的SqlParameter参数数组</param>   
  84         /// <param name="parameterValues">将要分配给存储过程参数的对象数组</param>   
  85         private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
  86         {
  87             if ((commandParameters == null) || (parameterValues == null))
  88             {
  89                 return;
  90             }
  91 
  92             // 确保对象数组个数与参数个数匹配,如果不匹配,抛出一个异常.   
  93             if (commandParameters.Length != parameterValues.Length)
  94             {
  95                 throw new ArgumentException("参数值个数与参数不匹配.");
  96             }
  97 
  98             // 给参数赋值   
  99             for (int i = 0, j = commandParameters.Length; i < j; i++)
 100             {
 101                 // If the current array value derives from IDbDataParameter, then assign its Value property   
 102                 if (parameterValues[i] is IDbDataParameter)
 103                 {
 104                     IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
 105                     if (paramInstance.Value == null)
 106                     {
 107                         commandParameters[i].Value = DBNull.Value;
 108                     }
 109                     else
 110                     {
 111                         commandParameters[i].Value = paramInstance.Value;
 112                     }
 113                 }
 114                 else if (parameterValues[i] == null)
 115                 {
 116                     commandParameters[i].Value = DBNull.Value;
 117                 }
 118                 else
 119                 {
 120                     commandParameters[i].Value = parameterValues[i];
 121                 }
 122             }
 123         }
 124 
 125         /// <summary>   
 126         /// 预处理用户提供的命令,数据库连接/事务/命令类型/参数   
 127         /// </summary>   
 128         /// <param name="command">要处理的SqlCommand</param>   
 129         /// <param name="connection">数据库连接</param>   
 130         /// <param name="transaction">一个有效的事务或者是null值</param>   
 131         /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>   
 132         /// <param name="commandText">存储过程名或都T-SQL命令文本</param>   
 133         /// <param name="commandParameters">和命令相关联的SqlParameter参数数组,如果没有参数为'null'</param>   
 134         /// <param name="mustCloseConnection"><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param>   
 135         private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
 136         {
 137             if (command == null) throw new ArgumentNullException("command");
 138             if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
 139 
 140             // If the provided connection is not open, we will open it   
 141             if (connection.State != ConnectionState.Open)
 142             {
 143                 mustCloseConnection = true;
 144                 connection.Open();
 145             }
 146             else
 147             {
 148                 mustCloseConnection = false;
 149             }
 150 
 151             // 给命令分配一个数据库连接.   
 152             command.Connection = connection;
 153 
 154             // 设置命令文本(存储过程名或SQL语句)   
 155             command.CommandText = commandText;
 156 
 157             // 分配事务   
 158             if (transaction != null)
 159             {
 160                 if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 161                 command.Transaction = transaction;
 162             }
 163 
 164             // 设置命令类型.   
 165             command.CommandType = commandType;
 166 
 167             // 分配命令参数   
 168             if (commandParameters != null)
 169             {
 170                 AttachParameters(command, commandParameters);
 171             }
 172             return;
 173         }
 174 
 175         #endregion 私有构造函数和方法结束
 176 
 177         #region 数据库连接
 178         /// <summary>   
 179         /// 一个有效的数据库连接字符串   
 180         /// </summary>   
 181         /// <returns></returns>   
 182         public static string GetConnSting()
 183         {
 184             return ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
 185         }
 186         /// <summary>   
 187         /// 一个有效的数据库连接对象   
 188         /// </summary>   
 189         /// <returns></returns>   
 190         public static SqlConnection GetConnection()
 191         {
 192             SqlConnection Connection = new SqlConnection(SqlHelper.GetConnSting());
 193             return Connection;
 194         }
 195         #endregion
 196 
 197         #region ExecuteNonQuery命令
 198 
 199         /// <summary>   
 200         /// 执行指定连接字符串,类型的SqlCommand.   
 201         /// </summary>   
 202         /// <remarks>   
 203         /// 示例:    
 204         ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");   
 205         /// </remarks>   
 206         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
 207         /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>   
 208         /// <param name="commandText">存储过程名称或SQL语句</param>   
 209         /// <returns>返回命令影响的行数</returns>   
 210         public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
 211         {
 212             return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
 213         }
 214 
 215         /// <summary>   
 216         /// 执行指定连接字符串,类型的SqlCommand.如果没有提供参数,不返回结果.   
 217         /// </summary>   
 218         /// <remarks>   
 219         /// 示例:    
 220         ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));   
 221         /// </remarks>   
 222         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
 223         /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>   
 224         /// <param name="commandText">存储过程名称或SQL语句</param>   
 225         /// <param name="commandParameters">SqlParameter参数数组</param>   
 226         /// <returns>返回命令影响的行数</returns>   
 227         public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 228         {
 229             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 230 
 231             using (SqlConnection connection = new SqlConnection(connectionString))
 232             {
 233                 connection.Open();
 234 
 235                 return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
 236             }
 237         }
 238 
 239         /// <summary>   
 240         /// 执行指定连接字符串的存储过程,将对象数组的值赋给存储过程参数,   
 241         /// 此方法需要在参数缓存方法中探索参数并生成参数.   
 242         /// </summary>   
 243         /// <remarks>   
 244         /// 这个方法没有提供访问输出参数和返回值.   
 245         /// 示例:    
 246         ///  int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);   
 247         /// </remarks>   
 248         /// <param name="connectionString">一个有效的数据库连接字符串/param>   
 249         /// <param name="spName">存储过程名称</param>   
 250         /// <param name="parameterValues">分配到存储过程输入参数的对象数组</param>   
 251         /// <returns>返回受影响的行数</returns>   
 252         public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
 253         {
 254             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 255             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 256 
 257             // 如果存在参数值   
 258             if ((parameterValues != null) && (parameterValues.Length > 0))
 259             {
 260                 // 从探索存储过程参数(加载到缓存)并分配给存储过程参数数组.   
 261                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
 262 
 263                 // 给存储过程参数赋值   
 264                 AssignParameterValues(commandParameters, parameterValues);
 265 
 266                 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
 267             }
 268             else
 269             {
 270                 // 没有参数情况下   
 271                 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
 272             }
 273         }
 274 
 275         /// <summary>   
 276         /// 执行指定数据库连接对象的命令   
 277         /// </summary>   
 278         /// <remarks>   
 279         /// 示例:    
 280         ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");   
 281         /// </remarks>   
 282         /// <param name="connection">一个有效的数据库连接对象</param>   
 283         /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>   
 284         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
 285         /// <returns>返回影响的行数</returns>   
 286         public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
 287         {
 288             return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
 289         }
 290 
 291         /// <summary>   
 292         /// 执行指定数据库连接对象的命令   
 293         /// </summary>   
 294         /// <remarks>   
 295         /// 示例:    
 296         ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));   
 297         /// </remarks>   
 298         /// <param name="connection">一个有效的数据库连接对象</param>   
 299         /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>   
 300         /// <param name="commandText">T存储过程名称或T-SQL语句</param>   
 301         /// <param name="commandParameters">SqlParamter参数数组</param>   
 302         /// <returns>返回影响的行数</returns>   
 303         public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 304         {
 305             if (connection == null) throw new ArgumentNullException("connection");
 306 
 307             // 创建SqlCommand命令,并进行预处理   
 308             SqlCommand cmd = new SqlCommand();
 309             bool mustCloseConnection = false;
 310             PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
 311 
 312             // Finally, execute the command   
 313             int retval = cmd.ExecuteNonQuery();
 314 
 315             // 清除参数,以便再次使用.   
 316             cmd.Parameters.Clear();
 317             if (mustCloseConnection)
 318                 connection.Close();
 319             return retval;
 320         }
 321 
 322         /// <summary>   
 323         /// 执行指定数据库连接对象的命令,将对象数组的值赋给存储过程参数.   
 324         /// </summary>   
 325         /// <remarks>   
 326         /// 此方法不提供访问存储过程输出参数和返回值   
 327         /// 示例:    
 328         ///  int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);   
 329         /// </remarks>   
 330         /// <param name="connection">一个有效的数据库连接对象</param>   
 331         /// <param name="spName">存储过程名</param>   
 332         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
 333         /// <returns>返回影响的行数</returns>   
 334         public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
 335         {
 336             if (connection == null) throw new ArgumentNullException("connection");
 337             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 338 
 339             // 如果有参数值   
 340             if ((parameterValues != null) && (parameterValues.Length > 0))
 341             {
 342                 // 从缓存中加载存储过程参数   
 343                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 344 
 345                 // 给存储过程分配参数值   
 346                 AssignParameterValues(commandParameters, parameterValues);
 347 
 348                 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
 349             }
 350             else
 351             {
 352                 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
 353             }
 354         }
 355 
 356         /// <summary>   
 357         /// 执行带事务的SqlCommand.   
 358         /// </summary>   
 359         /// <remarks>   
 360         /// 示例.:    
 361         ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");   
 362         /// </remarks>   
 363         /// <param name="transaction">一个有效的数据库连接对象</param>   
 364         /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>   
 365         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
 366         /// <returns>返回影响的行数/returns>   
 367         public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
 368         {
 369             return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
 370         }
 371 
 372         /// <summary>   
 373         /// 执行带事务的SqlCommand(指定参数).   
 374         /// </summary>   
 375         /// <remarks>   
 376         /// 示例:    
 377         ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));   
 378         /// </remarks>   
 379         /// <param name="transaction">一个有效的数据库连接对象</param>   
 380         /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>   
 381         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
 382         /// <param name="commandParameters">SqlParamter参数数组</param>   
 383         /// <returns>返回影响的行数</returns>   
 384         public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 385         {
 386             if (transaction == null) throw new ArgumentNullException("transaction");
 387             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 388 
 389             // 预处理   
 390             SqlCommand cmd = new SqlCommand();
 391             bool mustCloseConnection = false;
 392             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
 393 
 394             // 执行   
 395             int retval = cmd.ExecuteNonQuery();
 396 
 397             // 清除参数集,以便再次使用.   
 398             cmd.Parameters.Clear();
 399             return retval;
 400         }
 401 
 402         /// <summary>   
 403         /// 执行带事务的SqlCommand(指定参数值).   
 404         /// </summary>   
 405         /// <remarks>   
 406         /// 此方法不提供访问存储过程输出参数和返回值   
 407         /// 示例:    
 408         ///  int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);   
 409         /// </remarks>   
 410         /// <param name="transaction">一个有效的数据库连接对象</param>   
 411         /// <param name="spName">存储过程名</param>   
 412         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
 413         /// <returns>返回受影响的行数</returns>   
 414         public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
 415         {
 416             if (transaction == null) throw new ArgumentNullException("transaction");
 417             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 418             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 419 
 420             // 如果有参数值   
 421             if ((parameterValues != null) && (parameterValues.Length > 0))
 422             {
 423                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
 424                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 425 
 426                 // 给存储过程参数赋值   
 427                 AssignParameterValues(commandParameters, parameterValues);
 428 
 429                 // 调用重载方法   
 430                 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
 431             }
 432             else
 433             {
 434                 // 没有参数值   
 435                 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
 436             }
 437         }
 438 
 439         #endregion ExecuteNonQuery方法结束
 440 
 441         #region ExecuteDataset方法
 442 
 443         /// <summary>   
 444         /// 执行指定数据库连接字符串的命令,返回DataSet.   
 445         /// </summary>   
 446         /// <remarks>   
 447         /// 示例:    
 448         ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");   
 449         /// </remarks>   
 450         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
 451         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 452         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
 453         /// <returns>返回一个包含结果集的DataSet</returns>   
 454         public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
 455         {
 456             return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
 457         }
 458 
 459         /// <summary>   
 460         /// 执行指定数据库连接字符串的命令,返回DataSet.   
 461         /// </summary>   
 462         /// <remarks>   
 463         /// 示例:   
 464         ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));   
 465         /// </remarks>   
 466         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
 467         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 468         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
 469         /// <param name="commandParameters">SqlParamters参数数组</param>   
 470         /// <returns>返回一个包含结果集的DataSet</returns>   
 471         public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 472         {
 473             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 474 
 475             // 创建并打开数据库连接对象,操作完成释放对象.   
 476             using (SqlConnection connection = new SqlConnection(connectionString))
 477             {
 478                 connection.Open();
 479 
 480                 // 调用指定数据库连接字符串重载方法.   
 481                 return ExecuteDataset(connection, commandType, commandText, commandParameters);
 482             }
 483         }
 484 
 485         /// <summary>   
 486         /// 执行指定数据库连接字符串的命令,直接提供参数值,返回DataSet.   
 487         /// </summary>   
 488         /// <remarks>   
 489         /// 此方法不提供访问存储过程输出参数和返回值.   
 490         /// 示例:   
 491         ///  DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);   
 492         /// </remarks>   
 493         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
 494         /// <param name="spName">存储过程名</param>   
 495         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
 496         /// <returns>返回一个包含结果集的DataSet</returns>   
 497         public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
 498         {
 499             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 500             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 501 
 502             if ((parameterValues != null) && (parameterValues.Length > 0))
 503             {
 504                 // 从缓存中检索存储过程参数   
 505                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
 506 
 507                 // 给存储过程参数分配值   
 508                 AssignParameterValues(commandParameters, parameterValues);
 509 
 510                 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
 511             }
 512             else
 513             {
 514                 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
 515             }
 516         }
 517 
 518         /// <summary>   
 519         /// 执行指定数据库连接对象的命令,返回DataSet.   
 520         /// </summary>   
 521         /// <remarks>   
 522         /// 示例:    
 523         ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");   
 524         /// </remarks>   
 525         /// <param name="connection">一个有效的数据库连接对象</param>   
 526         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 527         /// <param name="commandText">存储过程名或T-SQL语句</param>   
 528         /// <returns>返回一个包含结果集的DataSet</returns>   
 529         public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
 530         {
 531             return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
 532         }
 533 
 534         /// <summary>   
 535         /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.   
 536         /// </summary>   
 537         /// <remarks>   
 538         /// 示例:    
 539         ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));   
 540         /// </remarks>   
 541         /// <param name="connection">一个有效的数据库连接对象</param>   
 542         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 543         /// <param name="commandText">存储过程名或T-SQL语句</param>   
 544         /// <param name="commandParameters">SqlParamter参数数组</param>   
 545         /// <returns>返回一个包含结果集的DataSet</returns>   
 546         public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 547         {
 548             if (connection == null) throw new ArgumentNullException("connection");
 549 
 550             // 预处理   
 551             SqlCommand cmd = new SqlCommand();
 552             bool mustCloseConnection = false;
 553             PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
 554 
 555             // 创建SqlDataAdapter和DataSet.   
 556             using (SqlDataAdapter da = new SqlDataAdapter(cmd))
 557             {
 558                 DataSet ds = new DataSet();
 559 
 560                 // 填充DataSet.   
 561                 da.Fill(ds);
 562 
 563                 cmd.Parameters.Clear();
 564 
 565                 if (mustCloseConnection)
 566                     connection.Close();
 567 
 568                 return ds;
 569             }
 570         }
 571 
 572         /// <summary>   
 573         /// 执行指定数据库连接对象的命令,指定参数值,返回DataSet.   
 574         /// </summary>   
 575         /// <remarks>   
 576         /// 此方法不提供访问存储过程输入参数和返回值.   
 577         /// 示例.:    
 578         ///  DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);   
 579         /// </remarks>   
 580         /// <param name="connection">一个有效的数据库连接对象</param>   
 581         /// <param name="spName">存储过程名</param>   
 582         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
 583         /// <returns>返回一个包含结果集的DataSet</returns>   
 584         public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
 585         {
 586             if (connection == null) throw new ArgumentNullException("connection");
 587             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 588 
 589             if ((parameterValues != null) && (parameterValues.Length > 0))
 590             {
 591                 // 比缓存中加载存储过程参数   
 592                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 593 
 594                 // 给存储过程参数分配值   
 595                 AssignParameterValues(commandParameters, parameterValues);
 596 
 597                 return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
 598             }
 599             else
 600             {
 601                 return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
 602             }
 603         }
 604 
 605         /// <summary>   
 606         /// 执行指定事务的命令,返回DataSet.   
 607         /// </summary>   
 608         /// <remarks>   
 609         /// 示例:    
 610         ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");   
 611         /// </remarks>   
 612         /// <param name="transaction">事务</param>   
 613         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 614         /// <param name="commandText">存储过程名或T-SQL语句</param>   
 615         /// <returns>返回一个包含结果集的DataSet</returns>   
 616         public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
 617         {
 618             return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
 619         }
 620 
 621         /// <summary>   
 622         /// 执行指定事务的命令,指定参数,返回DataSet.   
 623         /// </summary>   
 624         /// <remarks>   
 625         /// 示例:    
 626         ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));   
 627         /// </remarks>   
 628         /// <param name="transaction">事务</param>   
 629         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 630         /// <param name="commandText">存储过程名或T-SQL语句</param>   
 631         /// <param name="commandParameters">SqlParamter参数数组</param>   
 632         /// <returns>返回一个包含结果集的DataSet</returns>   
 633         public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 634         {
 635             if (transaction == null) throw new ArgumentNullException("transaction");
 636             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 637 
 638             // 预处理   
 639             SqlCommand cmd = new SqlCommand();
 640             bool mustCloseConnection = false;
 641             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
 642 
 643             // 创建 DataAdapter & DataSet   
 644             using (SqlDataAdapter da = new SqlDataAdapter(cmd))
 645             {
 646                 DataSet ds = new DataSet();
 647                 da.Fill(ds);
 648                 cmd.Parameters.Clear();
 649                 return ds;
 650             }
 651         }
 652 
 653         /// <summary>   
 654         /// 执行指定事务的命令,指定参数值,返回DataSet.   
 655         /// </summary>   
 656         /// <remarks>   
 657         /// 此方法不提供访问存储过程输入参数和返回值.   
 658         /// 示例.:    
 659         ///  DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);   
 660         /// </remarks>   
 661         /// <param name="transaction">事务</param>   
 662         /// <param name="spName">存储过程名</param>   
 663         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
 664         /// <returns>返回一个包含结果集的DataSet</returns>   
 665         public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
 666         {
 667             if (transaction == null) throw new ArgumentNullException("transaction");
 668             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 669             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 670 
 671             if ((parameterValues != null) && (parameterValues.Length > 0))
 672             {
 673                 // 从缓存中加载存储过程参数   
 674                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 675 
 676                 // 给存储过程参数分配值   
 677                 AssignParameterValues(commandParameters, parameterValues);
 678 
 679                 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
 680             }
 681             else
 682             {
 683                 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
 684             }
 685         }
 686 
 687         #endregion ExecuteDataset数据集命令结束
 688 
 689         #region ExecuteReader 数据阅读器
 690 
 691         /// <summary>   
 692         /// 枚举,标识数据库连接是由SqlHelper提供还是由调用者提供   
 693         /// </summary>   
 694         private enum SqlConnectionOwnership
 695         {
 696             /// <summary>由SqlHelper提供连接</summary>   
 697             Internal,
 698             /// <summary>由调用者提供连接</summary>   
 699             External
 700         }
 701 
 702         /// <summary>   
 703         /// 执行指定数据库连接对象的数据阅读器.   
 704         /// </summary>   
 705         /// <remarks>   
 706         /// 如果是SqlHelper打开连接,当连接关闭DataReader也将关闭.   
 707         /// 如果是调用都打开连接,DataReader由调用都管理.   
 708         /// </remarks>   
 709         /// <param name="connection">一个有效的数据库连接对象</param>   
 710         /// <param name="transaction">一个有效的事务,或者为 'null'</param>   
 711         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 712         /// <param name="commandText">存储过程名或T-SQL语句</param>   
 713         /// <param name="commandParameters">SqlParameters参数数组,如果没有参数则为'null'</param>   
 714         /// <param name="connectionOwnership">标识数据库连接对象是由调用者提供还是由SqlHelper提供</param>   
 715         /// <returns>返回包含结果集的SqlDataReader</returns>   
 716         private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
 717         {
 718             if (connection == null) throw new ArgumentNullException("connection");
 719 
 720             bool mustCloseConnection = false;
 721             // 创建命令   
 722             SqlCommand cmd = new SqlCommand();
 723             try
 724             {
 725                 PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
 726 
 727                 // 创建数据阅读器   
 728                 SqlDataReader dataReader;
 729 
 730                 if (connectionOwnership == SqlConnectionOwnership.External)
 731                 {
 732                     dataReader = cmd.ExecuteReader();
 733                 }
 734                 else
 735                 {
 736                     dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 737                 }
 738 
 739                 // 清除参数,以便再次使用..   
 740                 // HACK: There is a problem here, the output parameter values are fletched   
 741                 // when the reader is closed, so if the parameters are detached from the command   
 742                 // then the SqlReader can磘 set its values.   
 743                 // When this happen, the parameters can磘 be used again in other command.   
 744                 bool canClear = true;
 745                 foreach (SqlParameter commandParameter in cmd.Parameters)
 746                 {
 747                     if (commandParameter.Direction != ParameterDirection.Input)
 748                         canClear = false;
 749                 }
 750 
 751                 if (canClear)
 752                 {
 753                     cmd.Parameters.Clear();
 754                 }
 755 
 756                 return dataReader;
 757             }
 758             catch
 759             {
 760                 if (mustCloseConnection)
 761                     connection.Close();
 762                 throw;
 763             }
 764         }
 765 
 766         /// <summary>   
 767         /// 执行指定数据库连接字符串的数据阅读器.   
 768         /// </summary>   
 769         /// <remarks>   
 770         /// 示例:    
 771         ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");   
 772         /// </remarks>   
 773         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
 774         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 775         /// <param name="commandText">存储过程名或T-SQL语句</param>   
 776         /// <returns>返回包含结果集的SqlDataReader</returns>   
 777         public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
 778         {
 779             return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
 780         }
 781 
 782         /// <summary>   
 783         /// 执行指定数据库连接字符串的数据阅读器,指定参数.   
 784         /// </summary>   
 785         /// <remarks>   
 786         /// 示例:    
 787         ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));   
 788         /// </remarks>   
 789         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
 790         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 791         /// <param name="commandText">存储过程名或T-SQL语句</param>   
 792         /// <param name="commandParameters">SqlParamter参数数组(new SqlParameter("@prodid", 24))</param>   
 793         /// <returns>返回包含结果集的SqlDataReader</returns>   
 794         public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 795         {
 796             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 797             SqlConnection connection = null;
 798             try
 799             {
 800                 connection = new SqlConnection(connectionString);
 801                 connection.Open();
 802 
 803                 return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
 804             }
 805             catch
 806             {
 807                 // If we fail to return the SqlDatReader, we need to close the connection ourselves   
 808                 if (connection != null) connection.Close();
 809                 throw;
 810             }
 811 
 812         }
 813 
 814         /// <summary>   
 815         /// 执行指定数据库连接字符串的数据阅读器,指定参数值.   
 816         /// </summary>   
 817         /// <remarks>   
 818         /// 此方法不提供访问存储过程输出参数和返回值参数.   
 819         /// 示例:    
 820         ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);   
 821         /// </remarks>   
 822         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
 823         /// <param name="spName">存储过程名</param>   
 824         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
 825         /// <returns>返回包含结果集的SqlDataReader</returns>   
 826         public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
 827         {
 828             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 829             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 830 
 831             if ((parameterValues != null) && (parameterValues.Length > 0))
 832             {
 833                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
 834 
 835                 AssignParameterValues(commandParameters, parameterValues);
 836 
 837                 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
 838             }
 839             else
 840             {
 841                 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
 842             }
 843         }
 844 
 845         /// <summary>   
 846         /// 执行指定数据库连接对象的数据阅读器.   
 847         /// </summary>   
 848         /// <remarks>   
 849         /// 示例:    
 850         ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");   
 851         /// </remarks>   
 852         /// <param name="connection">一个有效的数据库连接对象</param>   
 853         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 854         /// <param name="commandText">存储过程名或T-SQL语句</param>   
 855         /// <returns>返回包含结果集的SqlDataReader</returns>   
 856         public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
 857         {
 858             return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
 859         }
 860 
 861         /// <summary>   
 862         /// [调用者方式]执行指定数据库连接对象的数据阅读器,指定参数.   
 863         /// </summary>   
 864         /// <remarks>   
 865         /// 示例:    
 866         ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));   
 867         /// </remarks>   
 868         /// <param name="connection">一个有效的数据库连接对象</param>   
 869         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 870         /// <param name="commandText">命令类型 (存储过程,命令文本或其它)</param>   
 871         /// <param name="commandParameters">SqlParamter参数数组</param>   
 872         /// <returns>返回包含结果集的SqlDataReader</returns>   
 873         public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 874         {
 875             return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
 876         }
 877 
 878         /// <summary>   
 879         /// [调用者方式]执行指定数据库连接对象的数据阅读器,指定参数值.   
 880         /// </summary>   
 881         /// <remarks>   
 882         /// 此方法不提供访问存储过程输出参数和返回值参数.   
 883         /// 示例:    
 884         ///  SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);   
 885         /// </remarks>   
 886         /// <param name="connection">一个有效的数据库连接对象</param>   
 887         /// <param name="spName">T存储过程名</param>   
 888         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
 889         /// <returns>返回包含结果集的SqlDataReader</returns>   
 890         public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
 891         {
 892             if (connection == null) throw new ArgumentNullException("connection");
 893             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 894 
 895             if ((parameterValues != null) && (parameterValues.Length > 0))
 896             {
 897                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 898 
 899                 AssignParameterValues(commandParameters, parameterValues);
 900 
 901                 return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
 902             }
 903             else
 904             {
 905                 return ExecuteReader(connection, CommandType.StoredProcedure, spName);
 906             }
 907         }
 908 
 909         /// <summary>   
 910         /// [调用者方式]执行指定数据库事务的数据阅读器,指定参数值.   
 911         /// </summary>   
 912         /// <remarks>   
 913         /// 示例:    
 914         ///  SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");   
 915         /// </remarks>   
 916         /// <param name="transaction">一个有效的连接事务</param>   
 917         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 918         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
 919         /// <returns>返回包含结果集的SqlDataReader</returns>   
 920         public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
 921         {
 922             return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
 923         }
 924 
 925         /// <summary>   
 926         /// [调用者方式]执行指定数据库事务的数据阅读器,指定参数.   
 927         /// </summary>   
 928         /// <remarks>   
 929         /// 示例:    
 930         ///   SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));   
 931         /// </remarks>   
 932         /// <param name="transaction">一个有效的连接事务</param>   
 933         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 934         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
 935         /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>   
 936         /// <returns>返回包含结果集的SqlDataReader</returns>   
 937         public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 938         {
 939             if (transaction == null) throw new ArgumentNullException("transaction");
 940             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 941 
 942             return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
 943         }
 944 
 945         /// <summary>   
 946         /// [调用者方式]执行指定数据库事务的数据阅读器,指定参数值.   
 947         /// </summary>   
 948         /// <remarks>   
 949         /// 此方法不提供访问存储过程输出参数和返回值参数.   
 950         ///   
 951         /// 示例:    
 952         ///  SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);   
 953         /// </remarks>   
 954         /// <param name="transaction">一个有效的连接事务</param>   
 955         /// <param name="spName">存储过程名称</param>   
 956         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
 957         /// <returns>返回包含结果集的SqlDataReader</returns>   
 958         public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
 959         {
 960             if (transaction == null) throw new ArgumentNullException("transaction");
 961             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 962             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 963 
 964             // 如果有参数值   
 965             if ((parameterValues != null) && (parameterValues.Length > 0))
 966             {
 967                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 968 
 969                 AssignParameterValues(commandParameters, parameterValues);
 970 
 971                 return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
 972             }
 973             else
 974             {
 975                 // 没有参数值   
 976                 return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
 977             }
 978         }
 979 
 980         #endregion ExecuteReader数据阅读器
 981 
 982         #region ExecuteScalar 返回结果集中的第一行第一列
 983 
 984         /// <summary>   
 985         /// 执行指定数据库连接字符串的命令,返回结果集中的第一行第一列.   
 986         /// </summary>   
 987         /// <remarks>   
 988         /// 示例:    
 989         ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");   
 990         /// </remarks>   
 991         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
 992         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
 993         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
 994         /// <returns>返回结果集中的第一行第一列</returns>   
 995         public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
 996         {
 997             // 执行参数为空的方法   
 998             return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
 999         }
1000 
1001         /// <summary>   
1002         /// 执行指定数据库连接字符串的命令,指定参数,返回结果集中的第一行第一列.   
1003         /// </summary>   
1004         /// <remarks>   
1005         /// 示例:    
1006         ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));   
1007         /// </remarks>   
1008         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
1009         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1010         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
1011         /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>   
1012         /// <returns>返回结果集中的第一行第一列</returns>   
1013         public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1014         {
1015             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1016             // 创建并打开数据库连接对象,操作完成释放对象.   
1017             using (SqlConnection connection = new SqlConnection(connectionString))
1018             {
1019                 connection.Open();
1020 
1021                 // 调用指定数据库连接字符串重载方法.   
1022                 return ExecuteScalar(connection, commandType, commandText, commandParameters);
1023             }
1024         }
1025 
1026         /// <summary>   
1027         /// 执行指定数据库连接字符串的命令,指定参数值,返回结果集中的第一行第一列.   
1028         /// </summary>   
1029         /// <remarks>   
1030         /// 此方法不提供访问存储过程输出参数和返回值参数.   
1031         ///   
1032         /// 示例:    
1033         ///  int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);   
1034         /// </remarks>   
1035         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
1036         /// <param name="spName">存储过程名称</param>   
1037         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
1038         /// <returns>返回结果集中的第一行第一列</returns>   
1039         public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
1040         {
1041             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1042             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1043 
1044             // 如果有参数值   
1045             if ((parameterValues != null) && (parameterValues.Length > 0))
1046             {
1047                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
1048                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
1049 
1050                 // 给存储过程参数赋值   
1051                 AssignParameterValues(commandParameters, parameterValues);
1052 
1053                 // 调用重载方法   
1054                 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
1055             }
1056             else
1057             {
1058                 // 没有参数值   
1059                 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
1060             }
1061         }
1062 
1063         /// <summary>   
1064         /// 执行指定数据库连接对象的命令,返回结果集中的第一行第一列.   
1065         /// </summary>   
1066         /// <remarks>   
1067         /// 示例:    
1068         ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");   
1069         /// </remarks>   
1070         /// <param name="connection">一个有效的数据库连接对象</param>   
1071         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1072         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
1073         /// <returns>返回结果集中的第一行第一列</returns>   
1074         public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
1075         {
1076             // 执行参数为空的方法   
1077             return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
1078         }
1079 
1080         /// <summary>   
1081         /// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.   
1082         /// </summary>   
1083         /// <remarks>   
1084         /// 示例:    
1085         ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));   
1086         /// </remarks>   
1087         /// <param name="connection">一个有效的数据库连接对象</param>   
1088         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1089         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
1090         /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>   
1091         /// <returns>返回结果集中的第一行第一列</returns>   
1092         public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1093         {
1094             if (connection == null) throw new ArgumentNullException("connection");
1095 
1096             // 创建SqlCommand命令,并进行预处理   
1097             SqlCommand cmd = new SqlCommand();
1098 
1099             bool mustCloseConnection = false;
1100             PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
1101 
1102             // 执行SqlCommand命令,并返回结果.   
1103             object retval = cmd.ExecuteScalar();
1104 
1105             // 清除参数,以便再次使用.   
1106             cmd.Parameters.Clear();
1107 
1108             if (mustCloseConnection)
1109                 connection.Close();
1110 
1111             return retval;
1112         }
1113 
1114         /// <summary>   
1115         /// 执行指定数据库连接对象的命令,指定参数值,返回结果集中的第一行第一列.   
1116         /// </summary>   
1117         /// <remarks>   
1118         /// 此方法不提供访问存储过程输出参数和返回值参数.   
1119         ///   
1120         /// 示例:    
1121         ///  int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);   
1122         /// </remarks>   
1123         /// <param name="connection">一个有效的数据库连接对象</param>   
1124         /// <param name="spName">存储过程名称</param>   
1125         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
1126         /// <returns>返回结果集中的第一行第一列</returns>   
1127         public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
1128         {
1129             if (connection == null) throw new ArgumentNullException("connection");
1130             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1131 
1132             // 如果有参数值   
1133             if ((parameterValues != null) && (parameterValues.Length > 0))
1134             {
1135                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
1136                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1137 
1138                 // 给存储过程参数赋值   
1139                 AssignParameterValues(commandParameters, parameterValues);
1140 
1141                 // 调用重载方法   
1142                 return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
1143             }
1144             else
1145             {
1146                 // 没有参数值   
1147                 return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
1148             }
1149         }
1150 
1151         /// <summary>   
1152         /// 执行指定数据库事务的命令,返回结果集中的第一行第一列.   
1153         /// </summary>   
1154         /// <remarks>   
1155         /// 示例:    
1156         ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");   
1157         /// </remarks>   
1158         /// <param name="transaction">一个有效的连接事务</param>   
1159         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1160         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
1161         /// <returns>返回结果集中的第一行第一列</returns>   
1162         public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
1163         {
1164             // 执行参数为空的方法   
1165             return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
1166         }
1167 
1168         /// <summary>   
1169         /// 执行指定数据库事务的命令,指定参数,返回结果集中的第一行第一列.   
1170         /// </summary>   
1171         /// <remarks>   
1172         /// 示例:    
1173         ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));   
1174         /// </remarks>   
1175         /// <param name="transaction">一个有效的连接事务</param>   
1176         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1177         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
1178         /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>   
1179         /// <returns>返回结果集中的第一行第一列</returns>   
1180         public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1181         {
1182             if (transaction == null) throw new ArgumentNullException("transaction");
1183             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1184 
1185             // 创建SqlCommand命令,并进行预处理   
1186             SqlCommand cmd = new SqlCommand();
1187             bool mustCloseConnection = false;
1188             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
1189 
1190             // 执行SqlCommand命令,并返回结果.   
1191             object retval = cmd.ExecuteScalar();
1192 
1193             // 清除参数,以便再次使用.   
1194             cmd.Parameters.Clear();
1195             return retval;
1196         }
1197 
1198         /// <summary>   
1199         /// 执行指定数据库事务的命令,指定参数值,返回结果集中的第一行第一列.   
1200         /// </summary>   
1201         /// <remarks>   
1202         /// 此方法不提供访问存储过程输出参数和返回值参数.   
1203         ///   
1204         /// 示例:    
1205         ///  int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);   
1206         /// </remarks>   
1207         /// <param name="transaction">一个有效的连接事务</param>   
1208         /// <param name="spName">存储过程名称</param>   
1209         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
1210         /// <returns>返回结果集中的第一行第一列</returns>   
1211         public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
1212         {
1213             if (transaction == null) throw new ArgumentNullException("transaction");
1214             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1215             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1216 
1217             // 如果有参数值   
1218             if ((parameterValues != null) && (parameterValues.Length > 0))
1219             {
1220                 // PPull the parameters for this stored procedure from the parameter cache ()   
1221                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1222 
1223                 // 给存储过程参数赋值   
1224                 AssignParameterValues(commandParameters, parameterValues);
1225 
1226                 // 调用重载方法   
1227                 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
1228             }
1229             else
1230             {
1231                 // 没有参数值   
1232                 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
1233             }
1234         }
1235 
1236         #endregion ExecuteScalar
1237 
1238         #region ExecuteXmlReader XML阅读器
1239         /// <summary>   
1240         /// 执行指定数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回.   
1241         /// </summary>   
1242         /// <remarks>   
1243         /// 示例:    
1244         ///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");   
1245         /// </remarks>   
1246         /// <param name="connection">一个有效的数据库连接对象</param>   
1247         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1248         /// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param>   
1249         /// <returns>返回XmlReader结果集对象.</returns>   
1250         public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
1251         {
1252             // 执行参数为空的方法   
1253             return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
1254         }
1255 
1256         /// <summary>   
1257         /// 执行指定数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数.  
1258         /// </summary>   
1259         /// <remarks>   
1260         /// 示例:    
1261         ///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));   
1262         /// </remarks>   
1263         /// <param name="connection">一个有效的数据库连接对象</param>   
1264         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1265         /// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param>   
1266         /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>   
1267         /// <returns>返回XmlReader结果集对象.</returns>   
1268         public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1269         {
1270             if (connection == null) throw new ArgumentNullException("connection");
1271 
1272             bool mustCloseConnection = false;
1273             // 创建SqlCommand命令,并进行预处理   
1274             SqlCommand cmd = new SqlCommand();
1275             try
1276             {
1277                 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
1278 
1279                 // 执行命令   
1280                 XmlReader retval = cmd.ExecuteXmlReader();
1281 
1282                 // 清除参数,以便再次使用.   
1283                 cmd.Parameters.Clear();
1284 
1285                 return retval;
1286             }
1287             catch
1288             {
1289                 if (mustCloseConnection)
1290                     connection.Close();
1291                 throw;
1292             }
1293         }
1294 
1295         /// <summary>   
1296         /// 执行指定数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数值.   
1297         /// </summary>   
1298         /// <remarks>   
1299         /// 此方法不提供访问存储过程输出参数和返回值参数.   
1300         ///   
1301         /// 示例:    
1302         ///  XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);   
1303         /// </remarks>   
1304         /// <param name="connection">一个有效的数据库连接对象</param>   
1305         /// <param name="spName">存储过程名称 using "FOR XML AUTO"</param>   
1306         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
1307         /// <returns>返回XmlReader结果集对象.</returns>   
1308         public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
1309         {
1310             if (connection == null) throw new ArgumentNullException("connection");
1311             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1312 
1313             // 如果有参数值   
1314             if ((parameterValues != null) && (parameterValues.Length > 0))
1315             {
1316                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
1317                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1318 
1319                 // 给存储过程参数赋值   
1320                 AssignParameterValues(commandParameters, parameterValues);
1321 
1322                 // 调用重载方法   
1323                 return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
1324             }
1325             else
1326             {
1327                 // 没有参数值   
1328                 return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
1329             }
1330         }
1331 
1332         /// <summary>   
1333         /// 执行指定数据库事务的SqlCommand命令,并产生一个XmlReader对象做为结果集返回.   
1334         /// </summary>   
1335         /// <remarks>   
1336         /// 示例:    
1337         ///  XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");   
1338         /// </remarks>   
1339         /// <param name="transaction">一个有效的连接事务</param>   
1340         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1341         /// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param>   
1342         /// <returns>返回XmlReader结果集对象.</returns>   
1343         public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
1344         {
1345             // 执行参数为空的方法   
1346             return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
1347         }
1348 
1349         /// <summary>   
1350         /// 执行指定数据库事务的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数.   
1351         /// </summary>   
1352         /// <remarks>   
1353         /// 示例:    
1354         ///  XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));   
1355         /// </remarks>   
1356         /// <param name="transaction">一个有效的连接事务</param>   
1357         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1358         /// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param>   
1359         /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>   
1360         /// <returns>返回XmlReader结果集对象.</returns>   
1361         public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1362         {
1363             if (transaction == null) throw new ArgumentNullException("transaction");
1364             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1365 
1366             // 创建SqlCommand命令,并进行预处理   
1367             SqlCommand cmd = new SqlCommand();
1368             bool mustCloseConnection = false;
1369             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
1370 
1371             // 执行命令   
1372             XmlReader retval = cmd.ExecuteXmlReader();
1373 
1374             // 清除参数,以便再次使用.   
1375             cmd.Parameters.Clear();
1376             return retval;
1377         }
1378 
1379         /// <summary>   
1380         /// 执行指定数据库事务的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数值.   
1381         /// </summary>   
1382         /// <remarks>   
1383         /// 此方法不提供访问存储过程输出参数和返回值参数.   
1384         ///   
1385         /// 示例:    
1386         ///  XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);   
1387         /// </remarks>   
1388         /// <param name="transaction">一个有效的连接事务</param>   
1389         /// <param name="spName">存储过程名称</param>   
1390         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
1391         /// <returns>返回一个包含结果集的DataSet.</returns>   
1392         public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
1393         {
1394             if (transaction == null) throw new ArgumentNullException("transaction");
1395             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1396             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1397 
1398             // 如果有参数值   
1399             if ((parameterValues != null) && (parameterValues.Length > 0))
1400             {
1401                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
1402                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1403 
1404                 // 给存储过程参数赋值   
1405                 AssignParameterValues(commandParameters, parameterValues);
1406 
1407                 // 调用重载方法   
1408                 return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
1409             }
1410             else
1411             {
1412                 // 没有参数值   
1413                 return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
1414             }
1415         }
1416 
1417         #endregion ExecuteXmlReader 阅读器结束
1418 
1419         #region FillDataset 填充数据集
1420         /// <summary>   
1421         /// 执行指定数据库连接字符串的命令,映射数据表并填充数据集.   
1422         /// </summary>   
1423         /// <remarks>   
1424         /// 示例:    
1425         ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});   
1426         /// </remarks>   
1427         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
1428         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1429         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
1430         /// <param name="dataSet">要填充结果集的DataSet实例</param>   
1431         /// <param name="tableNames">表映射的数据表数组   
1432         /// 用户定义的表名 (可有是实际的表名.)</param>   
1433         public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
1434         {
1435             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1436             if (dataSet == null) throw new ArgumentNullException("dataSet");
1437 
1438             // 创建并打开数据库连接对象,操作完成释放对象.   
1439             using (SqlConnection connection = new SqlConnection(connectionString))
1440             {
1441                 connection.Open();
1442 
1443                 // 调用指定数据库连接字符串重载方法.   
1444                 FillDataset(connection, commandType, commandText, dataSet, tableNames);
1445             }
1446         }
1447 
1448         /// <summary>   
1449         /// 执行指定数据库连接字符串的命令,映射数据表并填充数据集.指定命令参数.   
1450         /// </summary>   
1451         /// <remarks>   
1452         /// 示例:    
1453         ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));   
1454         /// </remarks>   
1455         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
1456         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1457         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
1458         /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>   
1459         /// <param name="dataSet">要填充结果集的DataSet实例</param>   
1460         /// <param name="tableNames">表映射的数据表数组   
1461         /// 用户定义的表名 (可有是实际的表名.)   
1462         /// </param>   
1463         public static void FillDataset(string connectionString, CommandType commandType,
1464             string commandText, DataSet dataSet, string[] tableNames,
1465             params SqlParameter[] commandParameters)
1466         {
1467             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1468             if (dataSet == null) throw new ArgumentNullException("dataSet");
1469             // 创建并打开数据库连接对象,操作完成释放对象.   
1470             using (SqlConnection connection = new SqlConnection(connectionString))
1471             {
1472                 connection.Open();
1473 
1474                 // 调用指定数据库连接字符串重载方法.   
1475                 FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
1476             }
1477         }
1478 
1479         /// <summary>   
1480         /// 执行指定数据库连接字符串的命令,映射数据表并填充数据集,指定存储过程参数值.   
1481         /// </summary>   
1482         /// <remarks>   
1483         /// 此方法不提供访问存储过程输出参数和返回值参数.   
1484         ///   
1485         /// 示例:    
1486         ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24);   
1487         /// </remarks>   
1488         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
1489         /// <param name="spName">存储过程名称</param>   
1490         /// <param name="dataSet">要填充结果集的DataSet实例</param>   
1491         /// <param name="tableNames">表映射的数据表数组   
1492         /// 用户定义的表名 (可有是实际的表名.)   
1493         /// </param>      
1494         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
1495         public static void FillDataset(string connectionString, string spName,
1496             DataSet dataSet, string[] tableNames,
1497             params object[] parameterValues)
1498         {
1499             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1500             if (dataSet == null) throw new ArgumentNullException("dataSet");
1501             // 创建并打开数据库连接对象,操作完成释放对象.   
1502             using (SqlConnection connection = new SqlConnection(connectionString))
1503             {
1504                 connection.Open();
1505 
1506                 // 调用指定数据库连接字符串重载方法.   
1507                 FillDataset(connection, spName, dataSet, tableNames, parameterValues);
1508             }
1509         }
1510 
1511         /// <summary>   
1512         /// 执行指定数据库连接对象的命令,映射数据表并填充数据集.   
1513         /// </summary>   
1514         /// <remarks>   
1515         /// 示例:    
1516         ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});   
1517         /// </remarks>   
1518         /// <param name="connection">一个有效的数据库连接对象</param>   
1519         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1520         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
1521         /// <param name="dataSet">要填充结果集的DataSet实例</param>   
1522         /// <param name="tableNames">表映射的数据表数组   
1523         /// 用户定义的表名 (可有是实际的表名.)   
1524         /// </param>      
1525         public static void FillDataset(SqlConnection connection, CommandType commandType,
1526             string commandText, DataSet dataSet, string[] tableNames)
1527         {
1528             FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
1529         }
1530 
1531         /// <summary>   
1532         /// 执行指定数据库连接对象的命令,映射数据表并填充数据集,指定参数.   
1533         /// </summary>   
1534         /// <remarks>   
1535         /// 示例:    
1536         ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));   
1537         /// </remarks>   
1538         /// <param name="connection">一个有效的数据库连接对象</param>   
1539         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1540         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
1541         /// <param name="dataSet">要填充结果集的DataSet实例</param>   
1542         /// <param name="tableNames">表映射的数据表数组   
1543         /// 用户定义的表名 (可有是实际的表名.)   
1544         /// </param>   
1545         /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>   
1546         public static void FillDataset(SqlConnection connection, CommandType commandType,
1547             string commandText, DataSet dataSet, string[] tableNames,
1548             params SqlParameter[] commandParameters)
1549         {
1550             FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
1551         }
1552 
1553         /// <summary>   
1554         /// 执行指定数据库连接对象的命令,映射数据表并填充数据集,指定存储过程参数值.   
1555         /// </summary>   
1556         /// <remarks>   
1557         /// 此方法不提供访问存储过程输出参数和返回值参数.   
1558         ///   
1559         /// 示例:    
1560         ///  FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36);   
1561         /// </remarks>   
1562         /// <param name="connection">一个有效的数据库连接对象</param>   
1563         /// <param name="spName">存储过程名称</param>   
1564         /// <param name="dataSet">要填充结果集的DataSet实例</param>   
1565         /// <param name="tableNames">表映射的数据表数组   
1566         /// 用户定义的表名 (可有是实际的表名.)   
1567         /// </param>   
1568         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
1569         public static void FillDataset(SqlConnection connection, string spName,
1570             DataSet dataSet, string[] tableNames,
1571             params object[] parameterValues)
1572         {
1573             if (connection == null) throw new ArgumentNullException("connection");
1574             if (dataSet == null) throw new ArgumentNullException("dataSet");
1575             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1576 
1577             // 如果有参数值   
1578             if ((parameterValues != null) && (parameterValues.Length > 0))
1579             {
1580                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
1581                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1582 
1583                 // 给存储过程参数赋值   
1584                 AssignParameterValues(commandParameters, parameterValues);
1585 
1586                 // 调用重载方法   
1587                 FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
1588             }
1589             else
1590             {
1591                 // 没有参数值   
1592                 FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
1593             }
1594         }
1595 
1596         /// <summary>   
1597         /// 执行指定数据库事务的命令,映射数据表并填充数据集.   
1598         /// </summary>   
1599         /// <remarks>   
1600         /// 示例:    
1601         ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});   
1602         /// </remarks>   
1603         /// <param name="transaction">一个有效的连接事务</param>   
1604         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1605         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
1606         /// <param name="dataSet">要填充结果集的DataSet实例</param>   
1607         /// <param name="tableNames">表映射的数据表数组   
1608         /// 用户定义的表名 (可有是实际的表名.)   
1609         /// </param>   
1610         public static void FillDataset(SqlTransaction transaction, CommandType commandType,
1611             string commandText,
1612             DataSet dataSet, string[] tableNames)
1613         {
1614             FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
1615         }
1616 
1617         /// <summary>   
1618         /// 执行指定数据库事务的命令,映射数据表并填充数据集,指定参数.   
1619         /// </summary>   
1620         /// <remarks>   
1621         /// 示例:    
1622         ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));   
1623         /// </remarks>   
1624         /// <param name="transaction">一个有效的连接事务</param>   
1625         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1626         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
1627         /// <param name="dataSet">要填充结果集的DataSet实例</param>   
1628         /// <param name="tableNames">表映射的数据表数组   
1629         /// 用户定义的表名 (可有是实际的表名.)   
1630         /// </param>   
1631         /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>   
1632         public static void FillDataset(SqlTransaction transaction, CommandType commandType,
1633             string commandText, DataSet dataSet, string[] tableNames,
1634             params SqlParameter[] commandParameters)
1635         {
1636             FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
1637         }
1638 
1639         /// <summary>   
1640         /// 执行指定数据库事务的命令,映射数据表并填充数据集,指定存储过程参数值.   
1641         /// </summary>   
1642         /// <remarks>   
1643         /// 此方法不提供访问存储过程输出参数和返回值参数.   
1644         ///   
1645         /// 示例:    
1646         ///  FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36);   
1647         /// </remarks>   
1648         /// <param name="transaction">一个有效的连接事务</param>   
1649         /// <param name="spName">存储过程名称</param>   
1650         /// <param name="dataSet">要填充结果集的DataSet实例</param>   
1651         /// <param name="tableNames">表映射的数据表数组   
1652         /// 用户定义的表名 (可有是实际的表名.)   
1653         /// </param>   
1654         /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>   
1655         public static void FillDataset(SqlTransaction transaction, string spName,
1656             DataSet dataSet, string[] tableNames,
1657             params object[] parameterValues)
1658         {
1659             if (transaction == null) throw new ArgumentNullException("transaction");
1660             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1661             if (dataSet == null) throw new ArgumentNullException("dataSet");
1662             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1663 
1664             // 如果有参数值   
1665             if ((parameterValues != null) && (parameterValues.Length > 0))
1666             {
1667                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
1668                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1669 
1670                 // 给存储过程参数赋值   
1671                 AssignParameterValues(commandParameters, parameterValues);
1672 
1673                 // 调用重载方法   
1674                 FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
1675             }
1676             else
1677             {
1678                 // 没有参数值   
1679                 FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
1680             }
1681         }
1682 
1683         /// <summary>   
1684         /// [私有方法][内部调用]执行指定数据库连接对象/事务的命令,映射数据表并填充数据集,DataSet/TableNames/SqlParameters.   
1685         /// </summary>   
1686         /// <remarks>   
1687         /// 示例:    
1688         ///  FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));   
1689         /// </remarks>   
1690         /// <param name="connection">一个有效的数据库连接对象</param>   
1691         /// <param name="transaction">一个有效的连接事务</param>   
1692         /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>   
1693         /// <param name="commandText">存储过程名称或T-SQL语句</param>   
1694         /// <param name="dataSet">要填充结果集的DataSet实例</param>   
1695         /// <param name="tableNames">表映射的数据表数组   
1696         /// 用户定义的表名 (可有是实际的表名.)   
1697         /// </param>   
1698         /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>   
1699         private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType,
1700             string commandText, DataSet dataSet, string[] tableNames,
1701             params SqlParameter[] commandParameters)
1702         {
1703             if (connection == null) throw new ArgumentNullException("connection");
1704             if (dataSet == null) throw new ArgumentNullException("dataSet");
1705 
1706             // 创建SqlCommand命令,并进行预处理   
1707             SqlCommand command = new SqlCommand();
1708             bool mustCloseConnection = false;
1709             PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
1710 
1711             // 执行命令   
1712             using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
1713             {
1714 
1715                 // 追加表映射   
1716                 if (tableNames != null && tableNames.Length > 0)
1717                 {
1718                     string tableName = "Table";
1719                     for (int index = 0; index < tableNames.Length; index++)
1720                     {
1721                         if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
1722                         dataAdapter.TableMappings.Add(tableName, tableNames[index]);
1723                         tableName += (index + 1).ToString();
1724                     }
1725                 }
1726 
1727                 // 填充数据集使用默认表名称   
1728                 dataAdapter.Fill(dataSet);
1729 
1730                 // 清除参数,以便再次使用.   
1731                 command.Parameters.Clear();
1732             }
1733 
1734             if (mustCloseConnection)
1735                 connection.Close();
1736         }
1737         #endregion
1738 
1739         #region UpdateDataset 更新数据集
1740         /// <summary>   
1741         /// 执行数据集更新到数据库,指定inserted, updated, or deleted命令.   
1742         /// </summary>   
1743         /// <remarks>   
1744         /// 示例:    
1745         ///  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");   
1746         /// </remarks>   
1747         /// <param name="insertCommand">[追加记录]一个有效的T-SQL语句或存储过程</param>   
1748         /// <param name="deleteCommand">[删除记录]一个有效的T-SQL语句或存储过程</param>   
1749         /// <param name="updateCommand">[更新记录]一个有效的T-SQL语句或存储过程</param>   
1750         /// <param name="dataSet">要更新到数据库的DataSet</param>   
1751         /// <param name="tableName">要更新到数据库的DataTable</param>   
1752         public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
1753         {
1754             if (insertCommand == null) throw new ArgumentNullException("insertCommand");
1755             if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
1756             if (updateCommand == null) throw new ArgumentNullException("updateCommand");
1757             if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
1758 
1759             // 创建SqlDataAdapter,当操作完成后释放.   
1760             using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
1761             {
1762                 // 设置数据适配器命令   
1763                 dataAdapter.UpdateCommand = updateCommand;
1764                 dataAdapter.InsertCommand = insertCommand;
1765                 dataAdapter.DeleteCommand = deleteCommand;
1766 
1767                 // 更新数据集改变到数据库   
1768                 dataAdapter.Update(dataSet, tableName);
1769 
1770                 // 提交所有改变到数据集.   
1771                 dataSet.AcceptChanges();
1772             }
1773         }
1774         #endregion
1775 
1776         #region CreateCommand 创建一条SqlCommand命令
1777         /// <summary>   
1778         /// 创建SqlCommand命令,指定数据库连接对象,存储过程名和参数.   
1779         /// </summary>   
1780         /// <remarks>   
1781         /// 示例:    
1782         ///  SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");   
1783         /// </remarks>   
1784         /// <param name="connection">一个有效的数据库连接对象</param>   
1785         /// <param name="spName">存储过程名称</param>   
1786         /// <param name="sourceColumns">源表的列名称数组</param>   
1787         /// <returns>返回SqlCommand命令</returns>   
1788         public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
1789         {
1790             if (connection == null) throw new ArgumentNullException("connection");
1791             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1792 
1793             // 创建命令   
1794             SqlCommand cmd = new SqlCommand(spName, connection);
1795             cmd.CommandType = CommandType.StoredProcedure;
1796 
1797             // 如果有参数值   
1798             if ((sourceColumns != null) && (sourceColumns.Length > 0))
1799             {
1800                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
1801                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1802 
1803                 // 将源表的列到映射到DataSet命令中.   
1804                 for (int index = 0; index < sourceColumns.Length; index++)
1805                     commandParameters[index].SourceColumn = sourceColumns[index];
1806 
1807                 // Attach the discovered parameters to the SqlCommand object   
1808                 AttachParameters(cmd, commandParameters);
1809             }
1810 
1811             return cmd;
1812         }
1813         #endregion
1814 
1815         #region ExecuteNonQueryTypedParams 类型化参数(DataRow)
1816         /// <summary>   
1817         /// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回受影响的行数.   
1818         /// </summary>   
1819         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
1820         /// <param name="spName">存储过程名称</param>   
1821         /// <param name="dataRow">使用DataRow作为参数值</param>   
1822         /// <returns>返回影响的行数</returns>   
1823         public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow)
1824         {
1825             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1826             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1827 
1828             // 如果row有值,存储过程必须初始化.   
1829             if (dataRow != null && dataRow.ItemArray.Length > 0)
1830             {
1831                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
1832                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
1833 
1834                 // 分配参数值   
1835                 AssignParameterValues(commandParameters, dataRow);
1836 
1837                 return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
1838             }
1839             else
1840             {
1841                 return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
1842             }
1843         }
1844 
1845         /// <summary>   
1846         /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回受影响的行数.   
1847         /// </summary>   
1848         /// <param name="connection">一个有效的数据库连接对象</param>   
1849         /// <param name="spName">存储过程名称</param>   
1850         /// <param name="dataRow">使用DataRow作为参数值</param>   
1851         /// <returns>返回影响的行数</returns>   
1852         public static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow)
1853         {
1854             if (connection == null) throw new ArgumentNullException("connection");
1855             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1856 
1857             // 如果row有值,存储过程必须初始化.   
1858             if (dataRow != null && dataRow.ItemArray.Length > 0)
1859             {
1860                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
1861                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1862 
1863                 // 分配参数值   
1864                 AssignParameterValues(commandParameters, dataRow);
1865 
1866                 return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
1867             }
1868             else
1869             {
1870                 return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
1871             }
1872         }
1873 
1874         /// <summary>   
1875         /// 执行指定连接数据库事物的存储过程,使用DataRow做为参数值,返回受影响的行数.   
1876         /// </summary>   
1877         /// <param name="transaction">一个有效的连接事务 object</param>   
1878         /// <param name="spName">存储过程名称</param>   
1879         /// <param name="dataRow">使用DataRow作为参数值</param>   
1880         /// <returns>返回影响的行数</returns>   
1881         public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
1882         {
1883             if (transaction == null) throw new ArgumentNullException("transaction");
1884             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1885             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1886 
1887             // Sf the row has values, the store procedure parameters must be initialized   
1888             if (dataRow != null && dataRow.ItemArray.Length > 0)
1889             {
1890                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
1891                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1892 
1893                 // 分配参数值   
1894                 AssignParameterValues(commandParameters, dataRow);
1895 
1896                 return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
1897             }
1898             else
1899             {
1900                 return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
1901             }
1902         }
1903         #endregion
1904 
1905         #region ExecuteDatasetTypedParams 类型化参数(DataRow)
1906         /// <summary>   
1907         /// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回DataSet.   
1908         /// </summary>   
1909         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
1910         /// <param name="spName">存储过程名称</param>   
1911         /// <param name="dataRow">使用DataRow作为参数值</param>   
1912         /// <returns>返回一个包含结果集的DataSet.</returns>   
1913         public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow)
1914         {
1915             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1916             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1917 
1918             //如果row有值,存储过程必须初始化.   
1919             if (dataRow != null && dataRow.ItemArray.Length > 0)
1920             {
1921                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
1922                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
1923 
1924                 // 分配参数值   
1925                 AssignParameterValues(commandParameters, dataRow);
1926 
1927                 return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
1928             }
1929             else
1930             {
1931                 return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
1932             }
1933         }
1934 
1935         /// <summary>   
1936         /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回DataSet.   
1937         /// </summary>   
1938         /// <param name="connection">一个有效的数据库连接对象</param>   
1939         /// <param name="spName">存储过程名称</param>   
1940         /// <param name="dataRow">使用DataRow作为参数值</param>   
1941         /// <returns>返回一个包含结果集的DataSet.</returns>   
1942         ///   
1943         public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow)
1944         {
1945             if (connection == null) throw new ArgumentNullException("connection");
1946             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1947 
1948             // 如果row有值,存储过程必须初始化.   
1949             if (dataRow != null && dataRow.ItemArray.Length > 0)
1950             {
1951                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
1952                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1953 
1954                 // 分配参数值   
1955                 AssignParameterValues(commandParameters, dataRow);
1956 
1957                 return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
1958             }
1959             else
1960             {
1961                 return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName);
1962             }
1963         }
1964 
1965         /// <summary>   
1966         /// 执行指定连接数据库事务的存储过程,使用DataRow做为参数值,返回DataSet.   
1967         /// </summary>   
1968         /// <param name="transaction">一个有效的连接事务 object</param>   
1969         /// <param name="spName">存储过程名称</param>   
1970         /// <param name="dataRow">使用DataRow作为参数值</param>   
1971         /// <returns>返回一个包含结果集的DataSet.</returns>   
1972         public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
1973         {
1974             if (transaction == null) throw new ArgumentNullException("transaction");
1975             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1976             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1977 
1978             // 如果row有值,存储过程必须初始化.   
1979             if (dataRow != null && dataRow.ItemArray.Length > 0)
1980             {
1981                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
1982                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1983 
1984                 // 分配参数值   
1985                 AssignParameterValues(commandParameters, dataRow);
1986 
1987                 return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
1988             }
1989             else
1990             {
1991                 return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
1992             }
1993         }
1994 
1995         #endregion
1996 
1997         #region ExecuteReaderTypedParams 类型化参数(DataRow)
1998         /// <summary>   
1999         /// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回DataReader.   
2000         /// </summary>   
2001         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
2002         /// <param name="spName">存储过程名称</param>   
2003         /// <param name="dataRow">使用DataRow作为参数值</param>   
2004         /// <returns>返回包含结果集的SqlDataReader</returns>   
2005         public static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow)
2006         {
2007             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2008             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2009 
2010             // 如果row有值,存储过程必须初始化.   
2011             if (dataRow != null && dataRow.ItemArray.Length > 0)
2012             {
2013                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
2014                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
2015 
2016                 // 分配参数值   
2017                 AssignParameterValues(commandParameters, dataRow);
2018 
2019                 return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
2020             }
2021             else
2022             {
2023                 return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
2024             }
2025         }
2026 
2027 
2028         /// <summary>   
2029         /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回DataReader.   
2030         /// </summary>   
2031         /// <param name="connection">一个有效的数据库连接对象</param>   
2032         /// <param name="spName">存储过程名称</param>   
2033         /// <param name="dataRow">使用DataRow作为参数值</param>   
2034         /// <returns>返回包含结果集的SqlDataReader</returns>   
2035         public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
2036         {
2037             if (connection == null) throw new ArgumentNullException("connection");
2038             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2039 
2040             // 如果row有值,存储过程必须初始化.   
2041             if (dataRow != null && dataRow.ItemArray.Length > 0)
2042             {
2043                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
2044                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
2045 
2046                 // 分配参数值   
2047                 AssignParameterValues(commandParameters, dataRow);
2048 
2049                 return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
2050             }
2051             else
2052             {
2053                 return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName);
2054             }
2055         }
2056 
2057         /// <summary>   
2058         /// 执行指定连接数据库事物的存储过程,使用DataRow做为参数值,返回DataReader.   
2059         /// </summary>   
2060         /// <param name="transaction">一个有效的连接事务 object</param>   
2061         /// <param name="spName">存储过程名称</param>   
2062         /// <param name="dataRow">使用DataRow作为参数值</param>   
2063         /// <returns>返回包含结果集的SqlDataReader</returns>   
2064         public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
2065         {
2066             if (transaction == null) throw new ArgumentNullException("transaction");
2067             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
2068             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2069 
2070             // 如果row有值,存储过程必须初始化.   
2071             if (dataRow != null && dataRow.ItemArray.Length > 0)
2072             {
2073                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
2074                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
2075 
2076                 // 分配参数值   
2077                 AssignParameterValues(commandParameters, dataRow);
2078 
2079                 return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
2080             }
2081             else
2082             {
2083                 return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName);
2084             }
2085         }
2086         #endregion
2087 
2088         #region ExecuteScalarTypedParams 类型化参数(DataRow)
2089         /// <summary>   
2090         /// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回结果集中的第一行第一列.   
2091         /// </summary>   
2092         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
2093         /// <param name="spName">存储过程名称</param>   
2094         /// <param name="dataRow">使用DataRow作为参数值</param>   
2095         /// <returns>返回结果集中的第一行第一列</returns>   
2096         public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow)
2097         {
2098             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2099             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2100 
2101             // 如果row有值,存储过程必须初始化.   
2102             if (dataRow != null && dataRow.ItemArray.Length > 0)
2103             {
2104                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
2105                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
2106 
2107                 // 分配参数值   
2108                 AssignParameterValues(commandParameters, dataRow);
2109 
2110                 return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
2111             }
2112             else
2113             {
2114                 return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
2115             }
2116         }
2117 
2118         /// <summary>   
2119         /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回结果集中的第一行第一列.  
2120         /// </summary>   
2121         /// <param name="connection">一个有效的数据库连接对象</param>   
2122         /// <param name="spName">存储过程名称</param>   
2123         /// <param name="dataRow">使用DataRow作为参数值</param>   
2124         /// <returns>返回结果集中的第一行第一列</returns>   
2125         public static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow)
2126         {
2127             if (connection == null) throw new ArgumentNullException("connection");
2128             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2129 
2130             // 如果row有值,存储过程必须初始化.   
2131             if (dataRow != null && dataRow.ItemArray.Length > 0)
2132             {
2133                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
2134                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
2135 
2136                 // 分配参数值   
2137                 AssignParameterValues(commandParameters, dataRow);
2138 
2139                 return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
2140             }
2141             else
2142             {
2143                 return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName);
2144             }
2145         }
2146 
2147         /// <summary>   
2148         /// 执行指定连接数据库事务的存储过程,使用DataRow做为参数值,返回结果集中的第一行第一列.   
2149         /// </summary>   
2150         /// <param name="transaction">一个有效的连接事务 object</param>   
2151         /// <param name="spName">存储过程名称</param>   
2152         /// <param name="dataRow">使用DataRow作为参数值</param>   
2153         /// <returns>返回结果集中的第一行第一列</returns>   
2154         public static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
2155         {
2156             if (transaction == null) throw new ArgumentNullException("transaction");
2157             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
2158             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2159 
2160             // 如果row有值,存储过程必须初始化.   
2161             if (dataRow != null && dataRow.ItemArray.Length > 0)
2162             {
2163                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
2164                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
2165 
2166                 // 分配参数值   
2167                 AssignParameterValues(commandParameters, dataRow);
2168 
2169                 return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
2170             }
2171             else
2172             {
2173                 return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
2174             }
2175         }
2176         #endregion
2177 
2178         #region ExecuteXmlReaderTypedParams 类型化参数(DataRow)
2179         /// <summary>   
2180         /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回XmlReader类型的结果集.   
2181         /// </summary>   
2182         /// <param name="connection">一个有效的数据库连接对象</param>   
2183         /// <param name="spName">存储过程名称</param>   
2184         /// <param name="dataRow">使用DataRow作为参数值</param>   
2185         /// <returns>返回XmlReader结果集对象.</returns>   
2186         public static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
2187         {
2188             if (connection == null) throw new ArgumentNullException("connection");
2189             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2190 
2191             // 如果row有值,存储过程必须初始化.   
2192             if (dataRow != null && dataRow.ItemArray.Length > 0)
2193             {
2194                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
2195                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
2196 
2197                 // 分配参数值   
2198                 AssignParameterValues(commandParameters, dataRow);
2199 
2200                 return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
2201             }
2202             else
2203             {
2204                 return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
2205             }
2206         }
2207 
2208         /// <summary>   
2209         /// 执行指定连接数据库事务的存储过程,使用DataRow做为参数值,返回XmlReader类型的结果集.   
2210         /// </summary>   
2211         /// <param name="transaction">一个有效的连接事务 object</param>   
2212         /// <param name="spName">存储过程名称</param>   
2213         /// <param name="dataRow">使用DataRow作为参数值</param>   
2214         /// <returns>返回XmlReader结果集对象.</returns>   
2215         public static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
2216         {
2217             if (transaction == null) throw new ArgumentNullException("transaction");
2218             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
2219             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2220 
2221             // 如果row有值,存储过程必须初始化.   
2222             if (dataRow != null && dataRow.ItemArray.Length > 0)
2223             {
2224                 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()   
2225                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
2226 
2227                 // 分配参数值   
2228                 AssignParameterValues(commandParameters, dataRow);
2229 
2230                 return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
2231             }
2232             else
2233             {
2234                 return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
2235             }
2236         }
2237         #endregion
2238 
2239     }
2240 
2241     /// <summary>   
2242     /// SqlHelperParameterCache提供缓存存储过程参数,并能够在运行时从存储过程中探索参数.   
2243     /// </summary>   
2244     public sealed class SqlHelperParameterCache
2245     {
2246         #region 私有方法,字段,构造函数
2247         // 私有构造函数,妨止类被实例化.   
2248         private SqlHelperParameterCache() { }
2249 
2250         // 这个方法要注意   
2251         private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
2252 
2253         /// <summary>   
2254         /// 探索运行时的存储过程,返回SqlParameter参数数组.   
2255         /// 初始化参数值为 DBNull.Value.   
2256         /// </summary>   
2257         /// <param name="connection">一个有效的数据库连接</param>   
2258         /// <param name="spName">存储过程名称</param>   
2259         /// <param name="includeReturnValueParameter">是否包含返回值参数</param>   
2260         /// <returns>返回SqlParameter参数数组</returns>   
2261         private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
2262         {
2263             if (connection == null) throw new ArgumentNullException("connection");
2264             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2265 
2266             SqlCommand cmd = new SqlCommand(spName, connection);
2267             cmd.CommandType = CommandType.StoredProcedure;
2268 
2269             connection.Open();
2270             // 检索cmd指定的存储过程的参数信息,并填充到cmd的Parameters参数集中.   
2271             SqlCommandBuilder.DeriveParameters(cmd);
2272             connection.Close();
2273             // 如果不包含返回值参数,将参数集中的每一个参数删除.   
2274             if (!includeReturnValueParameter)
2275             {
2276                 cmd.Parameters.RemoveAt(0);
2277             }
2278 
2279             // 创建参数数组   
2280             SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
2281             // 将cmd的Parameters参数集复制到discoveredParameters数组.   
2282             cmd.Parameters.CopyTo(discoveredParameters, 0);
2283 
2284             // 初始化参数值为 DBNull.Value.   
2285             foreach (SqlParameter discoveredParameter in discoveredParameters)
2286             {
2287                 discoveredParameter.Value = DBNull.Value;
2288             }
2289             return discoveredParameters;
2290         }
2291 
2292         /// <summary>   
2293         /// SqlParameter参数数组的深层拷贝.   
2294         /// </summary>   
2295         /// <param name="originalParameters">原始参数数组</param>   
2296         /// <returns>返回一个同样的参数数组</returns>   
2297         private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
2298         {
2299             SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
2300 
2301             for (int i = 0, j = originalParameters.Length; i < j; i++)
2302             {
2303                 clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
2304             }
2305 
2306             return clonedParameters;
2307         }
2308 
2309         #endregion 私有方法,字段,构造函数结束
2310 
2311         #region 缓存方法
2312 
2313         /// <summary>   
2314         /// 追加参数数组到缓存.   
2315         /// </summary>   
2316         /// <param name="connectionString">一个有效的数据库连接字符串</param>   
2317         /// <param name="commandText">存储过程名或SQL语句</param>   
2318         /// <param name="commandParameters">要缓存的参数数组</param>   
2319         public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
2320         {
2321             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2322             if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
2323 
2324             string hashKey = connectionString + ":" + commandText;
2325 
2326             paramCache[hashKey] = commandParameters;
2327         }
2328 
2329         /// <summary>   
2330         /// 从缓存中获取参数数组.   
2331         /// </summary>   
2332         /// <param name="connectionString">一个有效的数据库连接字符</param>   
2333         /// <param name="commandText">存储过程名或SQL语句</param>   
2334         /// <returns>参数数组</returns>   
2335         public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
2336         {
2337             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2338             if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
2339 
2340             string hashKey = connectionString + ":" + commandText;
2341 
2342             SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
2343             if (cachedParameters == null)
2344             {
2345                 return null;
2346             }
2347             else
2348             {
2349                 return CloneParameters(cachedParameters);
2350             }
2351         }
2352 
2353         #endregion 缓存方法结束
2354 
2355         #region 检索指定的存储过程的参数集
2356 
2357         /// <summary>   
2358         /// 返回指定的存储过程的参数集   
2359         /// </summary>   
2360         /// <remarks>   
2361         /// 这个方法将查询数据库,并将信息存储到缓存.   
2362         /// </remarks>   
2363         /// <param name="connectionString">一个有效的数据库连接字符</param>   
2364         /// <param name="spName">存储过程名</param>   
2365         /// <returns>返回SqlParameter参数数组</returns>   
2366         public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
2367         {
2368             return GetSpParameterSet(connectionString, spName, false);
2369         }
2370 
2371         /// <summary>   
2372         /// 返回指定的存储过程的参数集   
2373         /// </summary>   
2374         /// <remarks>   
2375         /// 这个方法将查询数据库,并将信息存储到缓存.   
2376         /// </remarks>   
2377         /// <param name="connectionString">一个有效的数据库连接字符.</param>   
2378         /// <param name="spName">存储过程名</param>   
2379         /// <param name="includeReturnValueParameter">是否包含返回值参数</param>   
2380         /// <returns>返回SqlParameter参数数组</returns>   
2381         public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
2382         {
2383             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2384             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2385 
2386             using (SqlConnection connection = new SqlConnection(connectionString))
2387             {
2388                 return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
2389             }
2390         }
2391 
2392         /// <summary>   
2393         /// [内部]返回指定的存储过程的参数集(使用连接对象).   
2394         /// </summary>   
2395         /// <remarks>   
2396         /// 这个方法将查询数据库,并将信息存储到缓存.   
2397         /// </remarks>   
2398         /// <param name="connection">一个有效的数据库连接字符</param>   
2399         /// <param name="spName">存储过程名</param>   
2400         /// <returns>返回SqlParameter参数数组</returns>   
2401         internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
2402         {
2403             return GetSpParameterSet(connection, spName, false);
2404         }
2405 
2406         /// <summary>   
2407         /// [内部]返回指定的存储过程的参数集(使用连接对象)   
2408         /// </summary>   
2409         /// <remarks>   
2410         /// 这个方法将查询数据库,并将信息存储到缓存.   
2411         /// </remarks>   
2412         /// <param name="connection">一个有效的数据库连接对象</param>   
2413         /// <param name="spName">存储过程名</param>   
2414         /// <param name="includeReturnValueParameter">   
2415         /// 是否包含返回值参数   
2416         /// </param>   
2417         /// <returns>返回SqlParameter参数数组</returns>   
2418         internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
2419         {
2420             if (connection == null) throw new ArgumentNullException("connection");
2421             using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone())
2422             {
2423                 return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
2424             }
2425         }
2426 
2427         /// <summary>   
2428         /// [私有]返回指定的存储过程的参数集(使用连接对象)   
2429         /// </summary>   
2430         /// <param name="connection">一个有效的数据库连接对象</param>   
2431         /// <param name="spName">存储过程名</param>   
2432         /// <param name="includeReturnValueParameter">是否包含返回值参数</param>   
2433         /// <returns>返回SqlParameter参数数组</returns>   
2434         private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
2435         {
2436             if (connection == null) throw new ArgumentNullException("connection");
2437             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2438 
2439             string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
2440 
2441             SqlParameter[] cachedParameters;
2442 
2443             cachedParameters = paramCache[hashKey] as SqlParameter[];
2444             if (cachedParameters == null)
2445             {
2446                 SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
2447                 paramCache[hashKey] = spParameters;
2448                 cachedParameters = spParameters;
2449             }
2450 
2451             return CloneParameters(cachedParameters);
2452         }
2453 
2454         #endregion 参数集检索结束
2455 
2456     }  
2457 }
View Code

 

posted @ 2017-07-03 18:47  酒香逢  阅读(1751)  评论(0编辑  收藏  举报