.NET Reference Guide

The Data Access Application Block

Last updated Oct 28, 2004.

The Data Access Application Block (DAAB) is a .NET component that contains optimized data access code that will help you call stored procedures and issue SQL text commands against a SQL Server database. It returns SqlDataReader, DataSet, and XmlReader objects. You can use it as a building block in your own .NET-based application to reduce the amount of custom code you need to create, test, and maintain.

Why Use the Data Access Application Block?

If you've written any ADO.NET data access code, you've probably realized that most of what you do is the same regardless of the table or database you're working with. You have to create a connection, set up a command, pass parameters, and then execute the command. Oh, and you have to handle exceptions and make sure that you're freeing unmanaged resources in the process. There is a whole lot of bookkeeping to manage just to execute a single query. For example:

SqlConnection nwConn = new SqlConnection(CONNECTION_STRING);
try
{
  nwConn.Open();
  SqlCommand cmd = new SqlCommand();
  cmd.CommandText = "CustOrderHist";
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Connection = nwConn;
  SqlParameter param = cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5);
  param.Value = "ANATR";
  SqlDataReader reader = cmd.ExecuteReader();
  try
  {
    OutputData(reader);
  }
  finally
  {
    reader.Close();
  }
}
finally
{
  nwConn.Close();
}

The Data Access Application Block is designed to relieve most of this tedium. By providing a small set of overloaded methods with a standard interface, the DAAB lets you duplicate the above functionality in just a few lines of code, like this:

SqlDataReader reader2 = SqlHelper.ExecuteReader(CONNECTION_STRING, "CustOrderHist", "ANATR"); 
try 
{
  OutputData(reader2);
}
finally
{
  reader2.Close();
}

DAAB handles all the bookkeeping work: creating and opening the connection, constructing the Command object, calling the appropriate ADO.NET methods, and cleaning up. On the face of it this doesn't look terribly efficient, and it wouldn't be if you were making multiple calls to the database and creating a new connection each time. The DAAB provides overloaded methods that allow you to pass an SqlConnection parameter rather than a connection string. These methods assume that your code--external to the DAAB--will manage the connection. In that case, the DAAB is just as efficient as any data access you are likely to code yourself.

In addition, the DAAB is tested and working code that handles exceptions and resource cleanup correctly. Finally, it's free, which is always a good thing.

What's in the DAAB?

The DAAB consists of two helper classes: SqlHelper and SqlHelperParameterCache. Both are sealed (can’t be inherited) classes with private constructors so that they can’t be instantiated. They’re wrappers for static methods.

SqlHelper contains methods for most common data access requirements.

SqlHelperParameterCache provides stored procedure parameter type caching to optimize access to stored procedures.

Most of your interaction with the DAAB will be through the SqlHelper class. SqlHelperParameterCache is used internally by SqlHelper and is not normally called directly by application programs. In rare situations, applications that need to cache parameters directly can do so.

The SqlHelper Interface

