Data Access Application Block Streamlines Your Data-Access Layer

The main purpose of an application's data-access layer is to transport required information back and forth between the database and the application. Because of the focused nature of the data-access code, the code in this layer can quickly become repetitive. For example, to interact with the database, you need to open a connection to the database, initialize the SQL command or stored procedure, and close the connection. Except for the name of the SQL command or stored procedure and the parameters passed to them, this is a common sequence you would have to follow from any data-access layer code.

Instead of creating this code in every project, wouldn't it be great to encapsulate it in a reusable block and reuse it in different projects? That is exactly what Microsoft's Enterprise Library (known as EntLib) Data Access Application Block provides. This article introduces the EntLib Data Access Application Block and shows how it makes data-access layer coding a breeze.

Encapsulation

Installation

1. Download the EntLib from MSDN.
2. From the menu, navigate to Start->All Programs->Microsoft patterns and practices->Enterprise Library->Build Enterprise Library. This will build all the EntLib blocks, including the Data Access Application Block.
3. Once the assembly has been built, you can add references to the required Data Access Application Block assemblies (Microsoft.Practices.EnterpriseLibrary.Data.dll and Microsoft.Practices.EnterpriseLibrary.Configuration.dll) from <DriveName>:\Program Files\Microsoft Enterprise Library\bin. Note that you need to add a reference to the configuration block assembly as well since the Data Access Application Block has a dependency on the configuration block for reading connection-string-related information from the configuration file.
4. Create the necessary configuration files using the Enterprise Library Configuration tool.
5. Once the assemblies are referenced, the next step is to import the required namespaces. To leverage the Data Access Application Block, add the following import statements to your code:

   
   using Microsoft.Practices.EnterpriseLibrary.Data;
   using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
   

Once you've installed the Data Access Application Block, create the configuration file through the configuration utility.

Creating Configuration Files

Click here for a larger image.

Figure 1. The EntLib Configuration Settings

When you save the settings shown in Figure 1, you create two files: one with the name that you entered in the file 'save as' prompt and the other with the name dataConfiguration.config. The dataConfiguration.config file is the one that contains the actual connection string.

Now that you have a general understanding of the Data Access Application Block, take a look at some examples.

Executing a Dynamic SQL Query

private void btnInvokeDynamicSql_Click(object sender, System.EventArgs e)
{
       Database db = DatabaseFactory.CreateDatabase("Northwind");
DataSet categoriesDataSet = db.ExecuteDataSet(CommandType.Text, 
"SELECT * FROM Categories"); 
gridOutput.DataSource = categoriesDataSet.Tables[0].DefaultView;
gridOutput.DataBind();
}
 
Everything starts out with DatabaseFactory.CreateDatabase(). 
 
Database db = DatabaseFactory.CreateDatabase("Northwind");

The above code specifies the database instance name as an argument to the CreateDatabase() method. If you don't specify a named instance of a database, it just grabs the default database in the configuration file. Then you simply execute the query using the ExecuteDataSet() method of the Database object:

DataSet categoriesDataSet = db.ExecuteDataSet(CommandType.Text, 
"SELECT * FROM Categories"); 

Finally, you simply bind the DataSet onto a DataGrid control for display.

If you navigate to the page with your browser and click on the button, you should see the output shown in Figure 2.

Click here for a larger image.

Figure 2. Result of DataSet Object Bound to a DataGrid

Executing a Stored Procedure with Input Parameters

Create PROCEDURE dbo.GetProductsByCategoryID
(
       @CategoryID int
)
 
AS
       SET NOCOUNT ON
       
Select ProductID, ProductName, QuantityPerUnit, UnitPrice  
from Products Where CategoryID = @CategoryID

The Data Access Application Block comes with a DBCommandWrapper class that is essentially a SQL Server wrapper for SqlCommand. The DBCommandWrapper is obtained through the call to the GetStoredProcCommandWrapper() method, which passes in the stored procedure name as an argument. You can use the AddInParameter() method of the DBCommandWrapper object to pass parameters to the stored procedure:

