Thursday, February 2, 2012

NHibernate Samples: Row Versioning with SQL Server Timestamp

Preface

I've recently switched from using FluentNHibernate to NHibernate. The decision was made to reduce the number of dependencies required by a solution. Since NHibernate (3.2+) now includes a means of mapping entities to database tables, I just didn't need to use FNH. The big problem I've run into as a result of the switch is the lack of samples on the 'net. Since I can't do my normal R&D (rip-off and duplicate), I've had to resort to actual learning. These articles are intended as a means to provide me with a way to consolidate what I've copied/learned. They should also serve as a way to give other people a handy reference.

I've also created a solution on github to host the code. Fee free to download it, copy it, recycle it, or ignore it.

The Problem

This post is related to a question on stackoverflow.com, NHibernate 3.2 By Code ClassMapping for Version Property. SQL Server has an 8-byte incremental number, the timestamp, which can be used for row versioning. However, it's not immediately obvious how to map the column in the database to an entity.

Note: DateTime is bad for Timestamp

It's worth noting that a DateTime does not have sufficient resolution to be used as a timestamp. See this stackoverflow question, and this msdn article.

The Solution

Since a SQL timestamp is equivalent to the C# byte[] type, NHibernate needs help making the translation. The solution is using a custom IUserVersionType. Conveniently, there is already an implementation buried within the NHibernate source code: the BinaryTimestamp. Using this class, it's possible to map an entity's property to the table's column.

The custom type

This type tells NHibernate how to handle the conversion between the SQL timestamp type and C# byte[] type.

    public class BinaryTimestamp : IUserVersionType
    {
        #region IUserVersionType Members

        public object Next(object current, ISessionImplementor session)
        {
            return current;
        }

        public object Seed(ISessionImplementor session)
        {
            return new byte[8];
        }

        public object Assemble(object cached, object owner)
        {
            return DeepCopy(cached);
        }

        public object DeepCopy(object value)
        {
            return value;
        }

        public object Disassemble(object value)
        {
            return DeepCopy(value);
        }

        public int GetHashCode(object x)
        {
            return x.GetHashCode();
        }

        public bool IsMutable
        {
            get { return false; }
        }

        public object NullSafeGet(IDataReader rs, string[] names, object owner)
        {
            return rs.GetValue(rs.GetOrdinal(names[0]));
        }

        public void NullSafeSet(IDbCommand cmd, object value, int index)
        {
            NHibernateUtil.Binary.NullSafeSet(cmd, value, index);
        }

        public object Replace(object original, object target, object owner)
        {
            return original;
        }

        public System.Type ReturnedType
        {
            get { return typeof(byte[]); }
        }

        public SqlType[] SqlTypes
        {
            get { return new[] { new SqlType(DbType.Binary, 8) }; }
        }

        public int Compare(object x, object y)
        {
            var xbytes = (byte[])x;
            var ybytes = (byte[])y;
            return CompareValues(xbytes, ybytes);
        }

        bool IUserType.Equals(object x, object y)
        {
            return (x == y);
        }

        #endregion

        private static int CompareValues(byte[] x, byte[] y)
        {
            if (x.Length < y.Length)
            {
                return -1;
            }
            if (x.Length > y.Length)
            {
                return 1;
            }
            for (int i = 0; i < x.Length; i++)
            {
                if (x[i] < y[i])
                {
                    return -1;
                }
                if (x[i] > y[i])
                {
                    return 1;
                }
            }
            return 0;
        }

        public static bool Equals(byte[] x, byte[] y)
        {
            return CompareValues(x, y) == 0;
        }
    }

An example table definition


This is an example table. Note the name of the timestamp column. Our entity will use a differently named property to store the value in memory.
create table Orders
(
 OrderId bigint primary key identity
 ,Comments varchar(max) not null default ''
 ,DatePlaced datetime not null default getdate()
 ,LastModified timestamp not null
)

An example model/entity

    public class Order
    {
        public virtual long OrderId { get; set; }
        public virtual string Comments { get; set; }
        public virtual DateTime DatePlaced { get; set; }
        public virtual byte[] Version { get; set; }

        public override string ToString()
        {
            return string.Format("Order ({0}) - {1}", OrderId, DatePlaced);
        }
    }

An example of the mapping

The version statement contains the instructions for NHibernate. mapper.Generated(VersionGeneration.Always) tells NHibernate that SQL Server will handle generation of the value. mapper.Column("LastModified") tells NHibernate that the name of the column is different from the name of the property. mapper.Type<BinaryTimestamp>() tells NHibernate what class to use in converting the sql timestamp type to a C# byte[] and vice versa.

    public class OrderMap : ClassMapping<Order>
    {
        public OrderMap()
        {
            Table(DatabaseTable.Orders);

            Id(order => order.OrderId, mapper => mapper.Generator(Generators.Identity));
            
            Property(order => order.Comments);
            Property(order => order.DatePlaced);
            
            Version(order => order.Version, mapper =>
                          {
                              mapper.Generated(VersionGeneration.Always);
                              mapper.Column("LastModified");
                              mapper.Type<BinaryTimestamp>();
                          });
        }
    }