2013年4月28日 星期日

讓專案透過 NuGet 安裝 Enterprise Library 5.0 - Data Access Application Block

我想很多人看到文章標題之後一定都會說,這有什麼難的嗎?這值得寫一篇文章嗎?

再看一下文章標題,我要說的是「EntLib DAAB 5」而不是最近才剛發佈的「EntLib 6」,的確,在 Enterprise Library 6.0 Release 之前,這實在是不必專門寫一篇文章來說明,但就在 EntLib 6 正式發佈之後就有點不一樣了,還是有些事情要說明一下,不然一定會有很多人撞牆的。

 


Enterprise Library 6 – April 2013

先從以下的相關資料連結對 Enterprise Library 6 做初步的認識與了解:

Enterprise Library - MSDN
http://msdn.microsoft.com/library/cc467894.aspx

Enterprise Library 6 - April 2013
http://msdn.microsoft.com/zh-TW/library/dn169621.aspx

Microsoft Enterprise Library 6 - Microsoft Download Center
http://www.microsoft.com/en-us/download/details.aspx?id=38789

Enterprise Library 6 - April Document Download - patterns & practices – Enterprise Library
https://entlib.codeplex.com/releases/view/64243

 

其中有一點相當重要,那就是使用 EntLib 6 的系統需求:

  • Supported architectures: x86 and x64.
  • Operating systems: Microsoft Windows 8, Microsoft Windows 7, Windows Server 2008 R2, Windows Server 2012.
  • Supported .NET Frameworks: Microsoft .NET Framework 4.5, .NET for Windows Store Apps (previously known as Windows Runtime) for Unity and Transient Fault Handling Application Blocks.

除了說作業系統必須是 Windows 7, 8 或是 Windows Server 2008 R2 或 Windows Server 2012 外,最重要的是專案開發所使用的 .NET Framework 版本必須要為 4.5,所以使用 4.0 或是更早之前版本的專案就不能使用 EntLib 6 了,所以不是以 .NET Framework 4.5 開發的專案就不能使用 EntLib 6,但還是可以繼續使用之前的 EntLib 5。

另外還有其他建議以及有關 Data Access Application Block 6 的系統需求:

For a rich development environment, the following are recommended:

  • Microsoft Visual Studio 2012 (Professional, Ultimate, or Express editions).

For the Data Access Application Block, the following is also required:

  • A database server running a database that is supported by a .NET Framework 4.5 data provider.

 

VS2010 可以選用的 .NET Framework 的 Target 版本最多只有到 4.0,

image

所以建議要使用 DAAB 6 的朋友就必須要用 VS2012。

 

我想很多人現在開發專案再安裝第三方組件的習慣已經漸漸地從以前在網站下載、解壓縮、專案加入參考的這些動作,現在多半都會優先使用 NuGet 來搜尋相關組件然後安裝到專案中,而 Enterprise Library 當然也可以從 NuGet 裡選擇要使用的 Application Block 然後點擊「安裝」按鍵就可以輕鬆地把要使用的應用區塊給安裝到專案裡。

但就如同我前面所說的,EntLib 6 已經在 2013-04-26 正式發佈,而 NuGet 裡也是一併更新,現在 NuGet 裡以「僅限穩定」的方式來尋找有關 Enterprise Library 的項目,官方所建立的套件都已經是 EntLib 6,

image

使用「包括發行前版本」來找之前「Enterprise Library 5.0」版本的套件,就是遍尋不到「Enterprise Library 5.0 - Data Access Application Block」,

image

image

如果在 VS2010 裡開發 .NET Framework 4.0 的專案,該怎麼安裝 Enterprise Library 5.0 - Data Access Application Block 呢?除了自行到官網所提供的網址去下載 EntLib5 的組件之外,是否還是可以透過 NuGet 安裝呢?

 

透過「NuGet 套件管理器主控台」為專案安裝 Enterprise Library 5.0 - Data Access Application Block

在使用 .NET Fraework 4.0 的專案透過 NuGet 安裝目前版本的 EntLib -DAAB 是會有以下的錯誤訊息顯示,

image

專案使用 .NET Framework 4.0 就只能安裝 EntLib 5 - DAAB,但其實不管在 VS2010 或是 VS2012,目前是無法在 NuGet 套件管理介面裡下載安裝 EntLib 5 - DAAB,但我們還是可以透過 NuGet 套件管理主控台來安裝,首先是透過 NuGet 套件管理介面先找到 EntLib 6 - DAAB,

image

