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);
}

Security Trimming an ASP.NET SiteMapCollection

Yes, I know what everybody is thinking right now, Don't SiteMaps already provide functionality for Security Trimming?   Well they do and they don't.  This functionality is available only if you are using an implementation of the ASP.NET MembershipProvider and RoleProvider.  In the applications that I have to work with in a day to day basis that is rarely the case, I always have to develop a custom security provider using forms or windows authentication that support some far fetched business requirements.   I still however love to use SiteMaps and have rarely seen the need to customize the base SiteMap provider for any other reason.    So, in order to have security trimming and still use and bind the information from my SiteMap I wrote the following block of code that just begged to be re-factored.

Exhibit A - Code that needs (begs) for refactoring.

// do security trimming.
List<SiteMapNode> nodes = new List<SiteMapNode>();
foreach (SiteMapNode node in SiteMap.RootNode.ChildNodes) {
    if (node.Roles.Count == 0) {
        // just add.
        nodes.Add(node);
        continue;
    } else {
        // validate security.
        foreach (var role in node.Roles) {
             if (this.Page.User.IsInRole(role.ToString())) {
                 nodes.Add(node);
                 break;
             }
        }
   }
}

// return list.
this.ListViewTopMenu.DataSource = SiteMap.RootNode.ChildNodes;
this.ListViewTopMenu.DataBind();


I decided that, because I was going to need to reuse this functionality in several places and I wanted to avoid creating a class that would perform this task specifically,  that I should add a new method to the SiteMapCollection class using a C# 3.0 Extension Method. 

Step 1 - Extension Method

public static class SiteMapNodeCollectionExtension {
	
	public static IList FilterByRoles(
		this SiteMapNodeCollection nodeColl, 
		IPrincipal user) {
		
		// collection used to store array values to return to browser
		List nodes = new List();

		// iterate through the node collection to return the valid nodes.
		foreach (SiteMapNode node in nodeColl) {
			if (node.Roles.Count == 0) {
				// just add.
				nodes.Add(node);
				continue;

			} else {
				// validate security.
				foreach (var role in node.Roles) {
					if (user.IsInRole(role.ToString())) {
						// add to collection.
						nodes.Add(node);
						break;
					}
				}
			}
		}
		return nodes;
	}
	
}

 

This blog is not intended to be a tutorial, but I would like to point out the facts that C# Extension methods have to be defined in a static class as a static method and the first parameter must be the class to be extended (Notice the use of the 'this' keyword on the fist parameter).

Step 2 - Refactoring of the client code.

// bind list.
this.ListViewTopMenu.DataSource = SiteMap.RootNode.ChildNodes.FilterByRoles(this.Page.User);
this.ListViewTopMenu.DataBind();

Thank you for reading,

Roberto Hernandez