August 28, 2012

A generic way to read data from an SqlDataReader

Through the years I have been struggling to find a really good way to read data from a datareader. If you know me, you also know that I prefer good old reliable methods that puts me in control to object relational mapping..

Consider the following code (didn't compile it so forgive me for eventual bugs):

    private static List<Book> ReadData(string connectionString)
        List<Book> books = new List<Books>();
        string queryString =
            "SELECT Author, Title, Year FROM Books";

        using (SqlConnection connection =
                   new SqlConnection(connectionString))
            SqlCommand command =
                new SqlCommand(queryString, connection);

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
                Book book = new Book();
                book.Author = reader("Book");
                book.Title= reader("Title");
                book.Year= reader("Year");

            return books;

This code should work pretty well, the problem comes when you start using nullables. If year were int? instead of int this code this would throw an exception as soon as a null value appears in the reader.

Null in Sql Server and in c# are two different things.
From the database points of view a field has three values:

  1. A value. For example 1993.
  2. A null value. We left the year empty because we didn't know the year.
  3. Empty, the book does not exist.
Now you might argue that if the row was empty a try would anyway give you an error and the pragmatical side of me totally agrees, but still, the issue stems from the fact that null in C# is the absence of a reference to an object, while null in a database is an uninitialized or empty value. It exists and we have to deal with it.

I like solving this with extension methods (actually I am generally pretty hooked on extension methods).

The standard approach is to use something like this:

public static int? ToNullableInt(this int value)
    return value.IsNull ? (int?) null : value.Value;

And then call the reader like:

book.Year= reader("Year").ToNullableInt();

It is a reasonable solution. But you would have to write those extension methods for every nullable data type.

A single function version that handles all data types plus basic conversion but also is slightly slower looks something like this:

           private static T Get<T>(this SqlDataReader reader, string index, T defaultValue = default(T))
                    var t = reader[index];
                    if (t == DBNull.Value)
                                return defaultValue;
                   Type type = typeof(T);
                   if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
                               var subType = Nullable.GetUnderlyingType(type);
                               return (T)Convert.ChangeType(t, subType);
                   return (T)System.Convert.ChangeType(t, typeof(T));

This would be called like this:

book.Year= reader.Get<int?>("Year");

When microseconds are not the issue, I prefer that solution, but I am always looking for something even better. Ideas? :)

August 1, 2012

My populator is beginning to take shape

I have been knee deep into my populate project the last days and I truly believes that meta programming is somewhat like LSD. It seriously fucks up your mind.

When you take a step away from your usual garbage in - garbage out project and enter the realm of reflection and generics stuff can be, well, abstract.

I hopefully will finish the project before ending up in an asylum.

In the meanwhile I am happy to see that there will be a Build conference this year. Read all about it here.

I went to the conference last year and loved it, will definitely take a talk with my boss about this one. So far there is no more info than that the registration will start at 8, the 8th day in month 8. Why are developers so beautifully nerdy? :)

Now back into the code again! See you!