【转】SqlHelper(带详细中文注释)

时间:2021-11-22 22:15:19
   1 using System;
   2 using System.Data;
   3 using System.Xml;
   4 using System.Data.SqlClient;
   5 using System.Collections;
   6 
   7 namespace DBUtility
   8 {
   9     /// <summary>
  10     /// SqlServer数据访问帮助类
  11     /// </summary>
  12     public sealed 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 }