More In-Depth About Nulls And DBNull
page 4 of 7
by Brian Mains
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 42772/ 102

The DBNull Type

When a value in the database is null in SQL Server or Oracle, the equivalent to it an instance of the DBNull type.  This type doesn't match the type of a nullable value type, a reference type, or a value type, and therefore is not directly assignable.

For instance, sometimes in code you will see items in a data row being used without checking for null first.  When the underlying field is not null, this is OK, but if a null is available, the following code will not work:

Listing 4

Decimal amount = (Decimal)row["AmountValue"];  //returned value is DBNull

Because DBNull is not the same type, an InvalidCastException occurs.  Rather, a null check has to be performed.  The safe alternative to this is the following:

Listing 5

Decimal amount = 0;
If (!row.IsNull("AmountValue"))
  amount = (Decimal)row["AmountValue"];

If you want to set a row's value, you can do something like below.  The row takes an instance of type object, and therefore can be assigned DBNull:

Listing 6

If (amountValue > 0)
  row["AmountValue"= amountValue;
Else
  row["AmountValue"= DBNull.Value;

The value property returns the actual instance of DBNull (a static property).  Sometimes, a helper routine would be good to handle this conversion for you, reducing the amount to a total of one line instead of four.  That may not seem like much, but a table with thirty columns will make this much coding a chore.


View Entire Article

User Comments

Title: Nasty syntax for getting round this   
Name: Tyrannosaurs
Date: 2008-02-20 11:50:11 AM
Comment:
You cast a null date time to a dbnull like this:

datetime? myDateTime;

myDateTime = null;

.... parameters.add(...).value = (object)myDateTime ?? System.DBNull.Value;

It's pretty horrible but works and is no worse than most of the alternatives.
Title: DBNULL   
Name: g
Date: 2007-11-19 6:37:19 AM
Comment:
this concept can be dealt in more detail
Title: Datetime & DBNull Reply   
Name: Brian
Date: 2007-11-10 11:29:55 PM
Comment:
You can't cast directly; DBNULL and DateTIme are two separate object types, and you can't cast one to the other. However, in an ADO.NET datarow object, the underlying value is object, which could accept both. However, if the field is not null, then you can't assign a null value to it. It all depends on your code.
Title: Datetime&DBnull   
Name: Erick
Date: 2007-11-09 6:49:21 AM
Comment:
When I assigned DBNull.Value to System.Datetime, it raised a exception. Can I set a Datetime field of the database to null by using DBNull.Value in ADO.net?






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-04-19 2:41:25 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search