SqlHelper is a sealed class (can't be inherited) with a private constructor so that it can't be instantiated. The entire interface to SqlHelper is through static member functions. Those functions provide 100% of the data access needs of most applications. Although there are many overloaded methods in the interface, conceptually you can think of the interface as containing eight methods:

• ExecuteNonQuery Executes a SQL command that doesn't return a result set. This could be a stored procedure that doesn't return anything, an INSERT, UPDATE, or DELETE, or even a table modification command.

• ExecuteDataset executes a SQL command that returns results in a Dataset. Call this method to execute any SQL SELECT statement, or stored procedures that return data.

• ExecuteScalar executes a SQL command that returns a single scalar value, for example "SELECT COUNT(*) FROM TABLE_NAME".

• FillDataset is similar to ExecuteDataset, but rather than returning a newly-created DataSet object instance, this populates a passed DataSet object with the returned data. This can be used to add data to an existing DataSet.

• ExecuteReader returns a SqlDataReader object--a read-only, forward-only view of data. This is an incredibly efficient way to rip through a result set. If all you want is to go through returned data (i.e. to list or maybe sum things or look for a particular item), it is much more efficient to use a SqlDataReader than to get a DataSet.

• ExecuteXmlReader returns an XmlDataReader object--a non-cached, forward-only, read-only view of XML data. XmlReader is very efficient when you just want to read the data once, maybe searching for something or converting XML into a different format.

• UpdateDataset is used to update the changed rows in a DataSet. The idea here is that you obtain a DataSet from one of the query commands, make changes (add, delete, or modify rows) to the DataSet, and then pass the modified Dataset to UpdateDataset. This method then performs the Insert, Update, and Delete commands necessary. You supply the insert, update, and delete commands (typically stored procedures) as parameters to this method and the internal .NET machinery takes care of making the required changes to the database.

• CreateCommand simplifies creation of SqlCommand objects, which typically are used by the UpdateDataset method.

The first six methods in this list have multiple overloads that allow you a lot of flexibility in how the command is executed. For example, the ExecuteNonQuery method has the following overloads:

int ExecuteNonQuery (ConnectionString, CommandType, CommandText)
int ExecuteNonQuery (ConnectionString, CommandType, CommandText, Parameters)
int ExecuteNonQuery (ConnectionString, spName, Parameters)
int ExecuteNonQuery (SqlConnection, CommandType, CommandText)
int ExecuteNonQuery (SqlConnection, CommandType, CommandText, Parameters)
int ExecuteNonQuery (SqlConnection, spName, Parameters)
int ExecuteNonQuery (SqlTransaction, CommandType, CommandText)
int ExecuteNonQuery (SqlTransaction, CommandType, CommandText, Parameters)
int ExecuteNonQuery (SqlTransaction, spName, Parameters)

These overloads allow you to supply the connection as a connection string, a SqlConnection object, or as a SqlTransaction object. The command can be a stored procedure or an ad-hoc SQL query, with or without parameters. All of these overloads exist for each of the six functions shown in the previous list, except for ExecuteXmlReader, which doesn’t support the ConnectionString overloads.

SqlHelper has corresponding methods for working with typed DataSets. These methods have the same names and include a "TypedParams" suffix. The methods that work with typed DataSets are:

ExecuteNonQueryTypedParams
ExecuteDatasetTypedParams
ExecuteScalarTypedParams
ExecuteReaderTypedParams
ExecuteXmlReaderTypedParams

These methods have the same overloads described above. The only real difference between the TypedParams versions and the normal versions is the use of typed Datasets. The general API remains the same.

If you're at all familiar with ADO.NET, you'll see that the API here does almost everything that you normally do with ADO.NET. It's just wrapped up in a pretty little API package that's much easier to work with.

SqlHelperParameterCache

Many of the SqlHelper methods query the database to discover stored procedure parameters. This is a huge benefit in that it prevents you from having to write code that creates SqlParameter arrays, but it's woefully inefficient if you do it for every stored procedure call. The SqlHelperParameterCache is a way to provide the convenience of parameter discovery while minimizing the cost. When you call one of the methods that does parameter discovery, SqlHelper checks the parameter cache to see if there has already been a call to that particular stored procedure on the supplied connection or connection string. If so, the parameter types are retrieved from the in-memory cache. If not, the SqlHelperParameterCache object discovers the parameters and then caches them. The result is that the first call to a given stored procedure incurs the cost of discovering the parameters, but subsequent calls get them from the cache, almost free. The cost of getting the parameters from the cache is less than creating the parameter array yourself for each call. The initial hit is expensive, but amortized over the application's lifetime, it's not expensive at all.

Most of the time you don’t even have to think about the SqlHelperParameterCache. The SqlHelper methods use it automatically and unless you’re doing something slightly odd, you won’t have to know that the cache is there.

There are some circumstances where it's not possible for SqlHelperParameterCache to discover the stored procedure parameters. In particular, it's not possible when you're calling system stored procedures (such as sp_rename) because those stored procedures are part of the Master database, and there's no reasonable way for the DAAB to locate the stored procedure there and obtain the parameter types. In these cases, your best bet is to create a parameter array and then call SqlHelperParameterCache.CacheParameterSet for the particular stored procedure you will be calling. You only need to do this once--before the first time you call the stored procedure. SqlHelper will be able to get the parameter type information from the cache on subsequent calls.

Using the DAAB

The DAAB is available free for the download from Microsoft’s . Once you get it, run the install package, which will install the DAAB on your system in the C:\Program Files\Microsoft Application Blocks directory.

To use it in your project, I strongly recommend that you copy the C# (or Visual Basic if that’s your language of choice) project to your solution directory and check it into source control with your solution. I recommend this so that your source control will contain the version of the DAAB that you used to create your project. It’s tempting to say "I’ll just get the latest version," but there lies DLL Hell. You know as well as I do that "backward compatibility" is often a pipe dream. Copy the thing to your solution and check it in.

Once you’ve added the DAAB project to your solution, compile the project so that Visual Studio knows it’s there. Then, in the project that’s going to use the application block, right click on References and select "Add Reference". Click on the "Projects" tab and select the DAAB. Doing this will maintain the dependencies and ensure that the DAAB is compiled before any project that attempts to use it.

I've developed a few rules for using the DAAB. These rules aren't set in stone, but they're very strong guidelines that have helped prevent a lot of problems over the time I've been using it.

Rule number one: Don't modify the application block source code. This rule is almost unbreakable. You should treat the DAAB as you would any part of the .NET Framework: as a pre-compiled tool that can't be modified. If you do modify the DAAB, you will likely run into one or more of the following problems:

• The resulting code will not be in sync with the documentation, unless you take the time to update the documentation.

• The architects and coders of the DAAB took great pains to ensure that the DAAB works optimally with the .NET Framework. You must take similar care with any modifications. This includes correctly handling exceptions and unmanaged resources.

• The code will not be as widely or as rigorously tested as the original DAAB.

• Upgrading to a new version of the DAAB will require that you duplicate the modifications in the new version.

Rule two: Always use a wrapper class. You shouldn't use in-line data access code in your application's main logic. That is, rather than writing:

  // set up to call SqlHelper
  // setup code here
  // now make the call
  SqlHelper.ExecuteDataset(...)

you should instead put the setup code and call to the SqlHelper in a separate method that has a meaningful name, and call that method from your program's main logic. For example:

  DataSet customerOrders = MyDataAccess.GetCustomerOrders();

My reasoning here is mostly a maintenance concern, and is valid for whatever data access method you choose. If you code the data access inline, then any other program that needs the same data will have to duplicate the code. But if you encapsulate your program's data access so that it is returning application-specific data, then it's trivial to move that data access to a tiered design or use the data access assembly in a standalone application. You have more flexibility, less duplication, and easier maintenance. The last is very important if you decide to change the low-level data access. By encapsulating the data access into a single assembly that contains just a handful of classes, you make it easier to replace the DAAB with something else.

Rule three: No ad-hoc SQL. Use only stored procedures. This applies to any data access application, regardless of your low-level framework. Applications that use ad-hoc SQL expose the SQL server to huge security risks. This is especially true if you're passing those queries to a middle tier that then calls into the database. Not only are stored procedures more secure because the DBA can prevent ad-hoc SQL calls from being accepted, stored procedures are more efficient because the SQL server pre-compiles and caches the query plans, meaning that there is no per-query compilation overhead. It's much easier to secure and optimize a handful of stored procedures than a whole bunch of ad-hoc queries.

Rule four: Use as intended. Rule four: Don't automatically assume that, because DAAB is designed to work with DataSet objects rather than DataTable, it is inherently inefficient or will require a lot more data to be pushed over the line. I will grant that there is some overhead when working with DataSet, but that is relatively minor when compared to the typical amount of data that will be returned in a query. It is folly to spend your time writing a DataTable-based (or other thing based) data access layer just to save a few percent of data transmission. By moving away from DAAB, you're losing the benefits of DataSets (especially typed DataSets), including much of the data-bound control functionality that's built into Windows Forms Controls and Web Forms Controls. You end up saving a little bit of data overhead, but incurring lots of time in your schedule to write and debug a data access layer, and create code that converts your data access types (whatever they are) to Datasets. It makes no sense.

DAAB Drawbacks

The only major drawback to using the DAAB is that it currently supports SQL Server only. As of yet, there is no DAAB for DB2, Oracle, Microsoft Access, or any other database. Microsoft is currently working on the next version of the Data Access Application Block, which will support those and other database systems.

The Data Access Application Block is much easier to work with and more robust than rolling your own data access. Developers throughout the .NET community have used it on with great success on many projects of all sizes. The API is clean, the code robust and efficient, and thousands of users have tested it "under fire." It is the recommended way to include data access in your .NET programs.