Brian Dobberteen

I’d like to describe a techinque that I’ve come up with to handle the ever-present issue of the easiest (best?) way to handle building an object from a database query that may or may not contain NULL values. I feel compelled to post this because I haven’t seen this approach detailed elsewhere online.

First, let’s create a simple class to demonstrate our needs:

public class Person {
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int? Age { get; set; }
    public DateTime DateCreated { get; set; }
    public DateTime? DateModified { get; set; }
}

Note the use of nullable value types on lines 5 and 7 above. For those not familiar with the concept of nullable types, have a look at: Using Nullable Types – MSDN.

In our class, we are anticipating that there may be NULL values stored for a Person’s Age and Last Modified Date.

Simple enough. But how to best build a Person object from a SqlDataReader? I’ve seen tons of different approaches, such as:

var people = new List<Person>();

using (var dr = cmd.ExecuteReader()) {
    while (dr.Read()) {
        people.Add(new Person {
            Id = (int) dr["Id"],
            FirstName = dr["FirstName"].ToString(),
            LastName = dr["LastName"].ToString(),
            Age = Convert.IsDbNull(dr["Age"]) ? null : (int?) dr["Age"],
            DateCreated = (DateTime) dr["DateCreated"],
            DateModified = Convert.IsDbNull(dr["DateModified"])
                                                 ? null
                                                 : (DateTime?) dr["DateModified"]
        });
    }
}

This approach certainly isn’t that bad, and seems to be a very common method for handling NULLs via nullable types.

But we’re not content with something that’s only ‘not bad,’ are we? Of course not.

Let’s try another way:

var people = new List<Person>();

using (var dr = cmd.ExecuteReader()) {
    while (dr.Read()) {
        people.Add(new Person {
            Id = (int) dr["Id"],
            FirstName = dr["FirstName"].ToString(),
            LastName = dr["LastName"].ToString(),
            Age = dr["Age"] as int?,
            DateCreated = (DateTime) dr["DateCreated"],
            DateModified = dr["DateModified"] as DateTime?
        });
    }
}

Check out lines 9 and 11 – we are using the ‘as’ operator rather than the clunky ternary, resulting in code that, IMHO, is more concise, clear, and therefore easier to read and maintain.

I’m surprised that I haven’t seen others doing this… I know I’m not the only one to have stumbled across this approach!