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);
Advertisements

Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


%d bloggers like this: