What is the difference between null and System.DBNull.Value?

Is there any difference between null and System.DBNull.Value? If yes, what is it?

I noticed this behavior now -

while (rdr.Read())
{
    if (rdr["Id"] != null) //if (rdr["Id"] != System.DBNull.Value)  
    {
        int x = Convert.ToInt32(rdr["Id"]);
    }
}

While I retrieve data from the database using a sql datareader, though there is no value returned if(rdr["Id"] != null) returned true and eventually threw an exception for casting a null as integer.

But, this if I use if (rdr["Id"] != System.DBNull.Value) returns false.

What's the difference between null and System.DBNull.Value?

Answers


Well, null is not an instance of any type. Rather, it is an invalid reference.

However, System.DbNull.Value, is a valid reference to an instance of System.DbNull (System.DbNull is a singleton and System.DbNull.Value gives you a reference to the single instance of that class) that represents nonexistent* values in the database.

*We would normally say null, but I don't want to confound the issue.

So, there's a big conceptual difference between the two. The keyword null represents an invalid reference. The class System.DbNull represents a nonexistent value in a database field. In general, we should try avoid using the same thing (in this case null) to represent two very different concepts (in this case an invalid reference versus a nonexistent value in a database field).

Keep in mind, this is why a lot of people advocate using the null object pattern in general, which is exactly what System.DbNull is an example of.


From the documentation of the DBNull class:

Do not confuse the notion of null in an object-oriented programming language with a DBNull object. In an object-oriented programming language, null means the absence of a reference to an object. DBNull represents an uninitialized variant or nonexistent database column.


DBNull.Value is annoying to have to deal with.

I use static methods that check if it's DBNull and then return the value.

SqlDataReader r = ...;
String firstName = getString(r[COL_Firstname]);

private static String getString(Object o) {
   if (o == DBNull.Value) return null;
   return (String) o;
}

Also, when inserting values into a DataRow, you can't use "null", you have to use DBNull.Value.

Have two representations of "null" is a bad design for no apparent benefit.


DBNull.Value is what the .NET Database providers return to represent a null entry in the database. DBNull.Value is not null and comparissons to null for column values retrieved from a database row will not work, you should always compare to DBNull.Value.

http://msdn.microsoft.com/en-us/library/system.dbnull.value.aspx


DataRow has a method that is called IsNull() that you can use to test the column if it has a null value - regarding to the null as it's seen by the database.

DataRow["col"]==null will allways be false.

use

DataRow r;
if (r.IsNull("col")) ...

instead.


Null is similar to zero pointer in C++. So it is a reference which not pointing to any value.

DBNull.Value is completely different and is a constant which is returned when a field value contains NULL.


Need Your Help

Set CORS header in Tomcat

tomcat header cors

I had a static website hosted by Tomcat.

Xcode 4: How do you view the console?

iphone objective-c xcode4

I can't seem to find a way to have the console run (to show NSLog comments) in XCode 4. The normal method for the previous version of XCode does not work. Does anyone have an idea of how to accompl...