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."

Floorer

Regarding 3. I understand that you don't always have control over it but how can your internal fields be a problem? Are fields just added without documentation on why null is allowed and what the difference from empty string is? – Elliottellipse

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.

Booth

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.

Bigot

... two mentions for CSLA. I have the Lhotka book, too. But offputtingly 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." – Floorer

Well, I first came across CSLA.NET when I joined a new team that used it and built out their own framework around it. I didn't read the book, nobody had to show me, I could see that it made sense, was intuitive to use and "just worked". The Lhotka book is mostly for finding out what's going on under the covers. In the realm of learning curves it isn't too steep I think, it just expands on standard OO, component-based programming, data access, GUI and other ideas. – Booth