How do we solve all this "Conversion from type DBNull to type String is not valid" nastiness?

Question

In our applications, I can't think of many cases where we care about null string fields. We just want them to show as empty strings in most situations.

So when using the built in ADO.NET dataset / datatables, the error:

Conversion from type DBNull to type String is not valid

is all too common later on in the app when referring to any old string data.

It's a particular problem because it can catch us out so easily (and is often not seen in testing)

I know there are various solutions:

1. Check for .IsXXXNull in all cases

But:

• that's a tedious extra few lines of code all over the app

• if we forget the check, even 1 time in 100, we've got a potential error lurking

2. In the dataset designer, change the NullValue property of the field from the default "Throw Exception" to "Empty"

But:

• we've got to identify and change every table and every string field we add to the dataset designer (remember the default is "Throw Exception")

• if we forget the change even 1 time in 100, we've got a potential error lurking

3. Avoid storing Nulls in the base data

But:

• we've got to identify and change every table and every string field we add to the database

• we don't always have that kind of control over the base data

4. Don't use the dataset designer, pull data into our own class objects, deal with all the DBNull messiness in our own code

But:

• yes, we do do that for our "major" tables. but the dataset designer is a nice, quick way to pull in a picklist or lookup table, where we only really need the default data behaviour

5. Use a Code Generator or Framework like CSLA instead of DataSets

But:

• big step to solve a small problem ... the second answer for the top ranked question on SO tagged CSLA mentions: "It's cons are that it has a bit of a learning curve."

Answer 1

This is why DataSets and DataTables are:

1. Good for quickly putting together an application that uses simple data access (as you mention).

2. Not good for a well designed enterprise application - there are too many simplifications and generalisations.

I have found that using a proper data-binding aware object model almost always trumps using DataSets and DataTables, and they can have all (and more of!) the funcionality, ease-of-use and speed of using DataSets and DataTables.

And you don't run into issues like these, because your business model is not tightly coupled to your database structure.

I have yet to meet somebody who has used a framework like CSLA.NET and wanted to go back to DataSets and DataTables.

Answer 2

5. (or maybe 4b)

Use a framework that provides the NULL functionality for you in your data access class. The aforementioned CSLA.NET framework (by Riko) uses a SafeDataReader class, which works exactly like a DataReader, but can convert all NULLs into empty values for your data access and business logic layers.