The Template Method Pattern is a design pattern designed to facilitate centralization of the skeleton of an algorithm, while providing well-defined places for the behavior of the algorithm to vary. I wrote about this topic years ago, showcasing a class that encapsulated the Microsoft recommended usage pattern for a DbDataReader, using events to provide the custom behavior. I’m going to revisit the same class today to show the same template method, but using generic delegates.

Everyone who’s used a DbDataReader to access a data source is familiar with the recommended usage pattern. Here is my rendering of it:

using (SqlConnection connection = new SqlConnection(_connectionString))
{
    using (SqlCommand command = new SqlCommand(_commandText, connection))
    {
        command.CommandType = _commandType;
        command.Parameters.AddRange(parameters);
        connection.Open();
        try
        {
            SqlDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    // Do stuff
                }
            }
            finally
            {
                reader.Close();
            }
        }
        finally
        {
            connection.Close();
        }
    }
}

All those lines are necessary just to get the connection, command, and reader set up. So what can you do to reuse this pattern? You could copy and paste it every time, but that will cause a lot of duplicate code and interfere with readability. You could write a function that just returns the reader and trusts the caller to close the reader and connection, but that requires some responsibility on the part of the caller. Last time I wrote about this, I offered another solution, which was to put a delegate on that Do Stuff line, and then wire up a handler. This time, I will show the same solution, only using a generic delegate that requires even less setup. Here is the code:

class SqlDataReaderHelper
{
    string _connectionString;
    CommandType _commandType;
    string _commandText;

    public SqlDataReaderHelper(string connectionString, CommandType commandType, string commandText)
    {
        _connectionString = connectionString;
        _commandType = commandType;
        _commandText = commandText;
    }

    public void ExecuteReader(Action<SqlDataReader> onRead, params SqlParameter[] parameters)
    {
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            using (SqlCommand command = new SqlCommand(_commandText, connection))
            {
                command.CommandType = _commandType;
                command.Parameters.AddRange(parameters);
                connection.Open();
                try
                {
                    SqlDataReader reader = command.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            if (onRead != null)
                                onRead(reader);
                        }
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}

And to illustrate how easy it is to use, here is an example program:

static void Main(string[] args)
{
    SqlDataReaderHelper helper = new SqlDataReaderHelper(
        "Server=(local);Database=Adventureworks;Integrated Security=SSPI",
        CommandType.Text,
        "select Name from Production.Product where Name like @Name");
    Console.WriteLine("Listing products beginning with B");
    helper.ExecuteReader((dr) => Console.WriteLine(dr["Name"]), new SqlParameter("@Name", "B%"));
    Console.WriteLine("Listing products beginning with C");
    helper.ExecuteReader((dr) => Console.WriteLine(dr["Name"]), new SqlParameter("@Name", "C%"));
                        
    Console.ReadLine();
}

This implementation of the DataReader template method has a few small advantages over the previous version, which utilized events where actions are now used. First of all, the helper class definition is now more compact, because there is no need to declare an event handler field. Secondly, there is also no need to create a custom EventArgs subclass to pass the SqlDataReader with. The final advantage, which is the ease with which custom behavior can be passed as a parameter by using lambda expressions, is one that is not unique to this implementation, but it does demonstrate how expressive lambda expressions can be.

In summary, the Template Method pattern is a great way to encapsulate and reuse best practices where the custom behavior is intermingled with the reusable algorithm. While the Template Method pattern has always been possible to implement by using inheritance or events, C# lambdas and generic delegates make the implementation of a template method even easier.