Saturday, May 28, 2011

Sql Server concurrency handling using its rowversion field type and Fluent NHibernate Auto Mapping

The model:
public class Product
{        
    public virtual int ProductId { get; set; }

    public virtual string ProductName { get; set; }
    public virtual string WarehouseLocation { get; set; }
    public virtual string LastAuditedBy { get; set; }
    public virtual int Quantity { get; set; }        
    public virtual byte[] TheRowversion { get; set; }
}


The concurrent update scenario:
class Program
{
    static void Main(string[] args)
    {
        var userA = Mapper.GetSessionFactory().OpenSession();
        var userB = Mapper.GetSessionFactory().OpenSession();
        
        // User A and User B happen to open the same record at the same time
        var a = userA.Get<Product>(1);
        var b = userB.Get<Product>(1);

        a.ProductName = "New & Improved" + a.ProductName + "!";

        b.WarehouseLocation = "Behind appliances section";
        b.LastAuditedBy = "Linus";
        b.Quantity = 7;

        userA.Save(a);
        userA.Flush();


        // This will fail. As the rowversion of the same record since it was first loaded, had 
        // already changed (the record was changed by User A first) when it's User B's time to save the same record.
        userB.Save(b);
        userB.Flush();

       
    }
}


DDL:
create table Product
(
ProductId int identity(1,1) not null primary key,
ProductName nvarchar(100) not null unique,
WarehouseLocation nvarchar(100) not null,
LastAuditedBy nvarchar(100) not null,
Quantity int not null,
TheRowversion rowversion not null
) 


Boilerplate code for mapping. If you decide to make all your tables' version column use Sql Server's built-in rowversion field type and use your own name for version column, you must do both line 59 and 65 respectively. Also, add the version convention to your ISessionFactory builder, line 36. The default(convention-over-configuration) rowversion column name when you don't implement line 59,65,36 is Version.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

using NHibernate;
using NHibernate.Dialect;

using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Automapping;
using FluentNHibernate.Conventions;
using FluentNHibernate.Conventions.Instances;

using FluentNHibernate.Conventions.Helpers;

using TestFluentAutomappingWithRowversion.Model;

namespace TestFluentAutomappingWithRowversion
{
    public static class Mapper
    {
        static ISessionFactory _sf = null;
        public static ISessionFactory GetSessionFactory()
        {
            if (_sf != null) return _sf;

            var fc = Fluently.Configure()
                    .Database(MsSqlConfiguration.MsSql2008.ConnectionString(@"Data Source=localhost; Initial Catalog=TestDb; User Id=sa; Password=P@$$w0rd; MultipleActiveResultSets=True"))
                    .Mappings
                    (m =>
                            m.AutoMappings.Add
                            (
                                AutoMap.AssemblyOf<Program>(new CustomConfiguration())
                                    .Conventions.Add<RowversionConvention>()                                
                            )
                    // .ExportTo(@"C:\_Misc\NH")
                    );


            // Console.WriteLine( "{0}", string.Join( ";\n", fc.BuildConfiguration().GenerateSchemaCreationScript(new MsSql2008Dialect() ) ) );
            // Console.ReadLine();

            _sf = fc.BuildSessionFactory();
            return _sf;
        }


        class CustomConfiguration : DefaultAutomappingConfiguration
        {
            IList<Type> _objectsToMap = new List<Type>()
            {
                typeof(Product)
            };
            public override bool ShouldMap(Type type) { return _objectsToMap.Any(x => x == type); }
            public override bool IsId(FluentNHibernate.Member member) { return member.Name == member.DeclaringType.Name + "Id"; }

            public override bool IsVersion(FluentNHibernate.Member member) { return member.Name == "TheRowversion"; }
        }


        class RowversionConvention : IVersionConvention
        {
            public void Apply(IVersionInstance instance) { instance.Generated.Always(); }
        }

    }
}

No comments:

Post a Comment