DBNullable helper – How to handle DBNull gracefully

When you use standard .NET readers (e.g. SqlDataReader) to read results from a database operation, NULL values are returned as DBNull, which are not the same as C#’s “null”.

Because data records (even value types) are boxed and treated as objects, you probably want to cast them into their expected type.

SqlDataReader reader = null;

// Code to instantiate the data reader...

int myInt = (int)reader["myInt"];

Unfortunately, this will throw an exception if reader[“myInt”] is DBNull, which can’t be translated into an Int32.

That’s why I often see the following syntax.

// This would work.
int myInt = reader["myInt"] != DBNull.Value ? (int)reader["myInt"] : 0;

// This would also work.
object value;

int myInt = (value = reader["myInt"]) is DBNull ? 0 : (int)value;

// I've seen worse...
int myInt;

try
{
    myInt= (int)reader["myInt"];
}
catch // This will catch the InvalidCastException.  It’s ugly and inefficient.
{
    myInt= 0;
}

My problem with the code samples above is that, even though they achieve the expected result, they look ugly and are hard to read (not to mention the third one, that’s just wrong).

Here comes DBNullable…

using System;

public static class DBNullable
{
    public static TOutput Cast<TOutput>(object value, TOutput defaultValue)
    {
        /* There are other ways to check if an object is a DBNull...
         * - Convert.IsDBNull()
         * - value is DBNull
         * ... but after a quick benchmark, this seems to be the fastest.
         */
        return value == DBNull.Value ? defaultValue : (TOutput)value;
    }

    public static TOutput Cast<TOutput>(object value)
    {
        return Cast(value, default(TOutput));
    }
}

DBNullable is a simple static helper that has two overrides of the same method. Cast() takes an object as the input, and returns either the value cast into the proper type, or a default value if the value was DBNull.

It can be used this way:

int myInt= DBNullable.Cast<int>(reader["myInt"], 0);

And because .NET will infer the type from the second argument most of the time, you can even drop the type specifier.

I find this much more concise and readable.

int myInt= DBNullable.Cast(reader["myInt"], 0);

Tags: Handle DBNull elegantly