private void btnExecuteStoredProcedure_Click(object sender, 
System.EventArgs e)
{
       Database db = DatabaseFactory.CreateDatabase("Northwind");
DBCommandWrapper dbc = 
db.GetStoredProcCommandWrapper("GetProductsByCategoryID");
dbc.AddInParameter("@CategoryID", DbType.Int32, 1);
DataSet categoriesDataSet =  db.ExecuteDataSet(dbc);
gridOutput.DataSource = categoriesDataSet.Tables[0].DefaultView;
gridOutput.DataBind();
}

As you can see, the AddInParameter() method accepts the data type of the parameter as well as the parameter value as arguments.

Executing a Stored Procedure with Output Parameters

CREATE PROCEDURE dbo.GetProductDetails
(
       @ProductID int,
       @ProductName nvarchar(40) output,
       @UnitPrice money output
)
 
AS
       SET NOCOUNT ON
       Select @ProductName = ProductName, @UnitPrice = UnitPrice
       from Products Where ProductID = @ProductID

As you can see, the stored procedure returns the product name and unit price for a specific product as output parameters. The Click event of the btnRetrieveSingleRow button executes the GetProductDetails using the Data Access Application Block:

private void btnRetrieveSingleRow_Click(object sender, System.EventArgs e)
{
Database db = DatabaseFactory.CreateDatabase("Northwind");
string procName = "GetProductDetails";
DBCommandWrapper dbc = db.GetStoredProcCommandWrapper(procName);
dbc.AddInParameter("@ProductID", DbType.Int32, 1);
dbc.AddOutParameter("@ProductName", DbType.String, 50);
dbc.AddOutParameter("@UnitPrice", DbType.Currency, 8);
db.ExecuteNonQuery(dbc);
lblResult.Text = "Product Name: " + 
dbc.GetParameterValue("@ProductName") + "
";
lblResult.Text += "Unit Price: " + dbc.GetParameterValue("@UnitPrice") 
+ "
";
}

As with the previous examples, the preceding code also specifies the input parameters using the AddInParameter() method of the DBCommandWrapper object. To specify the output parameters, you use the AddOutParameter() method. Once all the parameters are added, executing the stored procedure is very simple and straightforward. Just invoke the ExecuteNonQuery() method of the Database object. Finally, you retrieve the output parameter values returned by the stored procedure through the GetParameterValue() method of the DBCommandWrapper object. Figure 3 shows the output produced by the code.

Click here for a larger image.

Figure 3. Stored Procedure with Output Parameters

Retrieving a Single Scalar Value

Create PROCEDURE dbo.GetProductName
(
    @ProductID int           
)
 
AS
       SET NOCOUNT ON
       Select ProductName from Products 
Where ProductID = @ProductID

The following is the code required to execute this stored procedure:

private void btnRetrieveSingleItem_Click(object sender, System.EventArgs e)
{
Database db = DatabaseFactory.CreateDatabase("Northwind");
string productName = (string) db.ExecuteScalar("GetProductName", 1);
lblResult.Text = "Product Name: " +  productName;
}             

Once you have an instance of the Database class, you can then easily execute the stored procedure using the ExecuteScalar() method to pass in the name of the stored procedure and the parameter value to the stored procedure. The ExecuteScalar() method simply returns the product name as an object, which is then typecast into a string for display purposes.

Executing Code Within a Transaction

private void TransactionExample()
{
Database db = DatabaseFactory.CreateDatabase();
using (IDbConnection connection = db.GetConnection())
{
connection.Open();
IDbTransaction transaction = connection.BeginTransaction();
try
{
int amount = 1000;
string srcAccount = "AccountOne";
string dstAccount = "AccountTwo";
db.ExecuteNonQuery(transaction, "CreditAccount", 
srcAccount, amount);
db.ExecuteNonQuery(transaction, "DebitAccount", 
dstAccount, amount );
transaction.Commit();
}
catch
{
transaction.Rollback();
}  
connection.Close();
}
}

The above code example executes two stored procedures named CreditAccount and DebitAccount within the scope of a single transaction. If both the procedures are executed successfully, the transaction is committed; otherwise, it is rolled back.

Freedom from Tedious Data-Access Details

Download the Code

About the Author