Proper Etiquette for using MySQL in C# (Part of 3 of 3) – DbDataReader

Looking back on this series, we have discussed both proper management of database related resources and how to protect and improve the performance of our applications through the use of Parameterized Queries (Prepared Statements). Since the primary reason for accessing a database is to read data from it, it would be a shame if we didn’t take a look at the “dos and don’ts” of reading data from a database. When writing software there are a lot of things to consider – One of them is the readability of the code. Your code may function and perform it’s duty, but code which lacks readability can be hard to maintain. Readability is especially important when other programmers have to concurrently work on the same project or one day inherit the legacy of your work. This article will focus on how to cleanly read out entries from a database query result set so that the code is maximally readable.

For those who wish to follow along with this series – here is the layout:

When accessing a database (MySQL or otherwise) in C#, it is possible to write code such as this:

// SQL: 
//     SELECT Name, CountryCode, District, Population 
//     FROM City 
//     WHERE CountryCode = @CountryCode AND Population >= @Population
//
// Example of a DON'T way of reading data from a database
while (reader.Read())
{
    var city = new City()
    {
        /* Retrieving results based on column ordinal in SELECT expression... 
         * Various ways to convert data to the correct type...
         * Oh no... 
         */
        Name = reader[0].ToString(),
        CountryCode = reader[1].ToString(),
        District = Convert.ToString(reader[2]),
        Population = (int)reader[3]
    };
    cities.Add(city);
}

This code will compile and execute flawlessly. However, naturally when writing code there are an infinite number of ways in which you can write something, but only a small subset of those ways are appropriate. In this case, the resulting code is barely readable. Someone reading this code would have to constantly reference the SQL statement in order to make sure that the indexing was correct (the index is based on the column ordinal in the SELECT statement). The code also lacks uniformity in that when interpreting the data from the DbDataReader there are a variety of methods used. In this example, to get the data object to the correct type: Sometimes the ToString method is called on the data object, sometimes the Convert utility class is used on the data object, sometimes the data object is just casted to the correct type. In my opinion, this lack of uniformity and this makes it difficult to quickly determine the type of each column while scanning through the code.

Not only is this code a challenge to read, it is also more likely spawn bugs if the query ever needs to change. Personally, when I see code such as the above I think back to the Ian Malcolm rant during the dinner scene in Jurassic Park (specifically this part):

You stood on the shoulders of geniuses to accomplish something as fast as you could, and before you even knew what you had, you patented it, and packaged it, and slapped it on a plastic lunchbox…

So now that we have covered some of the common flaws of DbDataReader code using MySQL and other ADO .NET Data Providers. Let’s look at code which is improved in terms of readability:

// SQL: 
//     SELECT Name, CountryCode, District, Population 
//     FROM City 
//     WHERE CountryCode = @CountryCode AND Population >= @Population
//
// Example of a DO way of reading data from a database
while (reader.Read())
{
    var city = new City()
    {
        Name = reader.GetString("Name"),
        CountryCode = reader.GetString("CountryCode"),
        District = reader.GetString("District"),
        Population = reader.GetInt32("Population")
    };
    cities.Add(city);
}

So first off, we are no longer using the column ordinal to refer to the data in the result set. Instead by using the column name, a reader of the code immediately knows which part of the result set we are referring to when converting to the correct type. Speaking of converting to the correct type, instead of using a hodgepodge of methods to convert to the correct type we instead exclusively use the GetXXX methods of the DbDataReader class. In my opinion, this consistency as well as the naming convention of the conversion methods makes it much easier to quickly scan through the code and figure out the type of each column. Also, the use of the GetXXX methods just feels right as we are using the DbDataReader object to convert the data it is reading.

So that’s it! I hope these quick tips were helpful or at least made you think about your own data reader code.

Leave a Reply Cancel reply