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.