Friday, January 12, 2007

Since the beginning of my adventure with .NET I have always asked myself one question: why do we need the DBNull type? Is a simple null reference not enough?
MSDN says that DBNull "Represents a nonexistent value". From a logical point of view - this one sentence explains why a null reference cannot be used - because it is a null reference and not a lack of value. But is it enough to introduce a type that causes a lot of trouble?

DBNull is typically used in ADO.NET when returning data from the database. If one column does not have a value in the database, it will be represented as a DBNull object. That is something altogether different from what I would expect at the domain level. When working with objects when there is a Client object, and I ask for his Name then if the name is not there I expect to get null in return. It is very natural to me and probably to most of the developers. When we need to interact with data layer that is where most of the problems with DBNull kick in.

Consider a simple example of a Client's Name property which is of a String type. In your code you accept a situation when the Name is null which may mean that it has never been set. When you save such a Client to a database using a simple Insert call, everything works OK. (it was in fact my data access library that handled null inserts by changing them to DBNulls) You check the database and the data is in fact there and the Name is "null". When next time you try to get the Client from the database, you have to read all the columns and put the values into your object like in the following example:

Client c = new Client();
c.Id = (Guid)row["Id"];
c.Name = (string)row["Name"];
...

This will result in an InvalidCastException exception being thrown with a message "Unable to cast object of type 'System.DBNull' to type 'System.String'." That is of course because null values from the database are represented as DBNull objects.

Typical solution for this problem is to check the column for DBNull before casting or using an "as" operator which you have to be carefull with as I describe in another post.

Whichever way you choose, what you get is an overly complicated code that does a simple thing!

I don't think that the sentence from MSDN that describes the purpose of DBNull is a good excuse for introducing DBNull type. I don't think that returning null instead of DBNull would cause any loss of information. What I think is that DBNull should have never been introduced because it makes developer's work harder without adding any real value. Additionally ADO.NET is inconsistent in that it allows saving a null referrence to a database but does return DBNull instead. Because of the fact that I don't like to deal with DBNulls, in my data access library, I'm always replacing it with a null reference so I never get it to propagate to the upper layers. That way I get a better separation from a database level stuff.

BTW: If anyone has something to say in defense of the DBNull I woulreferenced gladly hear it.

kick it on DotNetKicks.com