Everybody's favorite topic - NULL
In V1 of .net, value types could not be null. Since, this was contradictory to relational databases use of null, the DataSet needed to have a concept of nullable value types – hence DBNull was invented.
DBNull is simple type in the System namespace. It does not have a constructor, but a static method (DBNull.Value) that returns a singleton instance of the class. Even though it is designed to support relational database scenarios, it does not support 3 part logic. For example equality is supported in a CLR manner:
Console.WriteLine(DBNull.Value == DBNull.Value); // returns true
Setting null for a value type means explicitly setting the value to be DBNull.Value:
DataColumn nullableColumn = new DataColumn("myInt", typeof(int));
table.Columns.Add(nullableColumn);
DataRow row = table.Rows.Add();
row["myInt"] = DBNull.Value;
One can explicitly check if a value is DbNull by comparing to DBNull.Value or calling DataRow.IsNull:
Console.WriteLine(row["myInt"] == DBNull.Value); // True - but bad news
Console.WriteLine(row.IsNull("myInt")); // True - the recommended way
Even though both work, calling DataRow.IsNull() is the recommended method of checking for DBNull valued column values. In fact, even though currently supported, no code should be doing the former. You will see the reason why in a moment.
Setting null for reference types is similar, but a little different in that since either null or DBNull.Value can be provided:
DataColumn nullableReferenceColumn = new DataColumn("myType", typeof(myCustomerType));
table.Columns.Add(nullableReferenceColumn);
row = table.Rows.Add();
row["myType"] = null;
row["myType"] = DBNull.Value;
Note – however, internally the DataSet translates the null value to be DBNull.Value. This can be seen by retrieving the value:
Console.WriteLine(row["myType"] == DBNull.Value); // True - but also bad news
Console.WriteLine(row["myType"] == null); // False - even though reference was set to null
Console.WriteLine(row.IsNull("myType")); // True - the recommended way
From this example, one can see that DataRow.IsNull() will return true whether the value was set to DBNull.Value or null. In a way, IsNull abstracts the DataRow consumer from what specific “null” the underlying value is. This becomes more obvious with the introduction of nullable types in V2.
BIG DISCLAIMER – THE FOLLOWING BEHAVIOR IS FROM VISUAL STUDIO 2005 BETA 1 AND IS BROKEN. HENCE IT WILL EITHER BE NOT SUPPORTED OR FIXED IN RTM OF VS 2005.
Now in V2, we have nullable value types through the use of Nullable<T>.
As of Beta 1 of Whidbey, the DataSet allows nullable types to be used as DataSet columns – but with some interesting problems.
If a column is a nullable value type, one can explicit set it to null like reference types in V1:
DataColumn nullableIntColumn = new DataColumn("myNullableInt", typeof(int?));
table.Columns.Add(nullableIntColumn);
DataRow row = table.Rows.Add();
row["myNullableInt"] = (int?)null;
//Note – this is different from setting the value to DBNull:
row["myNullableInt"] = DBNull.Value
In addition, one must set the null value of int? and not reference null. For those of you not completely familiar with nullable type support in C#, the following won’t even compile – which may surprise some:
int? c = (object)null; // compile error Cannot implicitly convert type 'object' to 'int?'.
// An explicit conversion exists (are you missing a cast?)
And casting the null value of int? to be an reference type boxes the value:
int? c = null;
object h = (object)c;
Console.WriteLine(h == null); // false!
Console.WriteLine(((int?)h) == null); // unboxed ... now true.
Remember, when accessing a column value via DataRow[], an object is returned. Henced, boxing happens automatically for nullable types, and the reason behind the following somewhat bizzare behavior with retrieving the nullable value type:
// warning - the following code is broken VS 2005 Beta1 and will change before RTM
Console.WriteLine((int?)row["myNullableInt"] == null); // true – unboxing value
Console.WriteLine(row["myNullableInt"] == null); // false
Console.WriteLine(row.IsNull("myNullableInt")); // false! Broken!
One of the interesting design questions is what to do whether DbNull.Value should be returned for DataRow[] for nullable value types. Current behavior is it is not:
Console.WriteLine(row["myNullableInt"] == DBNull.Value); // false, boxed value is null, but reference is not.
At this point, it may become obvious that unless one is ready to put the following all over their code in cases where the code needs to work independent of column type(or disallow nullable value typed columns), DataRow.IsNull() is by far the better technique:
if (row["myNullableInt"] == null || row["myNullableInt"] == DBNull.Value ||
!((System.INullableValue)row["myNullableInt"]).HasValue)
Console.WriteLine("null"); // value is null
One other interesing problem with the current support in Beta1 is that the AllowDBNull constraint is not enforced:
nullableIntColumn.AllowDBNull = false; //only enforces DBNull, not (int?) null
nullableIntColumn.DefaultValue = (int?)5;
DataRow row2 = table.Rows.Add();
row2["myNullableInt"] = (int?)null; // obviously
As noted before, the DataSet behavior WRT to nullable types will either be changed or not supported for RTM of VS 2005. However, it is very probable that it will be supported some time in the future.
Comments
- Anonymous
March 03, 2005
I like your way of working with null values.... - Anonymous
November 26, 2007
Hi, i tried creating a datacolumn of int nullable type but at runtime it gives me error saying "DataSet does not support System.Nullable <>"Please let me know your suggections on this - Anonymous
November 27, 2007
As rightly mentioned by you,the statement:DataColumn nullableIntColumn = new DataColumn("myNullableInt", typeof(int?));will throw exception. - Anonymous
January 21, 2009
PingBack from http://www.keyongtech.com/603805-nulls-and-data-retrieval-with