SQLHelper

using System;using System.Data;using System.Xml;using System.Data.SqlClient;using System.Collections;///<summary>/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for/// common uses of SqlClient.///</summary>public sealed class SqlHelper{private static string _connectionString;public static string ConnectionString{set{_connectionString = value;}}#region private utility methods & constructors//Since this class provides only static methods, make the default constructor private to prevent//instances from being created with "new SqlHelper()".private SqlHelper() {}static SqlHelper(){_connectionString = ""; // AppUtil.GetDBCfg().GetProperty("");}///<summary>/// This method is used to attach array of SqlParameters to a SqlCommand.////// This method will assign a value of DbNull to any parameter with a direction of/// InputOutput and a value of null.////// This behavior will prevent default values from being used, but///this will be the less common case than an intended pure output parameter (derived as InputOutput)/// where the user provided no input value.///</summary>///<param name="command">The command to which the parameters will be added</param>///<param name="commandParameters">an array of SqlParameters tho be added to command</param>private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters){foreach (SqlParameter p in commandParameters){//check for derived output value with no value assignedif((p.Direction == ParameterDirection.InputOutput) && (p.Value == null)){p.Value = DBNull.Value;}command.Parameters.Add(p);}}///<summary>/// This method assigns an array of values to an array of SqlParameters.///</summary>///<param name="commandParameters">array of SqlParameters to be assigned values</param>///<param name="parameterValues">array of objects holding the values to be assigned</param>private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues){if ((commandParameters == null) || (parameterValues == null)){//do nothing if we get no datareturn;}// we must have the same number of values as we pave parameters to put them inif (commandParameters.Length != parameterValues.Length){throw new ArgumentException("Parameter count does not match ParameterValue count.");}//iterate through the SqlParameters, assigning the values from the corresponding position in the//value arrayfor (int i = 0, j = commandParameters.Length; i < j; i++){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, /, 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>private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters){//if the provided connection is not open, we will open itif (connection.State != ConnectionState.Open){connection.Open();}//associate the connection with the commandcommand.Connection = connection;//set the command text (stored procedure name or SQL statement)mandText = commandText;//if we were provided a transaction, assign it.if (transaction != null){command.Transaction = transaction;}//set the command typemandType = commandType;//attach the command parameters if they are providedif (commandParameters != null){AttachParameters(command, commandParameters);}return;}#endregion private utility methods & constructors#region ExecuteNonQuery///<summary>///Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in/// the connection string.///</summary>///<remarks>/// e.g.:///int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");///</remarks>///<param name="connectionString">a valid connection string for a SqlConnection</param>///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>///<param name="commandText">the stored procedure name or T-SQL command</param>///<returns>an int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery(CommandType commandType, string commandText){//pass through the call providing null for the set of SqlParametersreturn ExecuteNonQuery(commandType, commandText, (SqlParameter[])null);}///<summary>///Execute a SqlCommand (that returns no resultset) against the database specified in the connection string/// using the provided parameters.///</summary>///<remarks>/// e.g.:///int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));///</remarks>///<param name="connectionString">a valid connection string for a SqlConnection</param>///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>///<param name="commandText">the stored procedure name or T-SQL command</param>///<param name="commandParameters">an array of SqlParamters used to execute the command</param>///<returns>an int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery(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 stringreturn 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>public static int ExecuteNonQuery(string spName, params object[] parameterValues) {//if we receive parameter values, we need to figure out where they goif ((parameterValues != null) && (parameterValues.Length > 0)){//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(_connectionString, spName);//assign the provided values to these parameters based on parameter orderAssignParameterValues(commandParameters, parameterValues);//call the overload that takes an array of SqlParametersreturn ExecuteNonQuery(CommandType.StoredProcedure, spName, commandParameters);}//otherwise we can just call the SP without paramselse{return ExecuteNonQuery(CommandType.StoredProcedure, spName);}}///<summary>///Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.///</summary>///<remarks>/// e.g.:///int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");///</remarks>///<param name="connection">a valid SqlConnection</param>///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>///<param name="commandText">the stored procedure name or T-SQL command</param>///<returns>an int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText){//pass through the call providing null for the set of SqlParametersreturn ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);}///<summary>///Execute a SqlCommand (that returns no resultset) against the specified SqlConnection/// using the provided parameters.///</summary>///<remarks>/// e.g.:///int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));///</remarks>///<param name="connection">a valid SqlConnection</param>///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>///<param name="commandText">the stored procedure name or T-SQL command</param>///<param name="commandParameters">an array of SqlParamters used to execute the command</param>///<returns>an int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters){//create a command and prepare it for executionSqlCommand 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>public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues){//if we receive parameter values, we need to figure out where they goif ((parameterValues != null) && (parameterValues.Length > 0)){//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);//assign the provided values to these parameters based on parameter orderAssignParameterValues(commandParameters, parameterValues);//call the overload that takes an array of SqlParametersreturn ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);}//otherwise we can just call the SP without paramselse{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>public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText){//pass through the call providing null for the set of SqlParametersreturn ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);}///<summary>///Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction/// using the provided parameters.///</summary>///<remarks>/// e.g.:/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));///</remarks>///<param name="transaction">a valid SqlTransaction</param>///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>///<param name="commandText">the stored procedure name or T-SQL command</param>///<param name="commandParameters">an array of SqlParamters used to execute the command</param>///<returns>an int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters){//create a command and prepare it for executionSqlCommand 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>public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues){//if we receive parameter values, we need to figure out where they goif ((parameterValues != null) && (parameterValues.Length > 0)){//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);//assign the provided values to these parameters based on parameter orderAssignParameterValues(commandParameters, parameterValues);//call the overload that takes an array of SqlParametersreturn ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);}//otherwise we can just call the SP without paramselse{return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);}}#endregion ExecuteNonQuery#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>public static DataSet ExecuteDataset(CommandType commandType, string commandText) {//pass through the call providing null for the set of SqlParametersreturn ExecuteDataset(commandType, commandText, (SqlParameter[])null);}///<summary>/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string/// using the provided parameters.///</summary>///<remarks>/// e.g.:///DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));///</remarks>///<param name="connectionString">a valid connection string for a SqlConnection</param>///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>///<param name="commandText">the stored procedure name or T-SQL command</param>///<param name="commandParameters">an array of SqlParamters used to execute the command</param>///<returns>a dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset(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 stringreturn 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 willquery the database to discover the parameters for the/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.///</summary>///<remarks>/// This method provides no access to output parameters or the stored procedure's return value parameter.////// e.g.:/// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);///</remarks>///<param name="connectionString">a valid connection string for a SqlConnection</param>///<param name="spName">the name of the stored procedure</param>///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>///<returns>a dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset(string spName, params object[] parameterValues){//if we receive parameter values, we need to figure out where they goif ((parameterValues != null) && (parameterValues.Length > 0)){//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(_connectionString, spName);//assign the provided values to these parameters based on parameter orderAssignParameterValues(commandParameters, parameterValues);//call the overload that takes an array of SqlParametersreturn ExecuteDataset(CommandType.StoredProcedure, spName, commandParameters);}//otherwise we can just call the SP without paramselse{return ExecuteDataset(CommandType.StoredProcedure, spName);}}///<summary>///Execute a SqlCommand (that returns a resultset and takes no parameters) againstthe provided SqlConnection.///</summary>///<remarks>/// e.g.:/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");///</remarks>///<param name="connection">a valid SqlConnection</param>///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>///<param name="commandText">the stored procedure name or T-SQL command</param>///<returns>a dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText){//pass through the call providing null for the set of SqlParametersreturn ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);}///<summary>///Execute a SqlCommand (that returns a resultset) against the specified SqlConnection/// using the provided parameters.///</summary>///<remarks>/// e.g.:/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));///</remarks>///<param name="connection">a valid SqlConnection</param>///<param name="commandType">the CommandType (stored procedure, text, etc.)</param>///<param name="commandText">the stored procedure name or T-SQL command</param>///<param name="commandParameters">an array of SqlParamters used to execute the command</param>///<returns>a dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters){//create a command and prepare it for executionSqlCommand cmd = new SqlCommand();PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);//create the DataAdapter & DataSetSqlDataAdapter 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 datasetreturn ds;}///<summary>///Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection/// using the provided parameter values. This method will query the database to discover the parameters for the/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.///</summary>///<remarks>/// This method provides no access to output parameters or the stored procedure's return value parameter.////// e.g.:/// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);///</remarks>///<param name="connection">a valid SqlConnection</param>///<param name="spName">the name of the stored procedure</param>///<param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>///<returns>a dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues){//if we receive parameter values, we need to figure out where they goif ((parameterValues != null) && (parameterValues.Length > 0)){//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters =SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);//assign the provided values to these parameters based on parameter orderAssignParameterValues(commandParameters, parameterValues);//call the overload that takes an array of SqlParametersreturn ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);}//otherwise we can just call the SP without paramselse{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>public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText){//pass through the call providing null for the set of SqlParametersreturn ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);}///<summary>///Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction/// using the provided parameters.///</summary>///<remarks>。