Working with the Enterprise Library's Data Access Application Block

By Scott Mitchell

Introduction

One of Microsoft's efforts over the past couple of years has been to provide developers with useful code libraries that illustrate best practices. I discussed Microsoft's latest foray into this arena in an earlier article, An Introduction to the Microsoft Enterprise Library. The Enterprise Library is a suite of classes - called application blocks - provided by Microsoft's Patterns and Practices Group that illustrate best practices for performing a myriad of common enterprise-related tasks. The Enterprise Library contains application blocks for caching, configuration, data access, cryptography, exception handling, logging and instrumentation, and security.

In this article we will examine one of the most commonly used application blocks in the Enterprise Library - the Data Access Application Block (DAAB). The DAAB has existed in a stand-alone form for quite some time (see John Jakovich's article Examining the Data Access Application Block), but the Enterprise Library has updated the DAAB of old so that it uses an abstract provider and offers greater integration with the other application blocks that make up the Enterprise Library. Read on to learn more about how to use the Enterprise Library's DAAB in your applications!

- continued -

Why Do We Need the DAAB?

If you're reading this article you've more than likely had to create an ASP.NET site that has worked with database data. In ASP.NET, the assorted ADO.NET classes are used to connect to a data store, issue a command, and, if needed, work with its results. For example, I've written code that looks like the following a countless number of times:

// Connect to the database SqlConnection myConnection = new SqlConnection(connection string); myConnection.Open(); // Specify the command to use to query the database SqlCommand myCommand = new SqlCommand(sql query, myConnection); // If the SQL query has parameters (i.e., @ParamName), add values for // the parameters in the query... myCommand.Parameters.Add("@ParamName1", ParamValue1); myCommand.Parameters.Add("@ParamName2", ParamValue2); ... myCommand.Parameters.Add("@ParamNameN", ParamValueN); // Retrieve the results of the query in a SqlDataReader SqlDataReader reader = myCommand.ExecuteReader(); // work with the returned data...

There are a few problems with the approach. First, the code is very verbose and repetitive. Should I really have to write upwards of ten lines of code to retrieve data from a database? Seeing as this is one of the most common tasks performed in a data-driven Web application, it makes sense to streamline the code as much as possible. Second, the code above tightly couples the implementation to the application. What happens if the CEO of the company buddies up with some Oracle sales reps and decides that the company will switch from Microsoft SQL Server to Oracle? Whoops, I would have to hunt and peck through all of my code and replace all instances of SqlConnection, SqlCommand, SqlDataReader and other provider-specific classes with their Oracle equivalents (OracleConnection, OracleCommand, and so on).

The DAAB solves these problems by providing a simple, streamlined interface for accessing data and by abstracting out provider information into a separate configuration file. The DAAB provides a number of methods for accessing data, but the jist is that such data access can be done in one line of code. This is because the DAAB's methods wrap the several lines of code needed to access data. Rather than having to write the 5-10 lines of code (as we saw above), the DAAB simplifies code down to the following:

// Create a database object Database db = DatabaseFactory.CreateDatabase(); // Get back a DataReader IDataReader reader = db.ExecuteReader(CommandType.Text, SQL query);

(If you're really serious about reducing the total lines of code, the above could be reduced to one line: DatabaseFactory.CreateDatabase().ExecuteReader(CommandType.Text, SQL query);.)

The DAAB also has the concept of an abstract provider. Note that in the DAAB code snippet we just saw there are no details about the data provider. Looking at the code you can't tell if I'm working against a SQL Server database or an Oracle database. In fact, there's no connection string information specified, either. All of these provider-specific bits of information are stored within a configuration file, dataConfiguration.config. The DAAB consults this configuration information at runtime to use the correct classes, connection strings, and so forth.

Accessing Data with the Data Access Application Block (DAAB)

Before you can use the DAAB in an ASP.NET application you'll need to first install the Enterprise Library. For instructions and a discussion on installation and setup of the EntLib, please refer back to An Introduction to the Microsoft Enterprise Library. As discussed in An Introduction to the Microsoft Enterprise Library, be certain to run the Install Services batch file after you have completed the installation of the Enterprise Library. Over the remainder of this article we'll be stepping through a sample ASP.NET application that utilizes the DAAB. This code discussed is available at the end of this article.

Configuring the DAAB

To demonstrate using the DAAB, start by creating a new Web application. Next, add the following Enterprise Library assemblies to the application's References folder (these assemblies will be located relative to the directory where you installed the Enterprise Library; on my computer they were at Program Files\Microsoft Enterprise Library\bin). Before we can start writing code to utilize the DAAB we first need to create the database configuration file that contains the information needed by the DAAB to connect to the appropriate data provider. In earlier versions of Microsoft's application blocks, specifying configuration settings typically meant mucking around in the Web.config file by hand, adding or editing various XML content. With the Enterprise Library, however, this information is stored externally to the Web.config file and a GUI tool is included to assist with setting configuration values.

To launch the GUI configuration tool go to Start --> Programs --> Microsoft patterns & practices --> Enterprise Library --> Enterprise Library Configuration. Next, click the Open Existing Application icon in the toolbar or go to the File menu and select Open Application. Browse to your Web application's Web.config file and click OK. This should load up a single node titled "Application" in the Enterprise Library Configuration tree in the left pane (see the screenshot to the right).

To add configuration information for the DAAB, right-click on the "Application" node and select New --> Data Access Application Block. This will add two new children under the "Application" node (as shown in the screenshot below):

Configuration Application Block, which contains information on how the DAAB configuration information is stored, and
Data Access Application Block, which contains information pertinent to data access, such as what provider is used, what connection string to use, and so on.

The first order of business is to specify the connection string. In the Data Access Application Block node you'll see a child node named Connection Strings, which has a child node named Sql Connection String, which has three children nodes itself: database, Integrated Security, and server. Clicking on any of these values will display the value in the pane on the right. You can customize these values to meet your connection string needs, or right-click on the Sql Connection String node and choose New / Parameter to add additional connection string parameters. (The connection string used is simply the concatenation of these parameters. So if you use the default three connection string parameters with their default values, the connection string used when accessing the database will be: server=server;database=database;Integrated Security=true;.)

In the demo I created I used the SQL Server Northwind database as my sample database. If you don't have the Northwind database installed on your SQL Server instance you can download it for free from Microsoft: Northwind and pubs Sample Databases.

By default the DAAB Enterprise Library is configured to use Microsoft SQL Server as its database. You can, however, change this to a different provider through the Database Types node. When specifying a provider you need to point to a class that is derived from the Microsoft.Practices.EnterpriseLibrary.Data.Database class. (The Enterprise Library ships with two built-in provider classes: one for Microsoft SQL Server and one for Oracle. You could create your own provider class for different database types if needed.)

Once you have the configuration values set, save the configuration information by clicking the Save icon in the toolbar. This will create a dataConfiguration.config file in your Web application directory with the configuration settings specified, as well as update the application's Web.config file to indicate that the Enterprise Library's DAAB settings can be found in dataConfiguration.config. We're now ready to start writing code in our ASP.NET application that works with data via the DAAB.

Accessing Data through the DAAB

Like past versions of the DAAB, the Enterprise Library version of the DAAB consists of a number of static methods. There are methods for returning different types of database objects - DataReaders or DataSets - as well as methods that are useful for running queries that either return a single, scalar value or return no results at all. Additionally, each of these methods contain a number of overloads that allow each method to work with ad-hoc SQL queries, parameterized stored procedures, and the use of transactions.

For example, to retrieve the results of a SELECT query in a DataReader we could use the following code:

// Create a database object Database db = DatabaseFactory.CreateDatabase(); // Get back a DataReader containing a list of all products. string sqlQuery = "SELECT * FROM Products ORDER BY ProductName"; IDataReader reader = db.ExecuteReader(CommandType.Text, sqlQuery);

The first thing to notice is that the ExecuteReader() method is returning an object that implements IDataReader. We aren't bringing back a provider-specific DataReader (like SqlDataReader or OracleDataReader). Instead, we are saying, "Give me back some class that implements IDataReader, which is an interface that spells out the methods that all DataReaders must provide. What we get back - be it a SqlDataReader or OracleDataReader - depends on the configuration information specified in dataConfiguration.config. Since we don't want to tie our code to a specific provider, we simply work in terms of interfaces, as in IDataReader.

The ExecuteReader() method has a number of overloads. In the example shown above, the first parameter specifies if the query is against a stored procedure, if an entire table's contents are being requested, or if an ad-hoc SQL query is specified. Realize that with the above overload parameters in the query or stored procedure cannot be used. If you want to use parameters you must create a DBCommandWrapper instance that specifies the ad-hoc SQL or stored procedure to execute along with the associated parameters. For example, to retrieve a DataReader with just those products that belong to a particular category ID, we would use:

// Create a database object Database db = DatabaseFactory.CreateDatabase(); // specify an ad-hoc SQL statement with a parameter... string sqlQuery = "SELECT * FROM Products WHERE CategoryID = @CategoryID"; // Get a GetSqlStringCommandWrapper to specify the query and parameters DBCommandWrapper command = db.GetSqlStringCommandWrapper(sqlQuery); // Set the parameter's value command.AddInParameter("@CategoryID", DbType.Int32, categoryID); // Get the query results in a DataReader IDataReader reader = db.ExecuteReader(command);

This same logic can be used to execute parameterized stored procedures, the only difference being instead of calling GetSqlStringCommandWrapper(SQL query) use GetStoredProcCommandWrapper(SQL query). Alternatively, you can specify the parameter values for a stored procedure directly in the ExecuteReader() method:

// Create a database object Database db = DatabaseFactory.CreateDatabase(); // specify the parameters for the sproc directly // Get the query results in a DataReader IDataReader reader = db.ExecuteReader("sprocName", paramValue1, paramValue2, ..., paramValueN);

In addition to the ExecuteReader() methods, there are the ExecuteDataSet(), ExecuteScalar(), and ExecuteNonQuery() methods, all of which share the same set of overloads but return either a DataSet, a scalar value, or no value at all.

Added Benefits of the Enterprise Library DAAB

If you've used past versions of the DAAB the DAAB's API should be familiar. The main difference with the Enterprise Library's version of the DAAB is the use of an abstract provider. Rather than having the SqlClient provider hard-coded in, the Enterprise Library DAAB uses the DatabaseFactory class to create an instance of a developer-specific provider class.

In addition to the abstract provider, the Enterprise Library DAAB offers even more benefits over older versions of the DAAB. There's the GUI tool for setting configuration values, which is nice. There's also built-in instrumentation. The Install Services batch file you ran after installing the Enterprise Library created a number of performance counters that the DAAB automatically records its metrics to. For example, with the DAAB's new instrumentation you can keep an eye on such metrics as number of connections opened per second, average command execution time, and other valuable statistics, all through the standard Performance monitor in Windows.

If you find yourself still accessing data by creating the ADO.NET classes by hand, consider checking out the Enterprise Library's DAAB. It's a tool I find indispensable. And if you are using an older version of the DAAB consider upgrading to the Enterprise Library. By moving toward the Enterprise Library you can also utilize the other application blocks to improve your application's functionality.

Happy Programming!

By Scott Mitchell

Attachments

Download the demo application