找到 EntLib 6 - DAAB 之後,點選右方資料欄位裡的「識別碼」,點選之後就會到以下的 NuGet Gallery 網頁裡,

https://nuget.org/packages/EnterpriseLibrary.Data

image

在上面網頁的下方「Version History」可以看到「Enterprise Library - Data Access Application Block 5.0.505.0」的項目,點擊該連結進入 EntLib 5 - DAAB 的頁面,

進入到頁面之後,頁面的上方就有訊息顯示說明現在看到的項目並非最近的版本,而我們在這個頁面所要的就是 Package Manager Console 執行的指令碼,就是中間黑色區塊裡所顯示的,我們就把「Install-Package EnterpriseLibrary.Data -Version 5.0.505.0」給複製起來,

image

接著就是到 Visual Studio 裡開啟 NuGet 的「套件管理主控台」,「工具 > 程式庫套件管理員 > 套件管理器主控台」

VS2010

image

VS2012

image

點選「套件管理器主控台」之後,會在下方開啟套件管理器介面,

VS2010

SNAGHTML665ca13

VS2012

image

接著就是把從網頁上所複製的執行碼「Install-Package EnterpriseLibrary.Data -Version 5.0.505.0」給貼到套件管理器主控台裡,然後按下 Enter 鍵就會開始進行安裝,

image

上圖為安裝的經過與完成的訊息內容,然後在專案的參考裡也可以看到安裝好的套件,

image

使用上面的操作步驟之後,就可以為專案安裝 Enterprise Library 5.0 - Data Access Application Block 了。

 

補充說明

如果專案無法使用 ADO.NET Entity Framework 或是其他 ORM 技術而必須使用傳統的 ADO.NET 方式來存取資料庫,我真的強烈建議可以使用 Enterprise Library  - Data Access Application Block,這邊要強調的是 DAAB 與一般傳統的 ADO.NET 是一樣的,沒有所謂取代不取代的問題,同樣都是以 ADO.NET 的方式來存取資料庫,只是 EntLib DAAB 所提供的資料存取方式是經過優化處理的,而且有提供許多加強的方法,讓我們可以用更好的方式來存取資料庫。

早期在網路上有流傳著一個名為「SqlHelper.cs」的檔案,這個檔案有提供很多方法,可以簡化原先使用 ADO.NET 存取資料的程式與步驟,如下:

SqlHelper.cs

/// <summary>
/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for 
/// common uses of SqlClient
/// </summary>
public sealed class SqlHelper
{
    #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() { }
 
    /// <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 to be added to command</param>
    private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
    {
        if (command == null) throw new ArgumentNullException("command");
        if (commandParameters != null)
        {
            foreach (SqlParameter p in commandParameters)
            {
                if (p != null)
                {
                    // Check for derived output value with no value assigned
                    if ((p.Direction == ParameterDirection.InputOutput ||
                        p.Direction == ParameterDirection.Input) &&
                        (p.Value == null))
                    {
                        p.Value = DBNull.Value;
                    }
                    command.Parameters.Add(p);
                }
            }
        }
    }
 
    /// <summary>
    /// This method assigns dataRow column values to an array of SqlParameters
    /// </summary>
    /// <param name="commandParameters">Array of SqlParameters to be assigned values</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
    private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
    {
        if ((commandParameters == null) || (dataRow == null))
        {
            // Do nothing if we get no data
            return;
        }
 
        int i = 0;
        // Set the parameters values
        foreach (SqlParameter commandParameter in commandParameters)
        {
            // Check the parameter name
            if (commandParameter.ParameterName == null ||
                commandParameter.ParameterName.Length <= 1)
                throw new Exception(
                    string.Format(
                        "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
                        i, commandParameter.ParameterName));
            if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
                commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
            i++;
        }
    }
 
