Converting Columns To a Collection with a Nhibernate UserType

For some of you who have been using Nhibernate for a while, may found this old news, but I thought this was coolest thing ever (at least this week).

I am working with a legacy database that is,.. unpleasant (yeah we’ll go with that).  I’m adding a new feature that is using a table with the following schema.

CREATE TABLE [dbo].[icpric](
…
           [begqty1] [decimal](15, 5) NULL CONSTRAINT [df_icpric_BEGQTY1]  DEFAULT ((0)),

            [endqty1] [decimal](15, 5) NULL CONSTRAINT [df_icpric_ENDQTY1]  DEFAULT ((0)),

            [factor1] [decimal](15, 5) NULL CONSTRAINT [df_icpric_FACTOR1]  DEFAULT ((0)),

            [unitpr1] [decimal](15, 5) NULL CONSTRAINT [df_icpric_UNITPR1]  DEFAULT ((0)),

            [begqty2] [decimal](15, 5) NULL CONSTRAINT [df_icpric_BEGQTY2]  DEFAULT ((0)),

            [endqty2] [decimal](15, 5) NULL CONSTRAINT [df_icpric_ENDQTY2]  DEFAULT ((0)),

            [factor2] [decimal](15, 5) NULL CONSTRAINT [df_icpric_FACTOR2]  DEFAULT ((0)),

            [unitpr2] [decimal](15, 5) NULL CONSTRAINT [df_icpric_UNITPR2]  DEFAULT ((0)),

            [begqty3] [decimal](15, 5) NULL CONSTRAINT [df_icpric_BEGQTY3]  DEFAULT ((0)),

            [endqty3] [decimal](15, 5) NULL CONSTRAINT [df_icpric_ENDQTY3]  DEFAULT ((0)),

            [factor3] [decimal](15, 5) NULL CONSTRAINT [df_icpric_FACTOR3]  DEFAULT ((0)),

            [unitpr3] [decimal](15, 5) NULL CONSTRAINT [df_icpric_UNITPR3]  DEFAULT ((0)),

…

This is really not that uncommon in legacy apps and it really does not lend it self to a understandable domain.  Essentially  this is a price bracketing scheme in one table.  I wanted to map it to the following domain model, with a collection of price brackets.

public class PricingSchedule
{
    private int _id;
    private string _itemCode;
    private string _popt;
    private string _description;
    private string _method;
    private string _source;
    private string _group;
    private IEnumerable<PricingScheduleBracket> _priceBrackets;

}

I wasn’t sure how I was going to do this, so I did some research to see if anything had been before, or what might work.  I finally decided I would give a Custom User Type a try.  I created a mapping file that used a custom type and passed in the columns I needed to convert to a list.

<property name="PriceBrackets" type="TRACS.Repositories.Impl.PriceBracketUserType, TRACS">
            <column name="begqty1"/>
            <column name="endqty1"/>
            <column name="factor1"/>
            <column name="unitpr1"/>
            <column name="begqty2"/>

</property>

I then created a class that implemented Nhibernates IUserType.  Here are the important methods to make this work.

Specify that the return type is IEnumerable

public Type ReturnedType
{
    get { return typeof(IEnumerable<PricingScheduleBracket>); }
}

The SqlType[] property must match the order and number specified in the mapping. In this case they are easy I had 24 decimal columns

public SqlType[] SqlTypes
{
    get { return new []
        {
            new NHibernate.SqlTypes.SqlType(DbType.Decimal),
            new NHibernate.SqlTypes.SqlType(DbType.Decimal),
            new NHibernate.SqlTypes.SqlType(DbType.Decimal),
            new NHibernate.SqlTypes.SqlType(DbType.Decimal),
            ...

The real work is done in the NullSaveGet method. this gives you the datareader pulling back the data and an array strings with the names of the column aliases for the columns specified in mapping file.  You can take these fields and transform them any way you want.  Don’t forget to account for possible null values.

public object NullSafeGet(IDataReader rs, string[] names, object owner)
{
    List<PricingScheduleBracket> brackets = new List<PricingScheduleBracket>();
    brackets.Add(new PricingScheduleBracket(getValue(rs[names[0]]), 
                                            getValue(rs[names[1]]), 
                                            getValue(rs[names[2]]), 
                                            getValue(rs[names[3]])));

    brackets.Add(new PricingScheduleBracket(getValue(rs[names[4]]),
                                            getValue(rs[names[5]]),
                                            getValue(rs[names[6]]),
                                            getValue(rs[names[7]])));
    ...

return brackets;

}

There is a corresponding NullSaveSet to set the values.  I am dealing with read-only values, so I left it empty.

Using techniques like this, you can use Nhibernate to effectively transforms even the most dificult database schemas into a domain that is coherent and truly models the reality it is trying to represent.