Streamlining Your Data Access Layer with Helper Methods

By Scott Mitchell

Introduction

A well-architected ASP.NET application provides definitive boundaries between the application's presentation logic, business logic, and data access code. That is, your ASP.NET web pages should consist solely of markup and logic for displaying data - the specific business rules and code for accessing and modifying the application's underlying data should both be implemented in a separate class libraries.

While there are different models for defining how these different tiers interact and collaborate with one another, the simple fact remains that, at minimum, presentation, business logic, and data access should be considered three separate tasks that are delegated each to their own components in the system. More tersely, your ASP.NET web pages shouldn't have data access code in them - no code to connect to a database, no SQL connections, nothing. Instead, this data access-specific code should be placed in the Data Access Layer (DAL), a separate class library that is designed to do nothing more than act as the relay between the application and the backend data store. (For more information and background on data access layers be sure to read Wayne Plourde's article Creating a Data Access Layer in .NET.)

Since the data access layer does, fundamentally, one thing - communicating with the database - the code written in this layer can quickly become repetitive. Anytime you find yourself repeating very similar blocks of code in a project you should think encapsulation, and search for a way to group the related functionality into a general 'black box' that can then be used. By encapsulating common functionality you'll reduce the number of lines of code in your DAL, thereby making it more readable, maintainable, and updateable. Additionally, code reuse also leads to fewer bugs since there is less chance for typos (which can be common when cutting and pasting large code blocks time and again) and reuse allows for a single block of code to be used more often in more situations, thereby increasing our certainty that said code works correctly.

This article focuses on how to streamline the data access layer through encapsulating common functionality with the aid of simple helper methods. The helper methods we'll examine in this article are ones that I have created and used in many of my consulting projects over the past several years, and have helped me to greatly trim down my data access layers. Read on to learn more!

- continued -

First Things First - The Data Access Application Block

The first and most important step to streamlining your data access layer is to use Microsoft's freely available, open-source Data Access Application Block (DAAB). The DAAB is a library that crunches down the common data access code:

Create a connection,
Specify a command,
Execute the query

from eight to ten lines of code down into one to three lines of code. There are two major different flavors of the Data Access Application Block. The first flavor, which encompassed versions 1 through 3 of the DAAB, shipped as a single project. A past 4Guys article, Examining the Data Access Application Block, looks at how to use the DAAB. The second flavor was released in January 2005 as the Enterprise Library, a larger package that included not only the Data Access Application Block but a number of other commonly used application blocks. The Enterprise Library is more trying to setup than the previous versions of the DAAB, but it does offer additional features, such as health monitoring, unit tests, and so on. For more information on working with the Enterprise Library flavor of the DAAB be sure to check out Working with the Enterprise Library's Data Access Application Block.

Additional Opportunities for Streamlining

While the DAAB helps remove the most tedious and oft-repeated code, its overall simplicity still places some burden on the developer for writing code to handle more minute details. While implementing these minute details may not require too much additional code on the behalf of the developer, having to implement them again and again in multiple data access layer methods can quickly bring back the horrid memories of code repetition that the DAAB had so kindly removed. In my projects I've find myself doing the following non-DAAB supported tasks often enough that I've invested the time in creating a helper method to encapsulate the functionality:

Not Having to Provide the Connection String Every Time - in the pre-Enterprise Library versions of the Data Access Application Block (specifically versions 1 and 2), you had to provide the connection string to the database each and every time you invoked one of the DAAB's methods. This quickly proved annoying enough that I created a helper method that I could invoke that would supply the connection string on my behalf.
Working with NULL-able Parameters - when calling a stored procedure and passing in input parameters I might want to pass in a value of NULL for the parameter based on the value of the parameter. For example, imagine that I prompt the user to provide their name and address in a Web Form, and the address is optional. If the user does not provide her address I may want to store a NULL value in the corresponding database field(s) rather than storing blank strings. Hence, when calling my stored procedure from the data access layer, I want to set the parameter's value to NULL if the user-supplied value is an empty string.
Retrieving Typed Scalar Values - the DAAB has a bevy of ExecuteScalar() overloads that return a scalar value from a database result (namely the first field of the first record in the resultset). However, ExecuteScalar() returns a type of Object. Many times I know I am getting back an integer or string or decimal, or some other specific type. To handle this, I created a number of ExecuteType() helper methods.
Feigning NULL-able Types - when working with data from a database that can be NULL, the issue of "how do I programmatically represent a NULL value integer?" crops up. This issue is solved with .NET 2.0's concept of NULL-able types, but if you are still living in the 1.x world, you need to 'fake' NULL-able types. I have a slew of helper methods to assist with this. (For more on nullable types in .NET 2.0 see Nullable Types in C#.)

When creating my helper methods I added another class to the data access layer class library. This additional class file, which I typically call DALHelpers, has a slew of static, public methods. Let's examine these helper methods for each of these four situations.

Not Having to Include the Connection String in Each Call

In versions 1 and 2 of the Data Access Application Block you have to pass in the database connection string each time you invoke a method that retrieves or modifies database data. The following sample DAAB version 2.0 call illustrates this:

SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "UpdateCustomer", parameters)

To circumvent having to enter the connection string each time I simply added the following helper method:

Public Shared Sub ExecuteNonQuery(ByVal sprocName As String, _ ByVal ParamArray commandParameters() As SqlParameter) SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, _ sprocName, commandParameters) End Sub

Of course you would need to repeat this method for each of the SqlHelper's methods - ExecuteReader(), ExecuteScalar(), and so on. Also note that I am assuming that all calls coming through the DAAB are stored procedures (as I have the command type hard-coded to CommandType.StoredProcedure). The ParamArray commandParameters() input parameter allows the developer using the helper method to pass in an arbitrary number of SqlParameter instances, just like with the DAAB.

In closing, be sure that you read the connection string is read from the Web.config file or some other configuration location. You should never hard-code the connection string in the DAAB calls since if the connection string changes you'll need to update potentially oodles of code. For more information on storing the connection string details in Web.config refer to Specifying Configuration Settings in Web.config.

In Part 2 we'll continue our look at these streamlining helper methods, seeing how to tersely work with NULL-able parameters.

Read Part 2!