Click here to Skip to main content
13,299,298 members (62,852 online)
Click here to Skip to main content
Add your own
alternative version


20 bookmarked
Posted 15 Oct 2009

DRY Database Interactions (.NET 2.0)

, 17 Oct 2009
Rate this:
Please Sign up or sign in to vote.
Don't Repeat Yourself (DRY) when working with your database. Simple and easy code that makes it harder to make common mistakes.


DRY Programming

"DRY" programming can be nicely summed up as "Don't Repeat Yourself". Another, more precise, short description comes from The Pragmatic Programmer as "Every piece of knowledge must have a single, unambiguous, authoritative representation within a system."

Here I'm going to "DRY up" some common code and share a simple, low cost way of making simple database interactions cleaner, easier, and less error prone.

Update - 16 Oct 2009

Astute readers noted that the solution in this article was similar to early versions of Microsoft's Database Access Application Block. Having never investigated it before, I checked it out. I was quite surprised to find that I had come to the same conclusions and solutions they provided. This was both good and bad news for me. I was happy to see that I had independently come to the same solutions as the "best practices" project provided. But I was disappointed to learn that my work was a duplication of effort.

For applications on the 3.5 Framework and later, I recommend using the Patters & Practices - Enterprise Library's Data Access Application Block. The solution in this article was created for an application stuck in the .NET 2.0 Framework. It is essentially a "lite" version of the Microsoft DAAB v2.

Problem Background - Constraints and Goals

It is worth mentioning that solutions rarely exist without constraints. This solution was designed to work in a C# Web Service or web application. While flexible, it assumes that the connection string is stored in either an App.config or Web.config. It also assumes that the connection string will define connection pooling so there is little cost to creating and closing database connections. This means when a connection is closed, it is not actually closed; it is returned to the .NET managed connection pool and is available for the next connection request.

I also want to mention that generally, I prefer using a good ORM (Object Relational Model) framework for interacting with my data. However, this code was created for an existing small project where introducing an ORM would be overkill.

With all that said, let's get started.

Standard - Boilerplate Database Code

You find code like the following everywhere. It is correct and works perfectly. The issue is, only about 4 or 5 lines of code actually deal with solving our business need. The rest is pretty boilerplate. Take a look:

// conn and reader declared outside try
// block for visibility in finally block
SqlConnection conn   = null;
SqlDataReader reader = null;

string inputCity = "London";
    // instantiate and open connection
    conn =  new SqlConnection("Server=(local);DataBase=Northwind;" + 
                              "Integrated Security=SSPI");

    // Declare command object with parameter
    SqlCommand cmd = 
      new SqlCommand("select * from Customers where city = @City", conn);

    // Add new parameter to command object
    cmd.Parameters.AddWithValue("@City", inputCity);

    // Get data reader
    reader = cmd.ExecuteReader();

    // write each record
        Console.WriteLine("{0}, {1}", 
          reader["CompanyName"], reader["ContactName"]);
    // close reader
    if (reader != null)

    // close connection
    if (conn != null)

If this was for a one-time solution, it would be fine. However, when the project repeats this type of code multiple times, it becomes wasteful and a hazard. How is it a hazard? What happens if someone misses one of those Close() statements? What if they forget to check if the variable is assigned? What if they don't use a try..finally block? The point is, all the "boilerplate code" is still important and must be done correctly to prevent problems.

What if we could remove most of the boilerplate code and still ensure our cleanup couldn't be forgotten or done incorrectly? What if it also meant we don't have to write so much code to solve a business problem?

DRY Solution

Let's jump right in and see what our equivalent DRY code looks like:

string inputCity = "London";

// Track our parameter
CommandParams insertParams = new CommandParams("@City", inputCity);

// Declare the query and execute
using (SqlDataReader reader = 
       "select * from Customers where city = @City", insertParams))
    // write each record
        Console.WriteLine("{0}, {1}", 
          reader["CompanyName"], reader["ContactName"]);
} // returned reader is cleaned up.

We've removed the boilerplate code and we're left with the important code that solves our business problem. All the important things are still being done, only now the important boilerplate code can't get messed up!

Note: CommandParams is a generic list of SqlParameter objects. We want it because it lets us cleanly separate the parameters from both the SqlConnection and SqlCommand objects. It also has some helpful overloaded constructors to make using it easier.

Stored Procedures

Calling a Stored Procedure is equally simple:

CommandParams paramValues = new CommandParams();
paramValues.Add("@CityName", city);
paramValues.Add("@State", state);
using (SqlDataReader reader = SqlClientHelper.ExecuteReader(
        "My_Stored_Procedure", paramValues, CommandType.StoredProcedure))
    // if there is anything to read...
    if (reader.Read())
        // Take some action on the stored procedure results
}// connection will close and dispose here