    /// <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 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++)
        {
            // If the current array value derives from IDbDataParameter, then assign its Value property
            if (parameterValues[i] is IDbDataParameter)
            {
                IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
                if (paramInstance.Value == null)
                {
                    commandParameters[i].Value = DBNull.Value;
                }
                else
                {
                    commandParameters[i].Value = paramInstance.Value;
                }
            }
            else if (parameterValues[i] == null)
            {
                commandParameters[i].Value = DBNull.Value;
            }
            else
            {
                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>
    /// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
    private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
    {
        if (command == null) throw new ArgumentNullException("command");
        if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
 
        // If the provided connection is not open, we will open it
        if (connection.State != ConnectionState.Open)
        {
            mustCloseConnection = true;
            connection.Open();
        }
        else
        {
            mustCloseConnection = false;
        }
 
        // Associate the connection with the command
        command.Connection = connection;
 
        // Set the command text (stored procedure name or SQL statement)
        command.CommandText = commandText;
 
        // If we were provided a transaction, assign it
        if (transaction != null)
        {
            if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            command.Transaction = transaction;
        }
 
        // Set the command type
        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
 
    /// <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(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>
    /// 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(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 
        // Create & open a SqlConnection, and dispose of it after we are done
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
 
            // Call the overload that takes a connection in place of the connection string
            return ExecuteNonQuery(connection, 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 connectionString, string spName, params object[] parameterValues)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
 
            // Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);
 
            // Call the overload that takes an array of SqlParameters
            return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <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 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>
    public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connection == null) throw new ArgumentNullException("connection");
 
        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
 
        // Finally, execute the command
        int retval = cmd.ExecuteNonQuery();
 
        // Detach the SqlParameters from the command object, so they can be used again
        cmd.Parameters.Clear();
        if (mustCloseConnection)
            connection.Close();
        return retval;
    }
 
    /// <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 (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 
            // Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);
 
            // Call the overload that takes an array of SqlParameters
            return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <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 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>
    public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 
        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
 
        // 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 (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 
            // Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);
 
            // Call the overload that takes an array of SqlParameters
            return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
        }
    }
 
    #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(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>
    public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
 
        // Create & open a SqlConnection, and dispose of it after we are done
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
 
            // Call the overload that takes a connection in place of the connection string
            return ExecuteDataset(connection, 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>
    public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
 
            // Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);
 
            // Call the overload that takes an array of SqlParameters
            return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <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>
    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);
    }
 
    /// <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)
    {
        if (connection == null) throw new ArgumentNullException("connection");
 
        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
 
        // Create the DataAdapter & DataSet
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
            DataSet ds = new DataSet();
 
            // Fill the DataSet using default values for DataTable names, etc
            da.Fill(ds);
 
            // Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
 
            if (mustCloseConnection)
                connection.Close();
 
            // Return the dataset
            return ds;
        }
    }
 
    /// <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 (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 
            // Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);
 
            // Call the overload that takes an array of SqlParameters
            return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            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 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>
    public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 
        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
 
        // Create the DataAdapter & DataSet
        using (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>
    public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 
            // Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);
 
            // Call the overload that takes an array of SqlParameters
            return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
        }
    }
 
    #endregion ExecuteDataset
 
    #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>
    private enum 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>
    private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
    {
        if (connection == null) throw new ArgumentNullException("connection");
 
        bool mustCloseConnection = false;
        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        try
        {
            PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
 
            // Create a reader
            SqlDataReader dataReader;
 
            // Call ExecuteReader with the appropriate CommandBehavior
            if (connectionOwnership == SqlConnectionOwnership.External)
            {
                dataReader = cmd.ExecuteReader();
            }
            else
            {
                dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
 
            // Detach the SqlParameters from the command object, so they can be used again.
            // HACK: There is a problem here, the output parameter values are fletched 
            // when the reader is closed, so if the parameters are detached from the command
            // then the SqlReader can磘 set its values. 
            // When this happen, the parameters can磘 be used again in other command.
            bool canClear = true;
            foreach (SqlParameter commandParameter in cmd.Parameters)
            {
                if (commandParameter.Direction != ParameterDirection.Input)
                    canClear = false;
            }
 
            if (canClear)
            {
                cmd.Parameters.Clear();
            }
 
            return dataReader;
        }
        catch
        {
            if (mustCloseConnection)
                connection.Close();
            throw;
        }
    }
 
    /// <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>
    public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
    {
        // Pass through the call providing null for the set of SqlParameters
        return ExecuteReader(connectionString, commandType, 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>
    public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        SqlConnection connection = null;
        try
        {
            connection = new SqlConnection(connectionString);
            connection.Open();
 
            // Call the private overload that takes an internally owned connection in place of the connection string
            return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
        }
        catch
        {
            // If we fail to return the SqlDatReader, we need to close the connection ourselves
            if (connection != null) connection.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>
    public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
 
            AssignParameterValues(commandParameters, parameterValues);
 
            return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            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>
    public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
    {
        // Pass through the call providing null for the set of SqlParameters
        return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
    }
 
    /// <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>
    public static 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>
    public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 
            AssignParameterValues(commandParameters, parameterValues);
 
            return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteReader(connection, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <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>
    public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
    {
        // Pass through the call providing null for the set of SqlParameters
        return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
    }
 
    /// <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>
    public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 
        // 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>
    public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 
            AssignParameterValues(commandParameters, parameterValues);
 
            return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
        }
    }
 
    #endregion ExecuteReader
 
    #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>
    public static object 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>
    public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        // Create & open a SqlConnection, and dispose of it after we are done
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
 
            // Call the overload that takes a connection in place of the connection string
            return ExecuteScalar(connection, 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>
    public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where 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);
        }
        else
        {
            // Otherwise we can just call the SP without params
            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>
    public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
    {
        // Pass through the call providing null for the set of SqlParameters
        return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
    }
 
    /// <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>
    public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connection == null) throw new ArgumentNullException("connection");
 
        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
 
        bool mustCloseConnection = false;
        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
 
        // Execute the command & return the results
        object retval = cmd.ExecuteScalar();
 
        // Detach the SqlParameters from the command object, so they can be used again
        cmd.Parameters.Clear();
 
        if (mustCloseConnection)
            connection.Close();
 
        return retval;
    }
 
    /// <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>
    public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 
            // Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);
 
            // Call the overload that takes an array of SqlParameters
            return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <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>
    public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
    {
        // Pass through the call providing null for the set of SqlParameters
        return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
    }
 
    /// <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>
    public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 
        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
 
        // 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>
    public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // PPull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 
            // Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);
 
            // Call the overload that takes an array of SqlParameters
            return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
        }
    }
 
    #endregion ExecuteScalar
 
    #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>
    public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
    {
        // Pass through the call providing null for the set of SqlParameters
        return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])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>
    public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (connection == null) throw new ArgumentNullException("connection");
 
        bool mustCloseConnection = false;
        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        try
        {
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
 
            // Create the DataAdapter & DataSet
            XmlReader retval = cmd.ExecuteXmlReader();
 
            // Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
 
            return retval;
        }
        catch
        {
            if (mustCloseConnection)
                connection.Close();
            throw;
        }
    }
 
    /// <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>
    public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 
            // Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);
 
            // Call the overload that takes an array of SqlParameters
            return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <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>
    public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
    {
        // Pass through the call providing null for the set of SqlParameters
        return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
    }
 
    /// <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>
    public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
 
        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
 
        // Create the DataAdapter & DataSet
        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>
    public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 
            // Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);
 
            // Call the overload that takes an array of SqlParameters
            return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
        }
    }
 
    #endregion ExecuteXmlReader
 
    #region FillDataset
    /// <summary>
    /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in 
    /// the connection string. 
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
    /// </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="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)</param>
    public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (dataSet == null) throw new ArgumentNullException("dataSet");
 
        // Create & open a SqlConnection, and dispose of it after we are done
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
 
            // Call the overload that takes a connection in place of the connection string
            FillDataset(connection, commandType, commandText, dataSet, tableNames);
        }
    }
 
    /// <summary>
    /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 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>
    /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    public static void FillDataset(string connectionString, CommandType commandType,
        string commandText, DataSet dataSet, string[] tableNames,
        params SqlParameter[] commandParameters)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (dataSet == null) throw new ArgumentNullException("dataSet");
        // Create & open a SqlConnection, and dispose of it after we are done
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
 
            // Call the overload that takes a connection in place of the connection string
            FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
        }
    }
 
    /// <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.:  
    ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24);
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>    
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    public static void FillDataset(string connectionString, string spName,
        DataSet dataSet, string[] tableNames,
        params object[] parameterValues)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (dataSet == null) throw new ArgumentNullException("dataSet");
        // Create & open a SqlConnection, and dispose of it after we are done
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
 
            // Call the overload that takes a connection in place of the connection string
            FillDataset(connection, spName, dataSet, tableNames, parameterValues);
        }
    }
 
    /// <summary>
    /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. 
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
    /// </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="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>    
    public static void FillDataset(SqlConnection connection, CommandType commandType,
        string commandText, DataSet dataSet, string[] tableNames)
    {
        FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
    }
 
    /// <summary>
    /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 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="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    public static void FillDataset(SqlConnection connection, CommandType commandType,
        string commandText, DataSet dataSet, string[] tableNames,
        params SqlParameter[] commandParameters)
    {
        FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
    }
 
    /// <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.:  
    ///  FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36);
    /// </remarks>
    /// <param name="connection">A valid SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    public static void FillDataset(SqlConnection connection, string spName,
        DataSet dataSet, string[] tableNames,
        params object[] parameterValues)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (dataSet == null) throw new ArgumentNullException("dataSet");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 
            // Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);
 
            // Call the overload that takes an array of SqlParameters
            FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
        }
    }
 
    /// <summary>
    /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. 
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
    /// </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="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    public static void FillDataset(SqlTransaction transaction, CommandType commandType,
        string commandText,
        DataSet dataSet, string[] tableNames)
    {
        FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
    }
 
    /// <summary>
    /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 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="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    public static void FillDataset(SqlTransaction transaction, CommandType commandType,
        string commandText, DataSet dataSet, string[] tableNames,
        params SqlParameter[] commandParameters)
    {
        FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
    }
 
    /// <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.:  
    ///  FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36);
    /// </remarks>
    /// <param name="transaction">A valid SqlTransaction</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
    public static void FillDataset(SqlTransaction transaction, string spName,
        DataSet dataSet, string[] tableNames,
        params object[] parameterValues)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (dataSet == null) throw new ArgumentNullException("dataSet");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 
            // Assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);
 
            // Call the overload that takes an array of SqlParameters
            FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
        }
        else
        {
            // Otherwise we can just call the SP without params
            FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
        }
    }
 
    /// <summary>
    /// Private helper method that execute a SqlCommand (that returns a resultset) against the specified SqlTransaction and SqlConnection
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="connection">A valid SqlConnection</param>
    /// <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="dataSet">A dataset wich will contain the resultset generated by the command</param>
    /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
    /// by a user defined name (probably the actual table name)
    /// </param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType,
        string commandText, DataSet dataSet, string[] tableNames,
        params SqlParameter[] commandParameters)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (dataSet == null) throw new ArgumentNullException("dataSet");
 
        // Create a command and prepare it for execution
        SqlCommand command = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
 
        // Create the DataAdapter & DataSet
        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
        {
 
            // Add the table mappings specified by the user
            if (tableNames != null && tableNames.Length > 0)
            {
                string tableName = "Table";
                for (int index = 0; index < tableNames.Length; index++)
                {
                    if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
                    dataAdapter.TableMappings.Add(tableName, tableNames[index]);
                    tableName += (index + 1).ToString();
                }
            }
 
            // Fill the DataSet using default values for DataTable names, etc
            dataAdapter.Fill(dataSet);
 
            // Detach the SqlParameters from the command object, so they can be used again
            command.Parameters.Clear();
        }
 
        if (mustCloseConnection)
            connection.Close();
    }
    #endregion
 
    #region UpdateDataset
    /// <summary>
    /// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
    /// </remarks>
    /// <param name="insertCommand">A valid transact-SQL statement or stored procedure to insert new records into the data source</param>
    /// <param name="deleteCommand">A valid transact-SQL statement or stored procedure to delete records from the data source</param>
    /// <param name="updateCommand">A valid transact-SQL statement or stored procedure used to update records in the data source</param>
    /// <param name="dataSet">The DataSet used to update the data source</param>
    /// <param name="tableName">The DataTable used to update the data source.</param>
    public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
    {
        if (insertCommand == null) throw new ArgumentNullException("insertCommand");
        if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
        if (updateCommand == null) throw new ArgumentNullException("updateCommand");
        if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
 
        // Create a SqlDataAdapter, and dispose of it after we are done
        using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
        {
            // Set the data adapter commands
            dataAdapter.UpdateCommand = updateCommand;
            dataAdapter.InsertCommand = insertCommand;
            dataAdapter.DeleteCommand = deleteCommand;
 
            // Update the dataset changes in the data source
            dataAdapter.Update(dataSet, tableName);
 
            // Commit all the changes made to the DataSet
            dataSet.AcceptChanges();
        }
    }
    #endregion
 
    #region CreateCommand
    /// <summary>
    /// Simplify the creation of a Sql command object by allowing
    /// a stored procedure and optional parameters to be provided
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
    /// </remarks>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="sourceColumns">An array of string to be assigned as the source columns of the stored procedure parameters</param>
    /// <returns>A valid SqlCommand object</returns>
    public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // Create a SqlCommand
        SqlCommand cmd = new SqlCommand(spName, connection);
        cmd.CommandType = CommandType.StoredProcedure;
 
        // If we receive parameter values, we need to figure out where they go
        if ((sourceColumns != null) && (sourceColumns.Length > 0))
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 
            // Assign the provided source columns to these parameters based on parameter order
            for (int index = 0; index < sourceColumns.Length; index++)
                commandParameters[index].SourceColumn = sourceColumns[index];
 
            // Attach the discovered parameters to the SqlCommand object
            AttachParameters(cmd, commandParameters);
        }
 
        return cmd;
    }
    #endregion
 
    #region ExecuteNonQueryTypedParams
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in 
    /// the connection string using the dataRow column values as the stored procedure's parameters values.
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
    /// </summary>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection 
    /// using the dataRow column values as the stored procedure's parameters values.  
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
    /// </summary>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified
    /// SqlTransaction using the dataRow column values as the stored procedure's parameters values.
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
    /// </summary>
    /// <param name="transaction">A valid SqlTransaction object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // Sf the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
        }
    }
    #endregion
 
    #region ExecuteDatasetTypedParams
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
    /// the connection string using the dataRow column values as the stored procedure's parameters values.
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
    /// </summary>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>A dataset containing the resultset generated by the command</returns>
    public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        //If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
    /// using the dataRow column values as the store procedure's parameters values.
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
    /// </summary>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>A dataset containing the resultset generated by the command</returns>
    public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction 
    /// using the dataRow column values as the stored procedure's parameters values.
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
    /// </summary>
    /// <param name="transaction">A valid SqlTransaction object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>A dataset containing the resultset generated by the command</returns>
    public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
        }
    }
 
    #endregion
 
    #region ExecuteReaderTypedParams
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in 
    /// the connection string using the dataRow column values as the stored procedure's parameters values.
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
    public static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
        }
    }
 
 
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
    /// using the dataRow column values as the stored procedure's parameters values.
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
    public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction 
    /// using the dataRow column values as the stored procedure's parameters values.
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="transaction">A valid SqlTransaction object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
    public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName);
        }
    }
    #endregion
 
    #region ExecuteScalarTypedParams
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in 
    /// the connection string using the dataRow column values as the stored procedure's parameters values.
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
    public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection 
    /// using the dataRow column values as the stored procedure's parameters values.
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
    public static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
    /// using the dataRow column values as the stored procedure's parameters values.
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="transaction">A valid SqlTransaction object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
    public static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
        }
    }
    #endregion
 
    #region ExecuteXmlReaderTypedParams
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection 
    /// using the dataRow column values as the stored procedure's parameters values.
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An XmlReader containing the resultset generated by the command</returns>
    public static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
        }
    }
 
    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction 
    /// using the dataRow column values as the stored procedure's parameters values.
    /// This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <param name="transaction">A valid SqlTransaction object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values.</param>
    /// <returns>An XmlReader containing the resultset generated by the command</returns>
    public static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
    {
        if (transaction == null) throw new ArgumentNullException("transaction");
        if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        // If the row has values, the store procedure parameters must be initialized
        if (dataRow != null && dataRow.ItemArray.Length > 0)
        {
            // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
 
            // Set the parameters values
            AssignParameterValues(commandParameters, dataRow);
 
            return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
        }
    }
    #endregion
 
}
 
