What is the point of DBNull?
I'm going to disagree with the trend here. I'll go on record:
I do not agree that
DBNull
serves any useful purpose; it adds unnecessary confusion, while contributing virtually no value.
The argument is often put forward that null
is an invalid reference, and that DBNull
is a null object pattern; neither is true. For example:
int? x = null;
this is not an "invalid reference"; it is a null
value. Indeed null
means whatever you want it to mean, and frankly I have absolutely no problem working with values that may be null
(indeed, even in SQL we need to correctly work with null
- nothing changes here). Equally, the "null object pattern" only makes sense if you are actually treating it as an object in OOP terms, but if we have a value that can be "our value, or a DBNull
" then it must be object
, so we can't be doing anything useful with it.
There are so many bad things with DBNull
:
- it forces you to work with
object
, since onlyobject
can holdDBNull
or another value - there is no real difference between "could be a value or
DBNull
" vs "could be a value ornull
" - the argument that it stems from 1.1 (pre-nullable-types) is meaningless; we could use
null
perfectly well in 1.1 - most APIs have "is it null?" methods, for example
DBDataReader.IsDBNull
orDataRow.IsNull
- neither of which actually requireDBNull
to exist in terms of the API DBNull
fails in terms of null-coalescing;value ?? defaultValue
doesn't work if the value isDBNull
DBNull.Value
can't be used in optional parameters, since it isn't a constant- the runtime semantics of
DBNull
are identical to the semantics ofnull
; in particular,DBNull
actually equalsDBNull
- so it does not do the job of representing the SQL semantic - it often forces value-type values to be boxed since it over-uses
object
- if you need to test for
DBNull
, you might as well have tested for justnull
- it causes huge problems for things like command-parameters, with a very silly behaviour that if a parameter has a
null
value it isn't sent... well, here's an idea: if you don't want a parameter sent - don't add it to the parameters collection - every ORM I can think of works perfectly well without any need or use of
DBNull
, except as an extra nuisance when talking to the ADO.NET code
The only even remotely compelling argument I've ever seen to justify the existence of such a value is in DataTable
, when passing in values to create a new row; a null
means "use the default", a DBNull
is explicitly a null - frankly this API could have had a specific treatment for this case - an imaginary DataRow.DefaultValue
for example would be much better than introducing a DBNull.Value
that infects vast swathes of code for no reason.
Equally, the ExecuteScalar
scenario is... tenuous at best; if you are executing a scalar method, you expect a result. In the scenario where there are no rows, returning null
doesn't seem too terrible. If you absolutely need to disambiguate between "no rows" and "one single null returned", there's the reader API.
This ship has sailed long ago, and it is far far too late to fix it. But! Please do not think that everyone agrees that this is an "obvious" thing. Many developers do not see value in this odd wrinkle on the BCL.
I actually wonder if all of this stems from two things:
- having to use the word
Nothing
instead of something involving "null" in VB - being able to us the
if(value is DBNull)
syntax which "looks just like SQL", rather than the oh-so-trickyif(value==null)
Summary:
Having 3 options (null
, DBNull
, or an actual value) is only useful if there is a genuine example where you need to disambiguate between 3 different cases. I have yet to see an occasion where I need to represent two different "null" states, so DBNull
is entirely redundant given that null
already exists and has much better language and runtime support.
The point is that in certain situations there is a difference between a database value being null and a .NET Null.
For example. If you using ExecuteScalar (which returns the first column of the first row in the result set) and you get a null back that means that the SQL executed did not return any values. If you get DBNull back it means a value was returned by the SQL and it was NULL. You need to be able to tell the difference.
DbNull
represents a box with no contents; null
indicates the non-existence of the box.