微软版的SqlHelper.cs类

一,微软SQLHelper.cs类 中文版:

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

 

二,微软版的SqlHelper.cs类:

   1 // ===============================================================================
   2 // Thanks for Microsoft Data Access Application Block team 
   3 // We use the SqlHelper2.0 in the ClassbaoFramework 
   4 // Xiongzaiqiren.Hero
   5 //================================================================================
   6 
   7 //
   8 // Microsoft Data Access Application Block for .NET
   9 // http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
  10 //
  11 // SQLHelper.cs
  12 //
  13 // This file contains the implementations of the SqlHelper and SqlHelperParameterCache
  14 // classes.
  15 //
  16 // For more information see the Data Access Application Block Implementation Overview. 
  17 // ===============================================================================
  18 // Release history
  19 // VERSION    DESCRIPTION
  20 //   2.0    Added support for FillDataset, UpdateDataset and "Param" helper methods
  21 //
  22 // ===============================================================================
  23 // Copyright (C) 2000-2001 Microsoft Corporation
  24 // All rights reserved.
  25 // THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
  26 // OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
  27 // LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
  28 // FITNESS FOR A PARTICULAR PURPOSE.
  29 // ==============================================================================
  30 
  31 using System;
  32 using System.Data;
  33 using System.Xml;
  34 using System.Data.SqlClient;
  35 using System.Collections;
  36 
  37 namespace Classbao.Data
  38 {
  39     /// <summary>
  40     /// The SqlHelper class is intended to encapsulate high performance, scalable best practices for 
  41     /// common uses of SqlClient
  42     /// </summary>
  43     public sealed partial class SqlHelper
  44     {
  45         //        // decrypt the ConnectionString
  46 
  47 
  48         #region private utility methods & constructors
  49 
  50         // Since this class provides only static methods, make the default constructor private to prevent 
  51         // instances from being created with "new SqlHelper()"
  52         private SqlHelper() { }
  53 
  54         /// <summary>
  55         /// This method is used to attach array of SqlParameters to a SqlCommand.
  56         /// 
  57         /// This method will assign a value of DbNull to any parameter with a direction of
  58         /// InputOutput and a value of null.  
  59         /// 
  60         /// This behavior will prevent default values from being used, but
  61         /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
  62         /// where the user provided no input value.
  63         /// </summary>
  64         /// <param name="command">The command to which the parameters will be added</param>
  65         /// <param name="commandParameters">An array of SqlParameters to be added to command</param>
  66         private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
  67         {
  68             if (command == null) throw new ArgumentNullException("command");
  69             if (commandParameters != null)
  70             {
  71                 foreach (SqlParameter p in commandParameters)
  72                 {
  73                     if (p != null)
  74                     {
  75                         // Check for derived output value with no value assigned
  76                         if ((p.Direction == ParameterDirection.InputOutput ||
  77                             p.Direction == ParameterDirection.Input) &&
  78                             (p.Value == null))
  79                         {
  80                             p.Value = DBNull.Value;
  81                         }
  82                         command.Parameters.Add(p);
  83                     }
  84                 }
  85             }
  86         }
  87 
  88         /// <summary>
  89         /// This method assigns dataRow column values to an array of SqlParameters
  90         /// </summary>
  91         /// <param name="commandParameters">Array of SqlParameters to be assigned values</param>
  92         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
  93         private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
  94         {
  95             if ((commandParameters == null) || (dataRow == null))
  96             {
  97                 // Do nothing if we get no data
  98                 return;
  99             }
 100 
 101             int i = 0;
 102             // Set the parameters values
 103             foreach (SqlParameter commandParameter in commandParameters)
 104             {
 105                 // Check the parameter name
 106                 if (commandParameter.ParameterName == null ||
 107                     commandParameter.ParameterName.Length <= 1)
 108                     throw new Exception(
 109                         string.Format(
 110                             "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
 111                             i, commandParameter.ParameterName));
 112                 if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
 113                     commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
 114                 i++;
 115             }
 116         }
 117 
 118         /// <summary>
 119         /// This method assigns an array of values to an array of SqlParameters
 120         /// </summary>
 121         /// <param name="commandParameters">Array of SqlParameters to be assigned values</param>
 122         /// <param name="parameterValues">Array of objects holding the values to be assigned</param>
 123         private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
 124         {
 125             if ((commandParameters == null) || (parameterValues == null))
 126             {
 127                 // Do nothing if we get no data
 128                 return;
 129             }
 130 
 131             // We must have the same number of values as we pave parameters to put them in
 132             if (commandParameters.Length != parameterValues.Length)
 133             {
 134                 throw new ArgumentException("Parameter count does not match Parameter Value count.");
 135             }
 136 
 137             // Iterate through the SqlParameters, assigning the values from the corresponding position in the 
 138             // value array
 139             for (int i = 0, j = commandParameters.Length; i < j; i++)
 140             {
 141                 // If the current array value derives from IDbDataParameter, then assign its Value property
 142                 if (parameterValues[i] is IDbDataParameter)
 143                 {
 144                     IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
 145                     if (paramInstance.Value == null)
 146                     {
 147                         commandParameters[i].Value = DBNull.Value;
 148                     }
 149                     else
 150                     {
 151                         commandParameters[i].Value = paramInstance.Value;
 152                     }
 153                 }
 154                 else if (parameterValues[i] == null)
 155                 {
 156                     commandParameters[i].Value = DBNull.Value;
 157                 }
 158                 else
 159                 {
 160                     commandParameters[i].Value = parameterValues[i];
 161                 }
 162             }
 163         }
 164 
 165         /// <summary>
 166         /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
 167         /// to the provided command
 168         /// </summary>
 169         /// <param name="command">The SqlCommand to be prepared</param>
 170         /// <param name="connection">A valid SqlConnection, on which to execute this command</param>
 171         /// <param name="transaction">A valid SqlTransaction, or 'null'</param>
 172         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 173         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 174         /// <param name="commandParameters">An array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
 175         /// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
 176         private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
 177         {
 178             if (command == null) throw new ArgumentNullException("command");
 179             if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
 180 
 181             // If the provided connection is not open, we will open it
 182             if (connection.State != ConnectionState.Open)
 183             {
 184                 mustCloseConnection = true;
 185                 connection.Open();
 186             }
 187             else
 188             {
 189                 mustCloseConnection = false;
 190             }
 191 
 192             // Associate the connection with the command
 193             command.Connection = connection;
 194 
 195             // Set the command text (stored procedure name or SQL statement)
 196             command.CommandText = commandText;
 197 
 198             // If we were provided a transaction, assign it
 199             if (transaction != null)
 200             {
 201                 if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 202                 command.Transaction = transaction;
 203             }
 204 
 205             // Set the command type
 206             command.CommandType = commandType;
 207 
 208             // Attach the command parameters if they are provided
 209             if (commandParameters != null)
 210             {
 211                 AttachParameters(command, commandParameters);
 212             }
 213             return;
 214         }
 215 
 216         #endregion private utility methods & constructors
 217 
 218         #region ExecuteNonQuery
 219 
 220         /// <summary>
 221         /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in 
 222         /// the connection string
 223         /// </summary>
 224         /// <remarks>
 225         /// e.g.:  
 226         ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
 227         /// </remarks>
 228         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
 229         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 230         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 231         /// <returns>An int representing the number of rows affected by the command</returns>
 232         public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
 233         {
 234             // Pass through the call providing null for the set of SqlParameters
 235             return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
 236         }
 237 
 238         /// <summary>
 239         /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string 
 240         /// using the provided parameters
 241         /// </summary>
 242         /// <remarks>
 243         /// e.g.:  
 244         ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
 245         /// </remarks>
 246         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
 247         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 248         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 249         /// <param name="timeout">The timeout time of command</param>
 250         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
 251         /// <returns>An int representing the number of rows affected by the command</returns>
 252         public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 253         {
 254             return ExecuteNonQuery(connectionString, commandType, commandText, -1, commandParameters);
 255         }
 256 
 257         /// <summary>
 258         /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string 
 259         /// using the provided parameters
 260         /// </summary>
 261         /// <remarks>
 262         /// e.g.:  
 263         ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
 264         /// </remarks>
 265         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
 266         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 267         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 268         /// <param name="timeout">The timeout time of command</param>
 269         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
 270         /// <returns>An int representing the number of rows affected by the command</returns>
 271         public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, int timeout, params SqlParameter[] commandParameters)
 272         {
 273             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 274 
 275             // Create & open a SqlConnection, and dispose of it after we are done
 276             using (SqlConnection connection = new SqlConnection(connectionString))
 277             {
 278                 connection.Open();
 279 
 280                 // Call the overload that takes a connection in place of the connection string
 281                 return ExecuteNonQuery(connection, commandType, commandText, timeout, commandParameters);
 282             }
 283         }
 284 
 285         /// <summary>
 286         /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in 
 287         /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
 288         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 289         /// </summary>
 290         /// <remarks>
 291         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 292         /// 
 293         /// e.g.:  
 294         ///  int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
 295         /// </remarks>
 296         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
 297         /// <param name="spName">The name of the stored prcedure</param>
 298         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
 299         /// <returns>An int representing the number of rows affected by the command</returns>
 300         public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
 301         {
 302             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 303             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 304 
 305             // If we receive parameter values, we need to figure out where they go
 306             if ((parameterValues != null) && (parameterValues.Length > 0))
 307             {
 308                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
 309                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
 310 
 311                 // Assign the provided values to these parameters based on parameter order
 312                 AssignParameterValues(commandParameters, parameterValues);
 313 
 314                 // Call the overload that takes an array of SqlParameters
 315                 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
 316             }
 317             else
 318             {
 319                 // Otherwise we can just call the SP without params
 320                 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
 321             }
 322         }
 323 
 324         /// <summary>
 325         /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection. 
 326         /// </summary>
 327         /// <remarks>
 328         /// e.g.:  
 329         ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
 330         /// </remarks>
 331         /// <param name="connection">A valid SqlConnection</param>
 332         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 333         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 334         /// <returns>An int representing the number of rows affected by the command</returns>
 335         public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
 336         {
 337             // Pass through the call providing null for the set of SqlParameters
 338             return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
 339         }
 340 
 341         /// <summary>
 342         /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection 
 343         /// using the provided parameters.
 344         /// </summary>
 345         /// <remarks>
 346         /// e.g.:  
 347         ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
 348         /// </remarks>
 349         /// <param name="connection">A valid SqlConnection</param>
 350         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 351         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 352         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
 353         /// <returns>An int representing the number of rows affected by the command</returns>
 354         public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 355         {
 356             return ExecuteNonQuery(connection, commandType, commandText, -1, commandParameters);
 357         }
 358 
 359         /// <summary>
 360         /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection 
 361         /// using the provided parameters.
 362         /// </summary>
 363         /// <remarks>
 364         /// e.g.:  
 365         ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
 366         /// </remarks>
 367         /// <param name="connection">A valid SqlConnection</param>
 368         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 369         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 370         /// <param name="timeout">The timeout time of command</param>
 371         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
 372         /// <returns>An int representing the number of rows affected by the command</returns>
 373         public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, int timeout, params SqlParameter[] commandParameters)
 374         {
 375             if (connection == null) throw new ArgumentNullException("connection");
 376 
 377             // Create a command and prepare it for execution
 378             SqlCommand cmd = new SqlCommand();
 379 
 380             // Setting a timeout value for the SQL command.  Setting timeout to zero means never time out, 
 381             // which we never want to do.  If value passed in is undesired, then simply don't set this parameter
 382             // and let it default to 30 seconds (according to MSDN).
 383             if ((timeout != null) && (timeout >= 0))
 384             {
 385                 cmd.CommandTimeout = timeout;
 386             }
 387 
 388 
 389             bool mustCloseConnection = false;
 390             PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
 391 
 392             // Finally, execute the command
 393             int retval = cmd.ExecuteNonQuery();
 394 
 395             // Detach the SqlParameters from the command object, so they can be used again
 396             cmd.Parameters.Clear();
 397             if (mustCloseConnection)
 398                 connection.Close();
 399             return retval;
 400         }
 401 
 402         /// <summary>
 403         /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection 
 404         /// using the provided parameter values.  This method will query the database to discover the parameters for the 
 405         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 406         /// </summary>
 407         /// <remarks>
 408         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 409         /// 
 410         /// e.g.:  
 411         ///  int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
 412         /// </remarks>
 413         /// <param name="connection">A valid SqlConnection</param>
 414         /// <param name="spName">The name of the stored procedure</param>
 415         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
 416         /// <returns>An int representing the number of rows affected by the command</returns>
 417         public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
 418         {
 419             if (connection == null) throw new ArgumentNullException("connection");
 420             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 421 
 422             // If we receive parameter values, we need to figure out where they go
 423             if ((parameterValues != null) && (parameterValues.Length > 0))
 424             {
 425                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
 426                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 427 
 428                 // Assign the provided values to these parameters based on parameter order
 429                 AssignParameterValues(commandParameters, parameterValues);
 430 
 431                 // Call the overload that takes an array of SqlParameters
 432                 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
 433             }
 434             else
 435             {
 436                 // Otherwise we can just call the SP without params
 437                 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
 438             }
 439         }
 440 
 441         /// <summary>
 442         /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction. 
 443         /// </summary>
 444         /// <remarks>
 445         /// e.g.:  
 446         ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
 447         /// </remarks>
 448         /// <param name="transaction">A valid SqlTransaction</param>
 449         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 450         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 451         /// <returns>An int representing the number of rows affected by the command</returns>
 452         public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
 453         {
 454             // Pass through the call providing null for the set of SqlParameters
 455             return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
 456         }
 457 
 458         /// <summary>
 459         /// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
 460         /// using the provided parameters.
 461         /// </summary>
 462         /// <remarks>
 463         /// e.g.:  
 464         ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
 465         /// </remarks>
 466         /// <param name="transaction">A valid SqlTransaction</param>
 467         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 468         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 469         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
 470         /// <returns>An int representing the number of rows affected by the command</returns>
 471         public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 472         {
 473             if (transaction == null) throw new ArgumentNullException("transaction");
 474             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 475 
 476             // Create a command and prepare it for execution
 477             SqlCommand cmd = new SqlCommand();
 478             bool mustCloseConnection = false;
 479             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
 480 
 481             // Finally, execute the command
 482             int retval = cmd.ExecuteNonQuery();
 483 
 484             // Detach the SqlParameters from the command object, so they can be used again
 485             cmd.Parameters.Clear();
 486             return retval;
 487         }
 488 
 489         /// <summary>
 490         /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified 
 491         /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
 492         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 493         /// </summary>
 494         /// <remarks>
 495         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 496         /// 
 497         /// e.g.:  
 498         ///  int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
 499         /// </remarks>
 500         /// <param name="transaction">A valid SqlTransaction</param>
 501         /// <param name="spName">The name of the stored procedure</param>
 502         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
 503         /// <returns>An int representing the number of rows affected by the command</returns>
 504         public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
 505         {
 506             if (transaction == null) throw new ArgumentNullException("transaction");
 507             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 508             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 509 
 510             // If we receive parameter values, we need to figure out where they go
 511             if ((parameterValues != null) && (parameterValues.Length > 0))
 512             {
 513                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
 514                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 515 
 516                 // Assign the provided values to these parameters based on parameter order
 517                 AssignParameterValues(commandParameters, parameterValues);
 518 
 519                 // Call the overload that takes an array of SqlParameters
 520                 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
 521             }
 522             else
 523             {
 524                 // Otherwise we can just call the SP without params
 525                 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
 526             }
 527         }
 528 
 529         #endregion ExecuteNonQuery
 530 
 531         #region ExecuteDataset
 532 
 533         /// <summary>
 534         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
 535         /// the connection string. 
 536         /// </summary>
 537         /// <remarks>
 538         /// e.g.:  
 539         ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
 540         /// </remarks>
 541         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
 542         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 543         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 544         /// <returns>A dataset containing the resultset generated by the command</returns>
 545         public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
 546         {
 547             // Pass through the call providing null for the set of SqlParameters
 548             return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
 549         }
 550 
 551         /// <summary>
 552         /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
 553         /// using the provided parameters.
 554         /// </summary>
 555         /// <remarks>
 556         /// e.g.:  
 557         ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
 558         /// </remarks>
 559         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
 560         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 561         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 562         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
 563         /// <returns>A dataset containing the resultset generated by the command</returns>
 564         public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 565         {
 566             return ExecuteDataset(connectionString, commandType, commandText, -1, commandParameters);
 567         }
 568 
 569 
 570         /// <summary>
 571         /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
 572         /// using the provided parameters.
 573         /// </summary>
 574         /// <remarks>
 575         /// e.g.:  
 576         ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
 577         /// </remarks>
 578         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
 579         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 580         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 581         /// <param name="timeout">Timeout value for the SQL command in seconds</param>
 582         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
 583         /// <returns>A dataset containing the resultset generated by the command</returns>
 584         public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, int timeout, params SqlParameter[] commandParameters)
 585         {
 586             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 587 
 588             // Create & open a SqlConnection, and dispose of it after we are done
 589             using (SqlConnection connection = new SqlConnection(connectionString))
 590             {
 591                 connection.Open();
 592 
 593                 // Call the overload that takes a connection in place of the connection string
 594                 return ExecuteDataset(connection, commandType, commandText, timeout, commandParameters);
 595             }
 596         }
 597 
 598         /// <summary>
 599         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
 600         /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
 601         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 602         /// </summary>
 603         /// <remarks>
 604         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 605         /// 
 606         /// e.g.:  
 607         ///  DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
 608         /// </remarks>
 609         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
 610         /// <param name="spName">The name of the stored procedure</param>
 611         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
 612         /// <returns>A dataset containing the resultset generated by the command</returns>
 613         public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
 614         {
 615             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 616             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 617 
 618             // If we receive parameter values, we need to figure out where they go
 619             if ((parameterValues != null) && (parameterValues.Length > 0))
 620             {
 621                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
 622                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
 623 
 624                 // Assign the provided values to these parameters based on parameter order
 625                 AssignParameterValues(commandParameters, parameterValues);
 626 
 627                 // Call the overload that takes an array of SqlParameters
 628                 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
 629             }
 630             else
 631             {
 632                 // Otherwise we can just call the SP without params
 633                 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
 634             }
 635         }
 636 
 637         /// <summary>
 638         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
 639         /// </summary>
 640         /// <remarks>
 641         /// e.g.:  
 642         ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
 643         /// </remarks>
 644         /// <param name="connection">A valid SqlConnection</param>
 645         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 646         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 647         /// <returns>A dataset containing the resultset generated by the command</returns>
 648         public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
 649         {
 650             // Pass through the call providing null for the set of SqlParameters
 651             return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
 652         }
 653 
 654         /// <summary>
 655         /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
 656         /// using the provided parameters.
 657         /// </summary>
 658         /// <remarks>
 659         /// e.g.:  
 660         ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
 661         /// </remarks>
 662         /// <param name="connection">A valid SqlConnection</param>
 663         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 664         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 665         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
 666         /// <returns>A dataset containing the resultset generated by the command</returns>
 667         public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 668         {
 669             return ExecuteDataset(connection, commandType, commandText, -1, commandParameters);
 670         }
 671 
 672         /// <summary>
 673         /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
 674         /// using the provided parameters.
 675         /// </summary>
 676         /// <remarks>
 677         /// e.g.:  
 678         ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
 679         /// </remarks>
 680         /// <param name="connection">A valid SqlConnection</param>
 681         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 682         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 683         /// <param name="timeout">Timeout value for the SQL command in seconds</param>
 684         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
 685         /// <returns>A dataset containing the resultset generated by the command</returns>
 686         public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, int timeout, params SqlParameter[] commandParameters)
 687         {
 688             if (connection == null) throw new ArgumentNullException("connection");
 689 
 690             // Create a command and prepare it for execution
 691             SqlCommand cmd = new SqlCommand();
 692 
 693             // Setting a timeout value for the SQL command.  Setting timeout to zero means never time out, 
 694             // which we never want to do.  If value passed in is undesired, then simply don't set this parameter
 695             // and let it default to 30 seconds (according to MSDN).
 696             if ((timeout != null) && (timeout >= 0))
 697             {
 698                 cmd.CommandTimeout = timeout;
 699             }
 700 
 701             bool mustCloseConnection = false;
 702             PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
 703 
 704             // Create the DataAdapter & DataSet
 705             using (SqlDataAdapter da = new SqlDataAdapter(cmd))
 706             {
 707                 DataSet ds = new DataSet();
 708 
 709                 // Fill the DataSet using default values for DataTable names, etc
 710                 da.Fill(ds);
 711 
 712                 // Detach the SqlParameters from the command object, so they can be used again
 713                 cmd.Parameters.Clear();
 714 
 715                 if (mustCloseConnection)
 716                     connection.Close();
 717 
 718                 // Return the dataset
 719                 return ds;
 720             }
 721         }
 722 
 723         /// <summary>
 724         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
 725         /// using the provided parameter values.  This method will query the database to discover the parameters for the 
 726         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 727         /// </summary>
 728         /// <remarks>
 729         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 730         /// 
 731         /// e.g.:  
 732         ///  DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
 733         /// </remarks>
 734         /// <param name="connection">A valid SqlConnection</param>
 735         /// <param name="spName">The name of the stored procedure</param>
 736         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
 737         /// <returns>A dataset containing the resultset generated by the command</returns>
 738         public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
 739         {
 740             if (connection == null) throw new ArgumentNullException("connection");
 741             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 742 
 743             // If we receive parameter values, we need to figure out where they go
 744             if ((parameterValues != null) && (parameterValues.Length > 0))
 745             {
 746                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
 747                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 748 
 749                 // Assign the provided values to these parameters based on parameter order
 750                 AssignParameterValues(commandParameters, parameterValues);
 751 
 752                 // Call the overload that takes an array of SqlParameters
 753                 return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
 754             }
 755             else
 756             {
 757                 // Otherwise we can just call the SP without params
 758                 return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
 759             }
 760         }
 761 
 762         /// <summary>
 763         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. 
 764         /// </summary>
 765         /// <remarks>
 766         /// e.g.:  
 767         ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
 768         /// </remarks>
 769         /// <param name="transaction">A valid SqlTransaction</param>
 770         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 771         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 772         /// <returns>A dataset containing the resultset generated by the command</returns>
 773         public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
 774         {
 775             // Pass through the call providing null for the set of SqlParameters
 776             return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
 777         }
 778 
 779         /// <summary>
 780         /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
 781         /// using the provided parameters.
 782         /// </summary>
 783         /// <remarks>
 784         /// e.g.:  
 785         ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
 786         /// </remarks>
 787         /// <param name="transaction">A valid SqlTransaction</param>
 788         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 789         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 790         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
 791         /// <returns>A dataset containing the resultset generated by the command</returns>
 792         public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 793         {
 794             if (transaction == null) throw new ArgumentNullException("transaction");
 795             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 796 
 797             // Create a command and prepare it for execution
 798             SqlCommand cmd = new SqlCommand();
 799             bool mustCloseConnection = false;
 800             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
 801 
 802             // Create the DataAdapter & DataSet
 803             using (SqlDataAdapter da = new SqlDataAdapter(cmd))
 804             {
 805                 DataSet ds = new DataSet();
 806 
 807                 // Fill the DataSet using default values for DataTable names, etc
 808                 da.Fill(ds);
 809 
 810                 // Detach the SqlParameters from the command object, so they can be used again
 811                 cmd.Parameters.Clear();
 812 
 813                 // Return the dataset
 814                 return ds;
 815             }
 816         }
 817 
 818         /// <summary>
 819         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified 
 820         /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
 821         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 822         /// </summary>
 823         /// <remarks>
 824         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 825         /// 
 826         /// e.g.:  
 827         ///  DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
 828         /// </remarks>
 829         /// <param name="transaction">A valid SqlTransaction</param>
 830         /// <param name="spName">The name of the stored procedure</param>
 831         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
 832         /// <returns>A dataset containing the resultset generated by the command</returns>
 833         public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
 834         {
 835             if (transaction == null) throw new ArgumentNullException("transaction");
 836             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 837             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 838 
 839             // If we receive parameter values, we need to figure out where they go
 840             if ((parameterValues != null) && (parameterValues.Length > 0))
 841             {
 842                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
 843                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 844 
 845                 // Assign the provided values to these parameters based on parameter order
 846                 AssignParameterValues(commandParameters, parameterValues);
 847 
 848                 // Call the overload that takes an array of SqlParameters
 849                 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
 850             }
 851             else
 852             {
 853                 // Otherwise we can just call the SP without params
 854                 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
 855             }
 856         }
 857 
 858         #endregion ExecuteDataset
 859 
 860         #region ExecuteReader
 861 
 862         /// <summary>
 863         /// This enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that
 864         /// we can set the appropriate CommandBehavior when calling ExecuteReader()
 865         /// </summary>
 866         private enum SqlConnectionOwnership
 867         {
 868             /// <summary>Connection is owned and managed by SqlHelper</summary>
 869             Internal,
 870             /// <summary>Connection is owned and managed by the caller</summary>
 871             External
 872         }
 873 
 874         /// <summary>
 875         /// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
 876         /// </summary>
 877         /// <remarks>
 878         /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
 879         /// 
 880         /// If the caller provided the connection, we want to leave it to them to manage.
 881         /// </remarks>
 882         /// <param name="connection">A valid SqlConnection, on which to execute this command</param>
 883         /// <param name="transaction">A valid SqlTransaction, or 'null'</param>
 884         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 885         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 886         /// <param name="commandParameters">An array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
 887         /// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
 888         /// <returns>SqlDataReader containing the results of the command</returns>
 889         private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
 890         {
 891             if (connection == null) throw new ArgumentNullException("connection");
 892 
 893             bool mustCloseConnection = false;
 894             // Create a command and prepare it for execution
 895             SqlCommand cmd = new SqlCommand();
 896             try
 897             {
 898                 PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
 899 
 900                 // Create a reader
 901                 SqlDataReader dataReader;
 902 
 903                 // Call ExecuteReader with the appropriate CommandBehavior
 904                 if (connectionOwnership == SqlConnectionOwnership.External)
 905                 {
 906                     dataReader = cmd.ExecuteReader();
 907                 }
 908                 else
 909                 {
 910                     dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 911                 }
 912 
 913                 // Detach the SqlParameters from the command object, so they can be used again.
 914                 // HACK: There is a problem here, the output parameter values are fletched 
 915                 // when the reader is closed, so if the parameters are detached from the command
 916                 // then the SqlReader can磘 set its values. 
 917                 // When this happen, the parameters can磘 be used again in other command.
 918                 bool canClear = true;
 919                 foreach (SqlParameter commandParameter in cmd.Parameters)
 920                 {
 921                     if (commandParameter.Direction != ParameterDirection.Input)
 922                         canClear = false;
 923                 }
 924 
 925                 if (canClear)
 926                 {
 927                     cmd.Parameters.Clear();
 928                 }
 929 
 930                 return dataReader;
 931             }
 932             catch
 933             {
 934                 if (mustCloseConnection)
 935                     connection.Close();
 936                 throw;
 937             }
 938         }
 939 
 940         /// <summary>
 941         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
 942         /// the connection string. 
 943         /// </summary>
 944         /// <remarks>
 945         /// e.g.:  
 946         ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
 947         /// </remarks>
 948         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
 949         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 950         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 951         /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
 952         public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
 953         {
 954             // Pass through the call providing null for the set of SqlParameters
 955             return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
 956         }
 957 
 958         /// <summary>
 959         /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
 960         /// using the provided parameters.
 961         /// </summary>
 962         /// <remarks>
 963         /// e.g.:  
 964         ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
 965         /// </remarks>
 966         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
 967         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
 968         /// <param name="commandText">The stored procedure name or T-SQL command</param>
 969         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
 970         /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
 971         public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 972         {
 973             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 974             SqlConnection connection = null;
 975             try
 976             {
 977                 connection = new SqlConnection(connectionString);
 978                 connection.Open();
 979 
 980                 // Call the private overload that takes an internally owned connection in place of the connection string
 981                 return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
 982             }
 983             catch
 984             {
 985                 // If we fail to return the SqlDatReader, we need to close the connection ourselves
 986                 if (connection != null) connection.Close();
 987                 throw;
 988             }
 989 
 990         }
 991 
 992         /// <summary>
 993         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
 994         /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
 995         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
 996         /// </summary>
 997         /// <remarks>
 998         /// This method provides no access to output parameters or the stored procedure's return value parameter.
 999         /// 
