WPF DataBinding and DBNulls

After spending a few hours with ADO.NET, typed dataset, and some WPF bindings I was feeling like I’d been 5 rounds with Mike Tyson.

Well, it wasn’t that bad, but it just felt that bad.

The trouble is the behaviour of ADO’s typed datasets when dealing with value types that can be NULL on the database. The implementation of this in ADO leaves much to be desired when interfacing with the real world.

Take the code I was working on today.  I created a value converter binding that converts null values to Visibility.Collapsed. As an example think of an address label that has 4 lines of address – if lines 2 & 3 are missing I want to collapse them so line 4 appears immediately under line 1.

The example above work ok, because strings are reference types and you can set how ADO.NET’s typed dataset behaves when the  column hold a null value.

The default behavior is to throw an exception when you attempt to read the value. Not ideal.

If you try to change the behaviour for Value Types such as the Int32 above you’ll get the standard error message that the property value is invalid for that type :

So, if you have a WPF binding that is bound directly to the contents of a ADO.Net data row, the binding will fail with an exception – not really what i’d like. Reference types are ok, we can simply change the default behaviour of NullValue for the column to (Null) and all will be well.

Since there’s no way to change the behaviour of ADO.Net’s properties, we must resort to using an object to wrap the data row (or if you’re feeling adventurous, you could always add to the partial strongly typed datarow – but you’d need to use a different name to represent your type)

Using a standard C# Class to wrap the data row, we’re basically mimicking the Active Record pattern (wrapping a Database Column with a custom business object). Well, almost, we still have to resort to using the rest of ADO to add our class to a dataset for writing out to the Database (unless we go for something like Castle Active record 😉 )

With a little code like the following we can wrap our column :

public class TestClass
{
    private ExampleDS.CustomersRow dataRow;

    public int? CustomerNumber {
        get {
            if (dataRow.IsCustomerLastOrderNumberNull())
                return null;
            else
                return dataRow.CustomerLastOrderNumber;
        }
        set {
            if (value.HasValue)
                dataRow.CustomerLastOrderNumber = value.Value;
            else
                dataRow.SetCustomerLastOrderNumberNull();
            }
    }
}

So, by using nullable types, we can convert the nasty DBNull into a real null making the interface to our data consistent across both value types and reference types.

Now when you bind to the data in your own class (after implementing INotifyPropertyChanged I hope!), you wont see any exceptions being thrown when the binding engine attempts to retrieve a real value type from a database column containing a DBNull!

If application is small and you’re binding to dataset data directly (even though the ADO classes do support property notification changes – See Beatriz Costa’s blog for more about that)  then it may not be worth the extra effort in making the data’s returned values consistent, but on a large application, its almost certainly worth making custom business object , or using one of the many ORM tools like iBatis,nHibernate or Castle’s Active Record, depending on your circumstances!