Setting Microsoft SQL's Context_Info

In this recipe, we will show you how to use Microsoft SQL Server's Context_Info to provide the current username to your audit triggers.

Getting ready

• Complete the previous recipe, Generating trigger-based auditing

• Download Ninject.dll and CommonServiceLocator.NinjectAdapter.dll from the Ninject project at http://ninject.org

• Download Microsoft.Practices.ServiceLocation.dll from the Microsoft patterns and practices team available at http://commonservicelocator.codeplex.com/

How to do it…

1. Add a reference to Ninject.dll, CommonServiceLocator.NinjectAdapter.dll and Microsoft.Practices.ServiceLocation.dll.

2. Add the following IAuditColumnSource implementation:

   public class CtxAuditColumnSource : IAuditColumnSource 
   {
   
   
     public IEnumerable<AuditColumn> 
       GetAuditColumns(Table dataTable)
     {
       var userStamp = new AuditColumn()
       {
         Name = "AuditUser",
         Value = new SimpleValue()
         {
           TypeName = NHibernateUtil.String.Name
         },
       ...