Sunday, May 22, 2011

NHibernate Concurrency Handling using Sql Server Rowversion

The following code implements concurrency handling on NHibernate; if you are using Sql Server 2005 onwards, it's better to use its built-in rowversion field type. Rowversion maps to byte array on .NET. NHibernate can use this field on WHERE clause whenever it do an update. If there's no record updated, it means that other user updated/deleted the current record first before the current user save the same record. To handle this scenario of non-successfully-saved record, the current user shall receive a message that the current record he/she is attempting to save is already stale. Then inform the user to re-open the updated version of the record he/she is editing


The Object(aka Model):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace NhConcurrentUpdateHandling.Models
{
    public class Product
    {
        public virtual int ProductID { get; set; }
        public virtual string ProductName { get; set; }
        public virtual int Quantity { get; set; }
        public virtual byte[] TheRowversion { get; set; }
    }
}

Relational:
create table Product
(
ProductID int identity(1,1) not null,
ProductName nvarchar(100) not null,
Quantity int not null,
TheRowversion rowversion not null
);

insert into Product(ProductName,Quantity) values
('Keyboard',9)

Mapping:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using FluentNHibernate.Mapping;

using NhConcurrentUpdateHandling.Models;


namespace NhConcurrentUpdateHandling.ModelsMappings
{
    public class ProductMapping : ClassMap<Product>
    {
        public ProductMapping()
        {
            Id(x => x.ProductID);
            Map(x => x.ProductName);
            Map(x => x.Quantity);
            Version(x => x.TheRowversion).Generated.Always();
        }
    }
}


Controller:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

using NHibernate;

using NhConcurrentUpdateHandling.Models;
using NhConcurrentUpdateHandling.ModelsMapper;



namespace NhConcurrentUpdateHandling.Controllers
{
    public class HomeController : Controller
    {
        //
        // GET: /Home/

        public ActionResult Index()
        {
            using (var db = SessionFactoryBuilder.GetSessionFactory().OpenSession())
            {
                var p = db.Get<Product>(1);
                return View(p);
            }
        }

        [HttpPost]
        public ActionResult Index(Product p)
        {

            try
            {
                
                using (var db = SessionFactoryBuilder.GetSessionFactory().OpenSession())
                {
                    var px = db.Merge(p);
                    db.Flush();

                    UpdateModel(px);

                    ViewBag.SuccessfulMessage = "Saved.";

                    return View(px);


                }
                
            }
            catch (StaleObjectStateException ex)
            {
                ViewBag.ErrorMessage = "Your changes is not saved. Please re-open this record to get its latest values. Other user already do something to this record";
                return View(p);
            }
            
        }

    }
}

View:

@model NhConcurrentUpdateHandling.Models.Product

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>

@ViewBag.ErrorMessage

@using (Html.BeginForm()) {
    @Html.ValidationSummary(true)
    <fieldset>
        <legend>Product</legend>

        @Html.HiddenFor(model => model.ProductID)

        <div class="editor-label">
            @Html.LabelFor(model => model.ProductName)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.ProductName)
            @Html.ValidationMessageFor(model => model.ProductName)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Quantity)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Quantity)
            @Html.ValidationMessageFor(model => model.Quantity)
        </div>

        @Html.HiddenFor(x => x.TheRowversion)

        <p>
            <input type="submit" value="Save" />
        </p>
    </fieldset>
}

@if(!string.IsNullOrEmpty(ViewBag.SuccessfulMessage)) {
    <b>@ViewBag.SuccessfulMessage</b>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>


Sample generated UPDATE statement when using SQL Server's rowversion as the means of concurrent update handling:

exec sp_executesql 
N'UPDATE [Product] SET ProductName = @p0, Quantity = @p1 
WHERE ProductID = @p2 AND TheRowversion = @p3',
N'@p0 nvarchar(4000),@p1 int,@p2 int,@p3 varbinary(8000)',
@p0=N'Keyboard',@p1=21,@p2=1,@p3=0x00000000000007E3

Notice that the ORM-generated UPDATE statement not only uses primary key on the WHERE clause, it also include the rowversion as well; so the ORM can determine if the record that was being saved is not modified by other users first. If there's no updated record, it means the rowversion had already changed, this happens when other users saved/deleted the record first. Exception will be thrown, and the simplest course of action is for the late-saver user to honor the record that was saved by the other user, the late-saver user shall re-open the saved record by the other user.



Boilerplate code for database connection: http://www.ienablemuch.com/2011/04/boilerplate-code-for-fluent-nh.html

No comments:

Post a Comment