Coping with DBNull

• Download source - 618 B

Introduction

If you are sick of constantly checking for DBNull.Value in your database results, here are a series of helpers to alleviate all that effort, and make your code more readable.

Background

Simple - I got sick of writing code like...

object result = cmd.ExecuteScalar();
int n = (result is DBNull) ? 0 : (int)result;

...and when you're dealing with a dozen or more columns like this in a data row, not only does it add up to lots of mundane typing, but the resulting code tends to look messy.

Odds are that this code will not handle all the DBNulls you have to deal with (e.g. I don't have anything here for BLOB data), but it has certainly made my coding and maintenance that much simpler.

Using the Code

The attached source is just a static DbConvert class with a set of data type conversions based on the System.Convert.ChangeType function, that checks for DBNull before attempting the conversion. They will still throw the normal InvalidCastException if you try to do something silly like converting "ABC" to an integer, but will quietly return a default value if they encounter either DBNull or null.

The method that does all the work is T DBConvert.GetValue<T>(object dbResult, [T defaultValue = Default(T)]). There are a pile of helpers that call this method to convert the standard .NET types. The bottom line is that the above two lines of code are now written simply as:

int n = DbConvert.GetInt(cmd.ExecuteScalar());

There is also an embedded Nullable class that has helper for the nullable data types, for those times when null is a valid value.

double? value = DbConvert.Nullable.GetDouble(columnValue[2]);
if (value == null) {
    // deal with it
}

There is also one other simple function, DBConvert.GetObject(object dbResult, [object defaultValue = null]), that does not perform any type conversions apart from changing DBNull to defaultValue.