.NET 2.0 nullable types and database null musings

Question

If .NET 2.0 nullable types was there from version 1, will DBNull.Value not be needed in the first place?

Or RDBMS's null has no bearing with .NET's null? That is DBNull.Value will still be needed anyhow, regardless of .NET version 1 already have nullable types.

Answer 1

null and DBNull.Value are two different things in any .NET version.

For example:

public string CustomerName(int Id)
{
   SqlCommand cmd = new SqlCommand(string.Format("SELECT Name FROM Customer WHERE id = {0}", custId), conn);

   object result = cmd.ExecuteScalar();

   if (result == null)
      return "Customer not found";

   if (result == System.DBNull.Value)
      return "Customer found but name is null";

   return (string) result;
}

Answer 2

Nullable types in .NET allow the variable in question to actually be null. DBNull is a way to say "in another environment, this value was considered to be null". Since we need a way do differentiate between actually null - or "natively" null, as in native to our current runtime - and null in another system we communicate with, nullable types and DBNull serve completely different purposes.

Answer 3

I do not really agree to this.

Nullable types in .NET allow the variable in question to actually be null. DBNull is a way to say "in another environment, this value was considered to be null". Since we need a way do differentiate between actually null - or "natively" null, as in native to our current runtime - and null in another system we communicate with, nullable types and DBNull serve completely different purposes.

This distinction is only required if you do not know if your local variable has allready been fetched from the database. If you know that the variable has been fetched from the database it would be okay to identify null and DBNull.

But the difference is that null in programming languages signals the absence of a value and null == null is true. In databases the null indicates something more like an unknown value. Therefore null == null is false in databases; null equals nothing at all because you cannot tell for an unknown value if it equals another unknown value. I am not sure if DBNull is implemented this way and DBNull.Value == DBNull.Value evaluates to false.

EDIT

I just tested it and DBNull does not behave as exspected. DBNull.Value == DBNull.Value evaluates to true but should yield false with the semantic of the database null.

Answer 4

If System.Data.DataSetExtensions.dll is anything to go by, I'm guessing that DBNull.Value probably wouldn't exist if nullable value types had been available at the time.

Using that library's extension methods on a DataRow that has a "foo" column set to DBNull.Value, you get...

row.Field<int?>("foo");  // returns a nullable int set to null
row.Field<int>("foo");   // throws an InvalidCastException

This is, of course, incredibly convenient when combined with the C# ?? operator, when you want to provide a default value in place of a database null. Personally, I wasted no time implementing similar extension methods on IDataReader/IDataRecord.