Handle Dbnull in C#

Best way to handle Datarow DBNull

Try:

foreach(DataRow row in table.Rows)
{
    object value = row["ColumnName"];
    if (value == DBNull.Value)
    {

    }
    else
    {
    }
}

What is the best way to handle a DBnull execption?

• Avoid using Convert.ToFoo, instead use the GetFoo methods on DataReader directly.
• If you use DataReader.IsDBNull() first then you won't get a DBNull-related exception in the first place.

Like so:

while (reader.Read())
{
    sessionInfo.ID = sessionId;
    sessionInfo.Name = reader.IsDBNull( "Name" ) ? null : reader.GetString( "Name" );
    sessionInfo.GroupId = reader.IsDBNull( "GroupId" ) ? null : reader.GetString( "GroupId" );
    sessionInfo.Disabled = reader.IsDBNull( "Disabled" ) ? false : reader.GetBoolean( "Disabled" );
    // etc...

How to handle System.DBNull?

This expression is always true

myObject != null || myObject != DBNull.Value

because myObject cannot be null and DBNull.Value at the same time. Replace || with && to fix.

Check DbNull before casting C#

You can check column value against DBNull.Value like this:

request_id = (reader["request_id"] == DBNull.Value) ? default(int) : (int)reader["request_id"];

If you want to simplify the process to all columns, create an extension method which compares against DBNull.Value like this example:

// using DBNull.Value comparison
public static T GetValue<T>(this SqlDataReader reader, string columnName)
{
    var value = reader[columnName]; // read column value

    return value == DBNull.Value ? default(T) : (T)value;
}

// alternative using GetOrdinal and IsDBNull
public static T GetValue<T>(this SqlDataReader reader, string columnName) 
{
    int index = reader.GetOrdinal(columnName); // read column index

    return reader.IsDBNull(index) ? default(T) : (T)reader.GetValue(index);
}

Usage example:

request_id = GetValue<int>(reader, "request_id");

Handling a DateTime DBNull

One possible option is store it as a nullable date time with the syntax DateTime?

Here is a link to the MSDN about using nullable types

What is the best way to deal with DBNull's

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.

---

Related Topics