• C# SqlHelper类的数据库操作


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

    1.添加名为SqlHelper的类,将以上代码复制进去。

    2.添加引用

        using System.Data;
        using System.Data.SqlClient;
        using System.Configuration;
        using System.Xml;

    3.添加链接字符串

        打开App.config增加以下代码:

        <connectionStrings>
            <add name="ConStr" connectionString="server=.;database=test;uid=sa;pwd=123456"/>
        </connectionStrings>

    4.数据库连接以及增删改查操作

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace SqlhelperTest
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                //连接数据库
                SqlConnection con = SqlHelper.GetConnection();
                Console.WriteLine("数据库连接成功");
    
                //建立一张表
                SqlHelper.ExecuteNonQuery(con, CommandType.Text, "create table student(id int primary key,name varchar(20),age int)");
                Console.WriteLine("建表成功");
    
                //插入数据
                SqlHelper.ExecuteNonQuery(con, CommandType.Text, "insert into student values(1,'Ghazi',21)");
                SqlHelper.ExecuteNonQuery(con, CommandType.Text, "insert into student values(2,'Jack',20)");
                Console.WriteLine("数据插入成功");
    
                //查询
                DataSet ds = SqlHelper.ExecuteDataset(con, CommandType.Text, "select* from student");
                foreach (DataRow col in ds.Tables[0].Rows) 
                {
                    for (int i = 0; i < 3; i++)
                    {
                        Console.WriteLine(col[i].ToString());
                    }
                } 
    
                //修改
                SqlHelper.ExecuteNonQuery(con, CommandType.Text, "update student set age=22 where id=2");
                Console.WriteLine("数据修改成功");
    
                DataSet ds1 = SqlHelper.ExecuteDataset(con, CommandType.Text, "select* from student");
                foreach (DataRow col in ds1.Tables[0].Rows)
                {
                    for (int i = 0; i < 3; i++)
                    {
                        Console.WriteLine(col[i].ToString());
                    }
                }
    
                //删除
                SqlHelper.ExecuteNonQuery(con, CommandType.Text, "delete from student where id=2");
                Console.WriteLine("数据删除成功");
    
                DataSet ds2 = SqlHelper.ExecuteDataset(con, CommandType.Text, "select* from student");
                foreach (DataRow col in ds2.Tables[0].Rows)
                {
                    for (int i = 0; i < 3; i++)
                    {
                        Console.WriteLine(col[i].ToString());
                    }
                }
            }
        }
    }

    5.相关知识

    DataSet类详解  https://msdn.microsoft.com/zh-cn/library/system.data.dataset.aspx

             http://www.cnblogs.com/losesea/archive/2012/12/08/2809142.html

    常用SQL语句     http://www.cnblogs.com/buzaixian/archive/2009/09/21/1571365.html

             

  • 相关阅读:
    第一章嵌入式系统基础1.5嵌入式系统性能评价
    第一章嵌入式系统基础1.4
    1.3嵌入式系统的硬件基础
    学习第二天
    经典的删除字符串中指定的字符
    字符串的两种表示
    股票数据定向爬虫
    淘宝商品比价定向爬虫
    中国大学排名定向爬虫
    python小练习 批量修改文件名
  • 原文地址:https://www.cnblogs.com/Ghazi/p/5800323.html
Copyright © 2020-2023  润新知