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.