//=============================================================================== // Microsoft Data Access Application Block for .NET微软.NET数据访问程序块 //http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp(可在此网页查看) // // SQLHelper.cs // // This file contains the implementations of the SqlHelper and SqlHelperParameterCache // classes. 这个文件实现了SqlHelper类和SqlHelperParameterCache类 //其中SqlHelper类执行各种方式的数据操作处理，而SqlHelperParameterCache类则是获得存储过程的参数集合 // For more information see the Data Access Application Block Implementation Overview. // //=============================================================================== // Copyright (C) 2000-2001 Microsoft Corporation // All rights reserved. // THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY // OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT // LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR // FITNESS FOR A PARTICULAR PURPOSE. //============================================================================== using System; using System.Data; using System.Xml; using System.Data.SqlClient; using System.Collections; namespace DataAccessTool { /**////<summary> /// The SqlHelper class is intended to encapsulate high performance, scalable best practices for /// common uses of SqlClient. /// SqlHelper类用来封装数据处理 ///</summary> publicsealedclass SqlHelper { private utility methods & constructors#region private utility methods & constructors publicstaticstring CONNSTR=System.Configuration.ConfigurationSettings.AppSettings["SqlServer"].ToString(); //Since this class provides only static methods, make the default constructor private to prevent //instances from being created with "new SqlHelper()". private SqlHelper() {} /**////<summary> /// 这个方法用来将命令对象和一组参数对象联系起来 /// /// This method will assign a value of DbNull to any parameter with a direction of /// InputOutput and a value of null. 给输出类型参数对象赋空值 /// /// This behavior will prevent default values from being used, but /// this will be the less common case than an intended pure output parameter (derived as InputOutput) /// where the user provided no input value. ///</summary> ///<param name="command">The command to which the parameters will be added</param> ///<param name="commandParameters">an array of SqlParameters tho be added to command</param> privatestaticvoid AttachParameters(SqlCommand command, SqlParameter[] commandParameters) { foreach (SqlParameter p in commandParameters) { //check for derived output value with no value assigned if ((p.Direction == ParameterDirection.InputOutput) && (p.Value ==null)) { p.Value = DBNull.Value; } command.Parameters.Add(p); } } /**////<summary> /// 这个方法用来给一组参数对象赋值 ///</summary> ///<param name="commandParameters">array of SqlParameters to be assigned values</param> ///<param name="parameterValues">array of objects holding the values to be assigned</param> privatestaticvoid AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues) { if ((commandParameters ==null) || (parameterValues ==null)) { //do nothing if we get no data return; } // we must have the same number of values as we pave parameters to put them in if (commandParameters.Length != parameterValues.Length) { thrownew ArgumentException("Parameter count does not match Parameter Value count."); } //iterate through the SqlParameters, assigning the values from the corresponding position in the //value array for (int i =0, j = commandParameters.Length; i < j; i++) { commandParameters[i].Value = parameterValues[i]; } } /**////<summary> /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command. ///</summary> ///<param name="command">the SqlCommand to be prepared</param> ///<param name="connection">a valid SqlConnection, on which to execute this command</param> ///<param name="transaction">a valid SqlTransaction, or 'null'</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param> privatestaticvoid PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters) { //if the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { connection.Open(); } //associate the connection with the command command.Connection = connection; //set the command text (stored procedure name or SQL statement) command.CommandText = commandText; //if we were provided a transaction, assign it. if (transaction !=null) { command.Transaction = transaction; } //set the command type command.CommandType = commandType; //attach the command parameters if they are provided if (commandParameters !=null) { AttachParameters(command, commandParameters); } return; } #endregion private utility methods & constructors ExecuteNonQuery#region ExecuteNonQuery /**////<summary> /// 执行一个指定连接串上的一个SqlCommand（不返回记录集也没有任何参数） /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in /// the connection string. ///</summary> ///<remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders"); ///</remarks> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<returns>an int representing the number of rows affected by the command</returns> publicstaticint ExecuteNonQuery(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null); } /**////<summary> /// 执行一个指定连接串上的一个SqlCommand（不返回记录集），使用指定的参数集 /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>an int representing the number of rows affected by the command</returns> publicstaticint ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn =new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteNonQuery(cn, commandType, commandText, commandParameters); } } /**////<summary> /// 执行一个存储过程并赋值，这个方法将从数据库中获得存储过程的参数对象并根据其顺序赋值 /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36); ///</remarks> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="spName">the name of the stored prcedure</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>an int representing the number of rows affected by the command</returns> publicstaticint ExecuteNonQuery(string connectionString, string spName, paramsobject[] parameterValues) { //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); } //otherwise we can just call the SP without params else { return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName); } } /**////<summary> /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection. ///</summary> ///<remarks> /// e.g.: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders"); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<returns>an int representing the number of rows affected by the command</returns> publicstaticint 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); } /**////<summary> /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>an int representing the number of rows affected by the command</returns> publicstaticint ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd =new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //finally, execute the command. int retval = cmd.ExecuteNonQuery(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } /**////<summary> /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>an int representing the number of rows affected by the command</returns> publicstaticint ExecuteNonQuery(SqlConnection connection, string spName, paramsobject[] parameterValues) { //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.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(connection, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName); } } /**////<summary> /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction. ///</summary> ///<remarks> /// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders"); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<returns>an int representing the number of rows affected by the command</returns> publicstaticint 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); } /**////<summary> /// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>an int representing the number of rows affected by the command</returns> publicstaticint ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd =new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //finally, execute the command. int retval = cmd.ExecuteNonQuery(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } /**////<summary> /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>an int representing the number of rows affected by the command</returns> publicstaticint ExecuteNonQuery(SqlTransaction transaction, string spName, paramsobject[] parameterValues) { //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.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(transaction, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteNonQuery ExecuteDataSet#region ExecuteDataSet /**////<summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. ///</summary> ///<remarks> /// e.g.: /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders"); ///</remarks> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<returns>a dataset containing the resultset generated by the command</returns> publicstatic DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null); } /**////<summary> /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>a dataset containing the resultset generated by the command</returns> publicstatic DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn =new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteDataset(cn, commandType, commandText, commandParameters); } } /**////<summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36); ///</remarks> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>a dataset containing the resultset generated by the command</returns> publicstatic DataSet ExecuteDataset(string connectionString, string spName, paramsobject[] parameterValues) { //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); } //otherwise we can just call the SP without params else { return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName); } } /**////<summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. ///</summary> ///<remarks> /// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders"); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<returns>a dataset containing the resultset generated by the command</returns> publicstatic 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); } /**////<summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>a dataset containing the resultset generated by the command</returns> publicstatic DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd =new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //create the DataAdapter & DataSet 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(); //return the dataset return ds; } /**////<summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>a dataset containing the resultset generated by the command</returns> publicstatic DataSet ExecuteDataset(SqlConnection connection, string spName, paramsobject[] parameterValues) { //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.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(connection, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteDataset(connection, CommandType.StoredProcedure, spName); } } /**////<summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. ///</summary> ///<remarks> /// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders"); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<returns>a dataset containing the resultset generated by the command</returns> publicstatic 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); } /**////<summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>a dataset containing the resultset generated by the command</returns> publicstatic DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd =new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //create the DataAdapter & DataSet 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(); //return the dataset return ds; } /**////<summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>a dataset containing the resultset generated by the command</returns> publicstatic DataSet ExecuteDataset(SqlTransaction transaction, string spName, paramsobject[] parameterValues) { //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.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(transaction, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteDataset(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteDataSet ExecuteReader#region ExecuteReader /**////<summary> /// this enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that /// we can set the appropriate CommandBehavior when calling ExecuteReader() ///</summary> privateenum SqlConnectionOwnership { /**////<summary>Connection is owned and managed by SqlHelper</summary> Internal, /**////<summary>Connection is owned and managed by the caller</summary> External } /**////<summary> /// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior. ///</summary> ///<remarks> /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed. /// /// If the caller provided the connection, we want to leave it to them to manage. ///</remarks> ///<param name="connection">a valid SqlConnection, on which to execute this command</param> ///<param name="transaction">a valid SqlTransaction, or 'null'</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param> ///<param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param> ///<returns>SqlDataReader containing the results of the command</returns> privatestatic SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) { //create a command and prepare it for execution SqlCommand cmd =new SqlCommand(); PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters); //create a reader SqlDataReader dr; // call ExecuteReader with the appropriate CommandBehavior if (connectionOwnership == SqlConnectionOwnership.External) { dr = cmd.ExecuteReader(); } else { dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); } // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return dr; } /**////<summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. ///</summary> ///<remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders"); ///</remarks> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<returns>a SqlDataReader containing the resultset generated by the command</returns> publicstatic SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null); } /**////<summary> /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>a SqlDataReader containing the resultset generated by the command</returns> publicstatic SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection SqlConnection cn =new SqlConnection(connectionString); cn.Open(); try { //call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader(cn, null, commandType, commandText, commandParameters,SqlConnectionOwnership.Internal); } catch { //if we fail to return the SqlDatReader, we need to close the connection ourselves cn.Close(); throw; } } /**////<summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36); ///</remarks> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>a SqlDataReader containing the resultset generated by the command</returns> publicstatic SqlDataReader ExecuteReader(string connectionString, string spName, paramsobject[] parameterValues) { //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 ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteReader(connectionString, CommandType.StoredProcedure, spName); } } /**////<summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. ///</summary> ///<remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders"); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<returns>a SqlDataReader containing the resultset generated by the command</returns> publicstatic 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); } /**////<summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>a SqlDataReader containing the resultset generated by the command</returns> publicstatic SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //pass through the call to the private overload using a null transaction value and an externally owned connection return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External); } /**////<summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>a SqlDataReader containing the resultset generated by the command</returns> publicstatic SqlDataReader ExecuteReader(SqlConnection connection, string spName, paramsobject[] parameterValues) { //if we receive parameter values, we need to figure out where they go if ((parameterValues !=null) && (parameterValues.Length >0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteReader(connection, CommandType.StoredProcedure, spName); } } /**////<summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. ///</summary> ///<remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders"); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<returns>a SqlDataReader containing the resultset generated by the command</returns> publicstatic 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); } /**////<summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>a SqlDataReader containing the resultset generated by the command</returns> publicstatic SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //pass through to private overload, indicating that the connection is owned by the caller return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External); } /**////<summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>a SqlDataReader containing the resultset generated by the command</returns> publicstatic SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, paramsobject[] parameterValues) { //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.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteReader(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteReader ExecuteScalar#region ExecuteScalar /**////<summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in /// the connection string. ///</summary> ///<remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount"); ///</remarks> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<returns>an object containing the value in the 1x1 resultset generated by the command</returns> publicstaticobject ExecuteScalar(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null); } /**////<summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>an object containing the value in the 1x1 resultset generated by the command</returns> publicstaticobject ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn =new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteScalar(cn, commandType, commandText, commandParameters); } } /**////<summary> /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36); ///</remarks> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>an object containing the value in the 1x1 resultset generated by the command</returns> publicstaticobject ExecuteScalar(string connectionString, string spName, paramsobject[] parameterValues) { //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 ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName); } } /**////<summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection. ///</summary> ///<remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<returns>an object containing the value in the 1x1 resultset generated by the command</returns> publicstaticobject 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); } /**////<summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>an object containing the value in the 1x1 resultset generated by the command</returns> publicstaticobject ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd =new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //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(); return retval; } /**////<summary> /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>an object containing the value in the 1x1 resultset generated by the command</returns> publicstaticobject ExecuteScalar(SqlConnection connection, string spName, paramsobject[] parameterValues) { //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.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(connection, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteScalar(connection, CommandType.StoredProcedure, spName); } } /**////<summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction. ///</summary> ///<remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount"); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<returns>an object containing the value in the 1x1 resultset generated by the command</returns> publicstaticobject 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); } /**////<summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>an object containing the value in the 1x1 resultset generated by the command</returns> publicstaticobject ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd =new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //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(); return retval; } /**////<summary> /// 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. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>an object containing the value in the 1x1 resultset generated by the command</returns> publicstaticobject ExecuteScalar(SqlTransaction transaction, string spName, paramsobject[] parameterValues) { //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.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(transaction, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteScalar(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteScalar ExecuteXmlReader#region ExecuteXmlReader /**////<summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. ///</summary> ///<remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders"); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> ///<returns>an XmlReader containing the resultset generated by the command</returns> publicstatic 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[])null); } /**////<summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>an XmlReader containing the resultset generated by the command</returns> publicstatic XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd =new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //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; } /**////<summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36); ///</remarks> ///<param name="connection">a valid SqlConnection</param> ///<param name="spName">the name of the stored procedure using "FOR XML AUTO"</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>an XmlReader containing the resultset generated by the command</returns> publicstatic XmlReader ExecuteXmlReader(SqlConnection connection, string spName, paramsobject[] parameterValues) { //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.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 ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName); } } /**////<summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. ///</summary> ///<remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders"); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> ///<returns>an XmlReader containing the resultset generated by the command</returns> publicstatic 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); } /**////<summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. ///</summary> ///<remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="commandType">the CommandType (stored procedure, text, etc.)</param> ///<param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> ///<param name="commandParameters">an array of SqlParamters used to execute the command</param> ///<returns>an XmlReader containing the resultset generated by the command</returns> publicstatic XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd =new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //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; } /**////<summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. ///</summary> ///<remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36); ///</remarks> ///<param name="transaction">a valid SqlTransaction</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> ///<returns>a dataset containing the resultset generated by the command</returns> publicstatic XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, paramsobject[] parameterValues) { //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.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 ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteXmlReader } /**////<summary> /// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the /// ability to discover parameters for stored procedures at run-time. /// SqlHelperParameterCache支持函数来实现静态缓存存储过程参数，并支持在运行时得到存储过程的参数 ///</summary> publicsealedclass SqlHelperParameterCache { private methods, variables, and constructors#region private methods, variables, and constructors //Since this class provides only static methods, make the default constructor private to prevent //instances from being created with "new SqlHelperParameterCache()". //类提供的都是静态方法，将默认构造函数设置为私有的以便阻止利用"new SqlHelperParameterCache()"来实例化类 private SqlHelperParameterCache() {} //存储过程参数缓存导HashTable中 privatestatic Hashtable paramCache = Hashtable.Synchronized(new Hashtable()); /**////<summary> /// resolve at run time the appropriate set of SqlParameters for a stored procedure /// 在运行时得到一个存储过程的一系列参数信息 ///</summary> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="connectionString">一个连接对象的有效连接串</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="spName">存储过程名</param> ///<param name="includeReturnValueParameter">是否有返回值参数</param> ///<returns>参数对象数组，存储过程的所有参数信息</returns> privatestatic SqlParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter) { using (SqlConnection cn =new SqlConnection(connectionString)) using (SqlCommand cmd =new SqlCommand(spName,cn)) { cn.Open(); cmd.CommandType = CommandType.StoredProcedure; //从 SqlCommand 指定的存储过程中检索参数信息，并填充指定的 SqlCommand 对象的 Parameters 集。 SqlCommandBuilder.DeriveParameters(cmd); if (!includeReturnValueParameter) { //移除第一个参数对象，因为没有返回值，而默认情况下，第一个参数对象是返回值 cmd.Parameters.RemoveAt(0); } SqlParameter[] discoveredParameters =new SqlParameter[cmd.Parameters.Count];; cmd.Parameters.CopyTo(discoveredParameters, 0); return discoveredParameters; } } //deep copy of cached SqlParameter array //复制缓存参数数组（克隆） privatestatic SqlParameter[] CloneParameters(SqlParameter[] originalParameters) { SqlParameter[] clonedParameters =new SqlParameter[originalParameters.Length]; for (int i =0, j = originalParameters.Length; i < j; i++) { clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone(); } return clonedParameters; } #endregion private methods, variables, and constructors caching functions#region caching functions /**////<summary> /// 将参数数组添加到缓存中 ///</summary> ///<param name="connectionString">有效的连接串</param> ///<param name="commandText">一个存储过程名或者T-SQL命令</param> ///<param name="commandParameters">一个要被缓存的参数对象数组</param> publicstaticvoid CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters) { string hashKey = connectionString +":"+ commandText; paramCache[hashKey] = commandParameters; } /**////<summary> /// 从缓存中获得参数对象数组 ///</summary> ///<param name="connectionString">有效的连接串</param> ///<param name="commandText">一个存储过程名或者T-SQL命令</param> ///<returns>一个参数对象数组</returns> publicstatic SqlParameter[] GetCachedParameterSet(string connectionString, string commandText) { string hashKey = connectionString +":"+ commandText; SqlParameter[] cachedParameters = (SqlParameter[])paramCache[hashKey]; if (cachedParameters ==null) { returnnull; } else { return CloneParameters(cachedParameters); } } #endregion caching functions Parameter Discovery Functions#region Parameter Discovery Functions /**////<summary> /// 获得存储过程的参数集 ///</summary> ///<remarks> /// 这个方法从数据库中获得信息，并将之存储在缓存，以便之后的使用 ///</remarks> ///<param name="connectionString">有效的连接串</param> ///<param name="commandText">一个存储过程名或者T-SQL命令</param> ///<returns>一个参数对象数组</returns> publicstatic SqlParameter[] GetSpParameterSet(string connectionString, string spName) { return GetSpParameterSet(connectionString, spName, false); } /**////<summary> /// 获得存储过程的参数集 ///</summary> ///<remarks> /// 这个方法从数据库中获得信息，并将之存储在缓存，以便之后的使用 ///</remarks> ///<param name="connectionString">a valid connection string for a SqlConnection</param> ///<param name="spName">the name of the stored procedure</param> ///<param name="includeReturnValueParameter">a bool value indicating whether the return value parameter should be included in the results</param> ///<returns>an array of SqlParameters</returns> ///<param name="connectionString">有效的连接串</param> ///<param name="commandText">一个存储过程名</param> //////<param name="includeReturnValueParameter">是否有返回值参数</param> ///<returns>一个参数对象数组</returns> publicstatic SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter) { string hashKey = connectionString +":"+ spName + (includeReturnValueParameter ?":include ReturnValue Parameter":""); SqlParameter[] cachedParameters; cachedParameters = (SqlParameter[])paramCache[hashKey]; if (cachedParameters ==null) { cachedParameters = (SqlParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter)); } return CloneParameters(cachedParameters); } #endregion Parameter Discovery Functions } }
posted on
2006-05-11 12:33十二随风
阅读(2044)
评论(0)
编辑收藏举报
//===============================================================================
{
/**//// <summary>
/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for 
/// </summary>
}
