API's that Suck

October 24, 2009

On the Stupid Inconsistencies between CLR Nulls, DB Nulls, and Nullable Nulls.

Filed under: Uncategorized — Grauenwolf @ 10:28 am

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();

Source Code

If you want them, source code for the CXxx? methods are available on CodePlex.



  1. I agree that the Framework must provide conversions between DBNull and Nullables.

    You wrote “…the shortest versions only work for strings. All the other primitive data types like DateTime and Int32 are structs, and thus don’t qualify”

    The following works:

    object o; int? val;
    o = 42; val = o as int?;
    o = DBNull.Value; val = o as int?;

    But the following won’t compile:

    val = DBNull.Value as int?;

    Comment by B.B. — October 30, 2009 @ 9:19 pm

  2. Isn’t part of the problem that SQL NULLs are semantically incoherent by themselves? They’re sometimes given special three-valued-logic semantics, treating them as meaning “there is some value here but I don’t know what it is”, giving you (10 = null) = Unknown (not False), and sometimes treated as just another value like null in C#.

    Comment by contextfree — November 22, 2009 @ 9:23 pm

    • Most types are “semantically incoherent”. Wehn you see a scalar such as an Integer, what does that really mean? It could be a quantity like a length, an index into something like a list or array, or represent a value in an enumeration. I have to wonder if the people who claim that nulls are too hard to resaon about have just as much trouble with other data types.

      Speaking of which, DateTime is another fun one. In .NET it can be used to represent a time, a date, or a date/time combination. In many languages including VB and T-SQL it is also used to represent a length of time.

      Going back to null, a SQL null is just as semantically ambigious as a CLR null. They both say “no value here” without telling you why.

      Comment by grauenwolf — November 22, 2009 @ 10:26 pm

  3. CLR nulls aren’t incoherent. CLR doesn’t try to give nulls any inherent semantics beyond “another value that you can’t do anything with, other than store and compare for reference equality”, so there’s no inconsistency. They work ok when you do want your type to have an extra value indicating “no value”, the only problem is that they’re included in every reference type whether you want them or not.

    SQL on the other hand does (sometimes) try to give NULL special semantics, but is totally inconsistent about it.

    Comment by contextfree — November 23, 2009 @ 7:47 pm

    • All you did was restate your claim. Show us the special semantics that you have in SQL that you don’t in CLR.

      Comment by grauenwolf — November 23, 2009 @ 7:53 pm

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: