Streamlining Your Data Access Layer with Helper MethodsBy Scott Mitchell
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
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
NULLfor 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
NULLvalue 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
NULLif 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
NULL-able Types - when working with data from a database that can be
NULL, the issue of "how do I programmatically represent a
NULLvalue 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#.)
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:
Of course you would need to repeat this method for each of the
SqlHelper's methods -
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
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
In Part 2 we'll continue our look at these streamlining helper methods, seeing
how to tersely work with