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!
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:
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.
