I hate writing the same code over and over again. Whenever I do, ennui begins to set in and even writing the same five lines of code again suddenly becomes unbearable. That’s usually a good time to refactor the duplicate code into a method or utility class. I recently found myself writing some code like this that defied the usual Extract Method refactoring: code that created and opened a connection, created a command, executed a reader, iterated through the reader’s results, and did something on every record. Read on to see how to refactor this code into its own reusable class by applying the Template Method Design Pattern...

First let’s take a look at the shape of the code I was writing to examine why Extract Method wasn’t an appropriate refactoring. The code looked something like this:

using ( SqlConnection connection = new SqlConnection() )
{
  using ( SqlCommand command = new SqlCommand() )
  {
    using ( SqlDataReader reader = command.ExecuteReader() )
    {
      while ( reader.Read() )
      {
        // Do something
      }
    }
  }
}

All I really wanted to do was get at that SqlDataReader and process its results. However, I couldn’t just define a method that returned the SqlDataReader from the innermost using statement, because once the using statements are exited, the objects are disposed, so I would be left with a reference to a SqlDataReader with no open SqlConnection. However, there is a design pattern that will solve this problem: the Template Method pattern.

Strictly defined, the Template Method is a pattern whose intent is “Define the skeleton of an algorithm in an operation, deferring some steps to subclasses.” (Gamma, Erich, Richard Helm, Ralph Johnson, and John Vlissides. Design Patterns: Elements of Reusable Object-Oriented Software.) Let’s take that basic idea of defining the skeleton of an algorithm and deferring some steps, and apply it to this problem.

First of all, when I execute code like this, it’s usually a one-time kind of thing, so I don’t really want to have to define a new subclass every time I want to use my new Template Method implementation. Instead, I’m going to define a class that offers events that can be handled to provide the same kind of functionality.

The heart of my new Template Method implementation is the method that defines the skeleton that I keep finding myself writing, and all other design decisions will flow from it. In order for the method to be flexible, I’ll need to be able to pass a ConnectionString, some CommandText, a CommandType, a flexible number of parameters, and maybe a CommandBehavior. That’s a mouthful, and yet I want to keep the actual method signature as small as possible. Since it’s more common to execute multiple commands against the same connection than it is to execute the same command against multiple connections, I decided to make ConnectionString a property of the class rather than a parameter of the method. I’ve observed that the CommandBehavior parameter of SqlCommand.ExecuteReader() is often either omitted or fairly constant; I almost always use CommandBehavior.SingleResult. At any rate, I think that this is another parameter that is better left as a property of the class rather than part of the method signature. That leaves me with CommandText, CommandType, and SqlParameters as my method parameters.

The whole point of this Template Method was to provide a simple way to inject specific logic into the skeleton of some standard data-access logic. I chose to do this with events so I wouldn’t have to define subclasses every time I wanted different logic. In my experience, the two most common places that I do stuff with a SqlDataReader is on each record (processing data), and after the DataReader is closed (reading output parameters). Since I want this new class to be flexible, I will also provide an event that is raised when the DataReader moves from one result to the next. I’ll define a catch-all event type that provides access to the SqlDataReader (for access to the data) and the SqlCommand (for access to the output parameters), and then define three events all of this type that get raised on Read, on NextResult, and on Close. Let’s take a look at the final result of all these decisions:

public int Execute( string commandText, CommandType commandType, params SqlParameter[] parameters )
{
  using ( SqlConnection connection = new SqlConnection( _ConnectionString ) )
  {
    using ( SqlCommand command = new SqlCommand( commandText, connection ) )
    {
      command.CommandType = commandType;
      AddParameters( command, parameters );
      connection.Open();
      using ( SqlDataReader reader = command.ExecuteReader( _Behavior ) )
      {
        ReaderCommandEventArgs args = new ReaderCommandEventArgs( command, reader );
        do
        {
          RaiseEvent( NextResult, args );
          while ( reader.Read() )
            RaiseEvent( Read, args );
        } while ( reader.NextResult() );
        reader.Close();
        RaiseEvent( Close, args );
        return reader.RecordsAffected;
      }
    }
  }
}

AddParameters() is a helper method that adds the parameters to the command, but doesn’t throw an exception if the array is null. RaiseEvent() is another helper method that raises assigned event handlers while skipping unassigned ones.

My new Template Method class is ready for use! I've decided to call it a ReaderCommand, since it invokes the ExecuteReader method of a SqlCommand. Let’s compare the old code with the equivalent code using my new class. Here’s the old code:

using ( SqlConnection connection = new SqlConnection( connectionString ) )
{
  using ( SqlCommand command = new SqlCommand( commandText, connection ) )
  {
    command.Parameters.AddWithValue( "@Param1", "abc" );
    command.Parameters.AddWithValue( "@Param2", 123 );
    connection.Open();
    using ( SqlDataReader reader = command.ExecuteReader( CommandBehavior.SingleResult ) )
    {
      while ( reader.Read() )
        Console.WriteLine( reader[ 0 ] );
    }
  }
}

It’s not that this code is so bad, but there is a lot of boilerplate code in there, with the important varying bits scattered throughout, and no less than four levels of indentation, with the most important logic at the center of it all. Here’s the new code:

ReaderCommand rc = new ReaderCommand( connectionString, CommandBehavior.SingleResult );
rc.Read += new ReaderCommandEventHandler( rc_Read );
rc.Execute( commandText, CommandType.Text, new SqlParameter( "@Param1", "abc" ), new SqlParameter( "@Param2", 123 ) );

static void rc_Read( object sender, ReaderCommandEventArgs e )
{
  Console.WriteLine( e.Reader[ 0 ] );
}

The old code was 14 lines long, with data access and program logic all jumbled together. The new code is 7 lines long (and 3 of those line were auto-generated by the IDE), with the program logic cleanly separated from the data access. The result is code that is easier to read and easier to write.

Today I’ve demonstrated how the Template Method design pattern can reduce redundant code and result in code that is easier to read and write. I hope that this example will inspire you to refactor your own redundant code into reusable classes. For more information on this topic, I highly recommend Head First Design Patterns by Freeman & Freeman as an excellent way to get started with design patterns, and Design Patterns by Erich Gamma, et al. as the definitive initial catalog.