易网时代-编程资源站
易网时代-编程资源站 数据库操作类真的没有必要自己去写，因为成熟的类库真的非常完善了，拿来直接用就好，省时省力。
本文就为大家介绍微软官方的程序PetShop4.0中的SqlHelper类，先来做一下简单的介绍，PetShop是一个范例，微软用它来展示.Net企业系统开发的能力。
那SqlHelper中封装了哪些方法呢？
里面的函数一堆，常用的就那几个，无非就是增删改查嘛，来看下几种常用的函数：
1.ExecuteNonQuery 执行增删改
2.ExecuteReader 执行查询
3.ExecuteScalar 返回首行首列
使用方法介绍
Web.config配置
<connectionStrings><add name="ConnectionString" connectionString="server=127.0.0.1;uid=sa;pwd=ok;database=PetShop;Max Pool Size =512; Min Pool Size=0; Connection Lifetime = 300;packet size=1000;" providerName="System.Data.SqlClient" /></connectionStrings>
调用函数的写法
sql = "UPDATE Student set Name = @Name WHERE Id = @Id";SqlHelper.ExecuteNonQuery（CommandType.Text, sql, new SqlParameter[]{ new SqlParameter（"@Name", name）, new SqlParameter（"@Id", id）}）;这样调用就比较简化，而且比较灵活
源码呈上
/// <summary>/// The SqlHelper class is intended to encapsulate high performance, /// scalable best practices for common uses of SqlClient./// </summary>public abstract class SqlHelper{//数据库连接字符串public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;#region 私有函数和方法/// <summary>/// This method is used to attach array of SqlParameters to a SqlCommand./// /// This method will assign a value of DbNull to any parameter with a direction of/// InputOutput and a value of null. /// /// This behavior will prevent default values from being used, but/// this will be the less common case than an intended pure output parameter （derived as InputOutput）/// where the user provided no input value./// </summary>/// <param name="command">The command to which the parameters will be added</param>/// <param name="commandParameters">An array of SqlParameters to be added to command</param>private static void AttachParameters（SqlCommand command, SqlParameter[] commandParameters）{if （command == null） throw new ArgumentNullException（"command"）;if （commandParameters ！= null）{foreach （SqlParameter p in commandParameters）{if （p ！= null）{// Check for derived output value with no value assignedif （（p.Direction == ParameterDirection.InputOutput ||p.Direction == ParameterDirection.Input） &&（p.Value == null））{p.Value = DBNull.Value;}command.Parameters.Add（p）;}}}}/// <summary>/// This method assigns dataRow column values to an array of SqlParameters/// </summary>/// <param name="commandParameters">Array of SqlParameters to be assigned values</param>/// <param name="dataRow">The dataRow used to hold the stored procedure"s parameter values</param>private static void AssignParameterValues（SqlParameter[] commandParameters, DataRow dataRow）{if （（commandParameters == null） || （dataRow == null））{// Do nothing if we get no datareturn;}int i = 0;// Set the parameters valuesforeach （SqlParameter commandParameter in commandParameters）{// Check the parameter nameif （commandParameter.ParameterName == null ||commandParameter.ParameterName.Length <= 1）throw new Exception（string.Format（"Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: "{1}".",i, commandParameter.ParameterName））;if （dataRow.Table.Columns.IndexOf（commandParameter.ParameterName.Substring（1）） ！= -1）commandParameter.Value = dataRow[commandParameter.ParameterName.Substring（1）];i++;}}/// <summary>/// This method assigns an array of values to an array of SqlParameters/// </summary>/// <param name="commandParameters">Array of SqlParameters to be assigned values</param>/// <param name="parameterValues">Array of objects holding the values to be assigned</param>private static void AssignParameterValues（SqlParameter[] commandParameters, object[] parameterValues）{if （（commandParameters == null） || （parameterValues == null））{// Do nothing if we get no datareturn;}// We must have the same number of values as we pave parameters to put them inif （commandParameters.Length ！= parameterValues.Length）{throw new ArgumentException（"Parameter count does not match Parameter Value count."）;}// Iterate through the SqlParameters, assigning the values from the corresponding position in the // value arrayfor （int i = 0, j = commandParameters.Length; i < j; i++）{// If the current array value derives from IDbDataParameter, then assign its Value propertyif （parameterValues[i] is IDbDataParameter）{IDbDataParameter paramInstance = （IDbDataParameter）parameterValues[i];if （paramInstance.Value == null）{commandParameters[i].Value = DBNull.Value;}else{commandParameters[i].Value = paramInstance.Value;}}else if （parameterValues[i] == null）{commandParameters[i].Value = DBNull.Value;}else{commandParameters[i].Value = parameterValues[i];}}}/// <summary>/// This method opens （if necessary） and assigns a connection, transaction, command type and parameters /// to the provided command/// </summary>/// <param name="command">The SqlCommand to be prepared</param>/// <param name="connection">A valid SqlConnection, on which to execute this command</param>/// <param name="transaction">A valid SqlTransaction, or "null"</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParameters to be associated with the command or "null" if no parameters are required</param>/// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>private static void PrepareCommand（SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection）{if （command == null） throw new ArgumentNullException（"command"）;if （commandText == null || commandText.Length == 0） throw new ArgumentNullException（"commandText"）;// If the provided connection is not open, we will open itif （connection.State ！= ConnectionState.Open）{mustCloseConnection = true;connection.Open（）;}else{mustCloseConnection = false;}// Associate the connection with the commandcommand.Connection = connection;// Set the command text （stored procedure name or SQL statement）command.CommandText = commandText;// If we were provided a transaction, assign itif （transaction ！= null）{if （transaction.Connection == null） throw new ArgumentException（"The transaction was rollbacked or commited, please provide an open transaction.", "transaction"）;command.Transaction = transaction;}// Set the command typecommand.CommandType = commandType;// Attach the command parameters if they are providedif （commandParameters ！= null）{AttachParameters（command, commandParameters）;}return;}#endregion private utility methods & constructors#region ExecuteNonQuerypublic static int ExecuteNonQuery（CommandType cmdType, string cmdText）{return ExecuteNonQuery（ConnectionString, cmdType, cmdText）;}public static int ExecuteNonQuery（CommandType cmdType, string cmdText, params SqlParameter[] commandParameters）{return ExecuteNonQuery（ConnectionString, cmdType, cmdText, commandParameters）;}/// <summary>/// Execute a SqlCommand （that returns no resultset and takes no parameters） against the database specified in /// the connection string/// </summary>/// <remarks>/// e.g.: /// int result = ExecuteNonQuery（connString, CommandType.StoredProcedure, "PublishOrders"）;/// </remarks>/// <param name="connectionString">A valid connection string for a SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <returns>An int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery（string connectionString, CommandType commandType, string commandText）{// Pass through the call providing null for the set of SqlParametersreturn ExecuteNonQuery（connectionString, commandType, commandText, （SqlParameter[]）null）;}/// <summary>/// Execute a SqlCommand （that returns no resultset） against the database specified in the connection string /// using the provided parameters/// </summary>/// <remarks>/// e.g.: /// int result = ExecuteNonQuery（connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter（"@prodid", 24））;/// </remarks>/// <param name="connectionString">A valid connection string for a SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>/// <returns>An int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery（string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters）{if （connectionString == null || connectionString.Length == 0） throw new ArgumentNullException（"connectionString"）;// Create & open a SqlConnection, and dispose of it after we are doneusing （SqlConnection connection = new SqlConnection（connectionString））{connection.Open（）;// Call the overload that takes a connection in place of the connection stringreturn ExecuteNonQuery（connection, commandType, commandText, commandParameters）;}}/// <summary>/// Execute a stored procedure via a SqlCommand （that returns no resultset） against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure （the first time each stored procedure is called）, and assign the values based on parameter order./// </summary>/// <remarks>/// This method provides no access to output parameters or the stored procedure"s return value parameter./// /// e.g.: /// int result = ExecuteNonQuery（connString, "PublishOrders", 24, 36）;/// </remarks>/// <param name="connectionString">A valid connection string for a SqlConnection</param>/// <param name="spName">The name of the stored prcedure</param>/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>/// <returns>An int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery（string connectionString, string spName, params object[] parameterValues）{if （connectionString == null || connectionString.Length == 0） throw new ArgumentNullException（"connectionString"）;if （spName == null || spName.Length == 0） throw new ArgumentNullException（"spName"）;// If we receive parameter values, we need to figure out where they goif （（parameterValues ！= null） && （parameterValues.Length > 0））{// Pull the parameters for this stored procedure from the parameter cache （or discover them & populate the cache）SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet（connectionString, spName）;// Assign the provided values to these parameters based on parameter orderAssignParameterValues（commandParameters, parameterValues）;// Call the overload that takes an array of SqlParametersreturn ExecuteNonQuery（connectionString, CommandType.StoredProcedure, spName, commandParameters）;}else{// Otherwise we can just call the SP without paramsreturn ExecuteNonQuery（connectionString, CommandType.StoredProcedure, spName）;}}/// <summary>/// Execute a SqlCommand （that returns no resultset and takes no parameters） against the provided SqlConnection. /// </summary>/// <remarks>/// e.g.: /// int result = ExecuteNonQuery（conn, CommandType.StoredProcedure, "PublishOrders"）;/// </remarks>/// <param name="connection">A valid SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <returns>An int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery（SqlConnection connection, CommandType commandType, string commandText）{// Pass through the call providing null for the set of SqlParametersreturn ExecuteNonQuery（connection, commandType, commandText, （SqlParameter[]）null）;}/// <summary>/// Execute a SqlCommand （that returns no resultset） against the specified SqlConnection /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// int result = ExecuteNonQuery（conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter（"@prodid", 24））;/// </remarks>/// <param name="connection">A valid SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>/// <returns>An int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery（SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters）{if （connection == null） throw new ArgumentNullException（"connection"）;// Create a command and prepare it for executionSqlCommand cmd = new SqlCommand（）;bool mustCloseConnection = false;PrepareCommand（cmd, connection, （SqlTransaction）null, commandType, commandText, commandParameters, out mustCloseConnection）;// Finally, execute the commandint retval = cmd.ExecuteNonQuery（）;// Detach the SqlParameters from the command object, so they can be used againcmd.Parameters.Clear（）;if （mustCloseConnection）connection.Close（）;return retval;}/// <summary>/// Execute a stored procedure via a SqlCommand （that returns no resultset） against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure （the first time each stored procedure is called）, and assign the values based on parameter order./// </summary>/// <remarks>/// This method provides no access to output parameters or the stored procedure"s return value parameter./// /// e.g.: /// int result = ExecuteNonQuery（conn, "PublishOrders", 24, 36）;/// </remarks>/// <param name="connection">A valid SqlConnection</param>/// <param name="spName">The name of the stored procedure</param>/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>/// <returns>An int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery（SqlConnection connection, string spName, params object[] parameterValues）{if （connection == null） throw new ArgumentNullException（"connection"）;if （spName == null || spName.Length == 0） throw new ArgumentNullException（"spName"）;// If we receive parameter values, we need to figure out where they goif （（parameterValues ！= null） && （parameterValues.Length > 0））{// Pull the parameters for this stored procedure from the parameter cache （or discover them & populate the cache）SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet（connection, spName）;// Assign the provided values to these parameters based on parameter orderAssignParameterValues（commandParameters, parameterValues）;// Call the overload that takes an array of SqlParametersreturn ExecuteNonQuery（connection, CommandType.StoredProcedure, spName, commandParameters）;}else{// Otherwise we can just call the SP without paramsreturn ExecuteNonQuery（connection, CommandType.StoredProcedure, spName）;}}/// <summary>/// Execute a SqlCommand （that returns no resultset and takes no parameters） against the provided SqlTransaction. /// </summary>/// <remarks>/// e.g.: /// int result = ExecuteNonQuery（trans, CommandType.StoredProcedure, "PublishOrders"）;/// </remarks>/// <param name="transaction">A valid SqlTransaction</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <returns>An int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery（SqlTransaction transaction, CommandType commandType, string commandText）{// Pass through the call providing null for the set of SqlParametersreturn ExecuteNonQuery（transaction, commandType, commandText, （SqlParameter[]）null）;}/// <summary>/// Execute a SqlCommand （that returns no resultset） against the specified SqlTransaction/// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// int result = ExecuteNonQuery（trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter（"@prodid", 24））;/// </remarks>/// <param name="transaction">A valid SqlTransaction</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>/// <returns>An int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery（SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters）{if （transaction == null） throw new ArgumentNullException（"transaction"）;if （transaction ！= null && transaction.Connection == null） throw new ArgumentException（"The transaction was rollbacked or commited, please provide an open transaction.", "transaction"）;// Create a command and prepare it for executionSqlCommand cmd = new SqlCommand（）;bool mustCloseConnection = false;PrepareCommand（cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection）;// Finally, execute the commandint retval = cmd.ExecuteNonQuery（）;// Detach the SqlParameters from the command object, so they can be used againcmd.Parameters.Clear（）;return retval;}/// <summary>/// Execute a stored procedure via a SqlCommand （that returns no resultset） against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure （the first time each stored procedure is called）, and assign the values based on parameter order./// </summary>/// <remarks>/// This method provides no access to output parameters or the stored procedure"s return value parameter./// /// e.g.: /// int result = ExecuteNonQuery（conn, trans, "PublishOrders", 24, 36）;/// </remarks>/// <param name="transaction">A valid SqlTransaction</param>/// <param name="spName">The name of the stored procedure</param>/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>/// <returns>An int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery（SqlTransaction transaction, string spName, params object[] parameterValues）{if （transaction == null） throw new ArgumentNullException（"transaction"）;if （transaction ！= null && transaction.Connection == null） throw new ArgumentException（"The transaction was rollbacked or commited, please provide an open transaction.", "transaction"）;if （spName == null || spName.Length == 0） throw new ArgumentNullException（"spName"）;// If we receive parameter values, we need to figure out where they goif （（parameterValues ！= null） && （parameterValues.Length > 0））{// Pull the parameters for this stored procedure from the parameter cache （or discover them & populate the cache）SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet（transaction.Connection, spName）;// Assign the provided values to these parameters based on parameter orderAssignParameterValues（commandParameters, parameterValues）;// Call the overload that takes an array of SqlParametersreturn ExecuteNonQuery（transaction, CommandType.StoredProcedure, spName, commandParameters）;}else{// Otherwise we can just call the SP without paramsreturn ExecuteNonQuery（transaction, CommandType.StoredProcedure, spName）;}}#endregion ExecuteNonQuery#region ExecuteDatasetpublic static DataSet ExecuteDataset（CommandType commandType, string commandText）{return ExecuteDataset（ConnectionString, commandType, commandText）;}public static DataSet ExecuteDataset（CommandType commandType, string commandText, params SqlParameter[] commandParameters）{return ExecuteDataset（ConnectionString, commandType, commandText, commandParameters）;}/// <summary>/// Execute a SqlCommand （that returns a resultset and takes no parameters） against the database specified in /// the connection string. /// </summary>/// <remarks>/// e.g.: /// DataSet ds = ExecuteDataset（connString, CommandType.StoredProcedure, "GetOrders"）;/// </remarks>/// <param name="connectionString">A valid connection string for a SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <returns>A dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset（string connectionString, CommandType commandType, string commandText）{// Pass through the call providing null for the set of SqlParametersreturn ExecuteDataset（connectionString, commandType, commandText, （SqlParameter[]）null）;}/// <summary>/// Execute a SqlCommand （that returns a resultset） against the database specified in the connection string /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// DataSet ds = ExecuteDataset（connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter（"@prodid", 24））;/// </remarks>/// <param name="connectionString">A valid connection string for a SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>/// <returns>A dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset（string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters）{if （connectionString == null || connectionString.Length == 0） throw new ArgumentNullException（"connectionString"）;// Create & open a SqlConnection, and dispose of it after we are doneusing （SqlConnection connection = new SqlConnection（connectionString））{connection.Open（）;// Call the overload that takes a connection in place of the connection stringreturn ExecuteDataset（connection, commandType, commandText, commandParameters）;}}/// <summary>/// Execute a stored procedure via a SqlCommand （that returns a resultset） against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure （the first time each stored procedure is called）, and assign the values based on parameter order./// </summary>/// <remarks>/// This method provides no access to output parameters or the stored procedure"s return value parameter./// /// e.g.: /// DataSet ds = ExecuteDataset（connString, "GetOrders", 24, 36）;/// </remarks>/// <param name="connectionString">A valid connection string for a SqlConnection</param>/// <param name="spName">The name of the stored procedure</param>/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>/// <returns>A dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset（string connectionString, string spName, params object[] parameterValues）{if （connectionString == null || connectionString.Length == 0） throw new ArgumentNullException（"connectionString"）;if （spName == null || spName.Length == 0） throw new ArgumentNullException（"spName"）;// If we receive parameter values, we need to figure out where they goif （（parameterValues ！= null） && （parameterValues.Length > 0））{// Pull the parameters for this stored procedure from the parameter cache （or discover them & populate the cache）SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet（connectionString, spName）;// Assign the provided values to these parameters based on parameter orderAssignParameterValues（commandParameters, parameterValues）;// Call the overload that takes an array of SqlParametersreturn ExecuteDataset（connectionString, CommandType.StoredProcedure, spName, commandParameters）;}else{// Otherwise we can just call the SP without paramsreturn ExecuteDataset（connectionString, CommandType.StoredProcedure, spName）;}}/// <summary>/// Execute a SqlCommand （that returns a resultset and takes no parameters） against the provided SqlConnection. /// </summary>/// <remarks>/// e.g.: /// DataSet ds = ExecuteDataset（conn, CommandType.StoredProcedure, "GetOrders"）;/// </remarks>/// <param name="connection">A valid SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <returns>A dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset（SqlConnection connection, CommandType commandType, string commandText）{// Pass through the call providing null for the set of SqlParametersreturn ExecuteDataset（connection, commandType, commandText, （SqlParameter[]）null）;}/// <summary>/// Execute a SqlCommand （that returns a resultset） against the specified SqlConnection /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// DataSet ds = ExecuteDataset（conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter（"@prodid", 24））;/// </remarks>/// <param name="connection">A valid SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>/// <returns>A dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset（SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters）{if （connection == null） throw new ArgumentNullException（"connection"）;// Create a command and prepare it for executionSqlCommand cmd = new SqlCommand（）;bool mustCloseConnection = false;PrepareCommand（cmd, connection, （SqlTransaction）null, commandType, commandText, commandParameters, out mustCloseConnection）;// Create the DataAdapter & DataSetusing （SqlDataAdapter da = new SqlDataAdapter（cmd））{DataSet ds = new DataSet（）;// Fill the DataSet using default values for DataTable names, etcda.Fill（ds）;// Detach the SqlParameters from the command object, so they can be used againcmd.Parameters.Clear（）;if （mustCloseConnection）connection.Close（）;// Return the datasetreturn ds;}}/// <summary>/// Execute a stored procedure via a SqlCommand （that returns a resultset） against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure （the first time each stored procedure is called）, and assign the values based on parameter order./// </summary>/// <remarks>/// This method provides no access to output parameters or the stored procedure"s return value parameter./// /// e.g.: /// DataSet ds = ExecuteDataset（conn, "GetOrders", 24, 36）;/// </remarks>/// <param name="connection">A valid SqlConnection</param>/// <param name="spName">The name of the stored procedure</param>/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>/// <returns>A dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset（SqlConnection connection, string spName, params object[] parameterValues）{if （connection == null） throw new ArgumentNullException（"connection"）;if （spName == null || spName.Length == 0） throw new ArgumentNullException（"spName"）;// If we receive parameter values, we need to figure out where they goif （（parameterValues ！= null） && （parameterValues.Length > 0））{// Pull the parameters for this stored procedure from the parameter cache （or discover them & populate the cache）SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet（connection, spName）;// Assign the provided values to these parameters based on parameter orderAssignParameterValues（commandParameters, parameterValues）;// Call the overload that takes an array of SqlParametersreturn ExecuteDataset（connection, CommandType.StoredProcedure, spName, commandParameters）;}else{// Otherwise we can just call the SP without paramsreturn ExecuteDataset（connection, CommandType.StoredProcedure, spName）;}}/// <summary>/// Execute a SqlCommand （that returns a resultset and takes no parameters） against the provided SqlTransaction. /// </summary>/// <remarks>/// e.g.: /// DataSet ds = ExecuteDataset（trans, CommandType.StoredProcedure, "GetOrders"）;/// </remarks>/// <param name="transaction">A valid SqlTransaction</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <returns>A dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset（SqlTransaction transaction, CommandType commandType, string commandText）{// Pass through the call providing null for the set of SqlParametersreturn ExecuteDataset（transaction, commandType, commandText, （SqlParameter[]）null）;}/// <summary>/// Execute a SqlCommand （that returns a resultset） against the specified SqlTransaction/// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// DataSet ds = ExecuteDataset（trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter（"@prodid", 24））;/// </remarks>/// <param name="transaction">A valid SqlTransaction</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>/// <returns>A dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset（SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters）{if （transaction == null） throw new ArgumentNullException（"transaction"）;if （transaction ！= null && transaction.Connection == null） throw new ArgumentException（"The transaction was rollbacked or commited, please provide an open transaction.", "transaction"）;// Create a command and prepare it for executionSqlCommand cmd = new SqlCommand（）;bool mustCloseConnection = false;PrepareCommand（cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection）;// Create the DataAdapter & DataSetusing （SqlDataAdapter da = new SqlDataAdapter（cmd））{DataSet ds = new DataSet（）;// Fill the DataSet using default values for DataTable names, etcda.Fill（ds）;// Detach the SqlParameters from the command object, so they can be used againcmd.Parameters.Clear（）;// Return the datasetreturn ds;}}/// <summary>/// Execute a stored procedure via a SqlCommand （that returns a resultset） against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure （the first time each stored procedure is called）, and assign the values based on parameter order./// </summary>/// <remarks>/// This method provides no access to output parameters or the stored procedure"s return value parameter./// /// e.g.: /// DataSet ds = ExecuteDataset（trans, "GetOrders", 24, 36）;/// </remarks>/// <param name="transaction">A valid SqlTransaction</param>/// <param name="spName">The name of the stored procedure</param>/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>/// <returns>A dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset（SqlTransaction transaction, string spName, params object[] parameterValues）{if （transaction == null） throw new ArgumentNullException（"transaction"）;if （transaction ！= null && transaction.Connection == null） throw new ArgumentException（"The transaction was rollbacked or commited, please provide an open transaction.", "transaction"）;if （spName == null || spName.Length == 0） throw new ArgumentNullException（"spName"）;// If we receive parameter values, we need to figure out where they goif （（parameterValues ！= null） && （parameterValues.Length > 0））{// Pull the parameters for this stored procedure from the parameter cache （or discover them & populate the cache）SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet（transaction.Connection, spName）;// Assign the provided values to these parameters based on parameter orderAssignParameterValues（commandParameters, parameterValues）;// Call the overload that takes an array of SqlParametersreturn ExecuteDataset（transaction, CommandType.StoredProcedure, spName, commandParameters）;}else{// Otherwise we can just call the SP without paramsreturn ExecuteDataset（transaction, CommandType.StoredProcedure, spName）;}}#endregion ExecuteDataset#region ExecuteReader/// <summary>/// This enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that/// we can set the appropriate CommandBehavior when calling ExecuteReader（）/// </summary>private enum SqlConnectionOwnership{/// <summary>Connection is owned and managed by SqlHelper</summary>Internal,/// <summary>Connection is owned and managed by the caller</summary>External}public static SqlDataReader ExecuteReader（CommandType cmdType, string cmdText）{return ExecuteReader（ConnectionString, cmdType, cmdText）;}public static SqlDataReader ExecuteReader（CommandType cmdType, string cmdText, params SqlParameter[] commandParameters）{return ExecuteReader（ConnectionString, cmdType, cmdText, commandParameters）;}/// <summary>/// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior./// </summary>/// <remarks>/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed./// /// If the caller provided the connection, we want to leave it to them to manage./// </remarks>/// <param name="connection">A valid SqlConnection, on which to execute this command</param>/// <param name="transaction">A valid SqlTransaction, or "null"</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParameters to be associated with the command or "null" if no parameters are required</param>/// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>/// <returns>SqlDataReader containing the results of the command</returns>private static SqlDataReader ExecuteReader（SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership）{if （connection == null） throw new ArgumentNullException（"connection"）;bool mustCloseConnection = false;// Create a command and prepare it for executionSqlCommand cmd = new SqlCommand（）;try{PrepareCommand（cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection）;// Create a readerSqlDataReader dataReader;// Call ExecuteReader with the appropriate CommandBehaviorif （connectionOwnership == SqlConnectionOwnership.External）{dataReader = cmd.ExecuteReader（）;}else{dataReader = cmd.ExecuteReader（CommandBehavior.CloseConnection）;}// Detach the SqlParameters from the command object, so they can be used again.// HACK: There is a problem here, the output parameter values are fletched // when the reader is closed, so if the parameters are detached from the command// then the SqlReader can磘 set its values. // When this happen, the parameters can磘 be used again in other command.bool canClear = true;foreach （SqlParameter commandParameter in cmd.Parameters）{if （commandParameter.Direction ！= ParameterDirection.Input）canClear = false;}if （canClear）{cmd.Parameters.Clear（）;}return dataReader;}catch{if （mustCloseConnection）connection.Close（）;throw;}}/// <summary>/// Execute a SqlCommand （that returns a resultset and takes no parameters） against the database specified in /// the connection string. /// </summary>/// <remarks>/// e.g.: /// SqlDataReader dr = ExecuteReader（connString, CommandType.StoredProcedure, "GetOrders"）;/// </remarks>/// <param name="connectionString">A valid connection string for a SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <returns>A SqlDataReader containing the resultset generated by the command</returns>public static SqlDataReader ExecuteReader（string connectionString, CommandType commandType, string commandText）{// Pass through the call providing null for the set of SqlParametersreturn ExecuteReader（connectionString, commandType, commandText, （SqlParameter[]）null）;}/// <summary>/// Execute a SqlCommand （that returns a resultset） against the database specified in the connection string /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// SqlDataReader dr = ExecuteReader（connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter（"@prodid", 24））;/// </remarks>/// <param name="connectionString">A valid connection string for a SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>/// <returns>A SqlDataReader containing the resultset generated by the command</returns>public static SqlDataReader ExecuteReader（string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters）{if （connectionString == null || connectionString.Length == 0） throw new ArgumentNullException（"connectionString"）;SqlConnection connection = null;try{connection = new SqlConnection（connectionString）;connection.Open（）;// Call the private overload that takes an internally owned connection in place of the connection stringreturn ExecuteReader（connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal）;}catch{// If we fail to return the SqlDatReader, we need to close the connection ourselvesif （connection ！= null） connection.Close（）;throw;}}/// <summary>/// Execute a stored procedure via a SqlCommand （that returns a resultset） against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure （the first time each stored procedure is called）, and assign the values based on parameter order./// </summary>/// <remarks>/// This method provides no access to output parameters or the stored procedure"s return value parameter./// /// e.g.: /// SqlDataReader dr = ExecuteReader（connString, "GetOrders", 24, 36）;/// </remarks>/// <param name="connectionString">A valid connection string for a SqlConnection</param>/// <param name="spName">The name of the stored procedure</param>/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>/// <returns>A SqlDataReader containing the resultset generated by the command</returns>public static SqlDataReader ExecuteReader（string connectionString, string spName, params object[] parameterValues）{if （connectionString == null || connectionString.Length == 0） throw new ArgumentNullException（"connectionString"）;if （spName == null || spName.Length == 0） throw new ArgumentNullException（"spName"）;// If we receive parameter values, we need to figure out where they goif （（parameterValues ！= null） && （parameterValues.Length > 0））{SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet（connectionString, spName）;AssignParameterValues（commandParameters, parameterValues）;return ExecuteReader（connectionString, CommandType.StoredProcedure, spName, commandParameters）;}else{// Otherwise we can just call the SP without paramsreturn ExecuteReader（connectionString, CommandType.StoredProcedure, spName）;}}/// <summary>/// Execute a SqlCommand （that returns a resultset and takes no parameters） against the provided SqlConnection. /// </summary>/// <remarks>/// e.g.: /// SqlDataReader dr = ExecuteReader（conn, CommandType.StoredProcedure, "GetOrders"）;/// </remarks>/// <param name="connection">A valid SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <returns>A SqlDataReader containing the resultset generated by the command</returns>public static SqlDataReader ExecuteReader（SqlConnection connection, CommandType commandType, string commandText）{// Pass through the call providing null for the set of SqlParametersreturn ExecuteReader（connection, commandType, commandText, （SqlParameter[]）null）;}/// <summary>/// Execute a SqlCommand （that returns a resultset） against the specified SqlConnection /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// SqlDataReader dr = ExecuteReader（conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter（"@prodid", 24））;/// </remarks>/// <param name="connection">A valid SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>/// <returns>A SqlDataReader containing the resultset generated by the command</returns>public static SqlDataReader ExecuteReader（SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters）{// Pass through the call to the private overload using a null transaction value and an externally owned connectionreturn ExecuteReader（connection, （SqlTransaction）null, commandType, commandText, commandParameters, SqlConnectionOwnership.External）;}/// <summary>/// Execute a stored procedure via a SqlCommand （that returns a resultset） against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure （the first time each stored procedure is called）, and assign the values based on parameter order./// </summary>/// <remarks>/// This method provides no access to output parameters or the stored procedure"s return value parameter./// /// e.g.: /// SqlDataReader dr = ExecuteReader（conn, "GetOrders", 24, 36）;/// </remarks>/// <param name="connection">A valid SqlConnection</param>/// <param name="spName">The name of the stored procedure</param>/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>/// <returns>A SqlDataReader containing the resultset generated by the command</returns>public static SqlDataReader ExecuteReader（SqlConnection connection, string spName, params object[] parameterValues）{if （connection == null） throw new ArgumentNullException（"connection"）;if （spName == null || spName.Length == 0） throw new ArgumentNullException（"spName"）;// If we receive parameter values, we need to figure out where they goif （（parameterValues ！= null） && （parameterValues.Length > 0））{SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet（connection, spName）;AssignParameterValues（commandParameters, parameterValues）;return ExecuteReader（connection, CommandType.StoredProcedure, spName, commandParameters）;}else{// Otherwise we can just call the SP without paramsreturn ExecuteReader（connection, CommandType.StoredProcedure, spName）;}}/// <summary>/// Execute a SqlCommand （that returns a resultset and takes no parameters） against the provided SqlTransaction. /// </summary>/// <remarks>/// e.g.: /// SqlDataReader dr = ExecuteReader（trans, CommandType.StoredProcedure, "GetOrders"）;/// </remarks>/// <param name="transaction">A valid SqlTransaction</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <returns>A SqlDataReader containing the resultset generated by the command</returns>public static SqlDataReader ExecuteReader（SqlTransaction transaction, CommandType commandType, string commandText）{// Pass through the call providing null for the set of SqlParametersreturn ExecuteReader（transaction, commandType, commandText, （SqlParameter[]）null）;}/// <summary>/// Execute a SqlCommand （that returns a resultset） against the specified SqlTransaction/// using the provided parameters./// </summary>/// <remarks>/// e.g.: ///SqlDataReader dr = ExecuteReader（trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter（"@prodid", 24））;/// </remarks>/// <param name="transaction">A valid SqlTransaction</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>/// <returns>A SqlDataReader containing the resultset generated by the command</returns>public static SqlDataReader ExecuteReader（SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters）{if （transaction == null） throw new ArgumentNullException（"transaction"）;if （transaction ！= null && transaction.Connection == null） throw new ArgumentException（"The transaction was rollbacked or commited, please provide an open transaction.", "transaction"）;// Pass through to private overload, indicating that the connection is owned by the callerreturn ExecuteReader（transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External）;}/// <summary>/// Execute a stored procedure via a SqlCommand （that returns a resultset） against the specified/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure （the first time each stored procedure is called）, and assign the values based on parameter order./// </summary>/// <remarks>/// This method provides no access to output parameters or the stored procedure"s return value parameter./// /// e.g.: /// SqlDataReader dr = ExecuteReader（trans, "GetOrders", 24, 36）;/// </remarks>/// <param name="transaction">A valid SqlTransaction</param>/// <param name="spName">The name of the stored procedure</param>/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>/// <returns>A SqlDataReader containing the resultset generated by the command</returns>public static SqlDataReader ExecuteReader（SqlTransaction transaction, string spName, params object[] parameterValues）{if （transaction == null） throw new ArgumentNullException（"transaction"）;if （transaction ！= null && transaction.Connection == null） throw new ArgumentException（"The transaction was rollbacked or commited, please provide an open transaction.", "transaction"）;if （spName == null || spName.Length == 0） throw new ArgumentNullException（"spName"）;// If we receive parameter values, we need to figure out where they goif （（parameterValues ！= null） && （parameterValues.Length > 0））{SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet（transaction.Connection, spName）;AssignParameterValues（commandParameters, parameterValues）;return ExecuteReader（transaction, CommandType.StoredProcedure, spName, commandParameters）;}else{// Otherwise we can just call the SP without paramsreturn ExecuteReader（transaction, CommandType.StoredProcedure, spName）;}}#endregion ExecuteReader#region ExecuteScalarpublic static object ExecuteScalar（CommandType cmdType, string cmdText）{return ExecuteScalar（ConnectionString, cmdType, cmdText）;}public static object ExecuteScalar（CommandType cmdType, string cmdText, params SqlParameter[] commandParameters）{return ExecuteScalar（ConnectionString, cmdType, cmdText, commandParameters）;}/// <summary>/// Execute a SqlCommand （that returns a 1x1 resultset and takes no parameters） against the database specified in /// the connection string. /// </summary>/// <remarks>/// e.g.: /// int orderCount = （int）ExecuteScalar（connString, CommandType.StoredProcedure, "GetOrderCount"）;/// </remarks>/// <param name="connectionString">A valid connection string for a SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>public static object ExecuteScalar（string connectionString, CommandType commandType, string commandText）{// Pass through the call providing null for the set of SqlParametersreturn ExecuteScalar（connectionString, commandType, commandText, （SqlParameter[]）null）;}/// <summary>/// Execute a SqlCommand （that returns a 1x1 resultset） against the database specified in the connection string /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// int orderCount = （int）ExecuteScalar（connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter（"@prodid", 24））;/// </remarks>/// <param name="connectionString">A valid connection string for a SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>public static object ExecuteScalar（string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters）{if （connectionString == null || connectionString.Length == 0） throw new ArgumentNullException（"connectionString"）;// Create & open a SqlConnection, and dispose of it after we are doneusing （SqlConnection connection = new SqlConnection（connectionString））{connection.Open（）;// Call the overload that takes a connection in place of the connection stringreturn ExecuteScalar（connection, commandType, commandText, commandParameters）;}}/// <summary>/// Execute a stored procedure via a SqlCommand （that returns a 1x1 resultset） against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure （the first time each stored procedure is called）, and assign the values based on parameter order./// </summary>/// <remarks>/// This method provides no access to output parameters or the stored procedure"s return value parameter./// /// e.g.: /// int orderCount = （int）ExecuteScalar（connString, "GetOrderCount", 24, 36）;/// </remarks>/// <param name="connectionString">A valid connection string for a SqlConnection</param>/// <param name="spName">The name of the stored procedure</param>/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>public static object ExecuteScalar（string connectionString, string spName, params object[] parameterValues）{if （connectionString == null || connectionString.Length == 0） throw new ArgumentNullException（"connectionString"）;if （spName == null || spName.Length == 0） throw new ArgumentNullException（"spName"）;// If we receive parameter values, we need to figure out where they goif （（parameterValues ！= null） && （parameterValues.Length > 0））{// Pull the parameters for this stored procedure from the parameter cache （or discover them & populate the cache）SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet（connectionString, spName）;// Assign the provided values to these parameters based on parameter orderAssignParameterValues（commandParameters, parameterValues）;// Call the overload that takes an array of SqlParametersreturn ExecuteScalar（connectionString, CommandType.StoredProcedure, spName, commandParameters）;}else{// Otherwise we can just call the SP without paramsreturn ExecuteScalar（connectionString, CommandType.StoredProcedure, spName）;}}/// <summary>/// Execute a SqlCommand （that returns a 1x1 resultset and takes no parameters） against the provided SqlConnection. /// </summary>/// <remarks>/// e.g.: /// int orderCount = （int）ExecuteScalar（conn, CommandType.StoredProcedure, "GetOrderCount"）;/// </remarks>/// <param name="connection">A valid SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>public static object ExecuteScalar（SqlConnection connection, CommandType commandType, string commandText）{// Pass through the call providing null for the set of SqlParametersreturn ExecuteScalar（connection, commandType, commandText, （SqlParameter[]）null）;}/// <summary>/// Execute a SqlCommand （that returns a 1x1 resultset） against the specified SqlConnection /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// int orderCount = （int）ExecuteScalar（conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter（"@prodid", 24））;/// </remarks>/// <param name="connection">A valid SqlConnection</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>public static object ExecuteScalar（SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters）{if （connection == null） throw new ArgumentNullException（"connection"）;// Create a command and prepare it for executionSqlCommand cmd = new SqlCommand（）;bool mustCloseConnection = false;PrepareCommand（cmd, connection, （SqlTransaction）null, commandType, commandText, commandParameters, out mustCloseConnection）;// Execute the command & return the resultsobject retval = cmd.ExecuteScalar（）;// Detach the SqlParameters from the command object, so they can be used againcmd.Parameters.Clear（）;if （mustCloseConnection）connection.Close（）;return retval;}/// <summary>/// Execute a stored procedure via a SqlCommand （that returns a 1x1 resultset） against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure （the first time each stored procedure is called）, and assign the values based on parameter order./// </summary>/// <remarks>/// This method provides no access to output parameters or the stored procedure"s return value parameter./// /// e.g.: /// int orderCount = （int）ExecuteScalar（conn, "GetOrderCount", 24, 36）;/// </remarks>/// <param name="connection">A valid SqlConnection</param>/// <param name="spName">The name of the stored procedure</param>/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>public static object ExecuteScalar（SqlConnection connection, string spName, params object[] parameterValues）{if （connection == null） throw new ArgumentNullException（"connection"）;if （spName == null || spName.Length == 0） throw new ArgumentNullException（"spName"）;// If we receive parameter values, we need to figure out where they goif （（parameterValues ！= null） && （parameterValues.Length > 0））{// Pull the parameters for this stored procedure from the parameter cache （or discover them & populate the cache）SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet（connection, spName）;// Assign the provided values to these parameters based on parameter orderAssignParameterValues（commandParameters, parameterValues）;// Call the overload that takes an array of SqlParametersreturn ExecuteScalar（connection, CommandType.StoredProcedure, spName, commandParameters）;}else{// Otherwise we can just call the SP without paramsreturn ExecuteScalar（connection, CommandType.StoredProcedure, spName）;}}/// <summary>/// Execute a SqlCommand （that returns a 1x1 resultset and takes no parameters） against the provided SqlTransaction. /// </summary>/// <remarks>/// e.g.: /// int orderCount = （int）ExecuteScalar（trans, CommandType.StoredProcedure, "GetOrderCount"）;/// </remarks>/// <param name="transaction">A valid SqlTransaction</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>public static object ExecuteScalar（SqlTransaction transaction, CommandType commandType, string commandText）{// Pass through the call providing null for the set of SqlParametersreturn ExecuteScalar（transaction, commandType, commandText, （SqlParameter[]）null）;}/// <summary>/// Execute a SqlCommand （that returns a 1x1 resultset） against the specified SqlTransaction/// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// int orderCount = （int）ExecuteScalar（trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter（"@prodid", 24））;/// </remarks>/// <param name="transaction">A valid SqlTransaction</param>/// <param name="commandType">The CommandType （stored procedure, text, etc.）</param>/// <param name="commandText">The stored procedure name or T-SQL command</param>/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>public static object ExecuteScalar（SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters）{if （transaction == null） throw new ArgumentNullException（"transaction"）;if （transaction ！= null && transaction.Connection == null） throw new ArgumentException（"The transaction was rollbacked or commited, please provide an open transaction.", "transaction"）;// Create a command and prepare it for executionSqlCommand cmd = new SqlCommand（）;bool mustCloseConnection = false;PrepareCommand（cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection）;// Execute the command & return the resultsobject retval = cmd.ExecuteScalar（