我想很多人看到文章標題之後一定都會說，這有什麼難的嗎？這值得寫一篇文章嗎？
再看一下文章標題，我要說的是「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，
所以建議要使用 DAAB 6 的朋友就必須要用 VS2012。
我想很多人現在開發專案再安裝第三方組件的習慣已經漸漸地從以前在網站下載、解壓縮、專案加入參考的這些動作，現在多半都會優先使用 NuGet 來搜尋相關組件然後安裝到專案中，而 Enterprise Library 當然也可以從 NuGet 裡選擇要使用的 Application Block 然後點擊「安裝」按鍵就可以輕鬆地把要使用的應用區塊給安裝到專案裡。
但就如同我前面所說的，EntLib 6 已經在 2013-04-26 正式發佈，而 NuGet 裡也是一併更新，現在 NuGet 裡以「僅限穩定」的方式來尋找有關 Enterprise Library 的項目，官方所建立的套件都已經是 EntLib 6，
使用「包括發行前版本」來找之前「Enterprise Library 5.0」版本的套件，就是遍尋不到「Enterprise Library 5.0 - Data Access Application Block」，
如果在 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 是會有以下的錯誤訊息顯示，
專案使用 .NET Framework 4.0 就只能安裝 EntLib 5 - DAAB，但其實不管在 VS2010 或是 VS2012，目前是無法在 NuGet 套件管理介面裡下載安裝 EntLib 5 - DAAB，但我們還是可以透過 NuGet 套件管理主控台來安裝，首先是透過 NuGet 套件管理介面先找到 EntLib 6 - DAAB，
找到 EntLib 6 - DAAB 之後，點選右方資料欄位裡的「識別碼」，點選之後就會到以下的 NuGet Gallery 網頁裡，
https://nuget.org/packages/EnterpriseLibrary.Data
在上面網頁的下方「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」給複製起來，
接著就是到 Visual Studio 裡開啟 NuGet 的「套件管理主控台」，「工具 > 程式庫套件管理員 > 套件管理器主控台」
VS2010
VS2012
點選「套件管理器主控台」之後，會在下方開啟套件管理器介面，
VS2010
VS2012
接著就是把從網頁上所複製的執行碼「Install-Package EnterpriseLibrary.Data -Version 5.0.505.0」給貼到套件管理器主控台裡，然後按下 Enter 鍵就會開始進行安裝，
上圖為安裝的經過與完成的訊息內容，然後在專案的參考裡也可以看到安裝好的套件，
使用上面的操作步驟之後，就可以為專案安裝 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 valuesforeach (SqlParameter commandParameter in commandParameters)
{ // Check the parameter nameif (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 arrayfor (int i = 0, j = commandParameters.Length; i < j; i++)
{ // If the current array value derives from IDbDataParameter, then assign its Value propertyif (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 commandcommand.Connection = connection;
// Set the command text (stored procedure name or SQL statement)command.CommandText = commandText;
// If we were provided a transaction, assign itif (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 typecommand.CommandType = commandType;
// Attach the command parameters if they are providedif (commandParameters != null)
{AttachParameters(command, commandParameters);
}
return;}
#endregion private utility methods & constructors
#region ExecuteNonQuery /// <summary> /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in /// the connection string /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="connectionString">A valid connection string for a SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or T-SQL command</param> /// <returns>An int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
{ // Pass through the call providing null for the set of SqlParametersreturn 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 doneusing (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 goif ((parameterValues != null) && (parameterValues.Length > 0))
{ // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Assign the provided values to these parameters based on parameter orderAssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of 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 SqlParametersreturn ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary> /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or T-SQL command</param> /// <param name="commandParameters">An array of SqlParamters used to execute the command</param> /// <returns>An int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{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 againcmd.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 goif ((parameterValues != null) && (parameterValues.Length > 0))
{ // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided values to these parameters based on parameter orderAssignParameterValues(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 SqlParametersreturn ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary> /// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or T-SQL command</param> /// <param name="commandParameters">An array of SqlParamters used to execute the command</param> /// <returns>An int representing the number of rows affected by the command</returns>public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{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 againcmd.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 goif ((parameterValues != null) && (parameterValues.Length > 0))
{ // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Assign the provided values to these parameters based on parameter orderAssignParameterValues(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 SqlParametersreturn 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 doneusing (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 goif ((parameterValues != null) && (parameterValues.Length > 0))
{ // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Assign the provided values to these parameters based on parameter orderAssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of 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 SqlParametersreturn ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or T-SQL command</param> /// <param name="commandParameters">An array of SqlParamters used to execute the command</param> /// <returns>A dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{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 & DataSetusing (SqlDataAdapter da = new SqlDataAdapter(cmd))
{ DataSet ds = new DataSet(); // Fill the DataSet using default values for DataTable names, etcda.Fill(ds);
// Detach the SqlParameters from the command object, so they can be used againcmd.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 goif ((parameterValues != null) && (parameterValues.Length > 0))
{ // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided values to these parameters based on parameter orderAssignParameterValues(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 SqlParametersreturn ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// 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 & DataSetusing (SqlDataAdapter da = new SqlDataAdapter(cmd))
{ DataSet ds = new DataSet(); // Fill the DataSet using default values for DataTable names, etcda.Fill(ds);
// Detach the SqlParameters from the command object, so they can be used againcmd.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 goif ((parameterValues != null) && (parameterValues.Length > 0))
{ // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Assign the provided values to these parameters based on parameter orderAssignParameterValues(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 readerSqlDataReader 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 SqlParametersreturn 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 stringreturn ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
}
catch { // If we fail to return the SqlDatReader, we need to close the connection ourselvesif (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 goif ((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 SqlParametersreturn 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 connectionreturn 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 goif ((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 SqlParametersreturn 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 goif ((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 SqlParametersreturn 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 doneusing (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 goif ((parameterValues != null) && (parameterValues.Length > 0))
{ // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Assign the provided values to these parameters based on parameter orderAssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of 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 SqlParametersreturn 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 againcmd.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 goif ((parameterValues != null) && (parameterValues.Length > 0))
{ // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided values to these parameters based on parameter orderAssignParameterValues(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 SqlParametersreturn 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 againcmd.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 goif ((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 orderAssignParameterValues(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 SqlParametersreturn 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 & DataSetXmlReader retval = cmd.ExecuteXmlReader();
// Detach the SqlParameters from the command object, so they can be used againcmd.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 goif ((parameterValues != null) && (parameterValues.Length > 0))
{ // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided values to these parameters based on parameter orderAssignParameterValues(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 SqlParametersreturn 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 & DataSetXmlReader retval = cmd.ExecuteXmlReader();
// Detach the SqlParameters from the command object, so they can be used againcmd.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 goif ((parameterValues != null) && (parameterValues.Length > 0))
{ // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Assign the provided values to these parameters based on parameter orderAssignParameterValues(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 doneusing (SqlConnection connection = new SqlConnection(connectionString))
{connection.Open();
// Call the overload that takes a connection in place of the connection stringFillDataset(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 doneusing (SqlConnection connection = new SqlConnection(connectionString))
{connection.Open();
// Call the overload that takes a connection in place of the connection stringFillDataset(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 doneusing (SqlConnection connection = new SqlConnection(connectionString))
{connection.Open();
// Call the overload that takes a connection in place of the connection stringFillDataset(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 goif ((parameterValues != null) && (parameterValues.Length > 0))
{ // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided values to these parameters based on parameter orderAssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParametersFillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
}
else { // Otherwise we can just call the SP without paramsFillDataset(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 goif ((parameterValues != null) && (parameterValues.Length > 0))
{ // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Assign the provided values to these parameters based on parameter orderAssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParametersFillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
}
else { // Otherwise we can just call the SP without paramsFillDataset(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 & DataSetusing (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
{ // Add the table mappings specified by the userif (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, etcdataAdapter.Fill(dataSet);
// Detach the SqlParameters from the command object, so they can be used againcommand.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 doneusing (SqlDataAdapter dataAdapter = new SqlDataAdapter())
{ // Set the data adapter commandsdataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// Update the dataset changes in the data sourcedataAdapter.Update(dataSet, tableName);
// Commit all the changes made to the DataSetdataSet.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 goif ((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 orderfor (int index = 0; index < sourceColumns.Length; index++)
commandParameters[index].SourceColumn = sourceColumns[index];
// Attach the discovered parameters to the SqlCommand objectAttachParameters(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 initializedif (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 valuesAssignParameterValues(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 initializedif (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 valuesAssignParameterValues(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 initializedif (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 valuesAssignParameterValues(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 initializedif (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 valuesAssignParameterValues(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 initializedif (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 valuesAssignParameterValues(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 initializedif (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 valuesAssignParameterValues(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 initializedif (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 valuesAssignParameterValues(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 initializedif (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 valuesAssignParameterValues(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 initializedif (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 valuesAssignParameterValues(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 initializedif (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 valuesAssignParameterValues(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 initializedif (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 valuesAssignParameterValues(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 initializedif (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 valuesAssignParameterValues(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 initializedif (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 valuesAssignParameterValues(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 initializedif (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 valuesAssignParameterValues(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 valueforeach (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
以上
請問版主，該如何評估哪些狀況該使用 Entity framework或是 Enterprise Library ?
回覆刪除這兩個...其實不是對等的比較，因為 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。
謝謝版主解惑
回覆刪除很不錯耶
回覆刪除我還在苦惱nuget抓不到entlib5，幸好看到你這篇
很高興能夠幫助到你.
刪除版大您好，小弟因專案原因，一個超過十年的系統用EntLib2.0，目前可能要大翻修，想順便把Enterprise Library升級版本，但目前搜尋了一下EnterpriseLibrary微軟早就沒在維護了.要找最新的6.0的資料也相當少..連Hands on Lab的連結都沒了>_<，是否可請教您5.0算是一個成熟的版本嗎?因為我感覺5.0的範例還比較多一點，或是如果拿5.0的範例去6.0參考不知可行不，我主要還是用DAAB這塊
回覆刪除有什麼理由一定還要在新專案用一個已經多年而且未來也不會維護的套件呢
刪除