Friday, January 12, 2007

What Is DBNull Needed For Anyway?

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

.NET
Friday, January 12, 2007 12:08:55 AM (Central European Standard Time, UTC+01:00)  #    Disclaimer  |  Comments [15]  |  Related posts:
Search Engine Optimization Tools - Online!
Invalid URI: The Uri scheme is too long
Hashtable Indexer - What The Hell?
Directory.Copy In .NET
Regex.Match().Groups Gotcha
Microsoft .NET Assemblies Public Key Names
Friday, January 12, 2007 9:23:46 AM (Central European Standard Time, UTC+01:00)
I think you just wrote what everybody have thought. DBNull does cause confusion, but you can improve on you example to stop it from breaking.

If you write
c.Name = row["Name"] as string;

it will return null if the value of the name field is DBNull. However, this only works on reference types so you have to think of something else for integers and other value types. The best way is to set the AllowNulls property in the database to false. It is only in certain foreign key columns it is preferable to allow nulls anyway.
Mads Kristensen
Friday, January 12, 2007 9:57:58 AM (Central European Standard Time, UTC+01:00)
In my other post I have also written why I think that using "as string" may not always be the good solution. (http://vaultofthoughts.net/SemanticalDifferenceBetweenAsOperatorAndCasting.aspx)
But generally in most cases I prefer to do what you propose because it works well.
As for nulls in the database. I think that we need them because there is a difference between having an empty value and having no value. The former means that there is no value and the later that we don't know yet. But here also I agree that the less nulls you have the better :-)
Michal Talaga
Friday, January 12, 2007 4:17:11 PM (Central European Standard Time, UTC+01:00)
Another use for DBNull is when inserting dates that are'nt required.
Example:
if (someDate == DateTime.MinValue)
command.Parameters.Add(new SqlParameter("@SomeDate", DBNull.Value));
else
command.Parameters.Add(new SqlParameter("@SomeDate", someDate));
Eirik
Friday, January 12, 2007 5:12:54 PM (Central European Standard Time, UTC+01:00)
Well, according to the docs for system.dbnull:

In database applications, a null object is a valid value for a field. This class differentiates between a null value (a null object) and an uninitialized value (the DBNull.Value instance). For example, a table can have records with uninitialized fields. By default, these uninitialized fields have the DBNull value.

if a null object is a valid value than you *do* have to differentiate between null is you know and love it and something else with a lack of value.

I usually use IIF in conjunction with the isdbnull function, but there are other ways of setting defaults for fields that return null values where you might want an empty string instead...
Dave Frank
Friday, January 12, 2007 8:50:38 PM (Central European Standard Time, UTC+01:00)
How about instead of storing Id as a Guid in your example:
c.Id = (Guid)row["Id"];

I say that Id is an integer. Now what do you expect to happen when you do this:
c.Id = (int) row["Id"];
if the value was null in the database?
Tim
Monday, January 15, 2007 9:59:56 AM (Central European Standard Time, UTC+01:00)
I think that DBNull is a needed thing, in fact, you proposal don't work for value types.
c.IntField = (int) row["IntField"];

Throws an INvalidCast if you return null, so is the same.

You cant return null in all cases, so you need to return something else and remark that case in a completely new way to distingish it from other cases and DbNull.Value is a perfect singleton for it, comparisons with the DbNull.Value are references comparisions and are completely fast.

I love the decisition to distingish from the common null.

BTW: if you use some code generation tool for your data access layer like CodeSmith, or something like NHibernate, you never deal with it (or you do it one time when you write the templates)

Another solution is to dont allow null values in some fields :P
Marcos
Monday, January 15, 2007 10:22:06 AM (Central European Standard Time, UTC+01:00)
Dave Frank: I cannot see how you can see the difference between those two types of "values" for a field when workin with ADO.NET.

Tim: I expect the same thing to happen that would happen if Id was uniqueidentifier in the DB. If I allow null values in the database that I have to use a nullable type in my model - or if not available - two fields: one for value and one to keep information if value is available.
For me, there is no difference if I get an exception because of int i = (int)null or because of int i = (int)DBNull.Value; True, the former will result in a NullReferenceException which is uglier than an InvalidCastException, but still you will only see it once, because both mean that there is something wrong with your code and you should fix it.
Marcos: See the answer I gave to Tim's comment. You both provide a code that won't work with null referencese but nor will it work with DBNull.
Michal Talaga
Monday, January 22, 2007 11:58:17 PM (Central European Standard Time, UTC+01:00)
I don't have anything new to say against DBNull, though I have one complain on SqlServer.

The following command return no rows but it should:

"select * from Northwind.Customers where Region = @Region"
(@Region = DBNull.Value)

MSDN says that if you want to query for null you must use the IS operator, or turn off some ANSI_NULL option and use either IS or =.

The following command throws an error, I think SqlCommand doesn't like parameters when using IS.

"select * from Northwind.Customers where Region IS @Region"
(@Region == DBNull.Value)

So the only option we have left is to drop the parameter and simply send:

"select * from Northwind.Customers" where Region IS NULL"

This sucks bigtime. When using MySql all of these commands work fine. Am I missing something?
max toro q
Monday, June 01, 2009 4:18:49 PM (Central European Standard Time, UTC+01:00)
Hello. The price one pays for pursuing any profession or calling is an intimate knowledge of its ugly side.
I am from Vanuatu and bad know English, tell me right I wrote the following sentence: "Key actions are anti inflammatory, anti allergic, anti spasmodic, relaxant and carminative."

Waiting for a reply :-(, Fairfax.
Fairfax
Tuesday, June 02, 2009 12:38:54 AM (Central European Standard Time, UTC+01:00)
Good Day. Nor do not saw the air too much with your hand, thus, but use all gently. For in the very torrent, tempest, and as I may say, whirlwind of passion, you must acquire and beget a temperance that may give it smoothness.
I am from Belgium and now teach English, please tell me right I wrote the following sentence: "It helps in faster wound healing and has blood purifying properties."

Thank you very much :-(. Violet.
Violet
Tuesday, June 02, 2009 7:44:57 PM (Central European Standard Time, UTC+01:00)
Just want to say i`m glad i found this site.
I am from Jordan and learning to speak English, tell me right I wrote the following sentence: "Nonspecific irritants from anti inflammatory agents using the."

THX :D, Kasib.
Kasib
Tuesday, September 29, 2009 4:34:53 AM (Central European Standard Time, UTC+01:00)
Hi everyone. I never vote for anyone; I always vote against.
I am from Vanuatu and also am speaking English, please tell me right I wrote the following sentence: "What are the latter accp9icstons during regular information decided? If the fighter is to remove a written wattage, what is the computer and what gets if we leave notice."

With love :-), Kiana.
Kiana
Sunday, October 18, 2009 11:07:51 PM (Central European Standard Time, UTC+01:00)
Greeting. Confidence is the sexiest thing a woman can have. It's much sexier than any body part.
I am from Tanzania and also am speaking English, give please true I wrote the following sentence: "There are british shutdowns of norse back government parishes."

THX :p, Magdalena.
Magdalena
Saturday, November 21, 2009 6:51:35 AM (Central European Standard Time, UTC+01:00)
Hi guys. We make a living by what we get, we make a life by what we give.
I am from Afghanistan and also now am reading in English, give please true I wrote the following sentence: "Mortgage note, in this loan, if the risk offers really have tough land on foreclosure to prevent the definite mortgage of the property, the pool could collect on the banker."

With best wishes 8-), Donnelly.
Donnelly
Wednesday, December 02, 2009 10:12:22 PM (Central European Standard Time, UTC+01:00)
Hello. Fig Newton: The force required to accelerate a fig 39.37 inches per sec.
I am from African and know bad English, please tell me right I wrote the following sentence: "Will my somnolencia adhd away?"

Thank :p Duke.
Duke
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):
Live Comment Preview