1000         /// e.g.:  
1001         ///  SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
1002         /// </remarks>
1003         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
1004         /// <param name="spName">The name of the stored procedure</param>
1005         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
1006         /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
1007         public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
1008         {
1009             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1010             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1011 
1012             // If we receive parameter values, we need to figure out where they go
1013             if ((parameterValues != null) && (parameterValues.Length > 0))
1014             {
1015                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
1016 
1017                 AssignParameterValues(commandParameters, parameterValues);
1018 
1019                 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
1020             }
1021             else
1022             {
1023                 // Otherwise we can just call the SP without params
1024                 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
1025             }
1026         }
1027 
1028         /// <summary>
1029         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
1030         /// </summary>
1031         /// <remarks>
1032         /// e.g.:  
1033         ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
1034         /// </remarks>
1035         /// <param name="connection">A valid SqlConnection</param>
1036         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1037         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1038         /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
1039         public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
1040         {
1041             // Pass through the call providing null for the set of SqlParameters
1042             return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
1043         }
1044 
1045         /// <summary>
1046         /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
1047         /// using the provided parameters.
1048         /// </summary>
1049         /// <remarks>
1050         /// e.g.:  
1051         ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
1052         /// </remarks>
1053         /// <param name="connection">A valid SqlConnection</param>
1054         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1055         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1056         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
1057         /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
1058         public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1059         {
1060             // Pass through the call to the private overload using a null transaction value and an externally owned connection
1061             return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
1062         }
1063 
1064         /// <summary>
1065         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
1066         /// using the provided parameter values.  This method will query the database to discover the parameters for the 
1067         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
1068         /// </summary>
1069         /// <remarks>
1070         /// This method provides no access to output parameters or the stored procedure's return value parameter.
1071         /// 
1072         /// e.g.:  
1073         ///  SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
1074         /// </remarks>
1075         /// <param name="connection">A valid SqlConnection</param>
1076         /// <param name="spName">The name of the stored procedure</param>
1077         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
1078         /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
1079         public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
1080         {
1081             if (connection == null) throw new ArgumentNullException("connection");
1082             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1083 
1084             // If we receive parameter values, we need to figure out where they go
1085             if ((parameterValues != null) && (parameterValues.Length > 0))
1086             {
1087                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1088 
1089                 AssignParameterValues(commandParameters, parameterValues);
1090 
1091                 return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
1092             }
1093             else
1094             {
1095                 // Otherwise we can just call the SP without params
1096                 return ExecuteReader(connection, CommandType.StoredProcedure, spName);
1097             }
1098         }
1099 
1100         /// <summary>
1101         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. 
1102         /// </summary>
1103         /// <remarks>
1104         /// e.g.:  
1105         ///  SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
1106         /// </remarks>
1107         /// <param name="transaction">A valid SqlTransaction</param>
1108         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1109         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1110         /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
1111         public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
1112         {
1113             // Pass through the call providing null for the set of SqlParameters
1114             return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
1115         }
1116 
1117         /// <summary>
1118         /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
1119         /// using the provided parameters.
1120         /// </summary>
1121         /// <remarks>
1122         /// e.g.:  
1123         ///   SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
1124         /// </remarks>
1125         /// <param name="transaction">A valid SqlTransaction</param>
1126         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1127         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1128         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
1129         /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
1130         public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1131         {
1132             if (transaction == null) throw new ArgumentNullException("transaction");
1133             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1134 
1135             // Pass through to private overload, indicating that the connection is owned by the caller
1136             return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
1137         }
1138 
1139         /// <summary>
1140         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
1141         /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
1142         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
1143         /// </summary>
1144         /// <remarks>
1145         /// This method provides no access to output parameters or the stored procedure's return value parameter.
1146         /// 
1147         /// e.g.:  
1148         ///  SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
1149         /// </remarks>
1150         /// <param name="transaction">A valid SqlTransaction</param>
1151         /// <param name="spName">The name of the stored procedure</param>
1152         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
1153         /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
1154         public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
1155         {
1156             if (transaction == null) throw new ArgumentNullException("transaction");
1157             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1158             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1159 
1160             // If we receive parameter values, we need to figure out where they go
1161             if ((parameterValues != null) && (parameterValues.Length > 0))
1162             {
1163                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1164 
1165                 AssignParameterValues(commandParameters, parameterValues);
1166 
1167                 return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
1168             }
1169             else
1170             {
1171                 // Otherwise we can just call the SP without params
1172                 return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
1173             }
1174         }
1175 
1176         #endregion ExecuteReader
1177 
1178         #region ExecuteScalar
1179 
1180         /// <summary>
1181         /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in 
1182         /// the connection string. 
1183         /// </summary>
1184         /// <remarks>
1185         /// e.g.:  
1186         ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
1187         /// </remarks>
1188         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
1189         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1190         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1191         /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
1192         public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
1193         {
1194             // Pass through the call providing null for the set of SqlParameters
1195             return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
1196         }
1197 
1198         /// <summary>
1199         /// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string 
1200         /// using the provided parameters.
1201         /// </summary>
1202         /// <remarks>
1203         /// e.g.:  
1204         ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
1205         /// </remarks>
1206         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
1207         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1208         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1209         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
1210         /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
1211         public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1212         {
1213             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1214             // Create & open a SqlConnection, and dispose of it after we are done
1215             using (SqlConnection connection = new SqlConnection(connectionString))
1216             {
1217                 connection.Open();
1218 
1219                 // Call the overload that takes a connection in place of the connection string
1220                 return ExecuteScalar(connection, commandType, commandText, commandParameters);
1221             }
1222         }
1223 
1224         /// <summary>
1225         /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in 
1226         /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
1227         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
1228         /// </summary>
1229         /// <remarks>
1230         /// This method provides no access to output parameters or the stored procedure's return value parameter.
1231         /// 
1232         /// e.g.:  
1233         ///  int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
1234         /// </remarks>
1235         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
1236         /// <param name="spName">The name of the stored procedure</param>
1237         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
1238         /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
1239         public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
1240         {
1241             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1242             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1243 
1244             // If we receive parameter values, we need to figure out where they go
1245             if ((parameterValues != null) && (parameterValues.Length > 0))
1246             {
1247                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
1248                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
1249 
1250                 // Assign the provided values to these parameters based on parameter order
1251                 AssignParameterValues(commandParameters, parameterValues);
1252 
1253                 // Call the overload that takes an array of SqlParameters
1254                 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
1255             }
1256             else
1257             {
1258                 // Otherwise we can just call the SP without params
1259                 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
1260             }
1261         }
1262 
1263         /// <summary>
1264         /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection. 
1265         /// </summary>
1266         /// <remarks>
1267         /// e.g.:  
1268         ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
1269         /// </remarks>
1270         /// <param name="connection">A valid SqlConnection</param>
1271         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1272         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1273         /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
1274         public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
1275         {
1276             // Pass through the call providing null for the set of SqlParameters
1277             return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
1278         }
1279 
1280         /// <summary>
1281         /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 
1282         /// using the provided parameters.
1283         /// </summary>
1284         /// <remarks>
1285         /// e.g.:  
1286         ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
1287         /// </remarks>
1288         /// <param name="connection">A valid SqlConnection</param>
1289         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1290         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1291         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
1292         /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
1293         public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1294         {
1295             if (connection == null) throw new ArgumentNullException("connection");
1296 
1297             // Create a command and prepare it for execution
1298             SqlCommand cmd = new SqlCommand();
1299 
1300             bool mustCloseConnection = false;
1301             PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
1302 
1303             // Execute the command & return the results
1304             object retval = cmd.ExecuteScalar();
1305 
1306             // Detach the SqlParameters from the command object, so they can be used again
1307             cmd.Parameters.Clear();
1308 
1309             if (mustCloseConnection)
1310                 connection.Close();
1311 
1312             return retval;
1313         }
1314 
1315         /// <summary>
1316         /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 
1317         /// using the provided parameter values.  This method will query the database to discover the parameters for the 
1318         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
1319         /// </summary>
1320         /// <remarks>
1321         /// This method provides no access to output parameters or the stored procedure's return value parameter.
1322         /// 
1323         /// e.g.:  
1324         ///  int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
1325         /// </remarks>
1326         /// <param name="connection">A valid SqlConnection</param>
1327         /// <param name="spName">The name of the stored procedure</param>
1328         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
1329         /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
1330         public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
1331         {
1332             if (connection == null) throw new ArgumentNullException("connection");
1333             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1334 
1335             // If we receive parameter values, we need to figure out where they go
1336             if ((parameterValues != null) && (parameterValues.Length > 0))
1337             {
1338                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
1339                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1340 
1341                 // Assign the provided values to these parameters based on parameter order
1342                 AssignParameterValues(commandParameters, parameterValues);
1343 
1344                 // Call the overload that takes an array of SqlParameters
1345                 return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
1346             }
1347             else
1348             {
1349                 // Otherwise we can just call the SP without params
1350                 return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
1351             }
1352         }
1353 
1354         /// <summary>
1355         /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction. 
1356         /// </summary>
1357         /// <remarks>
1358         /// e.g.:  
1359         ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
1360         /// </remarks>
1361         /// <param name="transaction">A valid SqlTransaction</param>
1362         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1363         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1364         /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
1365         public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
1366         {
1367             // Pass through the call providing null for the set of SqlParameters
1368             return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
1369         }
1370 
1371         /// <summary>
1372         /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
1373         /// using the provided parameters.
1374         /// </summary>
1375         /// <remarks>
1376         /// e.g.:  
1377         ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
1378         /// </remarks>
1379         /// <param name="transaction">A valid SqlTransaction</param>
1380         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1381         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1382         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
1383         /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
1384         public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1385         {
1386             if (transaction == null) throw new ArgumentNullException("transaction");
1387             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1388 
1389             // Create a command and prepare it for execution
1390             SqlCommand cmd = new SqlCommand();
1391             bool mustCloseConnection = false;
1392             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
1393 
1394             // Execute the command & return the results
1395             object retval = cmd.ExecuteScalar();
1396 
1397             // Detach the SqlParameters from the command object, so they can be used again
1398             cmd.Parameters.Clear();
1399             return retval;
1400         }
1401 
1402         /// <summary>
1403         /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified
1404         /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
1405         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
1406         /// </summary>
1407         /// <remarks>
1408         /// This method provides no access to output parameters or the stored procedure's return value parameter.
1409         /// 
1410         /// e.g.:  
1411         ///  int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
1412         /// </remarks>
1413         /// <param name="transaction">A valid SqlTransaction</param>
1414         /// <param name="spName">The name of the stored procedure</param>
1415         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
1416         /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
1417         public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
1418         {
1419             if (transaction == null) throw new ArgumentNullException("transaction");
1420             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1421             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1422 
1423             // If we receive parameter values, we need to figure out where they go
1424             if ((parameterValues != null) && (parameterValues.Length > 0))
1425             {
1426                 // PPull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
1427                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1428 
1429                 // Assign the provided values to these parameters based on parameter order
1430                 AssignParameterValues(commandParameters, parameterValues);
1431 
1432                 // Call the overload that takes an array of SqlParameters
1433                 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
1434             }
1435             else
1436             {
1437                 // Otherwise we can just call the SP without params
1438                 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
1439             }
1440         }
1441 
1442         #endregion ExecuteScalar    
1443 
1444         #region ExecuteXmlReader
1445         /// <summary>
1446         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
1447         /// </summary>
1448         /// <remarks>
1449         /// e.g.:  
1450         ///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
1451         /// </remarks>
1452         /// <param name="connection">A valid SqlConnection</param>
1453         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1454         /// <param name="commandText">The stored procedure name or T-SQL command using "FOR XML AUTO"</param>
1455         /// <returns>An XmlReader containing the resultset generated by the command</returns>
1456         public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
1457         {
1458             // Pass through the call providing null for the set of SqlParameters
1459             return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
1460         }
1461 
1462         /// <summary>
1463         /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
1464         /// using the provided parameters.
1465         /// </summary>
1466         /// <remarks>
1467         /// e.g.:  
1468         ///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
1469         /// </remarks>
1470         /// <param name="connection">A valid SqlConnection</param>
1471         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1472         /// <param name="commandText">The stored procedure name or T-SQL command using "FOR XML AUTO"</param>
1473         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
1474         /// <returns>An XmlReader containing the resultset generated by the command</returns>
1475         public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1476         {
1477             if (connection == null) throw new ArgumentNullException("connection");
1478 
1479             bool mustCloseConnection = false;
1480             // Create a command and prepare it for execution
1481             SqlCommand cmd = new SqlCommand();
1482             try
1483             {
1484                 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
1485 
1486                 // Create the DataAdapter & DataSet
1487                 XmlReader retval = cmd.ExecuteXmlReader();
1488 
1489                 // Detach the SqlParameters from the command object, so they can be used again
1490                 cmd.Parameters.Clear();
1491 
1492                 return retval;
1493             }
1494             catch
1495             {
1496                 if (mustCloseConnection)
1497                     connection.Close();
1498                 throw;
1499             }
1500         }
1501 
1502         /// <summary>
1503         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
1504         /// using the provided parameter values.  This method will query the database to discover the parameters for the 
1505         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
1506         /// </summary>
1507         /// <remarks>
1508         /// This method provides no access to output parameters or the stored procedure's return value parameter.
1509         /// 
1510         /// e.g.:  
1511         ///  XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);
1512         /// </remarks>
1513         /// <param name="connection">A valid SqlConnection</param>
1514         /// <param name="spName">The name of the stored procedure using "FOR XML AUTO"</param>
1515         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
1516         /// <returns>An XmlReader containing the resultset generated by the command</returns>
1517         public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
1518         {
1519             if (connection == null) throw new ArgumentNullException("connection");
1520             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1521 
1522             // If we receive parameter values, we need to figure out where they go
1523             if ((parameterValues != null) && (parameterValues.Length > 0))
1524             {
1525                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
1526                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1527 
1528                 // Assign the provided values to these parameters based on parameter order
1529                 AssignParameterValues(commandParameters, parameterValues);
1530 
1531                 // Call the overload that takes an array of SqlParameters
1532                 return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
1533             }
1534             else
1535             {
1536                 // Otherwise we can just call the SP without params
1537                 return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
1538             }
1539         }
1540 
1541         /// <summary>
1542         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. 
1543         /// </summary>
1544         /// <remarks>
1545         /// e.g.:  
1546         ///  XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");
1547         /// </remarks>
1548         /// <param name="transaction">A valid SqlTransaction</param>
1549         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1550         /// <param name="commandText">The stored procedure name or T-SQL command using "FOR XML AUTO"</param>
1551         /// <returns>An XmlReader containing the resultset generated by the command</returns>
1552         public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
1553         {
1554             // Pass through the call providing null for the set of SqlParameters
1555             return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
1556         }
1557 
1558         /// <summary>
1559         /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
1560         /// using the provided parameters.
1561         /// </summary>
1562         /// <remarks>
1563         /// e.g.:  
1564         ///  XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
1565         /// </remarks>
1566         /// <param name="transaction">A valid SqlTransaction</param>
1567         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1568         /// <param name="commandText">The stored procedure name or T-SQL command using "FOR XML AUTO"</param>
1569         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
1570         /// <returns>An XmlReader containing the resultset generated by the command</returns>
1571         public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
1572         {
1573             if (transaction == null) throw new ArgumentNullException("transaction");
1574             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1575 
1576             // Create a command and prepare it for execution
1577             SqlCommand cmd = new SqlCommand();
1578             bool mustCloseConnection = false;
1579             PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
1580 
1581             // Create the DataAdapter & DataSet
1582             XmlReader retval = cmd.ExecuteXmlReader();
1583 
1584             // Detach the SqlParameters from the command object, so they can be used again
1585             cmd.Parameters.Clear();
1586             return retval;
1587         }
1588 
1589         /// <summary>
1590         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified 
1591         /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
1592         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
1593         /// </summary>
1594         /// <remarks>
1595         /// This method provides no access to output parameters or the stored procedure's return value parameter.
1596         /// 
1597         /// e.g.:  
1598         ///  XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);
1599         /// </remarks>
1600         /// <param name="transaction">A valid SqlTransaction</param>
1601         /// <param name="spName">The name of the stored procedure</param>
1602         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
1603         /// <returns>A dataset containing the resultset generated by the command</returns>
1604         public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
1605         {
1606             if (transaction == null) throw new ArgumentNullException("transaction");
1607             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1608             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1609 
1610             // If we receive parameter values, we need to figure out where they go
1611             if ((parameterValues != null) && (parameterValues.Length > 0))
1612             {
1613                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
1614                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1615 
1616                 // Assign the provided values to these parameters based on parameter order
1617                 AssignParameterValues(commandParameters, parameterValues);
1618 
1619                 // Call the overload that takes an array of SqlParameters
1620                 return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
1621             }
1622             else
1623             {
1624                 // Otherwise we can just call the SP without params
1625                 return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
1626             }
1627         }
1628 
1629         #endregion ExecuteXmlReader
1630 
1631         #region FillDataset
1632         /// <summary>
1633         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
1634         /// the connection string. 
1635         /// </summary>
1636         /// <remarks>
1637         /// e.g.:  
1638         ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
1639         /// </remarks>
1640         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
1641         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1642         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1643         /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
1644         /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
1645         /// by a user defined name (probably the actual table name)</param>
1646         public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
1647         {
1648             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1649             if (dataSet == null) throw new ArgumentNullException("dataSet");
1650 
1651             // Create & open a SqlConnection, and dispose of it after we are done
1652             using (SqlConnection connection = new SqlConnection(connectionString))
1653             {
1654                 connection.Open();
1655 
1656                 // Call the overload that takes a connection in place of the connection string
1657                 FillDataset(connection, commandType, commandText, dataSet, tableNames);
1658             }
1659         }
1660 
1661         /// <summary>
1662         /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
1663         /// using the provided parameters.
1664         /// </summary>
1665         /// <remarks>
1666         /// e.g.:  
1667         ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
1668         /// </remarks>
1669         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
1670         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1671         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1672         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
1673         /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
1674         /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
1675         /// by a user defined name (probably the actual table name)
1676         /// </param>
1677         public static void FillDataset(string connectionString, CommandType commandType,
1678             string commandText, DataSet dataSet, string[] tableNames,
1679             params SqlParameter[] commandParameters)
1680         {
1681             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1682             if (dataSet == null) throw new ArgumentNullException("dataSet");
1683             // Create & open a SqlConnection, and dispose of it after we are done
1684             using (SqlConnection connection = new SqlConnection(connectionString))
1685             {
1686                 connection.Open();
1687 
1688                 // Call the overload that takes a connection in place of the connection string
1689                 FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
1690             }
1691         }
1692 
1693         /// <summary>
1694         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
1695         /// the connection string using the provided parameter values.  This method will query the database to discover the parameters for the 
1696         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
1697         /// </summary>
1698         /// <remarks>
1699         /// This method provides no access to output parameters or the stored procedure's return value parameter.
1700         /// 
1701         /// e.g.:  
1702         ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24);
1703         /// </remarks>
1704         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
1705         /// <param name="spName">The name of the stored procedure</param>
1706         /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
1707         /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
1708         /// by a user defined name (probably the actual table name)
1709         /// </param>    
1710         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
1711         public static void FillDataset(string connectionString, string spName,
1712             DataSet dataSet, string[] tableNames,
1713             params object[] parameterValues)
1714         {
1715             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
1716             if (dataSet == null) throw new ArgumentNullException("dataSet");
1717             // Create & open a SqlConnection, and dispose of it after we are done
1718             using (SqlConnection connection = new SqlConnection(connectionString))
1719             {
1720                 connection.Open();
1721 
1722                 // Call the overload that takes a connection in place of the connection string
1723                 FillDataset(connection, spName, dataSet, tableNames, parameterValues);
1724             }
1725         }
1726 
1727         /// <summary>
1728         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
1729         /// </summary>
1730         /// <remarks>
1731         /// e.g.:  
1732         ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
1733         /// </remarks>
1734         /// <param name="connection">A valid SqlConnection</param>
1735         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1736         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1737         /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
1738         /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
1739         /// by a user defined name (probably the actual table name)
1740         /// </param>    
1741         public static void FillDataset(SqlConnection connection, CommandType commandType,
1742             string commandText, DataSet dataSet, string[] tableNames)
1743         {
1744             FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
1745         }
1746 
1747         /// <summary>
1748         /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
1749         /// using the provided parameters.
1750         /// </summary>
1751         /// <remarks>
1752         /// e.g.:  
1753         ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
1754         /// </remarks>
1755         /// <param name="connection">A valid SqlConnection</param>
1756         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1757         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1758         /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
1759         /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
1760         /// by a user defined name (probably the actual table name)
1761         /// </param>
1762         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
1763         public static void FillDataset(SqlConnection connection, CommandType commandType,
1764             string commandText, DataSet dataSet, string[] tableNames,
1765             params SqlParameter[] commandParameters)
1766         {
1767             FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
1768         }
1769 
1770         /// <summary>
1771         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
1772         /// using the provided parameter values.  This method will query the database to discover the parameters for the 
1773         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
1774         /// </summary>
1775         /// <remarks>
1776         /// This method provides no access to output parameters or the stored procedure's return value parameter.
1777         /// 
1778         /// e.g.:  
1779         ///  FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36);
1780         /// </remarks>
1781         /// <param name="connection">A valid SqlConnection</param>
1782         /// <param name="spName">The name of the stored procedure</param>
1783         /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
1784         /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
1785         /// by a user defined name (probably the actual table name)
1786         /// </param>
1787         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
1788         public static void FillDataset(SqlConnection connection, string spName,
1789             DataSet dataSet, string[] tableNames,
1790             params object[] parameterValues)
1791         {
1792             if (connection == null) throw new ArgumentNullException("connection");
1793             if (dataSet == null) throw new ArgumentNullException("dataSet");
1794             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1795 
1796             // If we receive parameter values, we need to figure out where they go
1797             if ((parameterValues != null) && (parameterValues.Length > 0))
1798             {
1799                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
1800                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
1801 
1802                 // Assign the provided values to these parameters based on parameter order
1803                 AssignParameterValues(commandParameters, parameterValues);
1804 
1805                 // Call the overload that takes an array of SqlParameters
1806                 FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
1807             }
1808             else
1809             {
1810                 // Otherwise we can just call the SP without params
1811                 FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
1812             }
1813         }
1814 
1815         /// <summary>
1816         /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. 
1817         /// </summary>
1818         /// <remarks>
1819         /// e.g.:  
1820         ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
1821         /// </remarks>
1822         /// <param name="transaction">A valid SqlTransaction</param>
1823         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1824         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1825         /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
1826         /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
1827         /// by a user defined name (probably the actual table name)
1828         /// </param>
1829         public static void FillDataset(SqlTransaction transaction, CommandType commandType,
1830             string commandText,
1831             DataSet dataSet, string[] tableNames)
1832         {
1833             FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
1834         }
1835 
1836         /// <summary>
1837         /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
1838         /// using the provided parameters.
1839         /// </summary>
1840         /// <remarks>
1841         /// e.g.:  
1842         ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
1843         /// </remarks>
1844         /// <param name="transaction">A valid SqlTransaction</param>
1845         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1846         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1847         /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
1848         /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
1849         /// by a user defined name (probably the actual table name)
1850         /// </param>
1851         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
1852         public static void FillDataset(SqlTransaction transaction, CommandType commandType,
1853             string commandText, DataSet dataSet, string[] tableNames,
1854             params SqlParameter[] commandParameters)
1855         {
1856             FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
1857         }
1858 
1859         /// <summary>
1860         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified 
1861         /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
1862         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
1863         /// </summary>
1864         /// <remarks>
1865         /// This method provides no access to output parameters or the stored procedure's return value parameter.
1866         /// 
1867         /// e.g.:  
1868         ///  FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36);
1869         /// </remarks>
1870         /// <param name="transaction">A valid SqlTransaction</param>
1871         /// <param name="spName">The name of the stored procedure</param>
1872         /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
1873         /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
1874         /// by a user defined name (probably the actual table name)
1875         /// </param>
1876         /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
1877         public static void FillDataset(SqlTransaction transaction, string spName,
1878             DataSet dataSet, string[] tableNames,
1879             params object[] parameterValues)
1880         {
1881             if (transaction == null) throw new ArgumentNullException("transaction");
1882             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
1883             if (dataSet == null) throw new ArgumentNullException("dataSet");
1884             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
1885 
1886             // If we receive parameter values, we need to figure out where they go
1887             if ((parameterValues != null) && (parameterValues.Length > 0))
1888             {
1889                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
1890                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
1891 
1892                 // Assign the provided values to these parameters based on parameter order
1893                 AssignParameterValues(commandParameters, parameterValues);
1894 
1895                 // Call the overload that takes an array of SqlParameters
1896                 FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
1897             }
1898             else
1899             {
1900                 // Otherwise we can just call the SP without params
1901                 FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
1902             }
1903         }
1904 
1905         /// <summary>
1906         /// Private helper method that execute a SqlCommand (that returns a resultset) against the specified SqlTransaction and SqlConnection
1907         /// using the provided parameters.
1908         /// </summary>
1909         /// <remarks>
1910         /// e.g.:  
1911         ///  FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
1912         /// </remarks>
1913         /// <param name="connection">A valid SqlConnection</param>
1914         /// <param name="transaction">A valid SqlTransaction</param>
1915         /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
1916         /// <param name="commandText">The stored procedure name or T-SQL command</param>
1917         /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
1918         /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
1919         /// by a user defined name (probably the actual table name)
1920         /// </param>
1921         /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
1922         private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType,
1923             string commandText, DataSet dataSet, string[] tableNames,
1924             params SqlParameter[] commandParameters)
1925         {
1926             if (connection == null) throw new ArgumentNullException("connection");
1927             if (dataSet == null) throw new ArgumentNullException("dataSet");
1928 
1929             // Create a command and prepare it for execution
1930             SqlCommand command = new SqlCommand();
1931             bool mustCloseConnection = false;
1932             PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
1933 
1934             // Create the DataAdapter & DataSet
1935             using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
1936             {
1937 
1938                 // Add the table mappings specified by the user
1939                 if (tableNames != null && tableNames.Length > 0)
1940                 {
1941                     string tableName = "Table";
1942                     for (int index = 0; index < tableNames.Length; index++)
1943                     {
1944                         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");
1945                         dataAdapter.TableMappings.Add(tableName, tableNames[index]);
1946                         tableName += (index + 1).ToString();
1947                     }
1948                 }
1949 
1950                 // Fill the DataSet using default values for DataTable names, etc
1951                 dataAdapter.Fill(dataSet);
1952 
1953                 // Detach the SqlParameters from the command object, so they can be used again
1954                 command.Parameters.Clear();
1955             }
1956 
1957             if (mustCloseConnection)
1958                 connection.Close();
1959         }
1960         #endregion
1961 
1962         #region UpdateDataset
1963         /// <summary>
1964         /// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
1965         /// </summary>
1966         /// <remarks>
1967         /// e.g.:  
1968         ///  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
1969         /// </remarks>
1970         /// <param name="insertCommand">A valid transact-SQL statement or stored procedure to insert new records into the data source</param>
1971         /// <param name="deleteCommand">A valid transact-SQL statement or stored procedure to delete records from the data source</param>
1972         /// <param name="updateCommand">A valid transact-SQL statement or stored procedure used to update records in the data source</param>
1973         /// <param name="dataSet">The DataSet used to update the data source</param>
1974         /// <param name="tableName">The DataTable used to update the data source.</param>
1975         public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
1976         {
1977             if (insertCommand == null) throw new ArgumentNullException("insertCommand");
1978             if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
1979             if (updateCommand == null) throw new ArgumentNullException("updateCommand");
1980             if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
1981 
1982             // Create a SqlDataAdapter, and dispose of it after we are done
1983             using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
1984             {
1985                 // Set the data adapter commands
1986                 dataAdapter.UpdateCommand = updateCommand;
1987                 dataAdapter.InsertCommand = insertCommand;
1988                 dataAdapter.DeleteCommand = deleteCommand;
1989 
1990                 // Update the dataset changes in the data source
1991                 dataAdapter.Update(dataSet, tableName);
1992 
1993                 // Commit all the changes made to the DataSet
1994                 dataSet.AcceptChanges();
1995             }
1996         }
1997         #endregion
1998 
1999         #region CreateCommand
2000         /// <summary>
2001         /// Simplify the creation of a Sql command object by allowing
2002         /// a stored procedure and optional parameters to be provided
2003         /// </summary>
2004         /// <remarks>
2005         /// e.g.:  
2006         ///  SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
2007         /// </remarks>
2008         /// <param name="connection">A valid SqlConnection object</param>
2009         /// <param name="spName">The name of the stored procedure</param>
2010         /// <param name="sourceColumns">An array of string to be assigned as the source columns of the stored procedure parameters</param>
2011         /// <returns>A valid SqlCommand object</returns>
2012         public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
2013         {
2014             if (connection == null) throw new ArgumentNullException("connection");
2015             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2016 
2017             // Create a SqlCommand
2018             SqlCommand cmd = new SqlCommand(spName, connection);
2019             cmd.CommandType = CommandType.StoredProcedure;
2020 
2021             // If we receive parameter values, we need to figure out where they go
2022             if ((sourceColumns != null) && (sourceColumns.Length > 0))
2023             {
2024                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2025                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
2026 
2027                 // Assign the provided source columns to these parameters based on parameter order
2028                 for (int index = 0; index < sourceColumns.Length; index++)
2029                     commandParameters[index].SourceColumn = sourceColumns[index];
2030 
2031                 // Attach the discovered parameters to the SqlCommand object
2032                 AttachParameters(cmd, commandParameters);
2033             }
2034 
2035             return cmd;
2036         }
2037         #endregion
2038 
2039         #region ExecuteNonQueryTypedParams
2040         /// <summary>
2041         /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in 
2042         /// the connection string using the dataRow column values as the stored procedure's parameters values.
2043         /// This method will query the database to discover the parameters for the 
2044         /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
2045         /// </summary>
2046         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
2047         /// <param name="spName">The name of the stored procedure</param>
2048         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2049         /// <returns>An int representing the number of rows affected by the command</returns>
2050         public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow)
2051         {
2052             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2053             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2054 
2055             // If the row has values, the store procedure parameters must be initialized
2056             if (dataRow != null && dataRow.ItemArray.Length > 0)
2057             {
2058                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2059                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
2060 
2061                 // Set the parameters values
2062                 AssignParameterValues(commandParameters, dataRow);
2063 
2064                 return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
2065             }
2066             else
2067             {
2068                 return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
2069             }
2070         }
2071 
2072         /// <summary>
2073         /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection 
2074         /// using the dataRow column values as the stored procedure's parameters values.  
2075         /// This method will query the database to discover the parameters for the 
2076         /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
2077         /// </summary>
2078         /// <param name="connection">A valid SqlConnection object</param>
2079         /// <param name="spName">The name of the stored procedure</param>
2080         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2081         /// <returns>An int representing the number of rows affected by the command</returns>
2082         public static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow)
2083         {
2084             if (connection == null) throw new ArgumentNullException("connection");
2085             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2086 
2087             // If the row has values, the store procedure parameters must be initialized
2088             if (dataRow != null && dataRow.ItemArray.Length > 0)
2089             {
2090                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2091                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
2092 
2093                 // Set the parameters values
2094                 AssignParameterValues(commandParameters, dataRow);
2095 
2096                 return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
2097             }
2098             else
2099             {
2100                 return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
2101             }
2102         }
2103 
2104         /// <summary>
2105         /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified
2106         /// SqlTransaction using the dataRow column values as the stored procedure's parameters values.
2107         /// This method will query the database to discover the parameters for the 
2108         /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
2109         /// </summary>
2110         /// <param name="transaction">A valid SqlTransaction object</param>
2111         /// <param name="spName">The name of the stored procedure</param>
2112         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2113         /// <returns>An int representing the number of rows affected by the command</returns>
2114         public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
2115         {
2116             if (transaction == null) throw new ArgumentNullException("transaction");
2117             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
2118             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2119 
2120             // Sf the row has values, the store procedure parameters must be initialized
2121             if (dataRow != null && dataRow.ItemArray.Length > 0)
2122             {
2123                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2124                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
2125 
2126                 // Set the parameters values
2127                 AssignParameterValues(commandParameters, dataRow);
2128 
2129                 return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
2130             }
2131             else
2132             {
2133                 return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
2134             }
2135         }
2136         #endregion
2137 
2138         #region ExecuteDatasetTypedParams
2139         /// <summary>
2140         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
2141         /// the connection string using the dataRow column values as the stored procedure's parameters values.
2142         /// This method will query the database to discover the parameters for the 
2143         /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
2144         /// </summary>
2145         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
2146         /// <param name="spName">The name of the stored procedure</param>
2147         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2148         /// <returns>A dataset containing the resultset generated by the command</returns>
2149         public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow)
2150         {
2151             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2152             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2153 
2154             //If the row has values, the store procedure parameters must be initialized
2155             if (dataRow != null && dataRow.ItemArray.Length > 0)
2156             {
2157                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2158                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
2159 
2160                 // Set the parameters values
2161                 AssignParameterValues(commandParameters, dataRow);
2162 
2163                 return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
2164             }
2165             else
2166             {
2167                 return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
2168             }
2169         }
2170 
2171         /// <summary>
2172         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
2173         /// using the dataRow column values as the store procedure's parameters values.
2174         /// This method will query the database to discover the parameters for the 
2175         /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
2176         /// </summary>
2177         /// <param name="connection">A valid SqlConnection object</param>
2178         /// <param name="spName">The name of the stored procedure</param>
2179         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2180         /// <returns>A dataset containing the resultset generated by the command</returns>
2181         public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow)
2182         {
2183             if (connection == null) throw new ArgumentNullException("connection");
2184             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2185 
2186             // If the row has values, the store procedure parameters must be initialized
2187             if (dataRow != null && dataRow.ItemArray.Length > 0)
2188             {
2189                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2190                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
2191 
2192                 // Set the parameters values
2193                 AssignParameterValues(commandParameters, dataRow);
2194 
2195                 return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
2196             }
2197             else
2198             {
2199                 return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName);
2200             }
2201         }
2202 
2203         /// <summary>
2204         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction 
2205         /// using the dataRow column values as the stored procedure's parameters values.
2206         /// This method will query the database to discover the parameters for the 
2207         /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
2208         /// </summary>
2209         /// <param name="transaction">A valid SqlTransaction object</param>
2210         /// <param name="spName">The name of the stored procedure</param>
2211         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2212         /// <returns>A dataset containing the resultset generated by the command</returns>
2213         public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
2214         {
2215             if (transaction == null) throw new ArgumentNullException("transaction");
2216             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
2217             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2218 
2219             // If the row has values, the store procedure parameters must be initialized
2220             if (dataRow != null && dataRow.ItemArray.Length > 0)
2221             {
2222                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2223                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
2224 
2225                 // Set the parameters values
2226                 AssignParameterValues(commandParameters, dataRow);
2227 
2228                 return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
2229             }
2230             else
2231             {
2232                 return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
2233             }
2234         }
2235 
2236         #endregion
2237 
2238         #region ExecuteReaderTypedParams
2239         /// <summary>
2240         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
2241         /// the connection string using the dataRow column values as the stored procedure's parameters values.
2242         /// This method will query the database to discover the parameters for the 
2243         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
2244         /// </summary>
2245         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
2246         /// <param name="spName">The name of the stored procedure</param>
2247         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2248         /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
2249         public static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow)
2250         {
2251             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2252             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2253 
2254             // If the row has values, the store procedure parameters must be initialized
2255             if (dataRow != null && dataRow.ItemArray.Length > 0)
2256             {
2257                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2258                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
2259 
2260                 // Set the parameters values
2261                 AssignParameterValues(commandParameters, dataRow);
2262 
2263                 return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
2264             }
2265             else
2266             {
2267                 return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
2268             }
2269         }
2270 
2271 
2272         /// <summary>
2273         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
2274         /// using the dataRow column values as the stored procedure's parameters values.
2275         /// This method will query the database to discover the parameters for the 
2276         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
2277         /// </summary>
2278         /// <param name="connection">A valid SqlConnection object</param>
2279         /// <param name="spName">The name of the stored procedure</param>
2280         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2281         /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
2282         public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
2283         {
2284             if (connection == null) throw new ArgumentNullException("connection");
2285             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2286 
2287             // If the row has values, the store procedure parameters must be initialized
2288             if (dataRow != null && dataRow.ItemArray.Length > 0)
2289             {
2290                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2291                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
2292 
2293                 // Set the parameters values
2294                 AssignParameterValues(commandParameters, dataRow);
2295 
2296                 return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
2297             }
2298             else
2299             {
2300                 return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName);
2301             }
2302         }
2303 
2304         /// <summary>
2305         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction 
2306         /// using the dataRow column values as the stored procedure's parameters values.
2307         /// This method will query the database to discover the parameters for the 
2308         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
2309         /// </summary>
2310         /// <param name="transaction">A valid SqlTransaction object</param>
2311         /// <param name="spName">The name of the stored procedure</param>
2312         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2313         /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
2314         public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
2315         {
2316             if (transaction == null) throw new ArgumentNullException("transaction");
2317             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
2318             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2319 
2320             // If the row has values, the store procedure parameters must be initialized
2321             if (dataRow != null && dataRow.ItemArray.Length > 0)
2322             {
2323                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2324                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
2325 
2326                 // Set the parameters values
2327                 AssignParameterValues(commandParameters, dataRow);
2328 
2329                 return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
2330             }
2331             else
2332             {
2333                 return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName);
2334             }
2335         }
2336         #endregion
2337 
2338         #region ExecuteScalarTypedParams
2339         /// <summary>
2340         /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in 
2341         /// the connection string using the dataRow column values as the stored procedure's parameters values.
2342         /// This method will query the database to discover the parameters for the 
2343         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
2344         /// </summary>
2345         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
2346         /// <param name="spName">The name of the stored procedure</param>
2347         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2348         /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
2349         public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow)
2350         {
2351             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2352             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2353 
2354             // If the row has values, the store procedure parameters must be initialized
2355             if (dataRow != null && dataRow.ItemArray.Length > 0)
2356             {
2357                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2358                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
2359 
2360                 // Set the parameters values
2361                 AssignParameterValues(commandParameters, dataRow);
2362 
2363                 return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
2364             }
2365             else
2366             {
2367                 return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
2368             }
2369         }
2370 
2371         /// <summary>
2372         /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 
2373         /// using the dataRow column values as the stored procedure's parameters values.
2374         /// This method will query the database to discover the parameters for the 
2375         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
2376         /// </summary>
2377         /// <param name="connection">A valid SqlConnection object</param>
2378         /// <param name="spName">The name of the stored procedure</param>
2379         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2380         /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
2381         public static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow)
2382         {
2383             if (connection == null) throw new ArgumentNullException("connection");
2384             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2385 
2386             // If the row has values, the store procedure parameters must be initialized
2387             if (dataRow != null && dataRow.ItemArray.Length > 0)
2388             {
2389                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2390                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
2391 
2392                 // Set the parameters values
2393                 AssignParameterValues(commandParameters, dataRow);
2394 
2395                 return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
2396             }
2397             else
2398             {
2399                 return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName);
2400             }
2401         }
2402 
2403         /// <summary>
2404         /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
2405         /// using the dataRow column values as the stored procedure's parameters values.
2406         /// This method will query the database to discover the parameters for the 
2407         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
2408         /// </summary>
2409         /// <param name="transaction">A valid SqlTransaction object</param>
2410         /// <param name="spName">The name of the stored procedure</param>
2411         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2412         /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
2413         public static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
2414         {
2415             if (transaction == null) throw new ArgumentNullException("transaction");
2416             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
2417             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2418 
2419             // If the row has values, the store procedure parameters must be initialized
2420             if (dataRow != null && dataRow.ItemArray.Length > 0)
2421             {
2422                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2423                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
2424 
2425                 // Set the parameters values
2426                 AssignParameterValues(commandParameters, dataRow);
2427 
2428                 return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
2429             }
2430             else
2431             {
2432                 return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
2433             }
2434         }
2435         #endregion
2436 
2437         #region ExecuteXmlReaderTypedParams
2438         /// <summary>
2439         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
2440         /// using the dataRow column values as the stored procedure's parameters values.
2441         /// This method will query the database to discover the parameters for the 
2442         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
2443         /// </summary>
2444         /// <param name="connection">A valid SqlConnection object</param>
2445         /// <param name="spName">The name of the stored procedure</param>
2446         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2447         /// <returns>An XmlReader containing the resultset generated by the command</returns>
2448         public static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
2449         {
2450             if (connection == null) throw new ArgumentNullException("connection");
2451             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2452 
2453             // If the row has values, the store procedure parameters must be initialized
2454             if (dataRow != null && dataRow.ItemArray.Length > 0)
2455             {
2456                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2457                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
2458 
2459                 // Set the parameters values
2460                 AssignParameterValues(commandParameters, dataRow);
2461 
2462                 return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
2463             }
2464             else
2465             {
2466                 return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
2467             }
2468         }
2469 
2470         /// <summary>
2471         /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction 
2472         /// using the dataRow column values as the stored procedure's parameters values.
2473         /// This method will query the database to discover the parameters for the 
2474         /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
2475         /// </summary>
2476         /// <param name="transaction">A valid SqlTransaction object</param>
2477         /// <param name="spName">The name of the stored procedure</param>
2478         /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
2479         /// <returns>An XmlReader containing the resultset generated by the command</returns>
2480         public static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
2481         {
2482             if (transaction == null) throw new ArgumentNullException("transaction");
2483             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
2484             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2485 
2486             // If the row has values, the store procedure parameters must be initialized
2487             if (dataRow != null && dataRow.ItemArray.Length > 0)
2488             {
2489                 // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
2490                 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
2491 
2492                 // Set the parameters values
2493                 AssignParameterValues(commandParameters, dataRow);
2494 
2495                 return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
2496             }
2497             else
2498             {
2499                 return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
2500             }
2501         }
2502         #endregion
2503 
2504 
2505     }
2506 
2507     /// <summary>
2508     /// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
2509     /// ability to discover parameters for stored procedures at run-time.
2510     /// </summary>
2511     public sealed class SqlHelperParameterCache
2512     {
2513         #region private methods, variables, and constructors
2514 
2515         //Since this class provides only static methods, make the default constructor private to prevent 
2516         //instances from being created with "new SqlHelperParameterCache()"
2517         private SqlHelperParameterCache() { }
2518 
2519         private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
2520 
2521         /// <summary>
2522         /// Resolve at run time the appropriate set of SqlParameters for a stored procedure
2523         /// </summary>
2524         /// <param name="connection">A valid SqlConnection object</param>
2525         /// <param name="spName">The name of the stored procedure</param>
2526         /// <param name="includeReturnValueParameter">Whether or not to include their return value parameter</param>
2527         /// <returns>The parameter array discovered.</returns>
2528         private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
2529         {
2530             if (connection == null) throw new ArgumentNullException("connection");
2531             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2532 
2533             SqlCommand cmd = new SqlCommand(spName, connection);
2534             cmd.CommandType = CommandType.StoredProcedure;
2535 
2536             connection.Open();
2537             SqlCommandBuilder.DeriveParameters(cmd);
2538             connection.Close();
2539 
2540             if (!includeReturnValueParameter)
2541             {
2542                 cmd.Parameters.RemoveAt(0);
2543             }
2544 
2545             SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
2546 
2547             cmd.Parameters.CopyTo(discoveredParameters, 0);
2548 
2549             // Init the parameters with a DBNull value
2550             foreach (SqlParameter discoveredParameter in discoveredParameters)
2551             {
2552                 discoveredParameter.Value = DBNull.Value;
2553             }
2554             return discoveredParameters;
2555         }
2556 
2557         /// <summary>
2558         /// Deep copy of cached SqlParameter array
2559         /// </summary>
2560         /// <param name="originalParameters"></param>
2561         /// <returns></returns>
2562         private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
2563         {
2564             SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
2565 
2566             for (int i = 0, j = originalParameters.Length; i < j; i++)
2567             {
2568                 clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
2569             }
2570 
2571             return clonedParameters;
2572         }
2573 
2574         #endregion private methods, variables, and constructors
2575 
2576         #region caching functions
2577 
2578         /// <summary>
2579         /// Add parameter array to the cache
2580         /// </summary>
2581         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
2582         /// <param name="commandText">The stored procedure name or T-SQL command</param>
2583         /// <param name="commandParameters">An array of SqlParamters to be cached</param>
2584         public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
2585         {
2586             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2587             if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
2588 
2589             string hashKey = connectionString + ":" + commandText;
2590 
2591             paramCache[hashKey] = commandParameters;
2592         }
2593 
2594         /// <summary>
2595         /// Retrieve a parameter array from the cache
2596         /// </summary>
2597         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
2598         /// <param name="commandText">The stored procedure name or T-SQL command</param>
2599         /// <returns>An array of SqlParamters</returns>
2600         public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
2601         {
2602             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2603             if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
2604 
2605             string hashKey = connectionString + ":" + commandText;
2606 
2607             SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
2608             if (cachedParameters == null)
2609             {
2610                 return null;
2611             }
2612             else
2613             {
2614                 return CloneParameters(cachedParameters);
2615             }
2616         }
2617 
2618         #endregion caching functions
2619 
2620         #region Parameter Discovery Functions
2621 
2622         /// <summary>
2623         /// Retrieves the set of SqlParameters appropriate for the stored procedure
2624         /// </summary>
2625         /// <remarks>
2626         /// This method will query the database for this information, and then store it in a cache for future requests.
2627         /// </remarks>
2628         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
2629         /// <param name="spName">The name of the stored procedure</param>
2630         /// <returns>An array of SqlParameters</returns>
2631         public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
2632         {
2633             return GetSpParameterSet(connectionString, spName, false);
2634         }
2635 
2636         /// <summary>
2637         /// Retrieves the set of SqlParameters appropriate for the stored procedure
2638         /// </summary>
2639         /// <remarks>
2640         /// This method will query the database for this information, and then store it in a cache for future requests.
2641         /// </remarks>
2642         /// <param name="connectionString">A valid connection string for a SqlConnection</param>
2643         /// <param name="spName">The name of the stored procedure</param>
2644         /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
2645         /// <returns>An array of SqlParameters</returns>
2646         public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
2647         {
2648             if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
2649             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2650 
2651             using (SqlConnection connection = new SqlConnection(connectionString))
2652             {
2653                 return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
2654             }
2655         }
2656 
2657         /// <summary>
2658         /// Retrieves the set of SqlParameters appropriate for the stored procedure
2659         /// </summary>
2660         /// <remarks>
2661         /// This method will query the database for this information, and then store it in a cache for future requests.
2662         /// </remarks>
2663         /// <param name="connection">A valid SqlConnection object</param>
2664         /// <param name="spName">The name of the stored procedure</param>
2665         /// <returns>An array of SqlParameters</returns>
2666         internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
2667         {
2668             return GetSpParameterSet(connection, spName, false);
2669         }
2670 
2671         /// <summary>
2672         /// Retrieves the set of SqlParameters appropriate for the stored procedure
2673         /// </summary>
2674         /// <remarks>
2675         /// This method will query the database for this information, and then store it in a cache for future requests.
2676         /// </remarks>
2677         /// <param name="connection">A valid SqlConnection object</param>
2678         /// <param name="spName">The name of the stored procedure</param>
2679         /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
2680         /// <returns>An array of SqlParameters</returns>
2681         internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
2682         {
2683             if (connection == null) throw new ArgumentNullException("connection");
2684             using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone())
2685             {
2686                 return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
2687             }
2688         }
2689 
2690         /// <summary>
2691         /// Retrieves the set of SqlParameters appropriate for the stored procedure
2692         /// </summary>
2693         /// <param name="connection">A valid SqlConnection object</param>
2694         /// <param name="spName">The name of the stored procedure</param>
2695         /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
2696         /// <returns>An array of SqlParameters</returns>
2697         private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
2698         {
2699             if (connection == null) throw new ArgumentNullException("connection");
2700             if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
2701 
2702             string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
2703 
2704             SqlParameter[] cachedParameters;
2705 
2706             cachedParameters = paramCache[hashKey] as SqlParameter[];
2707             if (cachedParameters == null)
2708             {
2709                 SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
2710                 paramCache[hashKey] = spParameters;
2711                 cachedParameters = spParameters;
2712             }
2713 
2714             return CloneParameters(cachedParameters);
2715         }
2716 
2717         #endregion Parameter Discovery Functions
2718 
2719     }
2720     
2721 }

 

