What is the point of DBNull?

Asked 2010-12-20T10:25:25.767

In .NET there is the null reference, which is used everywhere to denote that an object reference is empty, and then there is the DBNull, which is used by database drivers (and few others) to denote... pretty much the same thing. Naturally, this creates a lot of confusion and conversion routines have to be churned out, etc.

Why did the original .NET authors decide to make this? To me it makes no sense. Their documentation makes no sense either:

The DBNull class represents a nonexistent value. In a database, for example, a column in a row of a table might not contain any data whatsoever. That is, the column is considered to not exist at all instead of merely not having a value. A DBNull object represents the nonexistent column. Additionally, COM interop uses the DBNull class to distinguish between a VT_NULL variant, which indicates a nonexistent value, and a VT_EMPTY variant, which indicates an unspecified value.

What's this about a "column not existing"? A column exists, it just doesn't have a value for the particular row. If it didn't exist, I'd get an exception trying to access the specific cell, not a DBNull! I can understand the need to differentiate between VT_NULL and VT_EMPTY, but then why not make a COMEmpty class instead? That would be a much neater fit in the whole .NET framework.

Am I missing something? Can anyone shed some light why DBNull was invented and what problems it helps to solve?

.net dbnull

Edit Report

2 Answers

There are some differences between a CLR null and a DBNull. First, null in relational databases has different "equals" semantics: null is not equal to null. CLR null IS equal to null.

But I suspect the main reason is to do with the way parameter default values work in SQL server and the implementation of the provider.

To see the difference, create a procedure with a parameter that has a default value:

CREATE PROC [Echo] @s varchar(MAX) = 'hello'
AS
    SELECT @s [Echo]

Well-structured DAL code should separate command creation from use (to enable using the same command many times, for example to invoke a stored procedure many times efficiently). Write a method that returns a SqlCommand representing the above procedure:

SqlCommand GetEchoProc()
{
    var cmd = new SqlCommand("Echo");
    cmd.Parameters.Add("@s", SqlDbType.VarChar);
    return cmd;
}

If you now invoke the command without setting the @s parameter, or set its value to (CLR) null, it will use the default value 'hello'. If on the other hand you set the parameter value to DBNull.Value, it will use that and echo DbNull.Value.

Since there's two different results using CLR null or database null as parameter value, you can't represent both cases with only one of them. If CLR null was to be the only one, it'd have to work the way DBNull.Value does today. One way to indicate to the provider "I want to use the default value" could then be to not declare the parameter at all (a parameter with a default value of course makes sense to describe as an "optional parameter"), but in a scenario where the command object is cached and reused this does lead to removing and re-adding the parameter.

I'm not sure if I think DBNull was a good idea or not, but a lot of people are unaware of the things I've mentioned here, so I figured it worth mentioning.

answered 2013-05-02T16:43:51.267

You use DBNull for missing data. Null in the .NET language means that there is no pointer for an object/variable.

DBNull missing data: http://msdn.microsoft.com/en-us/library/system.dbnull.value.aspx

The effects of missing data on statistics:

http://en.wikipedia.org/wiki/Missing_values

answered 2010-12-20T10:33:38.010

What is the point of DBNull?

2 Answers

Your Answer