In the Stored Procedure example, we call ExecuteReader directly and provide the CommandType. Also, in this example, we aren't expecting a set of rows, just one row. So, we can take some action if the Read() succeeds.

Where the Real Work Happens

All the real work and main savings is done in the ExecuteReader method:

public static SqlDataReader ExecuteReader(string commandText, 
              CommandParams paramValues, CommandType cmdType)
    SqlConnection connection = null;
    SqlDataReader dataReader = null;
    SqlCommand command = null;
        connection = new SqlConnection(GetConnectionString());
        command = new SqlCommand(commandText, connection);
        command.CommandType = cmdType;
        // Add the given params (if any) to the command object
        // Open the DB connection.
        // After executing the command, immediately close the connection.
        // This helps to ensure that connections are not unintentionally left open.
        dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
    catch (SqlException se)
        if (dataReader != null)
        // If errors occur, ensure connection is closed.
        if (connection != null)
        throw se;
    return dataReader;

Do you recognize a lot of the boilerplate code? One other special note is the CommandBehavior.CloseConnection usage. This means when our returned reader is closed, it will automatically close the associated database connection. That alone is handy. Now, when coupled with a using() block, it happens automatically for us!

using (SqlDataReader reader = SqlClientHelper.ExecuteReaderSelect(
       "select * from Customers where city = @City", 
       new CommandParams("@City", cityName)))
    // some work
} // returned reader is cleaned up.

When execution reaches the closing brace of the using block, the reader's Dispose method is automatically called. This will close both the reader's connection and the associated SqlConnection too because of the CommandBehavior. We just made it simple for ourselves, and others who will maintain our code, to "do the right thing". This also means we just made it much harder to make a mistake with the code.

You may also have noticed the GetConnectionString() call used when creating the SqlConnection object. This was created assuming the connection string could be pulled from either an App.config or Web.config file. If you have other needs, it can be set explicitly using the AssignConnectionSting() method.

Closing Thoughts

The code in the article and the attached file were helpful to me with DRY-ing up common database code. It helped me to make it easier to always do the right thing and harder to mess it up. This benefit is shared with junior developers who come after me and work on my code.

Attached Source File

The attached source file is designed to be easily portable and adaptable to other projects and other developers' needs. Feel free to modify it as you see fit.

Also note that there are other database helping routines included in the source file. Check them out and see if they might help you.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Mark Ericksen
Technical Lead
United States United States
Mark Ericksen is a Senior Developer, Technical Lead, Architect and more. He is passionate about technology, photography, and continually learning.

You may also be interested in...

Comments and Discussions

QuestionHow to close a Connection object automatically when we close the associated Reader object Pin
elizas19-Apr-10 0:59
groupelizas19-Apr-10 0:59 
AnswerRe: How to close a Connection object automatically when we close the associated Reader object Pin
Mark Ericksen19-Apr-10 6:55
memberMark Ericksen19-Apr-10 6:55 
GeneralPerhaps a small improvement Pin
Ajek20-Oct-09 9:23
memberAjek20-Oct-09 9:23 
GeneralRe: Perhaps a small improvement Pin
markeric21-Oct-09 12:09
membermarkeric21-Oct-09 12:09 
QuestionWhat about data set? Pin
mike_hac18-Oct-09 8:56
membermike_hac18-Oct-09 8:56 
AnswerRe: What about data set? Pin
markeric18-Oct-09 9:19
membermarkeric18-Oct-09 9:19 
GeneralMy vote of 2 Pin
shatl16-Oct-09 1:22
membershatl16-Oct-09 1:22 
QuestionData Access Application Block? Pin
BigJim6115-Oct-09 15:40
memberBigJim6115-Oct-09 15:40 
AnswerRe: Data Access Application Block? Pin
markeric15-Oct-09 18:05
membermarkeric15-Oct-09 18:05 
GeneralRe: Data Access Application Block? Pin
digital man15-Oct-09 22:31
memberdigital man15-Oct-09 22:31 
I would tend to agree with you: I have been using a similar solution to yours for a number of years where using ORM would add an unnecessary layer of complexity given the scope of the solution. I also found that, as good as they were, the Microsoft Data Access Application Block was a little too 'fat' for what it actually delivers.

I would also say that, for smaller solutions, I now use Linq-to-Sql and have abandoned what was a very practical and light-weight method of interacting with data.

GeneralRe: Data Access Application Block? Pin
markeric16-Oct-09 7:19
membermarkeric16-Oct-09 7:19 
QuestionORM? Pin
KinStephen15-Oct-09 14:03
memberKinStephen15-Oct-09 14:03 
AnswerRe: ORM? Pin
markeric15-Oct-09 17:14
membermarkeric15-Oct-09 17:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171207.1 | Last Updated 17 Oct 2009
Article Copyright 2009 by Mark Ericksen
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid