Posts tagged ‘NHibernate’

Audit Trail with NHibernate Using a Custom log4net Appender

I was originally using NHibernate event listeners to do an audit trail, and I might post on that some day. My issue with that was, you get your entities when intercepting. You can save off what’s going with your entites, but I really wanted to save the actual query that NHibernate generates. I wanted to log the same thing that NHProf shows; the actual SQL query.

The way to do this is to create a custom log4net appender that inserts info into your audit table. You need to hook into the NHibernate logs to do this. This is how I did it.

First, create the audit table.

CREATE TABLE [dbo].[Audit]
(
[Id] INT NOT NULL IDENTITY,
[User] NVARCHAR( 100 ) NOT NULL,
[Trail] NVARCHAR( MAX ) NOT NULL,
[Created] DATETIME NOT NULL
)
ALTER TABLE [dbo].[Audit]
ADD CONSTRAINT [PK_Audit_Id]
PRIMARY KEY (Id)
view raw Audit.sql hosted with ❤ by GitHub

Next, create the log4net appender.

public class NHibernateAuditAppender : AppenderSkeleton
{
private static IKernel kernel;
protected override void Append( LoggingEvent loggingEvent )
{
#if !DEBUG
if( loggingEvent.RenderedMessage.StartsWith( "SELECT" ) )
{
return;
}
#endif
var session = kernel.Get<ISession>();
var audit = new Audit
{
Created = DateTime.UtcNow,
Trail = loggingEvent.RenderedMessage,
User = Thread.CurrentPrincipal.Identity.Name
};
session.Save( audit );
}
}
view raw gistfile1.cs hosted with ❤ by GitHub

I am using Ninject to get the current NHibernate session being used. All that you really need to know is, get a session from the session factory. The Append method passes in a LoggingEvent. The data that you need from here is the RenderedMessage. The is the formatted message that is to be logged. Create an audit object and save it off to the database. This assumes that you have a mapping set up for for the Audit object to the Audit table. Next, we need to actually hook into the NHibernate logs.

public class NHibernateAuditAppender : AppenderSkeleton
{
private static IKernel kernel;
protected override void Append( LoggingEvent loggingEvent )
{
#if !DEBUG
if( loggingEvent.RenderedMessage.StartsWith( "SELECT" ) )
{
return;
}
#endif
var session = kernel.Get<ISession>();
var audit = new Audit
{
Created = DateTime.UtcNow,
Trail = loggingEvent.RenderedMessage,
User = Thread.CurrentPrincipal.Identity.Name
};
session.Save( audit );
}
public static void RegisterAppender( IKernel kernel )
{
NHibernateAuditAppender.kernel = kernel;
var repository = (Hierarchy)LogManager.GetRepository();
var appender = new NHibernateAuditAppender { Name = "NHibernateAuditAppender" };
var logger = (Logger)repository.GetLogger( "NHibernate.SQL" );
logger.Level = Level.Debug;
logger.Additivity = false;
logger.AddAppender( appender );
appender.ActivateOptions();
repository.Configured = true;
}
}

The RegisterAppender static method was added. Here we get the NHibernate.SQL logger. This is the logger that outputs the actual query that NHibernate generates. Add our appender to the logger, and it’s basically done. You can lookup the other details if you like. :)

NHibernate Mapping to System.Drawing.Color

In my POCO (Plain Old CLR Object) class, I had a property for FontColor that is of type System.Drawing.Color. I wanted this to map to an HTML string value in the database. i.e. #ffffff

Basically, I wanted to do a type conversion from #ffffff to System.Drawing.Color. At fist I didn’t realize that this sort of type conversion was built-in to NHibernate via the interface IUserType. I thought you had to use you new custom type in the POCO, but I found that you can just use the IUserType implementation to map to the native .NET type.

Here is how I did it…

First, create the custom type that implements IUserType. This will be used to do the type conversion from string to Color.

