第一步：DBUtility层 用于底层操作SqlHelper(一)

2008-01-12 17:13

// 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;
using System.Configuration;

namespace Buygo.DBUtility
{
///

/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for
/// common uses of SqlClient.
///

public sealed class SqlHelper
{
        public static readonly string ConnectionStringLocalBuygoMember = ConfigurationSettings.AppSettings["BuygoMember"].ToString();
        public static readonly string ConnectionStringLocalFriednDB = ConfigurationSettings.AppSettings["FriednDB"].ToString();

   #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() {}

   ///

   /// 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.
   ///

   /// The command to which the parameters will be added
   /// an array of SqlParameters tho be added to command
   private static void 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);
    }
   }

   ///

   /// This method assigns an array of values to an array of SqlParameters.
   ///

   /// array of SqlParameters to be assigned values
   /// array of objects holding the values to be assigned
   private static void 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)
    {
     throw new ArgumentException("Parameter count does not match Parameter Value count.");
    }

    //iterate through the SqlParameters, assigning the values from the corresponding position in the
    //value array
    for (int i = 0, j = commandParameters.Length; i < j; i++)
    {
     commandParameters[i].Value = parameterValues[i];
    }
   }

   ///

   /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
   /// to the provided command.
   ///

   /// the SqlCommand to be prepared
   /// 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
   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 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

   #region ExecuteNonQuery

   ///

   /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in
   /// the connection string.
   ///

   ///
   /// e.g.:
   /// 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, (SqlParameter[])null);
   }

   ///

   /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
   /// using the provided parameters.
   ///

   ///
   /// e.g.:
   /// 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 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);
    }
   }

   ///

   /// 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 procedure's return value parameter.
   ///
   /// e.g.:
   /// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
   ///
   /// a valid connection string for a SqlConnection
   /// the name of the stored prcedure
   /// an array of objects to be assigned as the input values 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 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);
    }
   }

   ///

   /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.
   ///

   ///
   /// e.g.:
   /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
   ///
   /// a valid 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(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 resultset) against the specified SqlConnection
   /// using the provided parameters.
   ///

   ///
   /// e.g.:
   /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 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 int representing the number of rows affected by the command
   public static int 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;
   }

   ///

   /// 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.
   ///
   /// e.g.:
   /// 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 values of 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 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);
    }
   }

   ///

   /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction.
   ///

   ///
   /// e.g.:
   /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
   ///
   /// a valid SqlTransaction
   /// 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(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 resultset) against the specified SqlTransaction
   /// using the provided parameters.
   ///

   ///
   /// e.g.:
   /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", 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 int representing the number of rows affected by the command
   public static int 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;
   }

   ///

   /// 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.
   ///
   /// e.g.:
   /// 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 representing the number of rows affected by the command
   public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] 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

   #region ExecuteDataSet

   ///

   /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
   /// the connection string.
   ///

   ///
   /// e.g.:
   /// DataSet ds = ExecuteDataset(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 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, (SqlParameter[])null);
   }

   ///

   /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
   /// using the provided parameters.
   ///

   ///
   /// e.g.:
   /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", 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
   /// a dataset containing the resultset generated by the command
   public static 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);
    }
   }

   ///

   /// 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.
   ///
   /// e.g.:
   /// DataSet ds = ExecuteDataset(connString, "GetOrders", 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 values 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 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);
    }
   }

   ///

   /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
   ///

   ///
   /// e.g.:
   /// 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 SqlCommand (that returns a resultset) against the specified SqlConnection
   /// using the provided parameters.
   ///

   ///
   /// e.g.:
   /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 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
   /// a dataset containing the resultset generated by the command
   public static 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;      
   }