Difference
between null and System.DBNull.Value
-
S.No
nullDBNull1Conceptual Difference:The keyword null represents an invalid reference.Conceptual Difference:The class System.DbNull represents a nonexistent value in a database field.2What 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.3Is 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:
Thank you, I have recently been searching for information about this topic for ages and yours is the best I have discovered so far.
ReplyDeletePIC Scheme Singapore
You are welcome!
ReplyDeleteCheck this one.....How to handle DBNul in Asp.Net
ReplyDeleteLing
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.
ReplyDeleteOnline Business