/// /// this method Opens (if Necessary) and assigns a connection, transaction, command type and parameters /// to the provided command /// /// a valid sqlconnection, on which to execute this command /// a valid sqltransaction , or 'null' /// The CommandType (Stored Procedure, Text, etc.) /// The Stored Procedure Name OR T-SQL Command /// an array of sqlparameters to be associated with the command or 'null' if no parameters are required /// true if the connection was opened by the method, otherwose is false. private static void PrepareCommand (SqlCommand command, SqlConnection connection, SqlTransaction tran saction, CommandType commandType, string commandText, SqlParameter [] commandParameters, out bool mustCloseConnection) {if (command == null) throw new ArgumentNullException ( "command"); if (commandText == null || commandText.Length == 0) throw New Argumentnullexception ("CommandText");
// If the provided connection is not open, we will open it if (connection.State = ConnectionState.Open!) {MustCloseConnection = true; connection.Open ();} else {mustCloseConnection = false;} // Associate the connection with The command command.connection = connection;
// set the command text (stored procedure name or sql stat) Command.comMandText = CommandText;
// If we were provided a transaction, assign it if (transaction! = Null) {if (transaction.Connection == null) throw new ArgumentException ( "The transaction was rollbacked or commited, please provide an open transaction.", "Transaction "); Command.transaction = transaction;
// set the command type = commandType;
// attach the command parameters if the isy area! = Null) {attachparameters (command, commandparameters);} return;
#ndregion private utility methods & constructors
#Region ExecutenonQuery
/// /// Execute a Sqlcommand (That Returns no results) against the database specified in //////// /// EG : /// int result = ExecuteNonQuery (connString, CommandType.StoredProcedure, "PublishOrders"); /// /// A valid connection string for a SqlConnection / // The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command / // An int representing the number of rows affected by the command public static int ExecuteNonQuery (string connectionString, commandType commandType, string commandText) {// Pass through the call providing null for the set of SqlParameters return ExecuteNonQuery (Connectionstring, CommandType, CommandText, (SQ LParameter []) null);
/// /// Execute a Sqlcommand (That Returns no results) against the database specified in the connection String ////// /// /// EG : /// int result = executenonQuery (connString, CommandType.StoredProcedure, "PublishORDERS", New SQLParameter ("@PRODID", 24)); /// /// a valid connection string for a SqlConnection /// The commandType (stored procedure, text, etc.) /// The stored procedure name or T-SQL command /// An array of SqlParamters used to execute the command /// An int representing the number of rows affected by the Command public static int executenonury (String Connectionstring, CommandType CommandType, String Commandtext, Params Sqlparameter [] COMMA ndParameters) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection (Connectionstring)) {connection.open ();
// Call The overload Takes a connection in place of the connection string return executenonury (connection, commandtype, commandtext, commandparameters);}}
/// /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method provides no access to output parameters Or the stored propedure's return value parameter. /// /// Eg: /// int result = ExecutenonQuery (Connstring, "PublishRDERS", 24, 36); /// /// a valid connection string for a sqlconnection /// the name of the stored prracture /// an Array Of Objects To be assigned as the input value of the stored procedure /// An int representing the number of rows affected by the command public static int ExecuteNonQuery (string connectionString, string spName, params object [] parameterValues) {if (connectionString == null || connectionString.Length == 0) Throw new argumentnullexception ("Connectionstring"); if (spname == null || spname.length == 0) Throw new argumentnullexception ("spname");
// If we receive parameter values, we need to figure out where they go if ((parameterValues! = Null) && (parameterValues.Length> 0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connectionString, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues (commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters return ExecuteNonQuery (connectionString, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteNonQuery (connectionString, CommandType.StoredProcedure, spName) }}
/// /// Execute a Sqlcommand (That Returns no results) against the provided sqlconnection. /// //// /// EG: /// int result = ExecuteNonQuery (conn, commandtype.storedprocedure); /// /// a valid sqlconnection /// THE CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command //// An Int Reprresenting The Number of rows affected by the command public static int ExecuteNonQuery (SqlConnection connection, commandType commandType, string commandText) {// Pass through the call providing null for the set of SqlParameters return ExecuteNonQuery (connection, commandType, commandText, (SqlParameter [ ]) null);
/// /// Execute a Sqlcommand (That Returns no results) against the specified SqlConnection //// /// /// EG: // / Int result = executenonQuery (conn, commandtype.storedProcedure), "PublishORDERS", New SQLParameter ("@ productID", 24)); /// /// a Valid SqlConnection < / param> /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command < / param> /// An array of SqlParamters used to execute the command /// An int representing the number of rows affected by the command public static Int ExecutenonQuery (SqlConnection Connection, CommandType CommandType, String Commandtext, Params Sqlparameter [] CommandParameters) {if (connection == null) THR OW new Argumentnullexception ("Connection");
// Create a command and prepare it for execution SqlCommand cmd = new SqlCommand (); bool mustCloseConnection = false; PrepareCommand (cmd, connection, (SqlTransaction) null, commandType, commandText, commandParameters, out mustCloseConnection); // Finally, execute the command int retval = cmd.ExecuteNonQuery (); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear (); if (mustCloseConnection) connection.Close (); return retval;}
/// /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. ////// Eg: /// Int Result = ExecutenonQuery (CONN, "PublishORDERS", 24, 36); /// /// a Valid SqlConnection /// The name of the stored procedure /// an array of objects to be assigned as the input value The Stored Procedure /// An int representing the number of rows affected by the command public static int ExecuteNonQuery (SqlConnection connection, string spName, params object [] parameterValues) {if (connection == null) throw new ArgumentNullException ( "connection"); if (spName == null || spName. Length == 0) Throw new argumentnullexception ("spname");
// If we receive parameter values, we need to figure out where they go if ((parameterValues! = Null) && (parameterValues.Length> 0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues (commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters return ExecuteNonQuery (connection, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteNonQuery (connection, CommandType.StoredProcedure, spName) }}
/// //////// //// /// Eg: /// int result> /// EG: /// int result = ExecutenonQuery (Trans, CommandType.StoredProcedure); /// /// a valid sqltransaction /// THE CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command //// An Int Reprresenting The Number of rows affected by the command public static int ExecuteNonQuery (SqlTransaction transaction, commandType commandType, string commandText) {// Pass through the call providing null for the set of SqlParameters return ExecuteNonQuery (transaction, commandType, commandText, (SqlParameter [ ]) null);
/// /// Execute a Sqlcommand (That Returns no results) against the specified sqltransaction ///// /// /// EG: // / Int result = executenonQuery (Trans, CommandType.StoredProcedure, "Getorders", New Sqlparameter ("@PRODID", 24)); /// /// a Valid Sqltransaction < / param> /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command < / param> /// An array of SqlParamters used to execute the command /// An int representing the number of rows affected by the command public static INT ExecutenonQuery (SqlTransaction Transaction, CommandType CommandType, String Commandtext, Params Sqlparameter [] CommandParameters) {if (Transaction == Null) throw new argu mentNullException ( "transaction"); if (! transaction = null && transaction.Connection == null) throw new ArgumentException ( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution SqlCommand cmd = new SqlCommand (); bool mustCloseConnection = false; PrepareCommand (cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Finally, execute the command Int retval = cmd.executenonQuery (); // Detach the sqlparameters from the command Object, so. can be used again cmd.parameters.clear (); return return;
/// /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. ////// EG: /// Int Result = ExecutenonQuery (conn, trans, "publishORDERS", 24, 36); /// /// A valid sqltransaction /// The name of the stored procedure /// an array of objects to be assigned as the input Values of the stored procedure /// An int reresting the number of rows a ffected by the command public static int ExecuteNonQuery (SqlTransaction transaction, string spName, params object [] parameterValues) {if (transaction == null) throw new ArgumentNullException ( "transaction");! if (transaction = null && transaction .Connection == null) throw new ArgumentException ( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException ( "spname");
// If we receive parameter values, we need to figure out where they go if ((parameterValues! = Null) && (parameterValues.Length> 0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (transaction.Connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues (commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters return ExecuteNonQuery (transaction, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteNonQuery (transaction, CommandType.StoredProcedure, spName) }}
#ENDREGON EXECUTENONQUERY
#REGON EXECUTEDASET
/// /// Execute A Sqlcommand (That Returns A ResultSet and takes no parameters) against the database specified in ///// /// /// Eg: /// DataSet DS = ExecuteDataSet (Connstring, CommandType.storedProcedure, "GetRDERS"); /// /// a Valid connection string for a SqlConnection /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL COMMAND /// A dataset containing the resultset generated by the command public static DataSet ExecuteDataset (string connectionString, commandType commandType, string commandText) {// Pass through the call providing null for the set of SqlParameters return ExecuteDataset ( Connectionstring, CommandType, CommandText, (Sqlpara Meter []) null;}
/// /// Execute a Sqlcommand (That Returns a resultset) Against the Database specified in the connection string /// using the provided parameters. /// /// /// Eg: /// DataSet DS = EXECUted, CommandType.StoredProcedure, "GetRDERS", New SqlParameter ("@PRODID", 24)); /// /// A Valid Connection String for a SqlConnection /// The CommandType (Stored Procedure, Text, etc.) /// The store procedure name or T-SQL command /// An array of SqlParamters used to execute the command /// A dataset containing the resultset generated by the command public static dataset executedataset (String Connectionstring, CommandType CommandType, String Commandtext, Params Sqlparameter [] Commandpar ameters) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection (Connectionstring)) {connection.open ();
// Call the overload Takes a connection in place of the connection string return executedataset (connection, commandtype, commandtext, commandparameters);}}
/// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// Eg: /// DataSet DS = ExecutetAset (Connstring, "GetRDERS", 24, 36); /// /// A valid connection string for a SqlConnection /// The name of the stored procedure /// An array of objects To be assigned as the input value of the stored procedure /// A dataset containing the resultset generated by the command public static DataSet ExecuteDataset (string connectionString, string spName, params object [] parameterValues) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException ( "spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues! = NULL) && (ParameterValues.length>
0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connectionString, spName); // Assign the provided values to these parameters based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of SqlParameters return ExecuteDataset (connectionString, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteDataset (connectionString, CommandType.StoredProcedure, spName) }}
/// /// ///// ////// DataSet DS Against the provided sqlconnection. /// /// /// EG: /// DataSet DS = ExecuteDataSet (CONN, CommandType.StoredProcedure, "getorders"); /// /// a valid sqlconnection /// THE CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command //// a DataSet Containing The Resultset generated by the command public static DataSet ExecuteDataset (SqlConnection connection, commandType commandType, string commandText) {// Pass through the call providing null for the set of SqlParameters return ExecuteDataset (connection, commandType, commandText, (SqlParameter []) null);} /// /// Execute a SQLCOM mand (that returns a resultset) against the specified SqlConnection /// using the provided parameters /// /// /// eg:. /// DataSet ds = ExecuteDataset (conn, CommandType.StoredProcedure , "Getorders", new sqlparameter ("@ productID", 24)); /// /// a valid sqlconnection ///
The stored procedure name or T-SQL command /// An array of SqlParamters used to execute the command /// A dataset containing the resultset generated by the command public static DataSet ExecuteDataset (SqlConnection connection, commandType commandType, string commandText, params SqlParameter [] commandParameters) {if (connection == null) throw new ArgumentNullException ( "connection"); // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand (); bool mustCloseConnection = false; PrepareCommand (cmd, connection, (SqlTransaction) null, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataSet using (SqlDataAdapter da = new SqlDataAdapter (cmd)) {DataSet DS = New Dataset ();
// Fill The Dataset Using Default Values for DataTable Names, etc Da.fill (DS); // Detach The Sqlparameters from The Command Object, So They Can Be Used Again Cmd.Parameters.clear ();
IF (MustCloseConnection "Connection.Close ();
// Return the dataset return ds;}} /// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameter values This method will query the. database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method Provides no access to output parameters or the storedure's return value parameter. /// /// EG: /// DataSet DS = ExecutetAset (conn, "getorders", 24, 36); /// // / a valid sqlconnection /// The name of the stored procedure /// an array Of Objects to Be Assigned As The Input Values of The Stored Procedure /// a d ataset containing the resultset generated by the command public static DataSet ExecuteDataset (SqlConnection connection, string spName, params object [] parameterValues) {if (connection == null) throw new ArgumentNullException ( "connection"); if (spName = = NULL || SPNAME.LENGTH == 0) Throw new argumentnull == "" SPNAME ");
// If we receive parameter values, we need to figure out where they go if ((parameterValues! = Null) && (parameterValues.Length> 0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues (commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters return ExecuteDataset (connection, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteDataset (connection, CommandType.StoredProcedure, spName) }}
/// ///////// ////// DataSet DS Against the project = Executedataset (Trans, CommandType.StoredProcedure, "GetRDERS"); /// /// a valid sqltransaction /// THE CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command //// a DataSet Containing The Resultset generated by the command public static DataSet ExecuteDataset (SqlTransaction transaction, commandType commandType, string commandText) {// Pass through the call providing null for the set of SqlParameters return ExecuteDataset (transaction, commandType, commandText, (SqlParameter []) NULL);} /// /// Execute A SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters /// /// /// eg:. /// DataSet ds = ExecuteDataset (trans, CommandType.StoredProcedure , "Getorders", New SQLParameter ("@ products", 24)); /// /// a valid sqltransaction /// The stored procedure name or T-SQL command /// An array of SqlParamters used to execute the command /// A dataset containing the resultset generated by the command public static DataSet ExecuteDataset (SqlTransaction transaction, commandType commandType, string commandText, params SqlParameter [] commandParameters) {if (transaction == null) throw new ArgumentNullException ( "transaction"); if (transaction! = null && transaction.Connection == null) throw new ArgumentException ( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand (); bool mustCloseConnection = false; PrepareCommand (cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataSet using (SqlDataAdapt Er Da = New SqldataAdapter (cmd)) {DataSet DS = New Dataset ();
// Fill The Dataset Using Default Values for DataTable Names, etc Da.fill (DS); // Detach The Sqlparameters from The Command Object, So They Can Be Used Again Cmd.Parameters.clear ();
// Return the dataset return ds;}} /// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified /// SqlTransaction using the provided parameter values This method will query the. database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method Provides no access to output parameters or the storedure's return value parameter. /// /// Eg: /// DataSet DS = ExecutetAset (Trans, "GetRDERS", 24, 36); /// // / a valid sqltransaction /// The name of the stored procedure /// an Array Of Objects to Be Assigned As The Input Values of The Stored Procedure /// A dataset containing the resultset generated by the command public static DataSet ExecuteDataset (SqlTransaction transaction, string spName, params object [] parameterValues) {if (transaction == null) throw new ArgumentNullException ( "transaction"); if ( ! transaction = null && transaction.Connection == null) throw new ArgumentException ( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) Throw new argumentnullexception ("
spName "); // If we receive parameter values, we need to figure out where they go if ((parameterValues = null!) && (parameterValues.Length> 0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (transaction.Connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues (commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters return ExecuteDataset (transaction, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteDataset (transaction, CommandType.StoredProcedure, spName) }}
#ndregion executedataset #REGON EXECUTEREADER
/// /// This enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that /// we can set the appropriate CommandBehavior when calling ExecuteReader () /// private enum SqlConnectionOwnership {/// Connection is owned and managed by SqlHelper Internal, /// Connection is owned and managed by the caller External}
/// /// crete and prepare a Sqlcommand, and call executereader with the appropriate commandbehavior. /// /// /// if we created and opened the connection, we want the connection Connection to Be Closed. ///// i i t c l i i t = m l i i i = "connection" > A Valid SqlConnection, On Which To Execute this Command /// a valid sqltransaction, or 'null' /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command /// indeicates WHETHER THE CONNECTION paramete r was provided by the caller, or created by SqlHelper /// SqlDataReader containing the results of the command private static SqlDataReader ExecuteReader (SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter [] CommandParameters, SqlConnectionownership ConnectionOwnership) {if (Connection == Null) Throw new ArgumentnullException ("Connection");
bool mustCloseConnection = false; // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand (); try {PrepareCommand (cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Create a reader SqlDataReader dataReader ; // Call ExecuteReader with the appropriate CommandBehavior if (connectionOwnership == SqlConnectionOwnership.External) {dataReader = cmd.ExecuteReader ();} else {dataReader = cmd.ExecuteReader (CommandBehavior.CloseConnection);} // Detach the SqlParameters from the command . object, so they can be used again // HACK: There is a problem here, the output parameter values are fletched // when the reader is closed, so if the parameters are detached from the command // then the SqlReader can Yao set ITS Values. //hen this happen, the parameters can be used again in Other Command. Bool CANCLEAR = True; Foreach (Sqlparameter CommandParameter In cmd.parameters) {if (CommandParameter.Direction! = parameterDirection.input) can () {cmd.Parameters.Clear ();
Return DataReader;} Catch {if (MustCloseConnection) connection.close (); throw;}}
/// /// Execute A Sqlcommand (That Returns A ResultSet and takes no parameters) against the database specified in ///// /// /// eg: /// SqlDataReader dr = ExecuteReader (connString, CommandType.StoredProcedure, "GetOrders"); /// /// A valid connection string for a SqlConnection /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL COMMAND /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReader (string connectionString, commandType commandType, string commandText) {// Pass through the call providing null for the set of SqlParameters return ExecuteReader ( Connectionstring, CommandType, Comman DTEXT, (SQLParameter [] NULL);
/// /// Execute a Sqlcommand (That Returns a resultset) Against the Database specified in the connection string /// using the provided parameters. /// /// /// Eg: /// SqldataReader Dr = ExecuteReader (Connstring, CommandType.StoredProcedure), "GetRDERS", New SqlParameter ("@PRODID", 24)); /// /// A Valid Connection String for a SqlConnection /// The CommandType (Stored Procedure, Text, etc.) /// The store procedure name or T-SQL command /// An array of SqlParamters used to execute the command /// A SqlDataReader containing the resultset generated by the command public static sqldataareader ExecuteReader (String Connectionstring, CommandType CommandType, String Commandtext, Params Sqlparame ter [] commandParameters) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); SqlConnection connection = null; try {connection = new SqlConnection (connectionString); connection.Open () ;
// Call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader (connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);} catch {// If we fail to return the SqlDatReader, we Need to close the connection ureselves if (connection! = null) connections.close (); throw;}}
/// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// Eg: /// SqlDataReader DR = ExecuteReader (Connstring, "GetRDERS", 24, 36); /// /// A valid connection string for a SqlConnection /// The name of the stored procedure /// An array of objects To be assigned as the input value of the stored procedure /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReader (string connectionString, string spName, params object [] parameterValues) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException ( "spName"); // If we receive parameter values, we need to figure out where they go if (( ParameterValues! = NULL &&
(Parametervalues.lendth> 0)) {SQLParameter [] CommandParameters = SQLHELPARPARETERCACHE.GETSPPARETERSET (CONNECTIONSTRING, SPNAME); AssignParameterValues (CommandParameters, Parameter);
return ExecuteReader (connectionString, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteReader (connectionString, CommandType.StoredProcedure, spName);}}
/// /// Execute a SqlCommand (That Returns A ResultSet and takes no parameters) against the provided sqlconnection. /// /// /// EG: /// SqldataReader DR = ExecuteReader (CONN, CommandType.StoredProcedure, "getorders"); /// /// a valid sqlconnection /// The CommandType (stored procedure, text, etc.) /// The stored procedure name or T-SQL command /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReader (SqlConnection connection, commandType commandType, string commandText) {// Pass through the call providing null for the set of SqlParameters return ExecuteReader (connection, commandType, commandText, (SqlParameter []) NULL);
/// /// Execute a Sqlcommand (That Returns a resultset) against the specified sqlconnection //// /// /// EG: // / SqlDataReader Dr = ExecuteReader (Conn, CommandType.StoredProcedure, "GetRDERS", New Sqlparameter ("@PRODID", 24)); /// /// a valid sqlconnection < / param> /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command < / param> /// An array of SqlParamters used to execute the command /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReader (SqlConnection Connection, CommandType CommandType, String Commandtext, Params Sqlparameter [] CommandParameters) {// Pass TH rough the call to the private overload using a null transaction value and an externally owned connection return ExecuteReader (connection, (SqlTransaction) null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);}
/// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// /// EG: /// SqlDataReader DR = ExecuteReader (CONN, "GetRDERS", 24, 36); /// /// a Valid SqlConnection /// The name of the stored procedure /// an array of objects to be assigned as the input value The Stored Procedure /// a SqldataReader Containing The ResultSet Generated by the command public static SqlDataReader ExecuteReader (SqlConnection connection, string spName, params object [] parameterValues) {if (connection == null) throw new ArgumentNullException ( "connection"); if (spName == null || spName .Length == 0) Throw new argumentnullexception ("spname");
// If we receive parameter values, we need to figure out where they go if ((parameterValues = null) && (parameterValues.Length> 0)!) {SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connection, spName); AssignParameterValues ( CommandParameters, ParameterValues;
return ExecuteReader (connection, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteReader (connection, CommandType.StoredProcedure, spName);}}
/// /// Execute A Sqlcommand (That Returns A ResultSet and takes no parameters) against the provided sqltransaction. /// /// /// EG: /// SqldataReader DR = ExecuteReader (Trans, CommandType.StoredProcedure); /// /// a valid sqltransaction /// The CommandType (stored procedure, text, etc.) /// The stored procedure name or T-SQL command /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReader (SqlTransaction transaction, commandType commandType, string commandText) {// Pass through the call providing null for the set of SqlParameters return ExecuteReader (transaction, commandType, commandText, (SqlParameter []) NULL);
/// /// Execute A SqlCommand (That Returns A Resultset) against the specified sqltransaction ///// /// //// EG: // / SqlDataReader Dr = ExecuteReader (Trans, CommandType.StoredProcedure, "Getorders", New Sqlparameter ("@PRODID", 24)); /// /// a valid sqltransaction < / param> /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command < / param> /// An array of SqlParamters used to execute the command /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReader (SQLTransaction Transaction, CommandType CommandType, String Commandtext, Params Sqlparameter [] CommandParameters) {ix (Transacti on == null) throw new ArgumentNullException ( "transaction");! if (transaction = null && transaction.Connection == null) throw new ArgumentException ( ". The transaction was rollbacked or commited, please provide an open transaction", "transaction "); // Pass through to private overload, indicating that the connection is owned by the caller return ExecuteReader (transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);}
/// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// /// Eg: /// SqldataReader Dr = ExecuteReader (Trans, "GetRDERS", 24, 36); /// /// a Valid sqltransaction /// The name of the stored urgedure /// an array of objects to be assigned as the input value The Stored Procedure /// a SqlDataReader Containing The ResultSet Generat ed by the command public static SqlDataReader ExecuteReader (SqlTransaction transaction, string spName, params object [] parameterValues) {if (transaction == null) throw new ArgumentNullException ( "transaction");! if (transaction = null && transaction .Connection == null) throw new ArgumentException ( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException ( "spname");
// If we receive parameter values, we need to figure out where they go if ((parameterValues = null) && (parameterValues.Length> 0)!) {SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (transaction.Connection, spName); AssignParameterValues (CommandParameters, ParameterValues);
return ExecuteReader (transaction, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteReader (transaction, CommandType.StoredProcedure, spName);}}
#ENDREGON EXECUTEREADER
#region ExecuteScalar /// /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in /// the connection string. /// /// /// eg: /// int ordercount = (int) ExecuteScalar (connString, CommandType.StoredProcedure, "getordercount"); /// /// a valid connection string For a sqlconnection /// The CommandType (Stored Procedure, Text, etc.) /// The Stored Procedure Name OR t -SQL command /// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalar (string connectionString, commandType commandType, string commandText) {// Pass through the Call Providing Null for the Set of Sqlparameters Return EXECU Tescalar (Connectionstring, CommandType, CommandText, (SqlParameter [] NULL);
/// /// Execute A Sqlcommand (That Returns a 1x1 Resultset) Against the Database Specified in The Commoded Parameters. /// /// // / eg: /// int ordercount = (int) ExecuteScalar (connString, CommandType.StoredProcedure, "GetOrdercount", New Sqlparameter ("@PRODID", 24)); /// /// a Valid connection string for a sqlconnection /// The CommandType (stored procedure, text, etc.) /// The stored procedure name or T-SQL command /// An array of SqlParamters used to execute the command /// An object containing the value In the 1x1 resultset generated by the command public static objectstring, commandtype commandtype, string commandtext, p arams SqlParameter [] commandParameters) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection CONNECTION = New SqlConnection (ConnectionsTRING)) {connection.open ();
// Call the overload Takes a connection in place of the connection string return executescalar (connection, commandtype, commandtext, commandparameters);}}
/// /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters For the /// stiled procedure (The First Time Each Stored Procedure (The First Time Each Stored Procedure), And Assign The VALUES BASED ON Parameter ORDER. /// /// // this method provides no access to output Parameters or the stiled procedure's return value parameter. ////// Eg: /// int ordercount = (int) ExecuteScalar (Connstring, "GetOrdercount", 24, 36); /// /// < Param name = "connectionstring"> a valid connection string for a sqlconnection /// The name of the stored procedure /// An Array Of Objects to Be Assigned As The Input Values of The Stored Procedure /// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalar (string connectionString, string spName, params object [] parameterValues) {if (connectionString == null || connectionString .Length == 0) throw new ArgumentNullException ( "connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException ( "spName"); // If we receive parameter values, we need to Figure out where it is goeth ((ParameterValues! = NULL) &&
(ParameterValues.Length> 0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connectionString, spName); // Assign the provided Values to these Parameters based on Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of SqlParameters return ExecuteScalar (connectionString, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteScalar (connectionString, CommandType.StoredProcedure, spName) }}
/// /// Execute A Sqlcommand (That Returns a 1x1 ResultSet and takes no parameters) against the provided sqlconnection. /// //// /// EG: /// int ORDERCOUNT = (int) ExecuteScalar (conn, commandtype.storedprocedure); /// /// a valid sqlConnection /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command //// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalar (SqlConnection connection, commandType commandType, string commandText) {// Pass through the call providing null for the set of SqlParameters return ExecuteScalar (connection, commandType , CommandText, (SQLParameter []) null;}
/// /// /////// ///// /// /// EG: / // int ordercount = (int) ExecuteScalar (conn, commandtype.storedprocedure, "getordercount", new sqlparameter ("@ productID", 24)); /// /// A Valid SqlConnection /// The CommandType (Stored Procedure, Text, etc.) /// The Stored Procedure Name OR t -SQL command /// An array of SqlParamters used to execute the command /// An object containing the value in the 1x1 resultset generated by the command public static Object Executescalar (SqlConnection Connection, CommandType CommandType, String CommandText, Params Sqlparameter [] CommandParameters) {if (connection = = NULL) Throw new argumentnullexception ("Connection"); // Create a command and prepare it for execution sqlcommand cmd = new sqlcommand ();
bool mustCloseConnection = false; PrepareCommand (cmd, connection, (SqlTransaction) null, commandType, commandText, commandParameters, out mustCloseConnection); // Execute the command & return the results object retval = cmd.ExecuteScalar (); // Detach the SqlParameters from The Command Object, So They Can Be Used Again Cmd.Parameters.clear ();
IF (MustCloseConnection "Connection.Close ();
Return RetVal;
/// /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the // / stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method provides no access to output parameters or the stored Procedure's return value parameter. /// /// Eg: /// int ordercount = (int) ExecuteScalar (conn, "getordercount", 24, 36); /// /// The name of the stored procedure /// an array of objects to be assigned as The Input Values of the Stored Procedure /// An Object Containing The value in the 1x 1 resultset generated by the command public static object ExecuteScalar (SqlConnection connection, string spName, params object [] parameterValues) {if (connection == null) throw new ArgumentNullException ( "connection"); if (spName == null || SPNAME.LENGTH == 0) Throw new argumentnullexception ("spname");
// If we receive parameter values, we need to figure out where they go if ((parameterValues! = Null) && (parameterValues.Length> 0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues (commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters return ExecuteScalar (connection, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteScalar (connection, CommandType.StoredProcedure, spName) }}
/// /// Execute A Sqlcommand (That Returns a 1x1 ResultSet and takes no parameters) against the provided sqltransaction. /// /// /// EG: /// int ORDERCOUNT = (int) ExecuteScalar (Trans, CommandType.storedProcedure, "GetOrdercount"); /// /// a valid sqltransaction /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command //// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalar (SqlTransaction transaction, commandType commandType, string commandText) {// Pass through the call providing null for the set of SqlParameters return ExecuteScalar (transaction, commandType , CommandText, (SQLParameter []) null;}
/// /// Execute A Sqlcommand (That Returns a 1x1 Resultset) against the specified sqltransaction ///// /// /// EG: / // int ordercount = (int) ExecuteScalar (Trans, CommandType.StoredProcedure, "GetOrdercount", New Sqlparameter ("@PRODID", 24)); /// /// A Valid SqlTransaction /// The CommandType (Stored Procedure, Text, etc.) /// The Stored Procedure Name OR t -SQL command /// An array of SqlParamters used to execute the command /// An object containing the value in the 1x1 resultset generated by the command public static Object Executescalar (SqlTransaction Transaction, CommandType CommandType, String Commandtext, Params Sqlparameter [] CommandParameters) {IF (Transac) {ix tion == null) throw new ArgumentNullException ( "transaction");! ". The transaction was rollbacked or commited, please provide an open transaction" if (transaction = null && transaction.Connection == null) throw new ArgumentException (, "transaction ");
// Create a command and prepare it for execution SqlCommand cmd = new SqlCommand (); bool mustCloseConnection = false; PrepareCommand (cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Execute the command & return THE RESULTS Object Retval = cmd.executescalar (); // Detach The Sqlparameters from The Command Object, So The CAN Be Used Again Cmd.Parameters.clear (); Return RetVal;}
/// /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the // / stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method provides no access to output parameters or the stored Procedure's return value parameter. /// /// Eg: /// int = (int) ExecuteScalar (Trans, "GetOrdercount", 24, 36); /// /// a valid sqltransaction /// the name of the stored procedure /// an array of objects to be assigned AS The Input Values of the Stored Procedure /// An Object Containing The Value in The 1x1 resultset generated by the command public static object ExecuteScalar (SqlTransaction transaction, string spName, params object [] parameterValues) {if (transaction == null) throw new ArgumentNullException ( "transaction");! If (transaction = null && transaction.Connection == null) throw new ArgumentException ( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new Argumentnullexception ("spname");
// If we receive parameter values, we need to figure out where they go if ((parameterValues! = Null) && (parameterValues.Length> 0)) {// PPull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (transaction.Connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues (commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters return ExecuteScalar (transaction, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteScalar (transaction, CommandType.StoredProcedure, spName) }}
#ndregion executescalar
#Region Executexmlreader /// /// Execute A SqlCommand (That Returns A ResultSet and takes no parameters) against the provided sqlconnection. /// /// /// EG: // / Xmlreader r = executexmlreader (conn, commandtype.storedprocedure, "getorders"); /// /// a valid sqlconnection /// The stored procedure name or t-sql command us "for xml auto" ///// / An XmlReader containing the resultset generated by the command public static XmlReader ExecuteXmlReader (SqlConnection connection, commandType commandType, string commandText) {// Pass through the call providing null for the set of SqlParameters return ExecuteXmlReader (connection, CommandType, CommandText, (SQLParameter [] N ULL);
/// /// Execute a Sqlcommand (That Returns a resultset) against the specified sqlconnection //// /// /// EG: // / Xmlreader r = executexmlreader (conn, commandtype.storedprocedure), "getorders", new sqlparameter ("@ productID", 24)); /// /// a Valid SqlConnection < / param> /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or t-sql command "FOR XML AUTO" /// An array of SqlParamters used to execute the command /// An XmlReader containing the resultset generated by the command Public Static XmlReader Executexmlreader (SqlConnection Connection, CommandType CommandType, String Commandtext, Params Sqlparameter [] CommandParameters) {IF connection == null) throw new ArgumentNullException ( "connection"); bool mustCloseConnection = false; // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand (); try {PrepareCommand (cmd, connection, (SqlTransaction) null, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataSet XmlReader retval = cmd.ExecuteXmlReader (); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear ();
return retval;} catch {if (mustCloseConnection) connection.Close (); throw;}} /// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// / // /// this method parameters or the stiled procedure's return value parameter. ///// Eg: /// xmlreader r = ExecuteExmlreader (conn, "getorders", 24, 36) ; /// /// a valid sqlconnection /// the name of the storedure using "for xml auto" < / param> /// an array of objects to be assign ed as the input values of the stored procedure /// An XmlReader containing the resultset generated by the command public static XmlReader ExecuteXmlReader (SqlConnection connection, string spName, params object [] parameterValues) { IF (Connection == Null) Throw new ArgumentNullexception ("Connection"); if (spname == null || spname.length == 0) Throw new ArgumentnullException ("spname");
// If we receive parameter values, we need to figure out where they go if ((parameterValues! = Null) && (parameterValues.Length> 0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues (commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters return ExecuteXmlReader (connection, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteXmlReader (connection, CommandType.StoredProcedure, spName) }}
/// ///////// ///// xik> /// EG: /// XmlReader r = Executexmlreader (Trans, CommandType.StoredProcedure); /// /// a valid sqltransaction /// THE CommandType (Stored Procedure, Text, etc.) /// The storedure name or t-sql command us "for xml auto" /// An XmlReader containing the resultset generated by the command public static XmlReader ExecuteXmlReader (SqlTransaction transaction, commandType commandType, string commandText) {// Pass through the call providing null for the set of SqlParameters return ExecuteXmlReader (transaction, commandType, commandText (SQLParameter [] NULL);
/// /// Execute A SqlCommand (That Returns A Resultset) against the specified sqltransaction ///// /// //// EG: // / XmlReader R = Executexmlreader (Trans, CommandType.StoredProcedure, "GetRDERS", New Sqlparameter ("@PRODID", 24)); /// /// a valid sqltransaction < / param> /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or t-sql commist using "FOR XML AUTO" /// An array of SqlParamters used to execute the command /// An XmlReader containing the resultset generated by the command Public Static Xmlreader Executexmlreader (SqlTransaction Transaction, CommandType CommandType, String CommandText, Params Sqlparameter [] CommandParameters) {IF (Transac tion == null) throw new ArgumentNullException ( "transaction");! ". The transaction was rollbacked or commited, please provide an open transaction" if (transaction = null && transaction.Connection == null) throw new ArgumentException (, "transaction ");
// Create a command and prepare it for execution SqlCommand cmd = new SqlCommand (); bool mustCloseConnection = false; PrepareCommand (cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataSet XmlReader Retval = cmd.executexmlreader (); // Detach The Sqlparameters from the Command Object, So They Can Be Used Again Cmd.Parameters.clear (); Return RetVal;}
/// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. ////// Eg: /// Xmlreader R = ExecuteExmlreader (Trans, "GetRDERS", 24, 36); /// /// a Valid sqltransaction /// The name of the stored urgedure /// an array of objects to be assigned as the input value The Stored Procedure /// a Dataset Containing The Resultset Generated by t he command public static XmlReader ExecuteXmlReader (SqlTransaction transaction, string spName, params object [] parameterValues) {if (transaction == null) throw new ArgumentNullException ( "transaction");! if (transaction = null && transaction.Connection == null) throw new ArgumentException ( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException ( "spName ");
// If we receive parameter values, we need to figure out where they go if ((parameterValues! = Null) && (parameterValues.Length> 0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (transaction.Connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues (commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters return ExecuteXmlReader (transaction, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteXmlReader (transaction, CommandType.StoredProcedure, spName) }}
#ENDREGION EXECUTEXMLREADER
#Region FillDataSet /// /// Execute A Sqlcommand (That Returns A ResultSet and Takes No Parameters) Against the Database Specified In ////// /// /// EG: /// FillDataSet (Connstring, CommandType.StoredProcedure, "GetRDERDERS", DS, New String [] {"Orders"}); /// /// A Valid Connection String for a SqlConnection /// The CommandType (Stored Procedure, Text, etc.) /// Stored Procedure Name OR T-SQL Command /// a Dataset Wich Will Contain The ResultSet generated by the command /// this Array Will Be Used to create Table mappings allowing the dataatables to be reference /// by a user defined name (probably the actual table) public static void fil lDataset (string connectionString, CommandType commandType, string commandText, DataSet dataSet, string [] tableNames) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); if (dataSet == null ) throw new ArgumentNullException ( "dataSet"); // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection (connectionString)) {connection.Open ();
// Call the overload Takes a connection in place of the connection string FillDataSet (Connection, CommandType, CommandText, Dataset, Tablenames);}}
/// /// Execute a Sqlcommand (That Returns a resultset) Against the Database specified in the connection string /// using the provided parameters. /// /// /// Eg: /// FillDataSet (Connstring, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"ORDERS"}, New SqlParameter ("@PRODID", 24)); /// // / a valid connection string for a sqlconnection /// The CommandType (Stored Procedure, Text, ETC.) /// < param name = "commandText"> The stored procedure name or T-SQL command /// An array of SqlParamters used to execute the command /// a Dataset Wich Will Contain The ResultSet generated by the command /// this array will be used to create Table mapings allowing the dataatables to be referenced /// by a user defined name /// (probably the actual table name) public static void FillDataset (string connectionString, CommandType commandType, string commandText, DataSet dataSet, string [] tableNames, params SqlParameter [] commandParameters ) {IF (Connectionstring == Null || Connectionstring.Length == 0) Throw new argumentnullexception ("Connectionstring"); if (dataset == null) throw new argumentnullexception ("dataset");
// Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection (connectionString)) {connection.Open (); // Call the overload that takes a connection in place of the connection string FillDataset (Connection, CommandType, CommandText, Dataset, Tablenames, CommandParameters);}}
/// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method provides no access to output parameters or the stored urgedure's return value parameter. /// /// EG: /// FillDataSet (Connstring, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"Orders"}, 24); // < / remarks> /// a valid connection string for a sqlconnection /// The name of the stored procedure /// < Param name = "dataset"> a Dataset Wich Will Contain The ResultSet generated by the command /// this array will be used to create table mappings allowing the datables to be reasoning /// by a user defined name (probably the actual table name) /// ////////////// // / An array of objects to be assigned as the input values of the stored procedure public static void FillDataset (string connectionString, string spName, DataSet dataSet, string [] tableNames, params object [ ] parametervalues) {if (connectionString ==
null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); if (dataSet == null) throw new ArgumentNullException ( "dataSet"); // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection (connectionString)) {connection.Open (); // Call the overload that takes a connection in place of the connection string FillDataset (connection, spName, dataSet, tableNames, parameterValues);}}
/// /// Execute a SqlCommand (That Returns A ResultSet and takes no parameters) against the provided sqlconnection. /// //// /// EG: /// FillDataSet ( CONN, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"Orders"}; /// /// a valid sqlconnection // / The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command ///// / A dataset wich will contain the resultset generated by the command /// This array will be used to create table mappings allowing the DataTables to be referenced /// by a user defined name /// public static void FillDataSet (SqlConnection Connection, CommandType CommandType, String C Ommandtext, DataSet DataSet, String [] Tablenames) {FillDataSet (Connection, CommandType, CommandText, Dataset, Tablenames, NULL);
/// /// Execute a Sqlcommand (That Returns a resultset) against the specified sqlconnection //// /// /// EG: // / FillDataSet (CONN, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"ORDERS"}, New SqlParameter ("@ productID", 24)); /// /// a Valid SqlConnection /// The CommandType (Stored Procedure, Text, etc.) /// The THE Stored Procedure Name OR T-SQL Command /// a Dataset Wich Will Contain The ResultSet generated by the command /// this array will be used to create table mappings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// /// An array of Sqlpara mters used to execute the command public static void FillDataset (SqlConnection connection, CommandType commandType, string commandText, DataSet dataSet, string [] tableNames, params SqlParameter [] commandParameters) {FillDataset (connection, null, commandType, commandText, dataSet , Tablenames, CommandParameters;
/// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. ////// Eg: /// FillDataSet (CONN, "GetRDERS", DS, New String [] {"Orders"}, 24, 36); /// //// a valid sqlconnection /// the name of the stored procedure // a Dataset Wich Will Contain The ResultSet generated by the command /// this array will be used to create Table Mapping s allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// /// An array of objects to be assigned as the input values of the stored procedure public static void FillDataset (SqlConnection connection, string spName, DataSet dataSet, string [] tableNames, params object [] parameterValues) {if (connection == null) throw new ArgumentNullException ( "connection") ; If (DataSet ==
null) throw new ArgumentNullException ( "dataSet"); if (spName == null || spName.Length == 0) throw new ArgumentNullException ( "spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues! = null) && (parameterValues.Length> 0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet ( Connection, SPNAME);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of SqlParameters FillDataset (connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);} else {// Otherwise we can just call the SP without params FillDataset (connection, CommandType.StoredProcedure, SpName, Dataset, Tablenames;}}
/// /// Execute A Sqlcommand (That Returns A ResultSet and takes no parameters) against the provided sqltransaction. /// /// /// EG: /// FillDataSet ( TRANS, CommandType.StoredProcedure, "GetRDERDERS", DS, New String [] {"Orders"}; /// /// a valid sqltransaction // / The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command ///// / A dataset wich will contain the resultset generated by the command /// This array will be used to create table mappings allowing the DataTables to be referenced /// by a user defined name /// public static void FillDataSet (SqlTransaction Transaction, CommandType CommandType, String Commandtext, Dataset Dataset, String [] Tablenames) {FillDataset (Transaction, CommandType, CommandText, Dataset, TableNames, NULL);
/// /// Execute A SqlCommand (That Returns A Resultset) against the specified sqltransaction ///// /// //// EG: // / FillDataSet (Trans, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"ORDERS"}, New SqlParameter ("@PRODID", 24)); /// /// a valid sqltransaction /// The CommandType (Stored Procedure, Text, ETC.) /// THE Stored Procedure Name OR T-SQL Command /// a Dataset Wich Will Contain The ResultSet generated by the command /// this array will be used to create table mappings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// /// An array of SQLP aramters used to execute the command public static void FillDataset (SqlTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string [] tableNames, params SqlParameter [] commandParameters) {FillDataset (transaction.Connection, transaction, commandType, commandText , DataSet, Tablenames, CommandParameters;
/// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. ////// Eg: /// FillDataSet (Trans, "GetRDERS", DS, New String [] {"Orders"}, 24, 36); /// //// a valid sqltransaction /// the name of the stored procedure /// a dataset wich Will Contain The ResultSet generated by the command /// An array of objects to be assigned as the input values of the stored procedure public static void FillDataset (SqlTransaction transaction, string spName, DataSet dataSet, string [] tableNames, params object [] parameterValues) {if (transaction == null) throw new ArgumentNullException ( "transaction") ; If (Transaction! = NULL &&
transaction.Connection == null) throw new ArgumentException ( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (dataSet == null) throw new ArgumentNullException ( "dataSet"); if ( spName == null || spName.Length == 0) throw new ArgumentNullException ( "spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues = null!) && (parameterValues. Length> 0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (transaction.Connection, spName);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of SqlParameters FillDataset (transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);} else {// Otherwise we can just call the SP without params FillDataset (transaction, CommandType.StoredProcedure, SpName, Dataset, Tablenames;}}
/// /// Private helper method that execute a SqlCommand (that returns a resultset) against the specified SqlTransaction and SqlConnection /// using the provided parameters. /// /// / // eg: /// FillDataSet (CONN, TRANS, CommandType.StoredProcedure, "GetORDERS", DS, New String [] {"ORDERS"}, New SqlParameter ("@ productID", 24)); /// /// a valid sqlconnection // a valid sqltransaction /// the CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command /// a dataset wich will contain the resultset generated by the command /// This array will be used to create table mappings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// /// An array of SqlParamters used to execute the command private static void FillDataset (SqlConnection connection, SqlTransaction transaction, commandType commandType, string commandText, DataSet dataSet, string [] tableNames, params SqlParameter [] commandParameters) {if (connection == null) throw new ArgumentNullException ( "connection"); if (dataSet ==
null) throw new ArgumentNullException ( "dataSet"); // Create a command and prepare it for execution SqlCommand command = new SqlCommand (); bool mustCloseConnection = false; PrepareCommand (command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection ); // Create the DataAdapter & DataSet using (SqlDataAdapter dataAdapter = new SqlDataAdapter (command)) {// Add the table mappings specified by the user if null && tableNames.Length (tableNames => 0) {string tableName = "Table! "; for (int index = 0; index
// DETACH The Sqlparameters from the Command Object, So The CAN be Used Again Command.Parameters.clear ();
IF (MustCloseConnection) Connection.Close ();} #endregion #region UpdatedataSet /// /// Executes The Respect, or deleted row in the dataset. /// / // /// Eg: /// UpdatedataSet (CONN, INSERTCOMMAND, DeleteCommand, UpdateCommand, DataSet, "ORDER"); /// /// a Valid transact-SQL statement or stored procedure to insert new records into the data source /// A valid transact-SQL statement or stored procedure to delete records from the data source /// a valid transact-sql statement or storedure used to update records in the data source /// The dataset used to update the data Source /// The datable used to update the data source. public static void UpdateDataset (SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName) {if (insertCommand == null) throw new ArgumentNullException ( "insertCommand"); if (deleteCommand == null) throw new ArgumentNullException ( "deleteCommand "); If (updatecommand == null) throw new argumentnullexception (" UpdateCommand "); if (TableName == Null || Tablename.length == 0) Throw new ArgumentnullException (" TableName ");
// Create a SqlDataAdapter, and dispose of it after we are done using (SqlDataAdapter dataAdapter = new SqlDataAdapter ()) {// Set the data adapter commands dataAdapter.UpdateCommand = updateCommand; dataAdapter.InsertCommand = insertCommand; dataAdapter.DeleteCommand = deleteCommand; // Update the dataset changes in the data source dataAdapter.Update (Dataset, TableName);
// commit all the change.acceptchange ();}} #ENDREGON
#Region createCommand /// /// simmage> /// ///// /// ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// Eg: /// Sqlcommand Command = CreateCommand (CREATECOMMAND (CRETETECUSTOMER "," Customerid "," Customername "); /// /// a valid sqlconnection Object /// The name of the stored procedure /// An array of string to be assigned as the source columns of the stored procedure parameters < / param> /// A valid SqlCommand object public static SqlCommand CreateCommand (SqlConnection connection, string spName, params string [] sourceColumns) {if (connection == null) throw new ArgumentNullException ( "connection") ; If (spname == null || spname.length == 0) throw new argumentnullexception ("spname"); // Create a sqlcommand sqlcommand cmd = new SQLCOMMAND (SPNAME, Connection); cmd.commandtype = commandtype.storedProcedure;
// If we receive parameter values, we need to figure out where they go if ((sourceColumns! = Null) && (sourceColumns.Length> 0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover Them & populate the cache) Sqlparameter [] CommandParameters = SQLHELPARPARETERCACHE.GETSPPARETERSET (Connection, SPNAME);
// Assign the provided source columns to these parameters based on parameter order for (int index = 0; index
// attach the discovered parameters to the sqlcommand object attachparameters (cmd, commandparameters);
Return cmd;} #ENDREGION
#region ExecuteNonQueryTypedParams /// /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in /// the connection string using the dataRow column values as the stored procedure's parameters values. // / This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on row values. /// /// a valid connection string for a sqlConnection /// The name of the stored processure /// the dataRow used to hold the stored procedure's parameter values. /// An int representing the number of rows affected by the command public static int ExecuteNonQueryTypedParams (String connectionString, String spName, DataRow dataRow) { i f (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException ( "spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow! = null && dataRow.ItemArray.Length> 0) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache ) SQLParameter [] CommandParameters =
SqlHelperParameterCache.GetSpParameterSet (connectionString, spName); // Set the parameters values AssignParameterValues (commandParameters, dataRow); return SqlHelper.ExecuteNonQuery (connectionString, CommandType.StoredProcedure, spName, commandParameters);} else {return SqlHelper.ExecuteNonQuery (connectionString, CommandType. StoredProcedure, spName);}} /// /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection /// using the dataRow column values as the stored procedure's parameters values //. / This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on row values. /// /// a valid sqlconnection object /// the name of the stored procedure /// The datarow buy to hold the store procedure's parameter values. /// An int representing the number of rows affected by the command public static int ExecuteNonQueryTypedParams (SqlConnection connection, String spName, DataRow dataRow) {if (connection == null Throw new argumentnullexception ("connection"); if (spname == null || spname.length == 0) throw new argumentnullexception ("spname");
// If the row has values, the store procedure parameters must be initialized if (dataRow! = Null && dataRow.ItemArray.Length> 0) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connection, spName); // Set the parameters values AssignParameterValues (commandParameters, dataRow); return SqlHelper.ExecuteNonQuery (connection, CommandType.StoredProcedure, spName, commandParameters);} else {return SQLHELPER.EXECUTENONQUERY (Connection, CommandType.StoredProcedure, SpName);}}
/// /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified /// SqlTransaction using the dataRow column values as the stored procedure's parameters values. /// This method will query the database To Discover THE Parameters for the /// stiled procedure (The First Time Each Stored Procedure IS Called), And Assign The VALUES BASED On Row Values. /// /// a valid SqlTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// An int representing the number of rows affected by the command public static int ExecuteNonQueryTypedParams (SqlTransaction transaction, String spName, DataRow dataRow) {if (transaction == null) throw new ArgumentNullException ("Transaction"); IF ! Transaction = null && transaction.Connection == null) throw new ArgumentException ( "The transaction was rollbacked or commited, please provide an open transaction.", "Transaction"); if (spName == null || spName.Length == 0) Throw new argumentnullexception ("spname");
// Sf the row has values, the store procedure parameters must be initialized if (dataRow! = Null && dataRow.ItemArray.Length> 0) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (transaction.Connection, spName); // Set the parameters values AssignParameterValues (commandParameters, dataRow); return SqlHelper.ExecuteNonQuery (transaction, CommandType.StoredProcedure, spName, commandParameters);} else {Return SqlHelper.executenonQuery (Transaction, CommandType.StoredProcedure, SPNAME);}} #ENDREGION
#region ExecuteDatasetTypedParams /// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in /// the connection string using the dataRow column values as the stored procedure's parameters values. // / This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on row values. /// /// a valid connection string for a sqlConnection /// The name of the stored processure /// the dataRow used to hold the stored procedure's parameter values. /// A dataset containing the resultset generated by the command public static DataSet ExecuteDatasetTypedParams (string connectionString, String spName, DataRow dataRow) {if ( connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException ( "spName");
// If the row has values, the store procedure parameters must be initialized if (dataRow! = Null && dataRow.ItemArray.Length> 0) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connectionString, spName); // Set the parameters values AssignParameterValues (commandParameters, dataRow); return SqlHelper.ExecuteDataset (connectionString, CommandType.StoredProcedure, spName, commandParameters);} else {return SQLHELPER.EXECUtedataset (Connectionstring, CommandType.StoredProcedure, SPNAME);}}
/// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the dataRow column values as the store procedure's parameters values. /// This method will query the database To Discover THE Parameters for the /// stiled procedure (the first time each storedure (the first time each storedure), and assign the values based on row value. /// /// a valid SqlConnection object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// A dataset containing the resultset generated by the command public static DataSet ExecuteDatasetTypedParams (SqlConnection connection, String spName, DataRow dataRow) {if (connection == null) throw new ArgumentNullException ( " Connection "); if (SpName == NULL || spname.length == 0) Throw new argumentnullexception ("spname");
// If the row has values, the store procedure parameters must be initialized if (dataRow! = Null && dataRow.ItemArray.Length> 0) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connection, spName); // Set the parameters values AssignParameterValues (commandParameters, dataRow); return SqlHelper.ExecuteDataset (connection, CommandType.StoredProcedure, spName, commandParameters);} else {return SQLHELPER.EXECUtedataset (Connection, CommandType.StoredProcedure);}}
/// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the dataRow column values as the stored procedure's parameters values. /// This method will query the database To Discover THE Parameters for the /// stiled procedure (The First Time Each Stored Procedure IS Called), And Assign The VALUES BASED On Row Values. /// /// a valid SqlTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// A dataset containing the resultset generated by the command public static DataSet ExecuteDatasetTypedParams (SqlTransaction transaction, String spName, DataRow dataRow) {if (transaction == null) throw new ArgumentNullException ( " "); IF ! Transaction = null && transaction.Connection == null) throw new ArgumentException ( "The transaction was rollbacked or commited, please provide an open transaction.", "Transaction"); if (spName == null || spName.Length == 0) Throw new argumentnullexception ("spname");
// If the row has values, the store procedure parameters must be initialized if (dataRow! = Null && dataRow.ItemArray.Length> 0) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (transaction.Connection, spName); // Set the parameters values AssignParameterValues (commandParameters, dataRow); return SqlHelper.ExecuteDataset (transaction, CommandType.StoredProcedure, spName, commandParameters);} else {Return Sqlhelper.executed, CommandType.StoredProcedure, SpName;}} #ENDREGION
#region ExecuteReaderTypedParams /// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in /// the connection string using the dataRow column values as the stored procedure's parameters values. // / This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// a valid connection string for a sqlConnection /// The name of the stored processure /// the dataRow used to hold the stored procedure's parameter values. /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReaderTypedParams (String connectionString, String spName, DataRow dataRow) {If (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException ( "spName"); / / If the row has values, the store procedure parameters must be initialized if (dataRow! = null && dataRow.ItemArray.Length> 0) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the Cache) SQLParameter [] CommandParameters =
SqlHelperParameterCache.GetSpParameterSet (connectionString, spName); // Set the parameters values AssignParameterValues (commandParameters, dataRow); return SqlHelper.ExecuteReader (connectionString, CommandType.StoredProcedure, spName, commandParameters);} else {return SqlHelper.ExecuteReader (connectionString, CommandType. StoredProcedure, spName);}} /// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the dataRow column values as the stored procedure's parameters values //. / This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// a Valid SqlConn Ection Object /// The name of the stored procedure /// The Datarow Used to hold The Stored Procedure's Parameter VALUES. < / param> /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReaderTypedParams (SqlConnection connection, String spName, DataRow dataRow) {if (connection == null) throw new ArgumentNullException ( "connection "); If (spname == null || spname.length == 0) throw new argumentnullexception (" spname ");
// If the row has values, the store procedure parameters must be initialized if (dataRow! = Null && dataRow.ItemArray.Length> 0) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connection, spName); // Set the parameters values AssignParameterValues (commandParameters, dataRow); return SqlHelper.ExecuteReader (connection, CommandType.StoredProcedure, spName, commandParameters);} else {return SqlHelper.ExecuteReader (connection, CommandType.StoredProcedure, spName);}} /// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the dataRow column va lues as the stored procedure's parameters values. /// This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. // / /// a valid sqltransaction Object /// The name of the stored procedure /// The Datarow Used to Hold The Stored Procedure's Parameter Values. /// a SqlDataReader Containing The ResultSet Generated by The Command <
/ Returns> public static SqlDataReader ExecuteReaderTypedParams (SqlTransaction transaction, String spName, DataRow dataRow) {if (transaction == null) throw new ArgumentNullException ( "transaction"); if (! Transaction = null && transaction.Connection == null) throw new ArgumentException ( ". The transaction was rollbacked or commited, please provide an open transaction", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException ( "spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow! = null && dataRow.ItemArray.Length> 0) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) Sqlparameter [] commandparameters = SQLHELPARPARETERCACHE.GETSPPARETERSET (Transaction.Connection, SpName); // Set The Parameters Values AssignParameterValu es (commandParameters, dataRow); return SqlHelper.ExecuteReader (transaction, CommandType.StoredProcedure, spName, commandParameters);} else {return SqlHelper.ExecuteReader (transaction, CommandType.StoredProcedure, spName);}} #endregion
#region ExecuteScalarTypedParams /// /// Execute a stored via procedure a SqlCommand (that returns a 1x1 resultset) against the database specified in /// the connection string using the dataRow column values as the stored procedure's parameters values. / // This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// < Param name = "connectionstring"> a valid connection string for a sqlconnection /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalarTypedParams (String connectionString, String spName, DataRowdataRow) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException ( "spName") ; // If the row has values, the store procedure parameters must be initialized if (dataRow = null && dataRow.ItemArray.Length> 0!) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & Populate the cache) SQLParameter [] commandparameters =
SqlHelperParameterCache.GetSpParameterSet (connectionString, spName); // Set the parameters values AssignParameterValues (commandParameters, dataRow); return SqlHelper.ExecuteScalar (connectionString, CommandType.StoredProcedure, spName, commandParameters);} else {return SqlHelper.ExecuteScalar (connectionString, CommandType. StoredProcedure, spName);}} /// /// Execute via a stored procedure a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection /// using the dataRow column values as the stored procedure's parameters values /. // This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// < Param name = "connection"> a Valid SQL Connection Object /// the name of the storedure /// The DataRow Used to hold The Stored Procedure's parameter value. < / param> /// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalarTypedParams (SqlConnection connection, String spName, DataRow dataRow) {if (connection == null) throw new Argumentnullexception ("Connection"); if (spname == null || spname.length == 0) throw new argumentnullexception ("spname");
// If the row has values, the store procedure parameters must be initialized if (dataRow! = Null && dataRow.ItemArray.Length> 0) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connection, spName); // Set the parameters values AssignParameterValues (commandParameters, dataRow); return SqlHelper.ExecuteScalar (connection, CommandType.StoredProcedure, spName, commandParameters);} else {return SQLHELPER.EXECUTESCALARARAR (CONNECTION, CommandType.StoredProcedure, SPNAME);}}
/// /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction /// using the dataRow column values as the stored procedure's parameters values. /// This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// A valid SqlTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values . /// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalarTypedParams (SqlTransaction transaction, String spName, DataRow dataRow) {if (transaction == null) Throw new argumentnullexception "Transaction");! If (transaction = null && transaction.Connection == null) throw new ArgumentException ( "The transaction was rollbacked or commited, please provide an open transaction.", "Transaction"); if (spName == null || SPNAME.LENGTH == 0) Throw new argumentnullexception ("spname");
// If the row has values, the store procedure parameters must be initialized if (dataRow! = Null && dataRow.ItemArray.Length> 0) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (transaction.Connection, spName); // Set the parameters values AssignParameterValues (commandParameters, dataRow); return SqlHelper.ExecuteScalar (transaction, CommandType.StoredProcedure, spName, commandParameters);} else {Return Sqlhelper.executeScalar (Transaction, CommandType.storedProcedure);}} #ENDREGION
#region ExecuteXmlReaderTypedParams /// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the dataRow column values as the stored procedure's parameters values. /// This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// /// A valid sqlconnection object /// the name of the stored procedure /// The DataRow Used to Hold The Stored ProCedure parameter values. /// An XmlReader containing the resultset generated by the command public static XmlReader ExecuteXmlReaderTypedParams (SqlConnection connection, String spName, DataRow dataRow) {if (connection == null) throw New Argumentnullexception ("Connection"); if (spname == null || spname.length == 0) throw new argumentnullexception ("spname");
// If the row has values, the store procedure parameters must be initialized if (dataRow! = Null && dataRow.ItemArray.Length> 0) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (connection, spName); // Set the parameters values AssignParameterValues (commandParameters, dataRow); return SqlHelper.ExecuteXmlReader (connection, CommandType.StoredProcedure, spName, commandParameters);} else {return SQLHELPER.EXECUTEXMLREADER (Connection, CommandType.StoredProcedure, SPNAME);}}
/// /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the dataRow column values as the stored procedure's parameters values. /// This method will query the database To Discover THE Parameters for the /// stiled procedure (the first time each storedure), and assign the values based on parameter ORDER. /// /// a valid SqlTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// An XmlReader containing the resultset generated by the command public static XmlReader ExecuteXmlReaderTypedParams (SqlTransaction transaction, String spName, DataRow dataRow) {if (transaction == null) throw new ArgumentNullException ( " TRANSACTI . On "!); If (transaction = null && transaction.Connection == null) throw new ArgumentException (" The transaction was rollbacked or commited, please provide an open transaction "," transaction "); if (spName == null | | spname.length == 0) Throw new argumentnullexception ("spname");
// If the row has values, the store procedure parameters must be initialized if (dataRow! = Null && dataRow.ItemArray.Length> 0) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter [] commandParameters = SqlHelperParameterCache.GetSpParameterSet (transaction.Connection, spName); // Set the parameters values AssignParameterValues (commandParameters, dataRow); return SqlHelper.ExecuteXmlReader (transaction, CommandType.StoredProcedure, spName, commandParameters);} else {Return SqlHelper.executexmlreader (Transaction, CommandType.storedProcedure, SpName);}} #ENDREGON}
/// /// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the /// ability to discover parameters for stored procedures at run-time. /// public sealed class SqlHelperParameterCache { #Region Private Methods, Variables, And Constructionors
// Since this class provides only static methods, make the default constructor private to prevent // instances from being created with "new SqlHelperParameterCache ()" private SqlHelperParameterCache () {}
Private static hashtable paramcache = hashtable.synchronized (new hashtable ());
/// /// resolve at run time the appropriate set of sqlparameters for a storemary> /// a valid sqlConnection Object // / The name of the stored procedure /// Whether or not to include their return value parameter /// The parameter array discovered private static SqlParameter [] DiscoverSpParameterSet (SqlConnection connection, string spName, bool includeReturnValueParameter) {if (connection == null) throw new ArgumentNullException ( "connection");. if (spName == null || spName .Length == 0) Throw new argumentnullexception ("spname"); sqlcommand cmd = new sqlcommand (spname, connection); cmd.commandtype = commandtype.storedProcedure;
Connection.open (); SQLCommandbuilder.deriveParameters (CMD); connection.close ();
IF (! incdudeRnValueParameter) {cmd.parameters.removeat (0);} SQLParameter [] discoveredParameters = New Sqlparameter [cmd.parameters.count];
Cmd.Parameters.copyTo (DiscoveredParameters, 0);
// init the parameters with a dbnull value foreach (sqlparameter discoveredparameter in discoveredparameters) {discoveredParameter.Value = dbnull.Value;} returnis valuedparameter;
/// /// Deep Copy of Cached Sqlparameter Array /// /// private stat Sqlparameter [] CloneParameters (SqlParameter [] originalParameters) {SqlParameter [] clonedParameters = new SqlParameter [originalParameters.Length]; for (int i = 0, j = originalParameters.Length; i
Return clonedparameters;
#ENDREGON Private Methods, Variables, And Constructionors
#Region Caching Functions
/// /// add parameter array to the cache /// /// a valid connection string for a sqlconnection /// The stored procedure name or T-SQL command /// An array of SqlParamters to be cached public static void CacheParameterSet (string connectionString, string commandText , params SqlParameter [] commandParameters) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); if (commandText == null || commandText.Length == 0) throw new ArgumentNullException ("CommandText");
String hashkey = connectionstring ":" CommandText;
Paramcache [Hashkey] = CommandParameters;
/// /// Retrieve a parameter array from the cache /// /// a valid connection string for a SqlConnection //// The stored procedure name or T-SQL command /// An array of SqlParamters public static SqlParameter [] GetCachedParameterSet (string connectionString, string commandText) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); if (commandText == null || commandText.Length == 0) throw new ArgumentNullException ( "commandText"); string hashKey = connectionString ":" CommandText;
Sqlparameter [] cachedparameters = paramcache [hashkey] as sqlparameter []; if (cachedparameters == null) {return null;} else {return cloneparameters (cachedparameters);}}
#ndregion caching functions
#Region Parameter Discovery Functions
/// /// Retrieves the set of sqlparameters appropriate for the storedure /// /// /// this method will query the database for this information, and then store IT IN a cache for future requests. /// /// a valid connection string for a SqlConnection /// The name of the stored procedure /// An array of SqlParameters public static SqlParameter [] GetSpParameterSet (string connectionString, string spName) {return GetSpParameterSet (connectionString, spName, false);}
/// /// Retrieves the set of sqlparameters appropriate for the storedure /// /// /// this method will query the database for this information, and then store IT IN a cache for future requests. /// /// a valid connection string for a SqlConnection /// The name of the stored procedure /// A bool value indicating whether the return value parameter should be included in the results /// An array of SqlParameters public static SqlParameter [] GetSpParameterSet (string connectionString, string spName, bool includeReturnValueParameter) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); if (spName == null || spName .Length == 0) Throw new argumentnullexception ("spname"); using (SqlConnection Connection = New SqlConnection) Nstring) {Return GetSpparametersetInternal (Connection, SPNAME, IncludereturnValueParameter);}}
/// /// Retrieves the set of sqlparameters appropriate for the storedure /// /// /// this method will query the database for this information, and then store IT IN a cache for future requests. /// /// a valid sqlconnection object /// The name of the stored procedure < / param> /// An array of SqlParameters internal static SqlParameter [] GetSpParameterSet (SqlConnection connection, string spName) {return GetSpParameterSet (connection, spName, false);}
/// /// Retrieves the set of sqlparameters appropriate for the storedure /// /// /// this method will query the database for this information, and then store IT IN a cache for future requests. /// /// a valid sqlconnection object /// The name of the stored procedure < / param> /// A bool value indicating whether the return value parameter should be included in the results /// An array of SqlParameters internal static SqlParameter [] GetSpParameterSet (SqlConnection connection, string spName, bool includeReturnValueParameter) {if (connection == null) throw new ArgumentNullException ( "connection"); using (SqlConnection clonedConnection = (SqlConnection) ((ICloneable) connection) .Clone ()) { Return GetSpparamet ERSETINTERNAL (CloneyDConnection, SPName, IncludeTurnValueParameter);}}
/// /// Retrieves the set of sqlparameters appropriate for the stored urgedure /// /// a valid sqlconnection object /// The name of the stored procedure /// A bool value indicating whether the return value parameter should be included in the results /// < returns> An array of SqlParameters private static SqlParameter [] GetSpParameterSetInternal (SqlConnection connection, string spName, bool includeReturnValueParameter) {if (connection == null) throw new ArgumentNullException ( "connection"); if (spName == null | | spName.Length == 0) throw new ArgumentNullException ( "spName"); string hashKey = connection.ConnectionString ? ":" spName (includeReturnValueParameter ": include ReturnValue Parameter": "");
SqlParameter [] cachedParameters; cachedParameters = paramCache [hashKey] as SqlParameter []; if (cachedParameters == null) {SqlParameter [] spParameters = DiscoverSpParameterSet (connection, spName, includeReturnValueParameter); paramCache [hashKey] = spParameters; cachedParameters = spParameters;} Return CloneParameters;} #ENDREGON Parameter Discovery Functions
}