/// <summary>
/// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
/// ability to discover parameters for stored procedures at run-time.
/// </summary>
public sealed class SqlHelperParameterCache
{
    #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()"
    private SqlHelperParameterCache() { }
 
    private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
 
    /// <summary>
    /// Resolve at run time the appropriate set of SqlParameters for a stored procedure
    /// </summary>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <param name="includeReturnValueParameter">Whether or not to include their return value parameter</param>
    /// <returns>The parameter array discovered.</returns>
    private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        SqlCommand cmd = new SqlCommand(spName, connection);
        cmd.CommandType = CommandType.StoredProcedure;
 
        connection.Open();
        SqlCommandBuilder.DeriveParameters(cmd);
        connection.Close();
 
        if (!includeReturnValueParameter)
        {
            cmd.Parameters.RemoveAt(0);
        }
 
        SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
 
        cmd.Parameters.CopyTo(discoveredParameters, 0);
 
        // Init the parameters with a DBNull value
        foreach (SqlParameter discoveredParameter in discoveredParameters)
        {
            discoveredParameter.Value = DBNull.Value;
        }
        return discoveredParameters;
    }
 
    /// <summary>
    /// Deep copy of cached SqlParameter array
    /// </summary>
    /// <param name="originalParameters"></param>
    /// <returns></returns>
    private static 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
 
    #region caching functions
 
    /// <summary>
    /// Add parameter array to the cache
    /// </summary>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters to be cached</param>
    public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
 
        string hashKey = connectionString + ":" + commandText;
 
        paramCache[hashKey] = commandParameters;
    }
 
    /// <summary>
    /// Retrieve a parameter array from the cache
    /// </summary>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <returns>An array of SqlParamters</returns>
    public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
 
        string hashKey = connectionString + ":" + commandText;
 
        SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
        if (cachedParameters == null)
        {
            return null;
        }
        else
        {
            return CloneParameters(cachedParameters);
        }
    }
 
    #endregion caching functions
 
    #region Parameter Discovery Functions
 
    /// <summary>
    /// Retrieves the set of SqlParameters appropriate for the stored procedure
    /// </summary>
    /// <remarks>
    /// This method will query the database for this information, and then store it in a cache for future requests.
    /// </remarks>
    /// <param name="connectionString">A valid connection string for a SqlConnection</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <returns>An array of SqlParameters</returns>
    public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
    {
        return GetSpParameterSet(connectionString, spName, false);
    }
 
    /// <summary>
    /// Retrieves the set of SqlParameters appropriate for the stored procedure
    /// </summary>
    /// <remarks>
    /// This method will query the database for this information, and then store it in a cache for future requests.
    /// </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>
    public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
    {
        if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
        }
    }
 
    /// <summary>
    /// Retrieves the set of SqlParameters appropriate for the stored procedure
    /// </summary>
    /// <remarks>
    /// This method will query the database for this information, and then store it in a cache for future requests.
    /// </remarks>
    /// <param name="connection">A valid SqlConnection object</param>
    /// <param name="spName">The name of the stored procedure</param>
    /// <returns>An array of SqlParameters</returns>
    internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
    {
        return GetSpParameterSet(connection, spName, false);
    }
 
    /// <summary>
    /// Retrieves the set of SqlParameters appropriate for the stored procedure
    /// </summary>
    /// <remarks>
    /// This method will query the database for this information, and then store it in a cache for future requests.
    /// </remarks>
    /// <param name="connection">A valid SqlConnection object</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>
    internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone())
        {
            return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
        }
    }
 
    /// <summary>
    /// Retrieves the set of SqlParameters appropriate for the stored procedure
    /// </summary>
    /// <param name="connection">A valid SqlConnection object</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>
    private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
 
        string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
 
        SqlParameter[] cachedParameters;
 
        cachedParameters = paramCache[hashKey] as SqlParameter[];
        if (cachedParameters == null)
        {
            SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
            paramCache[hashKey] = spParameters;
            cachedParameters = spParameters;
        }
 
        return CloneParameters(cachedParameters);
    }
 
    #endregion Parameter Discovery Functions
 
}

