Using extension methods to keep yourself DRY - Part 3

Hi there and welcome to part 3, the final in the series. It's time to put the icing on the cake to simplify our DAL code. In Part 2 we ended up with this code for our DAL:

public Person GetPerson(int id)
{
    using (var connection = new SqlConnection("SomeConnectionString"))
    {
        using (var command = new SqlCommand("dbo.PersonGetSingle", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@id", SqlDbType.Int).Value = id;

            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                return reader.ReadObject(this.ReadPerson);
            }
        }
    }
}

public List<Person> GetPersonsAll()
{
    using (var connection = new SqlConnection("SomeConnectionString"))
    {
        using (var command = new SqlCommand("dbo.PersonGetAll", connection))
        {
            command.CommandType = CommandType.StoredProcedure;

            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                return reader.ReadList(this.ReadPerson);
            }
        }
    }
}

private Person ReadPerson(DbDataReader reader)
{
    var person = new Person();
    person.Id = reader.GetInt32("id");
    person.Name = reader.GetString("name");
    person.DateOfBirth = reader.GetDateTime("dateOfBirth");
    person.EmailAddress = reader.GetString("emailAddress");

    return person;
}

Things are starting to look pretty nice, but we are still repeating some code. For example, we are initializing the SqlConnection and SqlCommmand, setting the SqlCommand to being a stored procedure, and in one case just adding a single parameter before executing the query. Let's make this easier for us by creating the following extension method:

public static T ExecuteAndReadObject<T>(this DbCommand command, Func<DbDataReader, T> objectMapper)
{
    using (var reader = command.ExecuteReader())
    {
        return reader.ReadObject(objectMapper);
    }
}

public static List<T> ExecuteAndReadList<T>(this DbCommand command, Func<DbDataReader, T> objectMapper)
{
    using (var reader = command.ExecuteReader())
    {
        return reader.ReadList(objectMapper);
    }
}

As you can see these methods look a lot like our already existing code, with the exception that they are now reusable. This results in us now being able to reduce the DAL code down to this:

public Person GetPerson(int id)
{
    using (var connection = new SqlConnection("SomeConnectionString"))
    {
        using (var command = new SqlCommand("dbo.PersonGetSingle", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@id", SqlDbType.Int).Value = id;

            connection.Open();
            return command.ExecuteAndReadObject(this.ReadPerson);
        }
    }
}

public List<Person> GetPersonsAll()
{
    using (var connection = new SqlConnection("SomeConnectionString"))
    {
        using (var command = new SqlCommand("dbo.PersonGetAll", connection))
        {
            command.CommandType = CommandType.StoredProcedure;

            connection.Open();
            return command.ExecuteAndReadList(this.ReadPerson);
        }
    }
}

private Person ReadPerson(DbDataReader reader)
{
    var person = new Person();
    person.Id = reader.GetInt32("id");
    person.Name = reader.GetString("name");
    person.DateOfBirth = reader.GetDateTime("dateOfBirth");
    person.EmailAddress = reader.GetString("emailAddress");

    return person;
}

Looking better already. But to really pull this all together we can create a small static helper method that looks like this:

public static T ExecuteAndReadObject<T>(string connectionString, string storedProcedureName, CommandType commandType, Func<DbDataReader, T> objectMapper, params SqlParameter[] parameters)
{
    using (var connection = new SqlConnection(connectionString))
    {
        using (var command = new SqlCommand(storedProcedureName, connection))
        {
            command.CommandType = commandType;
            foreach (var parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

            connection.Open();
            return command.ExecuteAndReadObject(objectMapper);
        }
    }
}

public static List<T> ExecuteAndReadList<T>(string connectionString, string storedProcedureName, CommandType commandType, Func<DbDataReader, T> objectMapper, params SqlParameter[] parameters)
{
    using (var connection = new SqlConnection(connectionString))
    {
        using (var command = new SqlCommand(storedProcedureName, connection))
        {
            command.CommandType = commandType;
            foreach (var parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

            connection.Open();
            return command.ExecuteAndReadList(objectMapper);
        }
    }
}

I know these two methods are technically not extensions methods, but they are pretty darn useful to reduce the code needed to execute simple statements. We can now reduce our DAL code down to this:

public Person GetPerson(int id)
{
    var parameter = new SqlParameter("@id", SqlDbType.Int) {Value = id};
    return DataReaderExtensions.ExecuteAndReadObject("SomeConnectionString", "dbo.PersonGetSingle", CommandType.StoredProcedure, this.ReadPerson, parameter);
}

public List<Person> GetPersonsAll()
{
    return DataReaderExtensions.ExecuteAndReadList("SomeConnectionString", "dbo.PersonGetAll", CommandType.StoredProcedure, this.ReadPerson);
}

private Person ReadPerson(DbDataReader reader)
{
    var person = new Person();
    person.Id = reader.GetInt32("id");
    person.Name = reader.GetString("name");
    person.DateOfBirth = reader.GetDateTime("dateOfBirth");
    person.EmailAddress = reader.GetString("emailAddress");

    return person;
}

Pretty impressive right? It doesn't get much simpler than this, and best of all, the methods we have created so far are all very reusable, since they contain next to no implementation detail.

Adhering to the DRY principal saves you a lot of time in the long run, makes the code easier to read and makes the code much more maintainable. Extension methods, especially coupled with the Func type, can help us a great deal to get rid of the common cruft.

I hope you enjoyed this series, and happy coding!