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

Question

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?

Well, they're unrelated. One is a static instance of a class in System.Data, and the other is a special value signifying the lack of a referent. They have nothing to do with each other. Can you elaborate on what you're confused about? Is your real question "why do DataRows and DataReaders put DBNull.Value inside of themselves instead of null?" — mqp
– mqp, Commented Feb 10, 2011 at 14:37
Well, my it wasn't initially but after learning from what you said, I am curious. Could you tell me why DataRows and DataReaders put DBNull.Value in themselves instead of null? — pavanred
– pavanred, Commented Feb 10, 2011 at 14:46
I'm not sure myself. Here's one answer: stackoverflow.com/questions/4488727/what-is-the-point-of-dbnull/… It's also possible that before nullable value types were around in C#, it would have been more of a hassle to deal with null. — mqp
– mqp, Commented Feb 10, 2011 at 15:36
I had an answer here, but I realised it was more suitable for stackoverflow.com/questions/4488727/what-is-the-point-of-dbnull - so I moved it — Marc Gravell
– Marc Gravell, Commented Mar 9, 2012 at 12:48

jason · Accepted Answer · 2011-02-10 14:42:31Z

138

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.

edited Feb 10, 2011 at 14:42

answered Feb 10, 2011 at 14:36

jason

243k35 gold badges436 silver badges532 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

C.Evenhuis Over a year ago

+1 A practical example: if you use IDbCommand.ExecuteScalar(), it can either return null (no records returned) or DbNull (first column in first record is a 'nonexistent value'). Without DbNull you would not be able to distinguish one from the other.

nicolas Over a year ago

I would strongly recommend to use a language which forbids the use of Null, and does so at absolutely 0 extra cost. life is too short for "null object pattern"

IS4 Over a year ago

A null reference is perfectly valid. ☺

klenium Over a year ago

@C.Evenhuis Well, there is another common advice: a function should return only one type of value. That is why people prefer well-typed TypeScript code. "What is the status of the execution" differs from "What is the computational result". Ie. they could have decided to implement this function somehow else (maybe an out parameter, or an object similar to HTTP request response objs). Sure, this is a not-really wanted complication, but if they had done that, maybe null could be used instead of DbNull.

Heinzi · Accepted Answer · 2011-02-10 14:38:37Z

23

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.

answered Feb 10, 2011 at 14:38

Heinzi

173k61 gold badges386 silver badges554 bronze badges

Comments

Loathing · Accepted Answer · 2012-06-25 22:34:57Z

12

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.

answered Jun 25, 2012 at 22:34

Loathing

5,3123 gold badges28 silver badges41 bronze badges

1 Comment

Iofacture Over a year ago

The sentiment of disgust we share re: your last statement is trumped only by the irony of showing up here to read this and finding out your username is "loathing"

James Michael Hare · Accepted Answer · 2012-03-09 14:43:01Z

5

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

edited Mar 9, 2012 at 14:43

answered Feb 10, 2011 at 14:35

James Michael Hare

38.6k10 gold badges75 silver badges83 bronze badges

3 Comments

James Michael Hare Over a year ago

p.s. You should also use DBNull.Value to pass a null parameter to the database, otherwise it may be interpretted as the parameter was not passed.

Marc Gravell Over a year ago

DBNull is not "what the database returns" - it is simply how ADO.NET chooses to interpret it; personally I'm not sure that this interpretation is very valuable

James Michael Hare Over a year ago

@MarcGravell Yes, Marc, you are correct. I worded that incorrectly. ASP.NET translates the database null column value into DBNull.Value

Daniel Mošmondor · Accepted Answer · 2011-02-10 14:37:38Z

4

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.

answered Feb 10, 2011 at 14:37

Daniel Mošmondor

20k12 gold badges61 silver badges104 bronze badges

Comments

Aliostad · Accepted Answer · 2011-02-10 14:38:16Z

3

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.

answered Feb 10, 2011 at 14:38

Aliostad

81.9k21 gold badges164 silver badges209 bronze badges

Collectives™ on Stack Overflow

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

6 Answers 6

4 Comments

Comments

1 Comment

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related