Nullable Types and Database nulls…how, exactly?

I love the concept of Nullable<T> being able to deal with null data coming out of the database – especially ints and datetimes. The only problem is that I cannot figure out how to leverage Nullable<T> with data coming from a database. I still have to test for null. I have also talked with Sahil Malik at length about this. He is just as confused. I have tried it in a number of different spots — from the data layer to the business layer —  with no luck.

Let’s say I’m working with a Nullable<Int32>. I have created a SqlDataReader from some SQL Server data.

Nullable<Int32> does not seem to grok System.dbNull. (Invalid Cast). That means I cannot assign mySqlDataReader.item(“myintfield”) directly to my Nullable<Int32> when it is null.

Though you *can* cast an Int to a Nullable<Int>, you cannot use DataReader’s GetInt32 method on a null database value. (SqlNullValueException). Therefore I can’t get at the null value this way either (without explicitly testing for Null myself).

So, let’s say I load the data into a DataTable.

I happen to know that my null integer is in the “someintdata” column of my 2nd row – so I am just coding specifically to test that data. This column is an Integer.

.NET will not implicitly  cast myTable.Rows(1).Item(“someintdata”) into my Nullable<Int>. It won’t even compile and helpfully suggests that I explicitly cast it.

Nor can I explicitly cast it. That attempt throws an InvalidCastException. It doesn’t like the null.

So it seems that no matter what,  I still have to test for Null before I can extract the data. And if I have to do that, what is the Nullable<T> buying me in this case?

(This is one of those posts that I worry I have *really* missed something obvious and am going to look like a total fool, but I am just going to go for it anyway…)

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.