三,SqlHelper类扩展,及Access支持:

  1 using System.Data.Common;
  2 
  3 namespace Classbao.Data
  4 {
  5     public sealed partial class SqlHelper
  6     {
  7 
  8         #region perfect SqlHelper
  9         private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
 10         {
 11             if (command == null) throw new ArgumentNullException("command");
 12             if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
 13 
 14             command.Connection = connection;
 15             command.CommandText = commandText;
 16             command.CommandType = commandType;
 17             if (transaction != null)
 18             {
 19                 if (transaction.Connection == null)
 20                     throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 21                 command.Transaction = transaction;
 22                 command.Connection = transaction.Connection;
 23             }
 24             AttachParameters(command, commandParameters);
 25         }
 26 
 27         public static object ExecuteScalar(SqlCommand command, CommandType commandType, string commandText)
 28         {
 29             return ExecuteReader(command, commandType, commandText, (SqlParameter[])null);
 30         }
 31 
 32         public static object ExecuteScalar(SqlCommand command, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 33         {
 34             if (command == null) throw new ArgumentNullException("SqlCommand");
 35             SqlCommand cmd = new SqlCommand();
 36             PrepareCommand(cmd, command.Connection, command.Transaction, commandType, commandText, commandParameters);
 37             object result = DataAccessHelper.ExecuteScalar(cmd);
 38             cmd.Parameters.Clear();
 39             return result;
 40         }
 41 
 42         public static SqlDataReader ExecuteReader(SqlCommand command, CommandType commandType, string commandText)
 43         {
 44             return ExecuteReader(command, commandType, commandText, (SqlParameter[])null);
 45         }
 46         public static SqlDataReader ExecuteReader(SqlCommand command, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 47         {
 48             if (command == null) throw new ArgumentNullException("SqlCommand");
 49             SqlCommand cmd = new SqlCommand();
 50             PrepareCommand(cmd, command.Connection, command.Transaction, commandType, commandText, commandParameters);
 51             SqlDataReader result = (SqlDataReader)DataAccessHelper.ExecuteReader(cmd);
 52             bool canClear = true;
 53             foreach (SqlParameter commandParameter in cmd.Parameters)
 54                 if (commandParameter.Direction != ParameterDirection.Input)
 55                 {
 56                     canClear = false;
 57                     break;
 58                 }
 59 
 60             if (canClear)
 61                 cmd.Parameters.Clear();
 62             return result;
 63         }
 64         public static int ExecuteNonQuery(SqlCommand command, CommandType commandType, string commandText)
 65         {
 66             return ExecuteNonQuery(command, commandType, commandText, (SqlParameter[])null);
 67         }
 68         public static int ExecuteNonQuery(SqlCommand command, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 69         {
 70             if (command == null) throw new ArgumentNullException("SqlCommand");
 71             SqlCommand cmd = new SqlCommand();
 72             cmd.CommandTimeout = command.CommandTimeout;
 73             PrepareCommand(cmd, command.Connection, command.Transaction, commandType, commandText, commandParameters);
 74             int retval = DataAccessHelper.ExecuteNonQuery(cmd);
 75             cmd.Parameters.Clear();
 76             return retval;
 77         }
 78 
 79         public static DataSet ExecuteDataset(SqlCommand command, CommandType commandType, string commandText)
 80         {
 81             // Pass through the call providing null for the set of SqlParameters
 82             return ExecuteDataset(command, commandType, commandText, (SqlParameter[])null);
 83         }
 84 
 85         public static DataSet ExecuteDataset(SqlCommand command, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
 86         {
 87             if (command == null) throw new ArgumentNullException("SqlCommand");
 88 
 89             // Create a command and prepare it for execution
 90             SqlCommand cmd = new SqlCommand();
 91             cmd.CommandTimeout = 600;
 92             PrepareCommand(cmd, command.Connection, command.Transaction, commandType, commandText, commandParameters);
 93             using (SqlDataAdapter da = new SqlDataAdapter(cmd))
 94             {
 95                 DataSet result = new DataSet();
 96                 DataAccessHelper.Fill(da, result);
 97                 cmd.Parameters.Clear();
 98                 return result;
 99             }
100 
101         }
102 
103         #endregion
104 
105         #region DataAccessHelper
106         class DataAccessHelper
107         {
108 
109             static void ExceptionHandler(SystemException e)
110             {
111                 throw (new Exception(e.Message, e));
112             }
113             public static int ExecuteNonQuery(DbCommand command)
114             {
115 
116                 bool isNeedClose = OpenConnection(command.Connection);
117                 try
118                 {
119                     return command.ExecuteNonQuery();
120                 }
121                 catch (SystemException e)
122                 {
123                     ExceptionHandler(e);
124                     return 0;
125                 }
126                 finally
127                 {
128                     if (isNeedClose)
129                         CloseConnection(command.Connection);
130                 }
131             }
132             public static object ExecuteScalar(DbCommand command)
133             {
134                 bool isNeedClose = OpenConnection(command.Connection);
135                 try
136                 {
137                     return command.ExecuteScalar();
138                 }
139                 catch (SystemException e)
140                 {
141                     ExceptionHandler(e);
142                     return null;
143                 }
144                 finally
145                 {
146                     if (isNeedClose)
147                         CloseConnection(command.Connection);
148                 }
149             }
150             public static void Fill(DbDataAdapter dataAdapter, DataSet set)
151             {
152                 dataAdapter.Fill(set);
153             }
154             public static DbDataReader ExecuteReader(DbCommand command)
155             {
156 
157                 bool isNeedClose = OpenConnection(command.Connection);
158                 DbDataReader result = null;
159                 try
160                 {
161                     if (isNeedClose)
162                         result = command.ExecuteReader(CommandBehavior.CloseConnection);
163                     else
164                         result = command.ExecuteReader();
165                 }
166                 catch (SystemException e)
167                 {
168                     ExceptionHandler(e);
169                     if (isNeedClose)
170                         CloseConnection(command.Connection);
171                 }
172                 return result;
173             }
174             public static bool OpenConnection(DbConnection connection)
175             {
176                 if (connection.State == ConnectionState.Closed)
177                 {
178                     connection.Open();
179                     return true;
180                 }
181                 return false;
182             }
183             public static bool CloseConnection(DbConnection connection)
184             {
185                 bool result = connection.State == ConnectionState.Open;
186                 connection.Close();
187                 return result;
188             }
189 
190         }
191 
192 
193         #endregion
194     }
195 }

 

posted @ 2016-03-11 15:41  熊仔其人  阅读(1726)  评论(1编辑  收藏  举报