Domain Objects and DataReaders

Its been a very long time since I've written on this page, but it has not been because I am lazy.

Being a .NET developer, I try to give back to the community as much as I can, but the internet as a whole is rather critical of us .NET developers, and sometimes maintaining a blog can be a rather thankless and frustrating experience.

I decided to take some time off from blogging, to tackle a handful of difficult programming projects and to come back when I was feeling more refreshed.

I have been working on an application framework for some time, and I thought that a small snippet from that would be beneficial to my readers.

A common practice when designing a N-tiered framework is to create custom domain (or business) objects that are responsible for populating themselves from raw datareaders.

For example, you may have a database table that represents a "Customer". This approach would create an object that models the table schema as a strongly typed class. My usual technique to populate such a class is to pass a database row in the form of a IDataReader to the class constructor.

However, you almost always end up in a scenario where the stored procedure that is ultimately responsible for the creation of the IDataReader may only return a portion of the columns that the domain object expects.

Because of this, people advocate "defensive" coding techniques, where you validate the existence of each column before attempting to read and assign it to a class property.

Because IDataReader (and its derivatives, such as SQLDataReader and ODBCDataReader) do not expose a "HasColumn" property, I have witnessed some creative and horrible approaches to this technique:

    1 try

    2 {

    3     this.CustomerName = reader["CustomerName"].ToString();

    4 }

    5 catch (IndexOutOfRangeException)

    6 {

    7     this.CustomerName = string.Empty;

    8 }

Imagine this code, repeated for each column that may have been returned in the DataReader. Not only is it excessively verbose, but it violates a cardinal rule: Utilizing exceptions to control program flow.

Exceptions are a good thing, but using them in this fashion is a poor design choice. Each time an exception is thrown and caught, the call stack must traversed and a great deal of overhead is added on a per column basis.

Lets see if we can do better.

The first order of business is to refractor the column checking logic into a static helper method. There is no need to repeat this code for each column. I already had a static Utility class in my database namespace, so I placed this method there.

The second order of business is to take a page out of the TryParse notebook, and utilize an output parameter to pass our domain object variable. The new methods signature is quite obvious as to how this method will work:

    1 public static void ReadColumn(ref SqlDataReader reader, string columnName, out string column)

The current SqlDataReader will be passed by reference to this method in addition to passing the desired column name and finally the actual variable used within the domain object.

I created overloaded versions of this method to handle boolean, integer, string, and DateTime data types, to simplify even more the logic required in the population method.

Here is an example of the string method:

    1 public static void ReadColumn(ref SqlDataReader reader, string columnName, out string column)

    2 {

    3     column = string.Empty;   

    4     if (checkColumn(ref reader, columnName))

    5     {               

    6         column = reader[columnName].ToString();

    7     }

    8 }

As you can see, this is a very simple method, and the other overloaded methods are just as simple, containing an additional TryParse call in them to ensure that they can successfully cast the columns contents into the desired data type.

This leaves just one snippet of code remaining, the private helper method "checkColumn".

    1 private static bool checkColumn(ref SqlDataReader reader, string columnName)

    2 {

    3     reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName='" + columnName + "'";

    4     return (reader.GetSchemaTable().DefaultView.Count > 0);

    5 }

IDataReader exposes a method that can return its schema in the form of a DataView object. Since DataView objects can be filtered using RowFilter, this method is able to create and filter the view by the desired column name. Finally, I check to see if there are any rows in the filtered view. This ensures that this column does exist in the DataReader.

Finally, jumping back to our domain objects populate code, we can replace all of the heavy duty defensive coding code with a simple and clean method call:

    1 SQLServer.Utility.ReadColumn(ref reader, "CustomerName", out this._customerName);

If anyone knows of a more efficient way to do this, please let me know. I am always a fan of optimizing out inefficiencies.