9.22.2012

null keyword vs DBNull class in C#

Difference between null and System.DBNull.Value
S.No
null
DBNull
1
Conceptual Difference:
The keyword null represents an invalid reference.
Conceptual Difference:
The class System.DbNull represents a nonexistent value in a database field.
2
What is the purpose of null keyword ?
NULL is used to check if a whole record is not found in the database.
What is the purpose of DBNull class ?
DBNULL is used to check if a certain field in a database is null.
3
Is it a Value / Reference / Nullable type ?
null is the default value of reference-type variables. Ordinary value types cannot be null.
Is it a Value / Reference / Nullable type ?
DBNull does not match the type of a nullable value type, a reference type, or a value type, and therefore is not directly assignable.

Please look at: Handle null, then DBNull to avoid InvalidCastException

Reason:
Of DBNull class only one instance can exist as it a singleton class. Sole instance is DBNull.Value.

Handle null, then DBNull to avoid InvalidCastException:

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:

Example-1

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:

Example-2

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:

Example-3

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.

Checking null and DBNull values

How to check for null value ?

1. Create the variable. A variable must be declared before using it. The code below allocates memory and declares the variable for use.
string myNullVar = null;
The code uses the "null" value assignment for the new variable.
2. Check if the variable is null. The code below checks if the variable is null. If it is null, the code assigns it a color value.
if(myNullVar == null) {
myNullVar = "purple";
}

3.Check if the variable is not null. In some cases, you may wan to reassign the variable back to null. The following code checks if the variable is not null, and assigns it a null value.
if(myNullVar != null) {
myNullVar = null;
}

How to check for DBNull values ?

4. Declare a variable. Just like section 1, to check for DBNull, a variable needs to be declared. However, the variable is assigned the DBNull value.
string myNullVar = System.DBNull;
5. Use the "Equals()" method from the string class to check for the DBNull value. The code below checks for this value and assigns myNullVar a color.
if (myNullVar.Equals(System.DBNull.Value)) {
myNullVar = "purple";
}
6.Evaluate if the variable is not null. Similar to section one, you may want to check if the variable is not null to reassign it back to DBNull. The "!" character evaluates to "not" in the following example.
if(!myNullValue.Equals(System.DBNull.Value)) {
myNullValue = System.DBNull;
}

Note:
However note that neither checking null of nor DBNull.value will work if the index is out of range. If we check for an index or a column that doesn't exist, it will throw an index out of range exception.

References:







4 comments:

  1. Thank you, I have recently been searching for information about this topic for ages and yours is the best I have discovered so far.

    PIC Scheme Singapore

    ReplyDelete
  2. Thank you for sharing this powerful article, your explanation is clear and very easy to understand. Please kindly visit our site to get more information about IT solution.
    Online Business

    ReplyDelete