Twitter Feed Popout byInfofru

OverrideThis.com

Adventures in .NET Software Craftsmanship!

NHibernate Audit w/Database Server Time

Disclaimer: This articles highlights a workaround not a best practice. In fact, if you are so inclined, please provide me with your feedback on how you would have solved this problem differently.

 

At a recent consulting gig a client’s IT department made the very common request that we store classic audit trail information in all database records.  What that means, at least to me, is that every single record in the database is going to have a [CreatedBy], [CreatedDate], [UpdatedBy], and [UpdatedDate] fields.  I found a lot of help on the internet on how to implement this by using NHibernate and its Event Model introduced (ported from Hibernate) in the 2.0 version.

 

These are some of the articles I based my solution on:

http://ayende.com/Blog/archive/2009/04/29/nhibernate-ipreupdateeventlistener-amp-ipreinserteventlistener.aspx

http://nhforge.org/wikis/howtonh/creating-an-audit-log-using-nhibernate-events/revision/1.aspx

 

Both articles are pretty much self explanatory and solve all the business requirements except one, and I quote “All dates in the audit database fields must be the Database Server Time not the Application Server Time”.  Now this might sound like a simple requirement that can be solved this by defining custom SQL queries for insert, update and delete operations in the NHibernate mapping files but doing so will introduce the following problems:


1.- Writing custom xml mapping files defeats the purpose of using FluentNHibernate and its AutoMapping capability.

2.- Our integration testing strategy that relies on the use of SQLite would be become complex as we would have to define custom .hbm mapping files for testing and different ones for production ones that use T-SQL for SQL Server 2008.

 

My solution, albeit probably not a good one, was to define a named query for retrieving the Database Server Time from the application.  The following is the code for registering that named query using the NHibernate’s fluent configuration API.

 

public const string SQLSERVER_HBM_MAPPING_XML =
    @"
        <hibernate-mapping xmlns='urn:nhibernate-mapping-2.2'>
        <sql-query name='GetServerDateTime'>
            SELECT GETDATE();
        </sql-query>
        </hibernate-mapping>
    ";

public const string SQLITE_HBM_MAPPING_XML =
    @"
        <hibernate-mapping xmlns='urn:nhibernate-mapping-2.2'>
        <sql-query name='GetServerDateTime'>
            SELECT datetime('now');
        </sql-query>
        </hibernate-mapping>
    ";

public static void RegisterServerDateTimeMethod(Configuration cfg) {
   if (cfg.Properties["connection.driver_class"].Contains("SQLite"))
        cfg.AddXmlString(SQLITE_HBM_MAPPING_XML);                    
   else
        cfg.AddXmlString(SQLSERVER_HBM_MAPPING_XML);
}

 

The key is to call the RegisterServerDateTimeMethod which registers the named query depending on the connection driver class settings name.   Now to make the process of calling this named query easier I wrote an extension method for NHibernate’s ISession that looks like the following.

 

public static class ISessionExtensions {
    public static DateTime GetServerDateTime(this ISession session) {
        var resultFromServerDateCall = session
     		.GetNamedQuery(NhSessionFactory.SERVER_DATETIME_NAMED_QUERY)
     		.UniqueResult();
        DateTime result;
        if (resultFromServerDateCall is string)
            result = DateTime.Parse((string)resultFromServerDateCall);
        else
            result = (DateTime) resultFromServerDateCall;
        return result;
    }
}

 

The following test illustrates how to use the Extension method.
* Needless to say this test is very brittle and doesn’t really test much, but it gives me some peace of mind as it verifies that the .hbm file mapping syntax is valid.

 

[Test]
public void NhRepositoryCanRetrieveServerDateTime() {
    var serverDate = this.Session.GetServerDateTime();
    Assert.AreEqual(serverDate.Year, DateTime.Now.Year);
    Assert.AreEqual(serverDate.Month, DateTime.Now.Month);
    Assert.AreEqual(serverDate.Day, DateTime.Now.Day);
}