DRY Database Interactions (.NET 2.0)

• Download source code - 3.45 KB

Introduction

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";
try
{
    // instantiate and open connection
    conn =  new SqlConnection("Server=(local);DataBase=Northwind;" + 
                              "Integrated Security=SSPI");
    conn.Open();

    // 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
    while(reader.Read())
    {
        Console.WriteLine("{0}, {1}", 
          reader["CompanyName"], reader["ContactName"]);
    }
}
finally
{
    // close reader
    if (reader != null)
    {
        reader.Close();
    }

    // close connection
    if (conn != null)
    {
        conn.Close();
    }
}

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 = 
       SqlClientHelper.ExecuteReaderSelect(
       "select * from Customers where city = @City", insertParams))
{
    // write each record
    while(reader.Read())
    {
        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;
    try
    {
        connection = new SqlConnection(GetConnectionString());
        command = new SqlCommand(commandText, connection);
        command.CommandType = cmdType;
        // Add the given params (if any) to the command object
        command.Parameters.AddRange(paramValues.ToArray());
        // Open the DB connection.
        connection.Open();
        // 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)
            dataReader.Dispose();
        // If errors occur, ensure connection is closed.
        if (connection != null)
            connection.Close();
        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.