A reusable datareader

Usually writing simple select statements against the database involves a lot of cermony. I'll show how this can refactored here.

For performance reasons I'm rewriting parts of an application to use the plain DataReader but I found that there is a lot of code that get's repeated. This is the code:

private List<Person> GetPersons(IDbConnection connection)
{
    var sql = @"SELECT
    `PersonId`,
    `DateOfBirth`,
    `EventorOrganisationId`,
    `OrganisationName`,
    `PersonName`,
    `IsDead`
    FROM person";

    var persons = new List<Person>();

    using (MySqlCommand cmd = new MySqlCommand(sql, connection as MySqlConnection))
    {
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                persons.Add(new Person
                {
                    Id = reader.GetInt64(0),
                    DateOfBirth = reader.GetDateTime(1),
                    EventorOrganisationId = reader.GetInt64(2),
                    OrganisationName = reader.GetString(3),
                    PersonName = reader.GetString(4),
                    reader.GetString(5)),
                    IsDead = reader.GetBoolean(6)
                });
            }
            reader.Close();
        }
    }

    return persons;
}

Nothing special and no bigger problems. Untill I decide to add the same function for another table and I have to repeat many of these lines. Of all principles DRY is probably the one I find most useful.

Luckily this is a very easy refactoring. Here is the method that contains all repetitive stuff.

private List<T> Read<T>(string sql, Func<MySqlDataReader, T> tranform, IDbConnection con)
{
    var list = new List<T>();
    using (MySqlCommand cmd = new MySqlCommand(sql, con as MySqlConnection))
    {
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                list.Add(tranform(reader));
            }
            reader.Close();
        }
    }

    return list;
}

And here is how the GetPersons method now looks like:

private List<Person> GetPersons(IDbConnection connection)
{
    var sql = @"SELECT
    `PersonId`,
    `DateOfBirth`,
    `EventorOrganisationId`,
    `OrganisationName`,
    `PersonName`,
    `IsDead`
    FROM person";

    return Read<Person>(sql, reader => new Person
                {
                    Id = reader.GetInt64(0),
                    DateOfBirth = reader.GetDateTime(1),
                    EventorOrganisationId = reader.GetInt64(2),
                    OrganisationName = reader.GetString(3),
                    PersonName = reader.GetString(4),
                    IsDead = reader.GetBoolean(6)
                }, connection);
}

This is sligthly more difficult if you need to work with parameterized queries but not that different.