DBNull and Nullable types


When we have C# code interacting with the data base you run into this problem. Let me try and explain it. The database has nullable columns and C# has nullable types. For example, let us take a table Employee which has Id (int), name (string) and age (int). Let us assume that age will be null if no values are provided.


 


When you query the database for employee, the dataset or datareader will have DBNull for age. Now, this DBNull is not the same as C# null. This is the beginning of the problem. In order to make sure your code works properly, it has to expect that the reader can return a DBNull. You have to do the following in your code:


 


 


int? age = null;


if (!DBNull.Value.Equals(reader[“Age”]))


{


    age = (int)reader[“Age”];


}


 


This code converts the DBNull to a C# null. The lack of common null types between the two makes code have two sets of defaults for data, one in the DB and the other in C#. Which can get confusing.


 


If you don’t use nullable types due to performance, you have to invent your own null value:


 


int age = -1;


if (!DBNull.Value.Equals(reader[“Age”]))


{


    age = (int)reader[“Age”];


}


 


This conversion seems ugly and it is all over the code. Is there any other elegant solution to this?


Comments (13)

  1. Trygve Valoy says:

    You could solve this with an extension method.

    The extension method would look something like this:

    public static TValue GetNullableValue<TValue>(this DbDataReader reader, string name)

    {

      object value = reader[name];

      if (DBNull.Value.Equals(value))

      {

          return default(TValue);

      }

      return (TValue)value;

    }

    And your data access code will be:

    int? age = customerReader.GetNullableValue<int?>("Age");

  2. jackbond says:

    I’ve encountered the same ugly convention as well, and was thinking along the lines of an extension method similiar to the previous poster. Although he missed the part about not being a nullable type (easy enough to tweak his method to be similiar to SQLs IsNull function.)

    When it is a nullable type, I’ve done this in the past:

    int? age = dr["Age"] as int?;

  3. Steve Perry says:

    This problem (dbnull) has been around since well before .net and Microsoft has never done anything to make it any better, nullable types don’t really bring us any closer to a way to map between a database null and a data type.

    Maybe the entity framework will finally fix this issue.

  4. farrio says:

    I also noticed this issue when I was using VB2005 to retrieve the data from SQL2005. I created a class named DBNullable(Of T) to deal with it. But I think M$ need to do something to fix this problem because it’s confusing and fussy when developing a data-based system.

  5. I love Trygve’s idea, but it doesn’t apply to DataSets (yes, some of us still need to use DataSets).

    …and farrio, re: ‘M$’, are you like 4 years old or something?

  6. Trygve Valoy says:

    There’s no reason why this shouldn’t work for DataSets. You just need another extension method. You can even take the table name as a parameter in this method to resolve which table of the dataset you want to get data from.

  7. thottams@microsoft.com says:

    I like the extension method idea as it will work for both nullable and non-nullable types.

    int? age = dr["Age"] as int?; also works. It works because .NET is unable to cast it and assigns a NULL instead, which ends up being what we desire in this case.

  8. Lothan says:

    I think this depends on whether you need to keep -1 as the default value or what you want to keep the null value. One possibility is to use the as operator for a safe type cast:

    int age = reader["Age"] as int? ?? -1;

    int? age = reader["Age"] as int?;

    Unfortunately, this has negative consequences that can be difficult to catch. If you mistakenly cast a smallint to byte, for example, the as operator will silently swallow InvalidCastException.

    I think a more reasonable solution with some measure of safety is to use a nullable cast either with or without the null coalescing operator:

    int age = (int?) reader["Age"] ?? -1;

    int? age = (int?) reader["Age"];

    The code is easy to read and it has the advantage that InvaidCastException is thrown early in case you mistakenly cast to the wrong type, which I’ve found is easy to do with large tables.

  9. Thanks – the as operator with ?? <defaultvalue> works perfectly when using nullable types for the database tier.

  10. I’ve been working like mad to get my 3-tier construction to work. As I’m not all that great in C# yet,

  11. Naison Garvasis Pekkattil says:

    Check out the SafeDataReader class from CSLA.NET. I had customized and used to solve the repeated dbnull check.

  12. Chi Afriq says:

    How do you make these conversions using VB2005? I was initially developing my database system in C# but had prblms cz I’m a beginner, switched back to VB and the NullReferenceException and InvalidCastExceptions are still giving me hell!

  13. vijay sharma says:

    cant we use the Convert.ToString()…….as compare to DBnull.value..i think its better.Is it ?