相同類似結構的檔案還有「OleDbHelper.cs」「OracleHelper.cs」,其實這些程式是相當早期的 Application Block 程式, 2000 ~ 2001 年的時候還沒有出現所謂的「Enterprise Library」,而是以「Microsoft Data Access Application Block for .NET 」的方式存在,下面補上程式碼裡原本的註解說明,

// ===============================================================================
// Microsoft Data Access Application Block for .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.
//
// For more information see the Data Access Application Block Implementation Overview. 
// ===============================================================================
// Release history
// VERSION    DESCRIPTION
//   2.0    Added support for FillDataset, UpdateDataset and "Param" helper methods
//
// ===============================================================================
// 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.
// ==============================================================================

而這些的 SqlHelper, OracleHelper, OleDbHelper 檔案就從 2000 開始一直到 2005 年之間廣為流傳,相信現在有很多人的專案裡面也應該有這些檔案的存在,而「Enterprise Library 1.0」是到了 2005 年的時候才正式發佈,目前我們所看到的 EntLib 架構雛形是基於 2006 年所發佈的「Enterprise Library 2.0」。

會再補充說明這一段「講古」,就是提醒還在使用傳統 ADO.NET 方式存取的朋友,可以試著改用 EntLib DAAB,並不會改變原本存取 DataSet, DataTable 的方式,一樣是使用 Connection, Command, DataAdapater, DataReader 等等,只是改變的是程式的部份寫法,而相信改用 EntLib DAAB 來存取資料庫後,往後會很難再去使用最傳統的 ADO.NET 的方式對資料庫做存取處理。

