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

c# null dbnull
pavanred picture pavanred · Feb 10, 2011 · Viewed 98.5k times · Source

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?

Answer

jason picture jason · Feb 10, 2011

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.

Related questions

C# ADO.NET: nulls and DbNull -- is there more efficient syntax?

I've got a DateTime? that I'm trying to insert into a field using a DbParameter. I'm creating the parameter like so: DbParameter datePrm = updateStmt.CreateParameter(); datePrm.ParameterName = "@change_date"; And then I want to put the value of the DateTime? …

Read More
How to check for NULL in MySqlDataReader by the column's name?

How can I check for a NULL value in an open MySqlDataReader? The following doesn't work; it's always hitting the else: if (rdr.GetString("timeOut") == null) { queryResult.Egresstime = "Logged in"; } else { queryResult.Egresstime = rdr.GetString("timeOut"); } rdr.IsDbNull(int i) …

Read More
How to safely cast nullable result from sqlreader to int?

I have a table which contains null values and I need to get data from the table using SqlDataReader. I can't figure out how I can safely cast DBNull to int. I'm doing it in this way at the moment: ... …

Read More