One of the most aggravating things about .NET is the false distinction between nulls. While semantically there is no difference between the three main types of nulls, developers are required to track them separately. As I have never seen anyone choose to use more than type of null in the same variable, I can’t help but think it is simply a horrible mistake.
Converting from CLR and Nullable nulls is no big deal. Not only is it rare, boxing and unboxing automatically takes care of the conversion. The real issue is going from DB nulls to the other two types. Here is some of the gyrations that VB and C# developers have to go through when reading from a DataTable.
name = row.IsNull("Name") ? null : (string)row["Name"]; name = row["Name"] != DBNull.Value ? null : (string)row["Name"]; name = row["Name"] as string;
name = If(row.IsNull("Name"), Nothing, CStr(row("Name"))) name = If(row("Name") IsNot DBNull.Value, Nothing, CStr(row("Name"))) name = TryCast(row("Name"), String)
As you can see, this can get quite tedious very quickly. And what’s worse, the shortest versions only work for strings. All the other primitive data types like DateTime and Int32 are structs, and thus don’t qualify. What we get instead is this mess.
age = If(row.IsNull("Age"), Nothing, CInt(row("Age"))) age = If(row("Age") IsNot DBNull.Value, Nothing, CInt(row("Age")))
Can you see the bug? It’s subtle, but if you think about it for a moment it makes sense. The If expression is inferring the return type Int32, not Nullable<Int32>, because that is the only type it sees. It is the = symbol that implicitly converts it from an Int32 to a Nullable<int32>. As for the Nothing keyword, this is considered to be a 0 in this context.
Since C# lacks an equivalent to Nothing, only the semantically valid version will compile. Without inference to add us, we have to use the slightly longer forms.
age = row.IsNull("Age") ? new int?() : (int)row["Age"]; age = row["Age"] == DBNull.Value ? new int?() : (int)row["Age"];
age = If(row.IsNull("Age"), New Integer?, CInt(row("Age"))) age = If(row("Age") IsNot DBNull.Value, New Integer?, CInt(row("Age")))
There is another variant I see people try to use from time to time, especially with C#.
name = Convert.ToString(row["Name"]); age = Convert.ToInt32(row["Age"]);
These two lines look reasonable, but both are wrong. The first converts DB Nulls not into a null string as expected, but rather an empty string. The second throws an exception when DB Nulls are encountered.
What should happen
Ideally Microsoft would just add an explicit cast from DBNull to whatever reference or nullable type you are targeting. But that isn’t very likely after all this time.
For VB, I am using an extension of its well known CXxx functions. I would like to name my functions CXxx?, but since that isn’t an option I am using the visually similar CXxx2. For example:
name = CStr2(row("Name")) age = CInt2(row("Age"))
In C# you cannot avoid including the static class name when calling a function. This encourages to turn to extension methods on object.
name = row["Name"].FromDBString(); age = row["Age"].FromDBInt();
If you want them, source code for the CXxx? methods are available on CodePlex.