Do you care about the null values?

In other words, do your business requirements specify that there's a difference between an empty field and one where the user never entered a value?

This is what null values are for - to make this distinction. Though it may be small consolation, if you don't care about this difference then your database design is flawed.

If you don't care about this difference then my first recommendation is to fix the database.

Of course that isn't the real world, and I realize that. We're typically stuck with ill-designed databases...

There's the whole thread with answers about how to remove nulls, which include several good techniques. I hate nulls. 90%+ of all apps out there have no need for them but must deal with them due to poor database design. To this end, in my business objects book I created a SafeDataReader, which wraps any real data reader (such as SqlDataReader) and removes null values before your code ever sees them. This way all the null-handling is consolidated into a framework component and doesn't clutter up your business logic.

On the other hand, if you DO care about the difference between an empty field and one where the user has never been, then you NEED null values all the way from the database through to the UI. These values (actual, empty or null) need to flow from UI to database and back again. Ultimately it is the UI that knows whether the user ever hit a field, entered an empty value or a real value. That information must be captured by the UI and passed down to the database for storage.

Since the normal data types (Integer, String, etc) don't have the concept of being null, this isn't necessarily trivial. You have three primary options.

1) You can use the Object type for all your variables. Slow and error prone (due to late binding), but it easily solves the problem of passing null around.

2) You can use the Sql data types for all your variables. Actually use the Sql data type for int instead of Integer for instance. This doesn't feel natural, but can be made to work, and these data types understand the concept of null.

3) You can create your own data types - like NullableInteger - which understand the concept of being null and also offer similar functionality to the real data type. This is slower than normal code, but isn't error prone like using Object. I did this with dates in my business objects book by creating a SmartDate class that is basically a nullable date value (which also has the beneficial side-effect of supporting data binding more easily than the normal Date type).

Rocky

Rockford Lhotka

Author of 'Expert One-on-One Visual Basic.NET Business Objects'

rocky@lhotka.net

http://www.lhotka.net