(轉)SQLHelper源碼

缺乏、安全感 2022-08-25 08:25 175阅读 0赞

![None.gif][] using  System;  
![None.gif][] using  System.Data;  
![None.gif][] using  System.Xml;  
![None.gif][] using  System.Data.SqlClient;  
![None.gif][] using  System.Collections;  
![None.gif][]  
![None.gif][]  
![None.gif][] namespace  Microsoft.ApplicationBlocks.Data  
![ExpandedBlockStart.gif][]![ContractedBlock.gif][] ... \{  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for  
![InBlock.gif][]/// common uses of SqlClient.  
![ExpandedSubBlockEnd.gif][]///</summary>  
![InBlock.gif][]publicsealedclass SqlHelper  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![ContractedSubBlock.gif][]![ExpandedSubBlockStart.gif][]private utility methods & constructors\#region private utility methods & constructors  
![InBlock.gif][]  
![InBlock.gif][]//Since this class provides only static methods, make the default constructor private to prevent  
![InBlock.gif][]//instances from being created with "new SqlHelper()".  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]private SqlHelper() ...\{\}  
![InBlock.gif][]  
![InBlock.gif][]  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// This method is used to attach array of SqlParameters to a SqlCommand.  
![InBlock.gif][]///  
![InBlock.gif][]/// This method will assign a value of DbNull to any parameter with a direction of  
![InBlock.gif][]/// InputOutput and a value of null.  
![InBlock.gif][]///  
![InBlock.gif][]/// This behavior will prevent default values from being used, but  
![InBlock.gif][]/// this will be the less common case than an intended pure output parameter (derived as InputOutput)  
![InBlock.gif][]/// where the user provided no input value.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<param name="command">The command to which the parameters will be added</param>  
![ExpandedSubBlockEnd.gif][]///<param name="commandParameters">an array of SqlParameters tho be added to command</param>  
![InBlock.gif][]privatestaticvoid AttachParameters(SqlCommand command, SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]foreach (SqlParameter p in commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//check for derived output value with no value assigned  
![InBlock.gif][]if ((p.Direction == ParameterDirection.InputOutput) && (p.Value ==null))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] p.Value = DBNull.Value;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][] command.Parameters.Add(p);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// This method assigns an array of values to an array of SqlParameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<param name="commandParameters">array of SqlParameters to be assigned values</param>  
![ExpandedSubBlockEnd.gif][]///<param name="parameterValues">array of objects holding the values to be assigned</param>  
![InBlock.gif][]privatestaticvoid AssignParameterValues(SqlParameter\[\] commandParameters, object\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]if ((commandParameters ==null) || (parameterValues ==null))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//do nothing if we get no data  
![InBlock.gif][]return;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][]// we must have the same number of values as we pave parameters to put them in  
![InBlock.gif][]if (commandParameters.Length != parameterValues.Length)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]thrownew ArgumentException("Parameter count does not match Parameter Value count.");  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][]//iterate through the SqlParameters, assigning the values from the corresponding position in the  
![InBlock.gif][]//value array  
![InBlock.gif][]for (int i =0, j = commandParameters.Length; i < j; i\++)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] commandParameters\[i\].Value = parameterValues\[i\];  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters  
![InBlock.gif][]/// to the provided command.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<param name="command">the SqlCommand to be prepared</param>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection, on which to execute this command</param>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction, or 'null'</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>  
![InBlock.gif][]privatestaticvoid PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if the provided connection is not open, we will open it  
![InBlock.gif][]if (connection.State != ConnectionState.Open)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] connection.Open();  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][]//associate the connection with the command  
![InBlock.gif][] command.Connection = connection;  
![InBlock.gif][]  
![InBlock.gif][]//set the command text (stored procedure name or SQL statement)  
![InBlock.gif][] command.CommandText = commandText;  
![InBlock.gif][]  
![InBlock.gif][]//if we were provided a transaction, assign it.  
![InBlock.gif][]if (transaction !=null)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] command.Transaction = transaction;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][]//set the command type  
![InBlock.gif][] command.CommandType = commandType;  
![InBlock.gif][]  
![InBlock.gif][]//attach the command parameters if they are provided  
![InBlock.gif][]if (commandParameters !=null)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] AttachParameters(command, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][]return;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][]  
![ExpandedSubBlockEnd.gif][]\#endregion private utility methods & constructors  
![InBlock.gif][]  
![ContractedSubBlock.gif][]![ExpandedSubBlockStart.gif][]ExecuteNonQuery\#region ExecuteNonQuery  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in  
![InBlock.gif][]/// the connection string.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an int representing the number of rows affected by the command</returns>  
![InBlock.gif][]publicstaticint ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an int representing the number of rows affected by the command</returns>  
![InBlock.gif][]publicstaticint ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//create & open a SqlConnection, and dispose of it after we are done.  
![InBlock.gif][]using (SqlConnection cn =new SqlConnection(connectionString))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] cn.Open();  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes a connection in place of the connection string  
![InBlock.gif][]return ExecuteNonQuery(cn, commandType, commandText, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in  
![InBlock.gif][]/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="spName">the name of the stored prcedure</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an int representing the number of rows affected by the command</returns>  
![InBlock.gif][]publicstaticint ExecuteNonQuery(string connectionString, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][]//assign the provided values to these parameters based on parameter order  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes an array of SqlParameters  
![InBlock.gif][]return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an int representing the number of rows affected by the command</returns>  
![InBlock.gif][]publicstaticint ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an int representing the number of rows affected by the command</returns>  
![InBlock.gif][]publicstaticint ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//create a command and prepare it for execution  
![InBlock.gif][] SqlCommand cmd =new SqlCommand();  
![InBlock.gif][] PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);  
![InBlock.gif][]  
![InBlock.gif][]//finally, execute the command.  
![InBlock.gif][]int retval = cmd.ExecuteNonQuery();  
![InBlock.gif][]  
![InBlock.gif][]// detach the SqlParameters from the command object, so they can be used again.  
![InBlock.gif][] cmd.Parameters.Clear();  
![InBlock.gif][]return retval;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection  
![InBlock.gif][]/// using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an int representing the number of rows affected by the command</returns>  
![InBlock.gif][]publicstaticint ExecuteNonQuery(SqlConnection connection, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][]//assign the provided values to these parameters based on parameter order  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes an array of SqlParameters  
![InBlock.gif][]return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an int representing the number of rows affected by the command</returns>  
![InBlock.gif][]publicstaticint ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an int representing the number of rows affected by the command</returns>  
![InBlock.gif][]publicstaticint ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//create a command and prepare it for execution  
![InBlock.gif][] SqlCommand cmd =new SqlCommand();  
![InBlock.gif][] PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);  
![InBlock.gif][]  
![InBlock.gif][]//finally, execute the command.  
![InBlock.gif][]int retval = cmd.ExecuteNonQuery();  
![InBlock.gif][]  
![InBlock.gif][]// detach the SqlParameters from the command object, so they can be used again.  
![InBlock.gif][] cmd.Parameters.Clear();  
![InBlock.gif][]return retval;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified  
![InBlock.gif][]/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an int representing the number of rows affected by the command</returns>  
![InBlock.gif][]publicstaticint ExecuteNonQuery(SqlTransaction transaction, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][]//assign the provided values to these parameters based on parameter order  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes an array of SqlParameters  
![InBlock.gif][]return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][]  
![ExpandedSubBlockEnd.gif][]\#endregion ExecuteNonQuery  
![InBlock.gif][]  
![ContractedSubBlock.gif][]![ExpandedSubBlockStart.gif][]ExecuteDataSet\#region ExecuteDataSet  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in  
![InBlock.gif][]/// the connection string.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a dataset containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a dataset containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//create & open a SqlConnection, and dispose of it after we are done.  
![InBlock.gif][]using (SqlConnection cn =new SqlConnection(connectionString))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] cn.Open();  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes a connection in place of the connection string  
![InBlock.gif][]return ExecuteDataset(cn, commandType, commandText, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in  
![InBlock.gif][]/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a dataset containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic DataSet ExecuteDataset(string connectionString, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][]//assign the provided values to these parameters based on parameter order  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes an array of SqlParameters  
![InBlock.gif][]return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a dataset containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteDataset(connection, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a dataset containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//create a command and prepare it for execution  
![InBlock.gif][] SqlCommand cmd =new SqlCommand();  
![InBlock.gif][] PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);  
![InBlock.gif][]  
![InBlock.gif][]//create the DataAdapter & DataSet  
![InBlock.gif][] SqlDataAdapter da =new SqlDataAdapter(cmd);  
![InBlock.gif][] DataSet ds =new DataSet();  
![InBlock.gif][]  
![InBlock.gif][]//fill the DataSet using default values for DataTable names, etc.  
![InBlock.gif][] da.Fill(ds);  
![InBlock.gif][]  
![InBlock.gif][]// detach the SqlParameters from the command object, so they can be used again.   
![InBlock.gif][] cmd.Parameters.Clear();  
![InBlock.gif][]  
![InBlock.gif][]//return the dataset  
![InBlock.gif][]return ds;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection  
![InBlock.gif][]/// using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a dataset containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic DataSet ExecuteDataset(SqlConnection connection, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][]//assign the provided values to these parameters based on parameter order  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes an array of SqlParameters  
![InBlock.gif][]return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteDataset(connection, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a dataset containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteDataset(transaction, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a dataset containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//create a command and prepare it for execution  
![InBlock.gif][] SqlCommand cmd =new SqlCommand();  
![InBlock.gif][] PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);  
![InBlock.gif][]  
![InBlock.gif][]//create the DataAdapter & DataSet  
![InBlock.gif][] SqlDataAdapter da =new SqlDataAdapter(cmd);  
![InBlock.gif][] DataSet ds =new DataSet();  
![InBlock.gif][]  
![InBlock.gif][]//fill the DataSet using default values for DataTable names, etc.  
![InBlock.gif][] da.Fill(ds);  
![InBlock.gif][]  
![InBlock.gif][]// detach the SqlParameters from the command object, so they can be used again.  
![InBlock.gif][] cmd.Parameters.Clear();  
![InBlock.gif][]  
![InBlock.gif][]//return the dataset  
![InBlock.gif][]return ds;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified  
![InBlock.gif][]/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a dataset containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic DataSet ExecuteDataset(SqlTransaction transaction, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][]//assign the provided values to these parameters based on parameter order  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes an array of SqlParameters  
![InBlock.gif][]return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockEnd.gif][]\#endregion ExecuteDataSet  
![InBlock.gif][]  
![ContractedSubBlock.gif][]![ExpandedSubBlockStart.gif][]ExecuteReader\#region ExecuteReader  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// this enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that  
![InBlock.gif][]/// we can set the appropriate CommandBehavior when calling ExecuteReader()  
![ExpandedSubBlockEnd.gif][]///</summary>  
![InBlock.gif][]privateenum SqlConnectionOwnership  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>Connection is owned and managed by SqlHelper</summary>  
![InBlock.gif][] Internal,  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>Connection is owned and managed by the caller</summary>  
![InBlock.gif][] External  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.  
![InBlock.gif][]///  
![InBlock.gif][]/// If the caller provided the connection, we want to leave it to them to manage.  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection, on which to execute this command</param>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction, or 'null'</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>  
![InBlock.gif][]///<param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>  
![ExpandedSubBlockEnd.gif][]///<returns>SqlDataReader containing the results of the command</returns>  
![InBlock.gif][]privatestatic SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter\[\] commandParameters, SqlConnectionOwnership connectionOwnership)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//create a command and prepare it for execution  
![InBlock.gif][] SqlCommand cmd =new SqlCommand();  
![InBlock.gif][] PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);  
![InBlock.gif][]  
![InBlock.gif][]//create a reader  
![InBlock.gif][] SqlDataReader dr;  
![InBlock.gif][]  
![InBlock.gif][]// call ExecuteReader with the appropriate CommandBehavior  
![InBlock.gif][]if (connectionOwnership == SqlConnectionOwnership.External)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] dr = cmd.ExecuteReader();  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][]// detach the SqlParameters from the command object, so they can be used again.  
![InBlock.gif][] cmd.Parameters.Clear();  
![InBlock.gif][]  
![InBlock.gif][]return dr;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in  
![InBlock.gif][]/// the connection string.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a SqlDataReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteReader(connectionString, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a SqlDataReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//create & open a SqlConnection  
![InBlock.gif][] SqlConnection cn =new SqlConnection(connectionString);  
![InBlock.gif][] cn.Open();  
![InBlock.gif][]  
![InBlock.gif][]try  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//call the private overload that takes an internally owned connection in place of the connection string  
![InBlock.gif][]return ExecuteReader(cn, null, commandType, commandText, commandParameters,SqlConnectionOwnership.Internal);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]catch  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we fail to return the SqlDatReader, we need to close the connection ourselves  
![InBlock.gif][] cn.Close();  
![InBlock.gif][]throw;  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in  
![InBlock.gif][]/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a SqlDataReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic SqlDataReader ExecuteReader(string connectionString, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][]//assign the provided values to these parameters based on parameter order  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes an array of SqlParameters  
![InBlock.gif][]return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a SqlDataReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteReader(connection, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a SqlDataReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call to the private overload using a null transaction value and an externally owned connection  
![InBlock.gif][]return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection  
![InBlock.gif][]/// using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a SqlDataReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic SqlDataReader ExecuteReader(SqlConnection connection, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteReader(connection, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a SqlDataReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteReader(transaction, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a SqlDataReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through to private overload, indicating that the connection is owned by the caller  
![InBlock.gif][]return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified  
![InBlock.gif][]/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a SqlDataReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteReader(transaction, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockEnd.gif][]\#endregion ExecuteReader  
![InBlock.gif][]  
![ContractedSubBlock.gif][]![ExpandedSubBlockStart.gif][]ExecuteScalar\#region ExecuteScalar  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in  
![InBlock.gif][]/// the connection string.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an object containing the value in the 1x1 resultset generated by the command</returns>  
![InBlock.gif][]publicstaticobject ExecuteScalar(string connectionString, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an object containing the value in the 1x1 resultset generated by the command</returns>  
![InBlock.gif][]publicstaticobject ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//create & open a SqlConnection, and dispose of it after we are done.  
![InBlock.gif][]using (SqlConnection cn =new SqlConnection(connectionString))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] cn.Open();  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes a connection in place of the connection string  
![InBlock.gif][]return ExecuteScalar(cn, commandType, commandText, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in  
![InBlock.gif][]/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an object containing the value in the 1x1 resultset generated by the command</returns>  
![InBlock.gif][]publicstaticobject ExecuteScalar(string connectionString, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][]//assign the provided values to these parameters based on parameter order  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes an array of SqlParameters  
![InBlock.gif][]return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an object containing the value in the 1x1 resultset generated by the command</returns>  
![InBlock.gif][]publicstaticobject ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteScalar(connection, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an object containing the value in the 1x1 resultset generated by the command</returns>  
![InBlock.gif][]publicstaticobject ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//create a command and prepare it for execution  
![InBlock.gif][] SqlCommand cmd =new SqlCommand();  
![InBlock.gif][] PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);  
![InBlock.gif][]  
![InBlock.gif][]//execute the command & return the results  
![InBlock.gif][]object retval = cmd.ExecuteScalar();  
![InBlock.gif][]  
![InBlock.gif][]// detach the SqlParameters from the command object, so they can be used again.  
![InBlock.gif][] cmd.Parameters.Clear();  
![InBlock.gif][]return retval;  
![InBlock.gif][]  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection  
![InBlock.gif][]/// using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an object containing the value in the 1x1 resultset generated by the command</returns>  
![InBlock.gif][]publicstaticobject ExecuteScalar(SqlConnection connection, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][]//assign the provided values to these parameters based on parameter order  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes an array of SqlParameters  
![InBlock.gif][]return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteScalar(connection, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an object containing the value in the 1x1 resultset generated by the command</returns>  
![InBlock.gif][]publicstaticobject ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteScalar(transaction, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an object containing the value in the 1x1 resultset generated by the command</returns>  
![InBlock.gif][]publicstaticobject ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//create a command and prepare it for execution  
![InBlock.gif][] SqlCommand cmd =new SqlCommand();  
![InBlock.gif][] PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);  
![InBlock.gif][]  
![InBlock.gif][]//execute the command & return the results  
![InBlock.gif][]object retval = cmd.ExecuteScalar();  
![InBlock.gif][]  
![InBlock.gif][]// detach the SqlParameters from the command object, so they can be used again.  
![InBlock.gif][] cmd.Parameters.Clear();  
![InBlock.gif][]return retval;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified  
![InBlock.gif][]/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an object containing the value in the 1x1 resultset generated by the command</returns>  
![InBlock.gif][]publicstaticobject ExecuteScalar(SqlTransaction transaction, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][]//assign the provided values to these parameters based on parameter order  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes an array of SqlParameters  
![InBlock.gif][]return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockEnd.gif][]\#endregion ExecuteScalar   
![InBlock.gif][]  
![ContractedSubBlock.gif][]![ExpandedSubBlockStart.gif][]ExecuteXmlReader\#region ExecuteXmlReader  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an XmlReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an XmlReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//create a command and prepare it for execution  
![InBlock.gif][] SqlCommand cmd =new SqlCommand();  
![InBlock.gif][] PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);  
![InBlock.gif][]  
![InBlock.gif][]//create the DataAdapter & DataSet  
![InBlock.gif][] XmlReader retval = cmd.ExecuteXmlReader();  
![InBlock.gif][]  
![InBlock.gif][]// detach the SqlParameters from the command object, so they can be used again.  
![InBlock.gif][] cmd.Parameters.Clear();  
![InBlock.gif][]return retval;  
![InBlock.gif][]  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection  
![InBlock.gif][]/// using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connection">a valid SqlConnection</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure using "FOR XML AUTO"</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an XmlReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic XmlReader ExecuteXmlReader(SqlConnection connection, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][]//assign the provided values to these parameters based on parameter order  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes an array of SqlParameters  
![InBlock.gif][]return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an XmlReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pass through the call providing null for the set of SqlParameters  
![InBlock.gif][]return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter\[\])null);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction  
![InBlock.gif][]/// using the provided parameters.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>  
![InBlock.gif][]///<param name="commandParameters">an array of SqlParamters used to execute the command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an XmlReader containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//create a command and prepare it for execution  
![InBlock.gif][] SqlCommand cmd =new SqlCommand();  
![InBlock.gif][] PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);  
![InBlock.gif][]  
![InBlock.gif][]//create the DataAdapter & DataSet  
![InBlock.gif][] XmlReader retval = cmd.ExecuteXmlReader();  
![InBlock.gif][]  
![InBlock.gif][]// detach the SqlParameters from the command object, so they can be used again.  
![InBlock.gif][] cmd.Parameters.Clear();  
![InBlock.gif][]return retval;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified  
![InBlock.gif][]/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the  
![InBlock.gif][]/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method provides no access to output parameters or the stored procedure's return value parameter.  
![InBlock.gif][]///  
![InBlock.gif][]/// e.g.:  
![InBlock.gif][]/// XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="transaction">a valid SqlTransaction</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>a dataset containing the resultset generated by the command</returns>  
![InBlock.gif][]publicstatic XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, paramsobject\[\] parameterValues)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//if we receive parameter values, we need to figure out where they go  
![InBlock.gif][]if ((parameterValues !=null) && (parameterValues.Length >0))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)  
![InBlock.gif][] SqlParameter\[\] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);  
![InBlock.gif][]  
![InBlock.gif][]//assign the provided values to these parameters based on parameter order  
![InBlock.gif][] AssignParameterValues(commandParameters, parameterValues);  
![InBlock.gif][]  
![InBlock.gif][]//call the overload that takes an array of SqlParameters  
![InBlock.gif][]return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]//otherwise we can just call the SP without params  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][]  
![ExpandedSubBlockEnd.gif][]\#endregion ExecuteXmlReader  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the  
![InBlock.gif][]/// ability to discover parameters for stored procedures at run-time.  
![ExpandedSubBlockEnd.gif][]///</summary>  
![InBlock.gif][]publicsealedclass SqlHelperParameterCache  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![ContractedSubBlock.gif][]![ExpandedSubBlockStart.gif][]private methods, variables, and constructors\#region private methods, variables, and constructors  
![InBlock.gif][]  
![InBlock.gif][]//Since this class provides only static methods, make the default constructor private to prevent  
![InBlock.gif][]//instances from being created with "new SqlHelperParameterCache()".  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]private SqlHelperParameterCache() ...\{\}  
![InBlock.gif][]  
![InBlock.gif][]privatestatic Hashtable paramCache = Hashtable.Synchronized(new Hashtable());  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// resolve at run time the appropriate set of SqlParameters for a stored procedure  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="includeReturnValueParameter">whether or not to include their return value parameter</param>  
![ExpandedSubBlockEnd.gif][]///<returns></returns>  
![InBlock.gif][]privatestatic SqlParameter\[\] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]using (SqlConnection cn =new SqlConnection(connectionString))  
![InBlock.gif][]using (SqlCommand cmd =new SqlCommand(spName,cn))  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] cn.Open();  
![InBlock.gif][] cmd.CommandType = CommandType.StoredProcedure;  
![InBlock.gif][]  
![InBlock.gif][] SqlCommandBuilder.DeriveParameters(cmd);  
![InBlock.gif][]  
![InBlock.gif][]if (!includeReturnValueParameter)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] cmd.Parameters.RemoveAt(0);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][] SqlParameter\[\] discoveredParameters =new SqlParameter\[cmd.Parameters.Count\];;  
![InBlock.gif][]  
![InBlock.gif][] cmd.Parameters.CopyTo(discoveredParameters, 0);  
![InBlock.gif][]  
![InBlock.gif][]return discoveredParameters;  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][]//deep copy of cached SqlParameter array  
![InBlock.gif][]privatestatic SqlParameter\[\] CloneParameters(SqlParameter\[\] originalParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] SqlParameter\[\] clonedParameters =new SqlParameter\[originalParameters.Length\];  
![InBlock.gif][]  
![InBlock.gif][]for (int i =0, j = originalParameters.Length; i < j; i\++)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] clonedParameters\[i\] = (SqlParameter)((ICloneable)originalParameters\[i\]).Clone();  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][]return clonedParameters;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockEnd.gif][]\#endregion private methods, variables, and constructors  
![InBlock.gif][]  
![ContractedSubBlock.gif][]![ExpandedSubBlockStart.gif][]caching functions\#region caching functions  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// add parameter array to the cache  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<param name="commandParameters">an array of SqlParamters to be cached</param>  
![InBlock.gif][]publicstaticvoid CacheParameterSet(string connectionString, string commandText, params SqlParameter\[\] commandParameters)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]string hashKey = connectionString \+":"\+ commandText;  
![InBlock.gif][]  
![InBlock.gif][] paramCache\[hashKey\] = commandParameters;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// retrieve a parameter array from the cache  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="commandText">the stored procedure name or T-SQL command</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an array of SqlParamters</returns>  
![InBlock.gif][]publicstatic SqlParameter\[\] GetCachedParameterSet(string connectionString, string commandText)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]string hashKey = connectionString \+":"\+ commandText;  
![InBlock.gif][]  
![InBlock.gif][] SqlParameter\[\] cachedParameters = (SqlParameter\[\])paramCache\[hashKey\];  
![InBlock.gif][]  
![InBlock.gif][]if (cachedParameters ==null)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]returnnull;  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]else  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return CloneParameters(cachedParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockEnd.gif][]\#endregion caching functions  
![InBlock.gif][]  
![ContractedSubBlock.gif][]![ExpandedSubBlockStart.gif][]Parameter Discovery Functions\#region Parameter Discovery Functions  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Retrieves the set of SqlParameters appropriate for the stored procedure  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method will query the database for this information, and then store it in a cache for future requests.  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an array of SqlParameters</returns>  
![InBlock.gif][]publicstatic SqlParameter\[\] GetSpParameterSet(string connectionString, string spName)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]return GetSpParameterSet(connectionString, spName, false);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]/\*\*////<summary>  
![InBlock.gif][]/// Retrieves the set of SqlParameters appropriate for the stored procedure  
![InBlock.gif][]///</summary>  
![InBlock.gif][]///<remarks>  
![InBlock.gif][]/// This method will query the database for this information, and then store it in a cache for future requests.  
![InBlock.gif][]///</remarks>  
![InBlock.gif][]///<param name="connectionString">a valid connection string for a SqlConnection</param>  
![InBlock.gif][]///<param name="spName">the name of the stored procedure</param>  
![InBlock.gif][]///<param name="includeReturnValueParameter">a bool value indicating whether the return value parameter should be included in the results</param>  
![ExpandedSubBlockEnd.gif][]///<returns>an array of SqlParameters</returns>  
![InBlock.gif][]publicstatic SqlParameter\[\] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][]string hashKey = connectionString \+":"\+ spName \+ (includeReturnValueParameter ?":include ReturnValue Parameter":"");  
![InBlock.gif][]  
![InBlock.gif][] SqlParameter\[\] cachedParameters;  
![InBlock.gif][]  
![InBlock.gif][] cachedParameters = (SqlParameter\[\])paramCache\[hashKey\];  
![InBlock.gif][]  
![InBlock.gif][]if (cachedParameters ==null)  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]...\{  
![InBlock.gif][] cachedParameters = (SqlParameter\[\])(paramCache\[hashKey\] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter));  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![InBlock.gif][]return CloneParameters(cachedParameters);  
![ExpandedSubBlockEnd.gif][] \}  
![InBlock.gif][]  
![ExpandedSubBlockEnd.gif][]\#endregion Parameter Discovery Functions  
![InBlock.gif][]  
![ExpandedSubBlockEnd.gif][] \}  
![ExpandedBlockEnd.gif][]\}

[None.gif]: /images/20220824/ba0a4324f6774d3489f0f1af9e00e43f.png
[ExpandedBlockStart.gif]: http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif
[ContractedBlock.gif]: /images/20220824/16c50d6c75e1432aaa7d16e0974a966d.png
[ExpandedSubBlockStart.gif]: /images/20220824/ed0f9630e2ec4b44ac56e7379439bdc5.png
[ContractedSubBlock.gif]: /images/20220824/2755c26f73434962a2672b6be2f75b04.png
[InBlock.gif]: /images/20220824/c4dcc24b9c534f10b6eb89ac6b1692f5.png
[ExpandedSubBlockEnd.gif]: /images/20220824/9d71caa1cb914679bfb91df5c1d54245.png
[ExpandedBlockEnd.gif]: /images/20220824/f6bbd9bcd80d4cec9bf16d2df2a750cc.png