101

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?

pavanred
  • 12,717
  • 14
  • 53
  • 59
  • 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 Feb 10 '11 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 Feb 10 '11 at 14:46
  • 1
    I'm not sure myself. Here's one answer: http://stackoverflow.com/questions/4488727/what-is-the-point-of-dbnull/4488758#4488758 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 Feb 10 '11 at 15:36
  • 1
    I had an answer here, but I realised it was more suitable for http://stackoverflow.com/questions/4488727/what-is-the-point-of-dbnull/ - so I moved it – Marc Gravell Mar 09 '12 at 12:48

6 Answers6

133

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.

jason
  • 236,483
  • 35
  • 423
  • 525
  • 48
    +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. – C.Evenhuis Feb 19 '13 at 07:33
  • 1
    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" – nicolas May 20 '14 at 14:52
  • 4
    A null reference is perfectly valid. ☺ – IS4 Jul 14 '15 at 00:27
  • 1
    @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. – klenium Aug 26 '20 at 16:43
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.

Heinzi
  • 167,459
  • 57
  • 363
  • 519
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.

Loathing
  • 5,109
  • 3
  • 24
  • 35
  • 2
    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" – Iofacture Apr 05 '19 at 21:02
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

James Michael Hare
  • 37,767
  • 9
  • 73
  • 83
  • 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. – James Michael Hare Feb 10 '11 at 14:37
  • 1
    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 – Marc Gravell Mar 09 '12 at 09:59
  • @MarcGravell Yes, Marc, you are correct. I worded that incorrectly. ASP.NET translates the database null column value into DBNull.Value – James Michael Hare Mar 09 '12 at 14:42
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.

Daniel Mošmondor
  • 19,718
  • 12
  • 58
  • 99
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.

Aliostad
  • 80,612
  • 21
  • 160
  • 208