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

Anytime you find yourself writing very similar code blocks in a project, consider encapsulation: grouping the related generic common functionality into a general black-box component and then reuse it from your application. Almost all applications need access to data, and they each need a data-access layer for this purpose. The data-access layer plumbing code is an example of the common code that you would write for all kinds of applications. By encapsulating this code, you can drastically reduce the amount of code in your data-access layer, thereby making the code more readable, maintainable, and updateable. Additionally, by reusing proven and tested code, you also reduce the chance for types, which results in increased overall application quality.

Installation

Before using the Data Access Application Block, take the following steps to install and configure it in your local machine:

  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

The enterprise library configuration tool helps create the necessary configuration files so that the Data Access Application Block can retrieve the configuration settings. Figure 1 shows the configuration settings used by the examples in this article.


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

To illustrate the advantage of using the Data Access Application Block, take a look at sample code that creates a DataSet object and binds it to a DataGrid using the Data Access Application Block. In general, returning a DataSet involves establishing a connection, creating a SqlCommand, and executing the command against the database. The resulting DataSet object can then be bound to a DataGrid:

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.


Figure 2. Result of DataSet Object Bound to a DataGrid

Executing a Stored Procedure with Input Parameters

To demonstrate the steps involved in executing a stored procedure with input parameters, the following stored procedure returns all the products based on the supplied category ID:

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.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read