What is the best way to deal with DBNull's

I frequently have problems dealing with DataRows returned from SqlDataAdapters. When I try to fill in an object using code like this:

DataRow row = ds.Tables[0].Rows[0];
string value = (string)row;

What is the best way to deal with DBNull's in this type of situation.

Nullable types are good, but only for types that are not nullable to begin with.

To make a type "nullable" append a question mark to the type, for example:

int? value = 5;

I would also recommend using the "as" keyword instead of casting. You can only use the "as" keyword on nullable types, so make sure you're casting things that are already nullable (like strings) or you use nullable types as mentioned above. The reasoning for this is

1. If a type is nullable, the "as" keyword returns null if a value is DBNull.
2. It's ever-so-slightly faster than casting though only in certain cases. This on its own is never a good enough reason to use as, but coupled with the reason above it's useful.

I'd recommend doing something like this

DataRow row = ds.Tables[0].Rows[0];
string value = row as string;

In the case above, if row comes back as DBNull, then value will become null instead of throwing an exception. Be aware that if your DB query changes the columns/types being returned, using as will cause your code to silently fail and make values simple null instead of throwing the appropriate exception when incorrect data is returned so it is recommended that you have tests in place to validate your queries in other ways to ensure data integrity as your codebase evolves.

If you aren't using nullable types, the best thing to do is check to see if the column's value is DBNull. If it is DBNull, then set your reference to what you use for null/empty for the corresponding datatype.

DataRow row = ds.Tables[0].Rows[0];
string value;

if (row["fooColumn"] == DBNull.Value)
{
   value = string.Empty;
}
else 
{
   value = Convert.ToString(row["fooColumn"]);
}

As Manu said, you can create a convert class with an overloaded convert method per type so you don't have to pepper your code with if/else blocks.

I will however stress that nullable types is the better route to go if you can use them. The reasoning is that with non-nullable types, you are going to have to resort to "magic numbers" to represent null. For example, if you are mapping a column to an int variable, how are you going to represent DBNull? Often you can't use 0 because 0 has a valid meaning in most programs. Often I see people map DBNull to int.MinValue, but that could potentially be problematic too. My best advice is this:

• For columns that can be null in the database, use nullable types.
• For columns that cannot be null in the database, use regular types.

Nullable types were made to solve this problem. That being said, if you are on an older version of the framework or work for someone who doesn't grok nullable types, the code example will do the trick.

Add a reference to System.Data.DataSetExtensions, that adds Linq support for querying data tables.

This would be something like:

string value = (
    from row in ds.Tables[0].Rows
    select row.Field<string>(0) ).FirstOrDefault();