‘SQL Query’ Micro Design Pattern

There are a few programming practices, that I like to call Micro Design Patterns, that I use over and over again on various software projects I work on. One pattern that I call SQL Query, and use quite frequently when I need to write ad-hoc SQL queries for use within a Data Access Layer. This pattern lends itself to easier testability and a cleaner separation of concerns.

What is the ‘SQL Query’ Micro Pattern?

Basically, the SQL Query micro pattern involves using a Factory Method to return an object that contains all the necessary data for performing a SQL query (the full SQL code and a collection of all the parameters and their values.)
Here’s an extremely simple example of the Factory Method (this one is implemented as Static for simplicity) and a necessary SQLQuery object:

public static class SQLGenerator
{
    public static SQLQuery GetPersonQuery(int id)
    {
        return new SQLQuery {
            SQL = @"SELECT * FROM [Person] WHERE [ID] = @ID",
            Parameters = new Dictionary<string, object> {
                { "@ID", id }
            }
        };
    }
}

public class SQLQuery
{
    public string SQL { get; set; }
    public IDictionary<string, object> Parameters { get; set; }
}

And, here’s an example using it:

var query = SQLGenerator.GetPersonQuery(4);

// SQL will equal 'SELECT * FROM [Person] WHERE [ID] = @ID'
// Parameters will contain a single entry with the key of '@ID'
// and the value of 4

Why / When to use the pattern?

Basically, when ever you are writing your own ad-hoc SQL queries or building them dynamically within code (instead of using an ORM like Entity Framework or LINQ to SQL) then the SQL Query pattern comes in handy.

Personally, I have encountered quite a few scenarios where it was necessary to drop down to using straight ad-hoc SQL queries against the database for performance reasons. This is where it became necessary for me to come up with this micro pattern.

Easily Testability and Separation of Concerns

The very nature of breaking out your SQL query generation code to a separate method or class of methods increases the overall separation of concerns within your code.

Think about how you would write unit tests for the following fairly traditional example:

var personID = 4;
using (var con = new SqlConnection(connectionString))
{
    using(var cmd = new SqlCommand(
        "SELECT * FROM [Person] WHERE ID = @ID", con))
    {
        cms.Parameters.AddWithValue("@ID", personId);

        con.Open();

        var reader = cmd.ExecuteReader();

        // Do something with the reader
    }
}

You really wouldn’t be able to write any unit tests for this code; unless you are willing to write a full integration test against the database which is not best practice just to test whether the correct ad-hoc SQL query is generated by the code.

Now look at it refactored to use the SQL Query micro pattern:

var personID = 4;

// use SQL Query pattern
var query = SQLGenerator.GetPersonQuery(personID);

using (var con = new SqlConnection(connectionString))
{
    using (var cmd = new SqlCommand(query.SQL, con))
    {
        // Add query parameters to SqlCommand
        foreach(var item in query.Parameters)
        {
            cmd.Parameters.AddWithValue(item.Key, item.Value);
        }

        con.Open();

        var reader = cmd.ExecuteReader();

        // Do something with the reader
    }
}

The overall code isn’t much different, but now the generation of the ad-hoc SQL query and its necessary parameters is separated out into a method call that is easily testable.

Using the SQL Query pattern you can write unit tests to verify that the correct ad-hoc SQL query and necessary parameters are being generated by the factory method (the GetPersonQuery method in the above example.)

Conclusion

As you can see, the ‘SQL Query’ micro pattern is rather simplistic, but it works to serve a few very important tenets; including: easier testability, increased separation of concerns, cleaner code and increased reusability.

The examples shown in this article are really simple for the purpose of making it easy to learn the pattern. The biggest benefits of using this pattern come into play when you have much more complex ad-hoc SQL query generation that might involve many conditional elements for building WHERE, JOIN, SELECT and ORDER BY clauses. In these cases it becomes even more of a necessity to unit test the SQL generation code.