而如果專案裡面還存有我剛剛說過的 SqlHelper.cs 檔案或是自己有整理了一支裡面有各式各樣存取資料方法檔案的朋友,也請改用 EntLib DAAB 吧,仍然使用 SqlHelper.cs 的人,請務必了解你們使用的可能是十多年前的舊程式,已經有更新更好的程式可以替代了,所以就不要再用舊程式了,而自己有整理各種資料庫存取方法的朋友,也請改用 EntLib DAAB,因為同樣是以 ADO.NET 來存取資料庫,EntLib DAAB 的程式會比我們自己東拼西湊的還有嚴謹與完整。

至於怎麼使用 Enterprise Library - Data Access Application Block,這就不在這篇文章所討論的內容了,而官方網站上面有詳細的資源以及文件檔案,就請各位朋友自行前往了解。

 

延伸閱讀:

簡述 Oracle + Enterprise Library 5.0 Data Access Application Block 的操作

Enterprise Library DAAB + Oracle:Provider 使用 ODP.NET

patterns & practices – Enterprise Library : Enterprise Library 5.0 - April 2010

Hands-On Labs for Microsoft Enterprise Library 5.0

Enterprise Library 5.0 – May 2011 Developer's Guide:Chapter 2 - Much ADO about Data Access

 

以上

