Tip

The Data Access Application Block

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. Find out more about it in this short excerpt from

    Requires Free Membership to View

Informit.

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 a 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.


Read more about these DAAB classes at Informit.


This was first published in November 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.