The DBNull class is a special situation when it comes to working with the ADO.NET framework, and even though it's mostly straightforward, I still see a lot of confusion about how it can be used.  So I am writing this in hopes that this may alleviate someone's problems in the future.

The .NET Framework comes with many different types defined within it.  A type is a class, structure, or other declaration; for instance, int, short, string, DataTable, List<string> are types, with the last one being a generic type.  The following code creates an instance of a type:

Listing 1

int I = 0;

The variable I is of type int, and is an instance of the int type with the value zero.  There are two kinds of types; value types and reference types.  Reference types are defined as a class.  For instance, the following is a reference type:

Listing 2

public class MyClass 
{ 
}

MyClass is a reference type, which means it's stored on the heap, and that it can be explicitly set to null.  Some of the reference types defined in the framework are: string, DataTable, DataSet, List<T>, and so on.  On the other hand, value types are defined as structures, but the value types int, short, etc. inherit from the base class ValueType.  The following is a value type:

Listing 3

public struct MyValueType 
{ 
}

Structures have certain rules, like they need to have at least one field defined within it, as well as a few other rules.  Some of the common value types are: DateTime, Rectangle, int, short, long, and many more.

There are many articles on the web discussing the differences between value and reference types.  Please consult them for more information.

Nullable types are a new feature to .NET 2.0, where they can make any value type support null values.  By defining a value type with a question mark after it (in C#), or using the Nullable<T> class (in VB.NET), the value type can support nulls simply by assigning null to it.  Nullable types add two additional members to it: the Value property and the HasValue property.  HasValue determines whether the nullable value actually has a value, because if the Value property (which represents the actual value) is accessed before being assigned a value, an exception is raised.

However, a nullable type is still an instance of the value type (int? or Nullable(Of Int32)) is still a value of type int), and isn't meant to represent or handle DBNull objects.  Although when using the table adapter feature of .NET 2.0 nullable types are used, they cannot handle DBNull, because of what is described in the next section.

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.

Table adapters generate their own interface based on the methods you create.  For instance, if a table that has the fields ID, Name, and Text, and the adapter generates the insert, update, and delete SQL statements, the adapter will create overloaded Insert, Update, and Delete methods with these fields as the parameters.  However, you will notice that it will use nullable type values for the value type parameters.

It may seem that a nullable type will be equivalent to DBNull, but the table adapter transforms a nullable type to DBNull, when the final execution is performed.  The adapter has its own interface for doing so.  However, when using a table adapter to return a custom data table and rows, there are special methods for determining if a field is null, and for setting it to null.  These methods are in the construct of Is<ColumnName>Null and Set<ColumnName>Null.  This handles using null values for particular fields.

When working with ADO.NET, without a table adapter, to set a row's value to null, use the DBNull.Value construct. This can also work with command objects, when setting up the parameter.  If you want to set a parameter to null, simply assign DBNull.Value to the command's parameter definition, as shown below:

Listing 7

Comand.Parameters.Add("@Field", SqlDbType.Varchar).Value = DBNull.Value

This translates to a null value in the database.

DBNull is a special type that has to be handled carefully, when working with various objects of the .NET 2.0 Framework. Take care when reading/writing ADO.NET values because a null value could raise an InvalidCastException.