7 則留言:

  1. 請問版主,該如何評估哪些狀況該使用 Entity framework或是 Enterprise Library ?

    回覆刪除
    回覆
    1. 這兩個...其實不是對等的比較,因為 ADO.NET Entity Framework 是 ORM Solution,
      而 Enterprise Library 包含了多個 Application Block(應用程式區塊):
      Data Access 資料存取
      Exception Handling 異常處理
      Logging 記錄
      Policy Injection
      Unity
      Validation 驗證
      Transient Fault Handling
      Semantic Logging

      Enter Library 不單只有資料存取這一塊的應用,也包含了其他開發應用程式時所需要的輔助與加強功能,
      所以你說 EF 與 EntLib 要怎麼評估,這就很難去做比較了,一個是在資料存取的應用,而另一個是大範圍的功能應用。

      其實你想要問的是 EF 與 EntLib 的 Data Access Application Block 怎麼評估使用吧!?
      如同我在文章所強調的,DAAB 只是加強與簡化原本傳統 ADO.NET 存取的程式寫法與操作,
      其實還是一樣是在程式裡玩轉 ADO.NET,一樣還是要面對 DataReader, DataSet 等等,
      而 EF 雖然底層還是 ADO.NET,EF 是個 ORM Solution,開發人員鮮少要再去處理 ADO.NET 的程式操作,
      所以一個是 ORM 的應用,而另一個是傳統 ADO.NET 的應用,
      這就需要看你的開發團隊或是開發的專案是不是適合使用 ORM Solution。

      如果專案所面對的資料庫是一開始沒有明確的做資料分析而會因為每次需求的不同而添加 table 的欄位,
      而且資料庫中含有大量的 Stored Procedure,程式對於資料的 CRUD 有很多要透過 SP 才能完成,
      如果你的程式沒有明確的物件導向(不是用了 C# or VB.NET 所開發的專案就是物件導向程式開發),
      以上的狀況大部分都有的話,那個還是繼續維持 ADO.NET 來做資料存取,而建議使用 EntLib DAAB。

      如果是新的專案、新的資料庫、表格、欄位,團隊有 OOAD OOP 的能力,
      那麼就使用 ADO.NET Entity Framework 或是其他 ORM Solution。

      刪除
  2. 很不錯耶
    我還在苦惱nuget抓不到entlib5,幸好看到你這篇

    回覆刪除
  3. 版大您好,小弟因專案原因,一個超過十年的系統用EntLib2.0,目前可能要大翻修,想順便把Enterprise Library升級版本,但目前搜尋了一下EnterpriseLibrary微軟早就沒在維護了.要找最新的6.0的資料也相當少..連Hands on Lab的連結都沒了>_<,是否可請教您5.0算是一個成熟的版本嗎?因為我感覺5.0的範例還比較多一點,或是如果拿5.0的範例去6.0參考不知可行不,我主要還是用DAAB這塊

    回覆刪除
    回覆
    1. 有什麼理由一定還要在新專案用一個已經多年而且未來也不會維護的套件呢

      刪除

提醒

千萬不要使用 Google Talk (Hangouts) 或 Facebook 及時通訊與我聯繫、提問,因為會掉訊息甚至我是過了好幾天之後才發現到你曾經傳給我訊息過,請多多使用「詢問與建議」(在左邊,就在左邊),另外比較深入的問題討論,或是有牽涉到你實作程式碼的內容,不適合在留言板裡留言討論,請務必使用「詢問與建議」功能(可以夾帶檔案),謝謝。