Mapping special null constant values into DBNull in a DataTable

Question

I'm working with an existing object framework that uses special values to represent NULL for primitives int, DateTime, long. Data containing these values are added to a DataTable and displayed in third-party controls such as XtraGrid.

All is fine, except when aggregates are applied to the data. In this case, obviously the special values are processed instead of the NULL entries.

So I think the best solution is to map the values to/from DBNull when putting into the DataRow. I thought about subclassing the DataTable and DataRow, but the base classes don't allow overriding of the accessors.

I could add extra Get/Set functions to the subclasses but this relies on remembering to use them. I could add static helper functions instead of subclassing, but this has the same problem.

Is there a more elegant solution?

Update It's the grid itself that is doing the aggregation, as it has flexible controls to let the user define summaries at run-time. So I think the only real solution is to map to/from DBNull somehow, just looking for an elegant way of doing this.

Answer 1

maybe you can create conditional aggregates with IIF like (silly example):

        DataTable t= new DataTable();
        t.Columns.Add("id");
        t.Columns.Add("id2");
        t.Columns.Add("id3", typeof(int), "IIF(id="+int.MinValue.ToString()+",id2,id+id2)");
        for (int i = 0; i < 5; i++)
        {
            t.Rows.Add(new object[] { i, 2 * i });
        }

        t.Rows.Add(new object[] { int.MinValue, 2000});

Edit: adapted to your comment on other post.

Answer 2

You may create an extension method to help you fill the datatable and convert values to dbnull:

public static class DataExtensions
{
    public static DataRow AddRow(this DataRowCollection rowCollection, params object[] values)
    {
        object[] newValues = new object[values.Length];

        for(int i=0;i<values.Length;i++)
        {
            object value = values[i];
            if (value != null)
            {                    
                Type t = value.GetType();
                //check for min value only for value types...
                if (t.IsValueType)
                {
                    //maybe you can do some caching for that...
                    FieldInfo info = t.GetField("MinValue",
                        System.Reflection.BindingFlags.Static
                        | System.Reflection.BindingFlags.Public
                        );
                    if (info != null)
                    {
                        object o = info.GetValue(null);
                        if (value.Equals(o))  //very important == will return false
                        {
                            value = DBNull.Value;
                        }
                    }
                }
            }
            newValues[i] = value;               
        }

        return rowCollection.Add(newValues);
    }
}

And then you will be able to write something like:

t.Rows.AddRow(a,b,c,d,e);