public class ColorUserType : IUserType
{
public object Assemble( object cached, object owner )
{
return cached;
}
public object DeepCopy( object value )
{
return value;
}
public object Disassemble( object value )
{
return value;
}
public new bool Equals( object x, object y )
{
if(ReferenceEquals(x, y ) )
{
return true;
}
if( x == null || y == null )
{
return false;
}
return x.Equals( y );
}
public int GetHashCode( object x )
{
return x == null ? typeof( Color ).GetHashCode() + 473 : x.GetHashCode();
}
public bool IsMutable
{
get
{
return true;
}
}
public object NullSafeGet( IDataReader rs, string[] names, object owner )
{
var obj = NHibernateUtil.String.NullSafeGet( rs, names[0] );
if( obj == null )
{
return null;
}
return ColorTranslator.FromHtml( (string)obj );
}
public void NullSafeSet( IDbCommand cmd, object value, int index )
{
if( value == null )
{
( (IDataParameter)cmd.Parameters[index] ).Value = DBNull.Value;
}
else
{
( (IDataParameter)cmd.Parameters[index] ).Value = ColorTranslator.ToHtml( (Color)value );
}
}
public object Replace( object original, object target, object owner )
{
return original;
}
public Type ReturnedType
{
get
{
return typeof( Color );
}
}
public SqlType[] SqlTypes
{
get
{
return new[] { new SqlType( DbType.StringFixedLength ) };
}
}
}
view raw ColorUserType.cs hosted with ❤ by GitHub

Next, create the POCO.

public class MyClass
{
public int Id { get; protected set; }
public Color FontColor { get; set; }
}
view raw MyClass.cs hosted with ❤ by GitHub

Next, create the mapping file. I’m using FluentNhibernate here.

public class MyClassMap : ClassMap<MyClass>
{
public MyClassMap()
{
SetupMapping();
}
private void SetupMapping()
{
Id( m => m.Id );
Map( m => m.FontColor ).CustomTypeIs<ColorUserType>();
}
}
view raw MyClassMap.cs hosted with ❤ by GitHub

Now when you get an instance of MyClass from NHibernate, the string color stored in the database will be converted to a System.Drawing.Color.

The New World of NHibernate

NHibernate has always been a great tool for data access but there are a couple things that have always bugged me; the mapping files and the non-type-safe querying. There are now some great tools out there that solve these problems.

Mapping files has always been a pain for me. It’s not the most intuitive thing and it’s really easy to miss-type something in the xml hbm files. I have started using Fluent Nhibernate to do my mapping files, which has made my life with NH a lot better. Not only do I no longer have to edit xml files, but now the mappings are type-safe. Fluent Nhibernate is a tool that I don’t think I could live without now.

POCO:

public class MyClass
{
public virtual int? Id { get; private set; }
public virtual string Name { get; set; }
public virtual MyReference MyReference { get; set; }
public virtual IList<MyReference> MyReferenceCollection { get; private set; }
}
view raw MyClass.cs hosted with ❤ by GitHub

Mapping:

public class MyClassMap : ClassMap<MyClass>
{
public MyClassMap()
{
Id( m => m.Id );
Map( m => m.Name );
References( m => m.MyReference );
HasMany( m => m.MyReferenceCollection );
}
}
view raw MyClassMap.cs hosted with ❤ by GitHub

The other annoying thing is creating queries that aren’t type-safe, which makes doing re-factors a pain. You would think when using the Criteria language instead of HQL that it would be type-safe, but you still are using strings for the column names. I recently found a tool called NHLambdaExtensions which makes doing Criteria queries type-safe. This makes me sleep a little better at night.

// This:
session.CreateCriteria( typeof( MyClass ) ).Add( Expression.Eq( "Name", name ) );
// Becomes this:
session.CreateCriteria( typeof( MyClass ) ).Add<MyClass>( m => m.Name == name ).List();
view raw Criteria.cs hosted with ❤ by GitHub

What about the Linq-To-Everything craze? I’m definitely a person that loves Linq and use it constantly, so naturally I’m wondering when a Linq-To-NHibernate provider will be coming out. Oren Eini had a simple implementation going but didn’t handle many of the complex scenarios (or so I’ve read, and I must say, he’s a coding machine). But don’t fear, Steve Strong has been employed to create a fully functional Linq-To-NHibernate provider. I’m excited to see it come to fruition and can’t wait to actually use it. You can keep up with the progress at Steve’s blog at http://blogs.imeta.co.uk/sstrong.

UPDATE:

[Fluent] NHibernate now comes with a Linq provider and I recommend using that over criteria.