Nullable Datatables – another gripe

This is sorta old news, but my last Asp.net gripe put me in the mood to continue with another longer-term gripe I’ve had. Don’t misunderstand, I love .Net technology, but the few annoyances I have with the technology seem to drive me nuts.

So this time, I’m talking about datatables, and specifically strongly typed datatables via the new-as-of-2.0  DataTableAdapters.

Strongly typed datatables work fine when bound to .Net UI components such as asp.net or winform grids, because the binding mechanism knows how to check for null values in the table without blowing things up. This is accomplished via calling a IsColumnNameNull() method for each column in the table row, and then only accessed the column value if it is not null.

The new DataTableAdapters allow attaching sql queries directly to the strongly typed datatable, so these become a quick-n-dirty data access method for things beyond just binding to visual components. I’ve built numerous apps that work with these adapterd in business object layers that never see the UI. But the pain being that any time in code I wish to reference a column value, I am back to having to write code like this:

int? MyValue = SomeTableRow.IsSomeColumnNull()? (int?)null:SomeTableRow.SomeColumn;

or alternatively:

int MyValue = 0; //assign a default value
if(!SomeTableRow.IsSomeColumnNull())
    MyValue = SomeTableRow.SomeColumn;

Let’s do some math. If your table has 20 columns that are non-nullable (varchar/strings can be configured to not need this check), and your datatable is referenced in 10 places in your app, this is 200 places you have to write this mindnumbing code. My last database had about 10 tables to work with, so I could estimate I wrote this about 2000 times. And each one is unique, so you can’t cut an paste it very well (thank God for intellisense making this just a bit easier).

The bigger problem with this… the *HUGE* problem actually… is that if you don’t put this code in all the right places, you will produce an application that will compile fine but could have horrible bugs that may not show up for years. The following code compiles without problem:

int MyValue = SomeTableRow.SomeColumn;

SomeColumn is an int property on the datatable’s datarow, and thus the compiler sees no problem with this line of code. And if your database always has data in SomeColumn and never encounters a null, this will run fine.

However, if the column does allow nulls in the actual database, and a null winds up in there, this will throw an exception at runtime when this tried to access the null value.

With the release of Linq to Sql, we now have a easily generated object representation of the database tables similar to what the typed datatables used to provide. The linq versions support nullable fields, so that any database field is represented by a nullable version of the base type it represents. So now the equivalent code as above in using a linq table is:

int? MyValue = SomeTableRow.SomeColumn;

This sample uses a nullable int for MyValue, so that it will handle a null value just fine. But even if you want to keep the original non-nullable MyValue variable, you can still very easily do this:

int MyValue = SomeTableRow.SomeColumn.GetValueOrDefault();  

And this will use the nullable default method to return a default int value if it is null.

Not only are both of these nullable methods much cleaner and easier to use, the (again HUGE) benefit is that they are safe – if it compiles, it will not throw an exception at runtime… for nulls, anyway.

I had hoped that MS would add nullable column handling to these datatables in Visual Studio 2008 when it was released.. unfortunately not. And I’ve already had another project since 2008 release where I had to cuss my way through a huge pile of IsColumnNull()’s and the subsequent app blowups from the ones I managed to miss. I know developers have been asking Microsoft to add this feature for a long time, and it’s not a difficult thing to do, PLUS it adds so much safety to the compiled code… If this is good enough for Linq to Sql, why not for out trusty old DataTables? I don’t understand why this has dragged on so long.