Working with the Enterprise Library's Data Access Application BlockBy Scott Mitchell
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!
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:
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
SqlDataReader and other
provider-specific classes with their Oracle equivalents (
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:
(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.configfile by hand, adding or editing various XML content. With the Enterprise Library, however, this information is stored externally to the
Web.configfile 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
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:
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
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
to indicate that the Enterprise Library's DAAB settings can be found in
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:
The first thing to notice is that the
ExecuteReader() method is returning an object that
IDataReader. We aren't bringing back a provider-specific DataReader (like
OracleDataReader). Instead, we are saying, "Give me back some class that implements
which is an interface that spells out the methods that all DataReaders must provide. What we get back
- be it a
OracleDataReader - depends on the configuration information
dataConfiguration.config. Since we don't want to tie our code to a specific provider,
we simply work in terms of interfaces, as in
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:
This same logic can be used to execute parameterized stored procedures, the only difference being instead of
GetSqlStringCommandWrapper(SQL query) use
Alternatively, you can specify the parameter values for a stored procedure directly in the
In addition to the
ExecuteReader() methods, there are the
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
SqlClientprovider hard-coded in, the Enterprise Library DAAB uses the
DatabaseFactoryclass 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.