When creating lambdas (including LINQ expressions) inside a for each loop, don’t use the iterator variable in your lambda. Let me explain with some code:

foreach (Type controllerType in controllerTypes)
{
     kernel.Bind(controllerType).ToSelf().InRequestScope();
     kernel.Bind<IController>().ToMethod(ctx => ctx.kernel.Get(controllerType)).Named(GetName(controllerType));
}

Why am I using a method to get the controller? It just so happens that my AccountController is also a domain event handler for my AccountNameAlreadyUsed event. This goes back to Ayende’s tip in my domain events post: To get a message back to the UI, fire a new event and have the UI listen for it. In this case, I need the UI to complain when the account name they selected is already being used.

In case your mind has wandered to the dark side, throwing exceptions is not an acceptable way of passing messages in an application.

Now, why the odd mappings? Let’s say I bind IController and IHandle<AccountNameAlreadyUsed> to AccountController in the request scope. It doesn’t quite work like you would first expect. You will have one instance of AccountController returned for IController and a separate instance for IHandle<AccountNameAlreadyUsed>.

Instead, I’m saying that for each request for an IController, go get an AccountController, essentially delegating the request to the ToSelf() binding. I have a similar ToMethod() lambda binding for IHandle<AccountNameAlreadyUsed>. Since both interface bindings are fulfilled by the ToSelf binding, only one instance of AccountController is created for the request, instead of two.

So, this explains why I need the lambda in the first place. Why didn’t the code above work?

As it turns out, there was some funny business going on underneath the covers between the foreach iterator and the lambda. Here’s the symptom: No matter which “instance” of the lambda was being referenced, inside the lambda, the iterator variable (controllerType) was always the first value that was iterated. No matter which controller I requested, I always got an instance of AccountController, since it happens to be first alphabetically.

Once you realize what’s going on, the fix is simple. Create another variable and use it inside the lambda instead. So, instead of the code above, we get this:

foreach (Type controllerType in controllerTypes)
{
     var controllerType2 = controllerType;
     kernel.Bind(controllerType).ToSelf().InRequestScope();
     kernel.Bind<IController>().ToMethod(ctx => ctx.kernel.Get(controllerType2)).Named(GetName(controllerType));
}

If, instead of a lambda, we had a LINQ expression, the compiler would generate a warning about this issue – at least in VB.NET. If I hadn’t already seen that warning from LINQ expressions, I would probably still be bug hunting.

Jason

• Fabio has been busy on some really amazing mapping magic.
• Aaron Cure’s NHibernate 2.x Beginners Guide from Packt Publishing will be out in May.
• I’m making progress on Project Nenverse. The concept is similar to Hibernate’s Envers. I’m not ready to announce it to the world just yet, but you want to look over the code or even contribute, it’s out there.
• In an effort to avoid meta-blogging at all cost, I won’t mention the cool new stuff over on the right, or the new text up above.

That’s it for now.

The ALT.NET crowd is almost exclusively C#, except where they’ve moved beyond it.  If I’m going to participate, it’s past time for me to become fluent. Until at least April, I won’t write a single line of VB.NET outside of work.

The Problem

My previous examples have used system_user as the source of user information in the audit log, which required you to impersonate users down to the database level, that is to open the connection in the context of the application user, not some shared generic account. There are several reasons why this is not a good idea:

1. You either don’t have per-user credentials appropriate to create SQL server accounts (typical in public-facing web applications), or are unable to do so for various reasons ranging from security concerns to account management workload.
2. When each user authenticates with their own credentials, this effectively disables connection pooling. Especially in high-volume web scenarios, this will kill performance and put a strain on server resources.
3. I believe the loss of connection pooling will also burn through your license pool with per-user licensed SQL servers. I may be mistaken on this point, as I haven’t worked with a per-user licensed SQL server for the better part of a decade.
4. If you are unwilling to use SQL server authentication, you will most likely have to set up Kerberos constrained delegation so that your database server trusts your web server to authenticate users. This is a royal PITA to implement.

The Trouble With Triggers

As far as input inside a trigger, you only have access to the data being manipulated, system functions, and very little else. You can’t use SELECT statements in triggers either, at least not against normal tables and views.

SQL Context Info

Starting with MS SQL Server 2000, you can associate up to 128 bytes of data with the current connection using the SET CONTEXT_INFO command. Starting with SQL 2005, you can use the CONTEXT_INFO function to retrieve this data. This data is available everywhere, including triggers.

If you use connection pooling (and you probably are), this data is not reset when the connection is reused.

To set your context info data, use the SET CONTEXT_INFO command like so:

DECLARE @Ctx varbinary(128)
SELECT @Ctx = CONVERT(varbinary(128), 'My context data goes here.')
SET CONTEXT_INFO @Ctx

SET CONTEXT_INFO can only be used with variables and constants, meaning you can’t CONVERT data types or concatenate strings or any other calculation in the same command.

To retrieve the context data, use the CONTEXT_INFO function:

DECLARE @CtxData varchar(128)
SELECT @CtxData = CONVERT(VarChar(128), CONTEXT_INFO())
PRINT @CtxData

This will output ‘My context data goes here.\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0’ etc. Note – The \0 is ASCII character 0 (NULL). Why? We’re converting the string to fixed-length binary and then converting it back.

Know Your Roots

There was a time when programmers weren’t protected from the fact that strings are just chunks of memory. Just like programmers in the old days, you have two options: Pascal strings or C strings. You can stuff a length byte at the beginning of your context as in Pascal, or you can search for the null termination of your string as in the C language.

Also, because nchar and nvarchar are UNICODE UCS-2 strings, they use two bytes (of your 128 maximum) per character. char and varchar only use one byte per character, but the character set is limited. It’s a trade-off.

How does this help me?

We can use context info data to get information from our application in to our audit triggers. For example, we can get the current user name from our application or evem some identifying information about the location in our application, such as the name of the controller and action that triggered the event. Just be conscious of the 128 byte limitation. It’s probably better to store an id for more information such as the id of the user account, or an id that references some lookup table with application locations.

The Code, Sir.

We will override the GetConntion() method of DriverConnectionProvider so that every NHibernate connection will have the current username stored in the context.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate.Connection;
using System.Data;

namespace NHibernate.Connection
{
    public class ContextDriverConnectionProvider : DriverConnectionProvider
    {

        public override System.Data.IDbConnection GetConnection()
        {
            var conn = base.GetConnection();
            SetContext(conn);
            return conn;
        }

        private const string CONTEXT_SQL = "declare @Length tinyint\ndeclare @Ctx varbinary(128)\nselect @Length = len(@username)\nselect @Ctx = convert(binary(1), @Length) + convert(varbinary(127), @username)\nset context_info @Ctx";

        protected virtual void SetContext(IDbConnection conn)
        {
            IDbCommand cmd = conn.CreateCommand();
            IDbDataParameter param = cmd.CreateParameter();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = CONTEXT_SQL;

            param.ParameterName = "@username";
            param.DbType = DbType.AnsiString;
            param.Size = 127;
            param.Value = System.Environmnt.UserName;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
        }

    }
}

This will run the following SQL code when NHibernate opens a SQL connection. Note that @username is a variable defined as a parameter on our IDbCommand. Also, I chose to use Pascal strings with the length stored in the first byte.

declare @Length tinyint
declare @Ctx varbinary(128)
select @Length = len(@username)
select @Ctx = convert(binary(1), @Length) + convert(varbinary(127), @username)
set context_info @Ctx

We’ll alter our trigger code to get the username from the context instead of system_user.

set nocount on
declare @Username varchar(127)
declare @Length tinyint
declare @Ctx varbinary(128)
select @Ctx = CONTEXT_INFO()
select @Length = convert(tinyint, substring(@Ctx, 1, 1))
select @Username = convert(varchar(127), substring(@Ctx, 2, 1 + @Length))
if (@Username is null) select @Username = SYSTEM_USER

Security Implications

I am not a security expert. I am certainly not a SQL security expert. However, I believe this method is secure for most scenarios. In my opinion, this is at least as secure as auditing through NHibernate interceptors and events.

Here’s a few ways to circumvent the system, and how you can prevent that from happening:

• Manipulate the audit logs directly. Obviously, in any audit scenario, you should set permission on your audit tables to make them select and insert only.
• Alter the context_info data directly. A user overwrites their own context_info with that of another user, effectively blaming their actions on someone else. This can be done through a SQL injection attack some other security breach. If you go on vacation with the front door open, don’t be surprised when your house is robbed.
• Fail to set context_info. Again, this requires some other security breach. You may also change your trigger code to ROLLBACK TRANSACTION and RAISERROR when context_info is not set. This will force a rollback of the transaction (or in the case of no explicit transaction, the SQL statement that caused the trigger), abort the remainder of the SQL batch, and report an error. The approach requires everyone, including your DBA, to set an appropriate context_info before altering any audited data.

This is certainly my worst blog post this year, but don’t worry. I can do worse.

- Jason

In my last post, we used the NHibernate mapping as source data to create our script by hand. With this version, we’re going to take it a step further. When you add an IAuxilaryDatabaseObject to the mappings, it will be included in the ddl output of SchemaExport. These have no effect outside of SchemaExport. It’s a simple way to include additional items in your database setup scripts.

In our main program, we set up some additional columns to hold our audit data – user, timestamp, and action. These columns will be added to each audit table. Then, we use TriggerStrategy to add auxiliary database objects to our configuration.

        static void AddAuditing(Configuration cfg)
        {
            var columns = new List<auditcolumn>();
            columns.Add(new AuditColumn() {
                Name = "AuditUser",
                SqlType = "sysname",
                IsNullable = false,
                ValueFunction = delegate(TriggerActions actions)
                {
                    return "system_user";
                }
            });

            columns.Add(new AuditColumn()
            {
                Name = "AuditTimestamp",
                Value = new SimpleValue() { TypeName = NHibernate.NHibernateUtil.DateTime.Name },
                IsNullable = false,
                IncludeInPrimaryKey = true,
                ValueFunction = delegate(TriggerActions actions)
                {
                    return "getdate()";
                }
            });

            columns.Add(new AuditColumn()
            {
                Name = "AuditOperation",
                Value = new SimpleValue() { TypeName = NHibernate.NHibernateUtil.AnsiChar.Name },
                Length = 1,
                IsNullable = false,
                ValueFunction = delegate(TriggerActions actions)
                {
                    switch (actions)
                    {
                        case TriggerActions.Insert:
                            return "'I'";
                        case TriggerActions.Update:
                            return "'U'";
                        case TriggerActions.Delete:
                            return "'D'";
                        default:
                            throw new ApplicationException("Triggers handling multiple event types (INSERT, UPDATE, DELETE) are unsupported. ");
                    }
                }
            });

            new NHibernate.Audit.TriggerStrategy(
                new NHibernate.Audit.AuditTableBuilder(),
                new NHibernate.Audit.MsSqlTriggerBuilder(),
                columns).Configure(cfg);
	}

Trigger strategy simply hands off the work of audit table builder and trigger builder.

Audit table builder creates an audit table object for each table already in your mapping. It is also responsible for calculating the name of each audit table. Yes, the naming should probably be pulled in to a separate class, but oh well.

Audit table uses NHibernate’s table object internally to create a duplicate of each table, plus the additional audit columns, then exposes the drop/create scripts for this table object as an IAuxilaryDatabaseObject. I’ve ommitted some of the name-related static below.

using System.Collections.Generic;
using NHibernate.Mapping;

namespace NHibernate.Mapping
{

    public class AuditTable : AbstractAuxiliaryDatabaseObject
    {

        public AuditTable(string Name, Table DataTable, IEnumerable<AuditColumn> AuditColumns)
        {
            dataTable = DataTable;
            auditTable = new Table();
            auditColumns = AuditColumns;
            BuildAuditTable(Name);
        }

        protected readonly Table dataTable;
        protected readonly Table auditTable;
        protected readonly IEnumerable<AuditColumn> auditColumns;

        protected virtual void BuildAuditTable(string Name)
        {
            auditTable.Catalog = dataTable.Catalog;
            auditTable.Schema = dataTable.GetQuotedSchema();
            auditTable.Name = Name;

            foreach (Column column in dataTable.ColumnIterator)
                CopyColumn(column);

            CopyPrimaryKey(dataTable);

            AddAuditColumns();
        }

        protected virtual void CopyColumn(Column column)
        {
            auditTable.AddColumn((Column)column.Clone());
        }

        protected virtual void CopyPrimaryKey(Table dataTable)
        {
            if (dataTable.PrimaryKey != null)
            {
                var pk = new PrimaryKey();
                auditTable.PrimaryKey = pk;
                foreach (Column c in dataTable.PrimaryKey.ColumnIterator)
                {
                    Column auditTableColumn = auditTable.ColumnIterator.WithName(c.Name);
                    pk.AddColumn(auditTableColumn);
                }
            }
            else
            {
                throw new System.NotSupportedException("Tables without primary keys are not supported.");
            }
        }

        protected virtual void AddAuditColumns()
        {
            foreach (AuditColumn source in auditColumns)
            {
                Column dest = (Column)source.Clone();
                auditTable.AddColumn(dest);
                if (source.IncludeInPrimaryKey)
                    auditTable.PrimaryKey.AddColumn(dest);
            }
        }

        public override string SqlCreateString(NHibernate.Dialect.Dialect dialect, NHibernate.Engine.IMapping p, string defaultCatalog, string defaultSchema)
        {
            return auditTable.SqlCreateString(dialect, p, defaultCatalog, defaultSchema);
        }

        public override string SqlDropString(NHibernate.Dialect.Dialect dialect, string defaultCatalog, string defaultSchema)
        {
            return auditTable.SqlDropString(dialect, defaultCatalog, defaultSchema);
        }
    }

}

That handles our audit tables. Now let’s look at the triggers.

Abstract trigger builder builds insert, update, and delete triggers for each table. You should override BuildTriggerBody to customize the body of your trigger. I’ve created MsSqlTriggerBuilder to output my preferred trigger code.

NHibernate dialects have no concept of triggers and lack the required strings to build a DBMS-specific CREATE TRIGGER statements. Instead, you specify a trigger type inherited from AbstractTrigger specific to your database to build the CREATE TRIGGER statement. I’ve included MsSqlTrigger.

That’s all folks. You can grab the code from my SVN repository at http://basiclyeverything.googlecode.com/svn/trunk/AuditLogExample/

Since I’m still using the system_user function to get the current user, you will still need to impersonate the user all the way down to the database level. I’ve found a way around this, and I will show it to you next time. Until then, Happy New Year.

What I’m going to show you today is the poor-mans version. It certainly doesn’t qualify as parallel model, but it’s pretty good audit logging if you meet the prerequisites.

The Problem

You need some audit logs for your current application, but you’re behind. In fact, you started the project already a month behind schedule. This needs to be quick and reliable. It’d be great if it didn’t intrude on the application, because you’ve got enough rattling around in your head already.

It sounds like you want SQL trigger audit logging. By the way, let’s say you’re using MS SQL Server. Let’s also say your application impersonates the user all the way down to the database level. That means your connection string says integrated security=SSPI or your dba enjoys the extra work of maintaining a sql login for each user. Those are prerequisites for this approach.

What’s a trigger?

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
- CREATE TRIGGER, MSDN

If tables are the data structures in your database and stored procedures are the methods, then triggers are the event handlers. We’re concerned with three specific events – INSERT, UPDATE, and DELETE. We want to log each time those events happen to rows in our tables.

Trigger auditing has been around since at least the 1990s – since before SQL Server was even worth a damn. That’s MSSQL 6.5 folks. It’s sturdy and well-proven, although poorly implemented triggers will bring your server to a screeching halt. Fair warning – mine may be one of them.

We want to know what the data was before the change, what the data is now, who changed it, and when.

When you insert records in a table, the insert trigger for that table executes. The same goes for update and delete.

Triggers have two imaginary tables to help us with this task – inserted and deleted. Inside a trigger, they mostly work like real tables. Inserted always has the new data. Deleted always has the old data (from before the change.) So, in the case of an INSERT trigger, deleted is empty. For a DELETE trigger, inserted is always empty, since there is no new data with a deletion. For an UPDATE trigger, they both have data.

How does this help us? Well, suppose we have want to audit a table called Person. Let’s make a table called Audit_Person with the exact same columns as Person. When we delete data from Person, our DELETE trigger on Person will run. Inside our trigger, the deleted table has all of the rows that were just deleted from Person. We can copy that data to our audit table so we have a record of it by doing INSERT INTO Audit_Person SELECT * FROM deleted. Since the columns match up exactly, it works.

Wait a minute though. We need to know who deleted that data and when. Oh, and we need some way to indicate the different events.

Lucky for us, there’s some easy functions in SQL. system_user will give us the login of the current user – either a sql account name or a windows account name. Of course, GetDate() will tell us the current time and we’ll just write that to the table.

Let’s go ahead and write out a full trigger.

CREATE TRIGGER Person_onInsert ON Person FOR INSERT AS
SET NOCOUNT ON
INSERT INTO Audit_Person SELECT *, system_user, getdate(), 'I' FROM Inserted

Oh yeah, we need to add some fields in Audit_Person to hold the extra stuff. system_user is a custom sql type called sysname. It’s really nvarchar(256). Of course, getdate() is a datetime. Finally, we’re passing a capital letter I (for Inserted) as the last field, so we’ll need to make that a char(1) type.

The SET NOCOUNT ON command is there to suppress the extra (X rows affected) messages that SQL spits out. You’ll know what I’m talking about if you’ve ever clicked on the Messages tab after running a query.

So, that’s the audit logging. Now we just need to create an audit table and 3 triggers for each table in your application.

What does that get us?

Suppose we set up the audit table and all 3 triggers, then ran these 5 SQL commands:

INSERT INTO [Person] VALUES (NEWID(), 'Jason 1')
INSERT INTO [Person] VALUES (NEWID(), 'Jason 2')
INSERT INTO [Person] VALUES (NEWID(), 'Jason 3')
UPDATE Person SET Name = 'Jason 4'
DELETE FROM Person 

Our Audit_Person table would have these rows: (click the picture for 100% size)

Being Lazy

You’ve got 200 tables? Well, you’d better get to typing then.

OK. Just like you can generate a SQL script using NHibernate.Tool.hbm2ddl.SchemaExport, we can generate a script to build our audit tables and triggers from the mappings.

Now, we’re not going to parse out the XML in the hbm files. We’re going to read the proposed table schema – what SchemaExport will give you – directly from the NHibernate configuration object. This is the same way SchemaExport does it’s magic.

Here’s the code:

using System.IO;
using NHibernate.Cfg;
using NHibernate.Mapping;
using NHibernate.AdoNet.Util;

namespace AuditLogExample
{

    public class AuditScripter
    {

        public const string USER_DEFAULT = "system_user";
        public const string TIMESTAMP_DEFAULT = "getdate()";

        private enum Operations
        {
            Insert,
            Update,
            Delete
        }

        public AuditScripter(Configuration Configuration)
        {
            cfg = Configuration;
            mapping = cfg.BuildMapping();
            dialect = NHibernate.Dialect.Dialect.GetDialect(cfg.Properties);
            formatter = (NHibernate.Util.PropertiesHelper.GetBoolean(NHibernate.Cfg.Environment.FormatSql, cfg.Properties, true) ? FormatStyle.Ddl : FormatStyle.None).Formatter;
        }

        private readonly NHibernate.Dialect.Dialect dialect;
        private readonly Configuration cfg;
        private readonly NHibernate.Engine.IMapping mapping;
        private readonly IFormatter formatter;

        public void Execute(System.IO.TextWriter script)
        {
            var mappings = cfg.CreateMappings(dialect);
            ScriptObjectsForTables(mappings, script);
        }

        private void ScriptObjectsForTables(Mappings mappings, TextWriter script)
        {
            var tables = mappings.IterateTables;
            var auditTableBuilder = new AuditTableBuilder();
            foreach (Table table in tables)
            {
                if (table.IsPhysicalTable)
                {
                    var auditTable = auditTableBuilder.BuildAuditTable(table);
                    ScriptObjectsForTable(table, auditTable, script);
                }
            }
        }

        private void ScriptObjectsForTable(Table dataTable, Table auditTable, TextWriter script)
        {
            ScriptAuditTable(auditTable, script);
            ScriptInsertTrigger(dataTable, auditTable, script);
            ScriptUpdateTrigger(dataTable, auditTable, script);
            ScriptDeleteTrigger(dataTable, auditTable, script);
        }

        private void ScriptAuditTable(Table auditTable, TextWriter script)
        {
            string defaultCatalog = NHibernate.Util.PropertiesHelper.GetString(NHibernate.Cfg.Environment.DefaultCatalog, cfg.Properties, null);
            string defaultSchema = NHibernate.Util.PropertiesHelper.GetString(NHibernate.Cfg.Environment.DefaultSchema, cfg.Properties, null);

            string sqlCreateString = auditTable.SqlCreateString(dialect, mapping, defaultCatalog, defaultSchema);

            script.WriteLine(formatter.Format(sqlCreateString));

            var comments = auditTable.SqlCommentStrings(dialect, defaultCatalog, defaultSchema);

            foreach (string line in comments)
                script.Write(formatter.Format(line));

            script.WriteLine(formatter.Format("go")); // create trigger statements must in their own batch
        }

        private void ScriptInsertTrigger(Table dataTable, Table auditTable, TextWriter script)
        {
            ScriptTrigger(string.Format("{0}_onInsert", dataTable.Name),
                "insert", "'I'", "inserted",
                dataTable, auditTable, script);
        }

        private void ScriptUpdateTrigger(Table dataTable, Table auditTable, TextWriter script)
        {
            ScriptTrigger(string.Format("{0}_onUpdate", dataTable.Name),
                "update", "'U'", "inserted",
                dataTable, auditTable, script);
        }

        private void ScriptDeleteTrigger(Table dataTable, Table auditTable, TextWriter script)
        {
            ScriptTrigger(string.Format("{0}_onDelete", dataTable.Name),
                "delete", "'D'", "deleted",
                dataTable, auditTable, script);
        }

        /// <summary>
        /// Scripts out a create trigger sql statement
        /// </summary>
        /// <param name="triggerName">The name of the trigger</param>
        /// <param name="triggerOperation">insert, update, or delete</param>
        /// <param name="auditOperationValue">Value to insert for AuditOperation field</param>
        /// <param name="triggerTable">The "built-in" trigger table with affected data - inserted or deleted</param>
        /// <param name="dataTable"></param>
        /// <param name="auditTable"></param>
        /// <param name="script"></param>
        private void ScriptTrigger(string triggerName, string triggerOperation,
            string auditOperationValue, string triggerTable,
            Table dataTable, Table auditTable, TextWriter script)
        {

            Table trigger; // Just to calculate the name of the trigger
            trigger = new Table(triggerName);
            trigger.Schema = dataTable.Schema;
            trigger.Catalog = dataTable.Catalog;
            trigger.IsQuoted = dataTable.IsQuoted;

            string dataTableName = dataTable.GetQualifiedName(dialect);
            string auditTableName = auditTable.GetQualifiedName(dialect);
            triggerName = trigger.GetQualifiedName(dialect);

            string triggerHeading;
            string triggerBody;

            triggerHeading = string.Format("create trigger {0} on {1} for {2} as", triggerName, dataTableName, triggerOperation);
            triggerBody = string.Format("insert into {0} select *, {1}, {2}, {3} from {4}", auditTableName, TIMESTAMP_DEFAULT, USER_DEFAULT, auditOperationValue, triggerTable);

            script.Write(formatter.Format(triggerHeading));
            script.Write(formatter.Format("set nocount on"));
            script.Write(formatter.Format(triggerBody));
            script.WriteLine(formatter.Format("go")); // create trigger statements must in their own batch

        }

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate.Mapping;

namespace AuditLogExample
{
    public class AuditTableBuilder
    {

        public Table BuildAuditTable(Table dataTable)
        {
            Table auditTable = new Table(string.Format("Audit_{0}",dataTable.Name));
            auditTable.Catalog = dataTable.Catalog;
            auditTable.Schema = dataTable.Schema;
            auditTable.IsQuoted = dataTable.IsQuoted;

            foreach(Column column in dataTable.ColumnIterator)
                CopyColumn(column, auditTable);

            CopyPrimaryKey(dataTable, auditTable);
            AddAuditColumns(auditTable);

            return auditTable;
        }

        private void CopyColumn(Column sourceColumn, Table auditTable)
        {
            Column newColumn = (Column)sourceColumn.Clone();
            auditTable.AddColumn(newColumn);
        }

        private void CopyPrimaryKey(Table dataTable, Table auditTable)
        {
            var pk = new PrimaryKey();
            foreach (Column sourceColumn in dataTable.PrimaryKey.ColumnIterator)
            {
                pk.AddColumn(auditTable.Column(sourceColumn.Name));
            }
            auditTable.PrimaryKey = pk;
        }

        private void AddAuditColumns(Table auditTable)
        {

            Column ts = new Column("AuditTimestamp");
            ts.SqlType = "datetime";
            ts.IsNullable = false;
            ts.DefaultValue = AuditScripter.TIMESTAMP_DEFAULT;
            auditTable.AddColumn(ts);
            auditTable.PrimaryKey.AddColumn(ts);

            Column user = new Column("AuditUser");
            user.Length = 256; // system_user returns a sysname, which is really nvarchar(256)
            user.SqlType = "sysname";
            user.IsNullable = false;
            user.DefaultValue = AuditScripter.USER_DEFAULT;
            auditTable.AddColumn(user);
            auditTable.PrimaryKey.AddColumn(user);

            Column op = new Column("AuditOperation");
            op.SqlType = "char";
            op.Length = 1;
            op.IsNullable = false;
            auditTable.AddColumn(op);

        }

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate.Mapping;

namespace NHibernate.Mapping
{
    public static class TableExtensions
    {

        public static Column Column(this Table Table, string ColumnName)
        {
            return Table.ColumnIterator.Where(c => c.Name == ColumnName).FirstOrDefault();
        }

    }
}

I have a couple of gotchas and details to explain.

First, this works for me. I’m sure there are edge cases where everything goes horribly wrong. Please let me know when you find them.

Next, CREATE TRIGGER commands are VERY anti-social. They refuse to share a batch with any other statements. That’s why there’s a “go” between everything. In query analyzer (or the SSMS query window now), it starts a fresh batch.

If you know your SQL, this should look pretty simple. Pass an NHibernate configuration to the constructor of AuditScripter, then call Execute. The code loops through all of the physical tables in your configuration, first building a nearly-identical audit table, then creating the three triggers.

The primary key of the audit table has all the fields from the primary key of your data table, plus AuditUser and AuditTimestamp. A single user cannot alter the same record in two different ways at the exact same moment. In fact, I don’t think any two users could, but I’ll let it slide.

The code could probably be improved to automatically include some common indexes on the audit tables.

I’m sure this is the worst possible way to generate code, but I wanted something non-tool specific. I thought about T4 templates since everyone probably has Visual Studio, but this was quicker than figuring out how to get a full NHibernate configuration in to a T4 template.

Expanding the idea

You can definitely build on this idea to generate all sorts of repetitive code based on your NHibernate schema. For instance, if you’re unlucky enough to work in a shop with one of those stored-proc crazed database Nazis, this should help quite a bit. You can use this same idea to generate thousands of CRUD stored procedures in a few minutes.

In my MVC applications, I sometimes bind my views directly to NHibernate entities. a lot. like always. I also do session-per-request.

I don’t remember what prompted this thought – probably Twitter – but it seemed questionable to access the database from the view by loading a lazy collection. I knew you shouldn’t put database access code in the view, but this would just be a property, nothing complex at all. Ayende set me straight.

You should avoid it. It is dangerous to do loading in the
view, it is subject to too many changes.

How do we prevent it? Of course, the simple answer is “just don’t do it.” Anything more quickly falls in the category of protecting you from yourself, of which I’m usually not a fan. Still, this is easy to overlook. The Morts of the world – and me – will fall in to this trap easily.

ViewModel Solution

What if we had some sort of model, but instead of being generic for the entire application, it only dealt with the concerns of a specific view. Oh, and what if we called it something weird like viewmodel.

This is the best way to avoid problems. Instead of binding directly to an entity, each view should have a corresponding viewmodel class, that contains all of the data being pushed down to the view as well as all the data collected by the view from the user. It’s a POCO specific to the view, with no association to the NHibernate session, so it can’t accidentally load up some data.

You can easily test your viewmodels. Since they also make your views pretty darn stupid, you can maybe skip some of that time-consuming UI testing. Well, skip it safely this time. ViewModels also work well for all that validation attribute markup. Just sayin’.

This isn’t the point of my post today, and what I’ve described isn’t the traditional Model-View-ViewModel. It’s some weird hybrid of separated presentation patterns. You can read more about view models here and here and on Jose’s Chinook Media Manager application series.

Exploding View Solution

While I can’t say it’s worse than something silently breaking, I don’t like the idea of something bad silently working. Fragile code only works until the worst possible moment.

So, without going for a full ViewModel implementation, I thought it would be a good thing (or at least slightly better) to make NHibernate throw an exception when we hit the DB in the view so we have to fix it now while we’re debugging instead of later. I initially had thoughts about interceptors or connection providers and all sorts of craziness. Ayende’s answer is simply to close the session at the controller boundary.

How do we implement this? We close the session between the controller action and the view. So, instead of session per (the entire) request, we trim the scope at the end just a bit.

Let’s take a high-level look at a chunk of the ASP.NET MVC lifecycle:

1. A controller action is chosen based on the request and action filters like HttpGet or HttpPost.
2. The action (the actual controller method) executes, returning an action result – a ViewResult, RedirectToActionResult, ContentResult, or some other built-in or custom action result.
3. The action result executes. In the case of a ViewResult, the view is rendered down to actual HTML and written out to the response stream.

We need to close down the session between #2 and #3. Lucky for us, every controller has an overloadable / overridable method called OnResultExecuting. This method gets called just before the action result is executed. We can simply override this method in our application’s base controller class. You have one of those, right? They’re handy for all sorts of stuff.

Just close down any session we may have open inside OnResultExecuting. Considering the references, this takes a little bit of plumbing, but I’ll leave that up to you since it’s dependent on your method of opening and tracking NHibernate sessions through the request.

One last thing

Maybe in a later version of NH Profiler, the problem I described today will trigger an alert. Maybe not.

If you’re using NHibernate without NH Profiler, you’re either writing substandard code or wasting time – probably both. If you don’t believe me, download a trial and see what it tells you about your last NHibernate app.

SQL Audit Setup

The database uses MS SQL Server trigger-based audit logs to record data changes with a username and timestamp. They are literally for audit purposes, and not used in the application. Every database table has a matching Audit table with exactly the same fields plus a username, change type, and timestamp field.

Only updates and deletes trigger an entry in the audit log. The data recorded in the log represents the record prior to the change. The username field is defaulted to SYSTEM_USER, a SQL server specific variable that returns the current account name associated with the connection. This can be either a SQL server account or a windows account. Change type is a single character U or D depending on the action being performed. Timestamp is a datetime field defaulted to GETDATE().

In order to associate audit log records with a particular username, all updates and deletes must run in the context of the user.

Authentication and Impersonation

Customers do not log in to the website. There is no authentication. When a customer interacts with the database, we used a generic SQL account specified in the connection string. All customer changes are recorded with the name of this generic account.

When the office staff uses the website, they are required to authenticate with their windows accounts. This account is impersonated all the way to the database so the audit log will record the specific user name.

There are several ways to achieve this impersonation. The option I chose to use is basic authentication. Over SSL (an https website) this is just as secure as any other data. The primary drawback is that users will get the standard, generic popup log in box. Since the username and password are transmitted to the web server, the web server can easily impersonate this user to any resource, including SQL, even on another server.

You can also run Windows authentication. This was referred to as NTLM back in the day. The benefit of this over basic authentication is that Internet Explorer will authenticate with the web server automatically using the current user’s windows credentials. Since this authentication is done with public keys instead of passwords, the web server can’t impersonate the user to resources (including SQL server) on other servers. This means it’s not really a working solution by itself. If you’re running SQL server on your production web server, stop reading now and go fix that.

Fortunately, windows authentication can take advantage of delegated Kerberos authentication. This is difficult to set up and well beyond the scope of this post. Basically, after trading some more public keys around, the SQL server knows that the web server is telling the truth about the current user and agrees to run the SQL statements in the context of that user. Internet Explorer users are still not prompted for their user names and passwords, and it just works. 

Warning: Impersonation, no matter which method you choose, is not recommended. It effectively disables connection pooling, which stops your application from scaling. In fact, this whole post is probably one bad idea after another.

In this particular case, I’m safe. We can’t physically fit more than a dozen “staff” users, and we’ll certainly never get approval to hire even that many. I’ve used this same architecture for hundreds of users before I knew any better – and the hardware was much slower. In short, it’ll work in this case.

Dynamic Connection Strings

This led to another small issue. Customers should log in with a generic SQL account. Staff should log in with their windows accounts.

Jose R. led me to this article on the NHForge wiki from November 2008. Well, things have changed slightly in the last year. Also, I wanted something a little more pluggable.

NHibernate uses a DriverConnectionProvider to supply it with a connection. To alter the connection string as needed, we only need to override the ConnectionString property of this class.

using NHibernate.Connection;

namespace DynamicConnectionStringExample
{
    public class DynamicConnectionProvider : DriverConnectionProvider
    {

        public static IConnectionStringProvider connectionStringProvider;

        protected override string ConnectionString
        {
            get
            {
                if (null!=connectionStringProvider)
                {
                    return connectionStringProvider.GetConnectionString();
                }
                return base.ConnectionString;
            }
        }

    }
}

Note: The example I show here assumes you are using one of the standard bytecode providers such as NHibernate.Bytecode.Castle. However, you could very easily use an IoC bytecode provider (except Ninject) from uNHAddIns and use constructor dependency injection to inject the IConnectionStringProvider dependency. If not, you’ll need to manually set the ConnectionStringProvider.

To use this, just tell NHibernate about it:

Environment.Properties[Environment.ConnectionProvider] = typeof(DynamicConnectionProvider).AssemblyQualifiedName;

Of course, the “magic” really happens in the IConnectionStringProvider. If we have an authenticated user, GetConnectionString() returns a connection string with Integrated Security = true. If we don’t, we return one with a SQL username and password.

Your Problem

Let’s use the ever-popular “preferred customer” example. You’ve built this beautiful ecommerce application. You even implemented coupon code discounts and stuff. Customers put items in the carts, checkout, and magically get their stuff in 2 to 93.5 days.

Today, the sales director emails you about their new “Preferred customer” program. A preferred customer gets an automatic 2% discount on orders over $1000 (up to $20). This will be a piece of cake. You quickly write up a few new tests, slap an IsPreferred boolean property on your customer entity, and build a new order total strategy for this discount scenario. All the tests turn green.

Then you naively email him to ask how a customer becomes “preferred.” The sales director replies “After the 20th order over $1000, the customer automatically becomes preferred.” Simple enough. He continues “When that happens, we need to place an order for a complimentary gift basket and the sales rep needs an email about it. Also, any customer with a single order over $20,000 or two orders over $10,000 is automatically enrolled in the program. Next Tuesday, we’re meeting to discuss the new Gold preferred program. I want you there.”

Let’s step back and analyze what just happened here. Your beautiful application just got owned by Yet Another Tool With an MBA and you have to share oxygen with him for an hour next Tuesday. More importantly, you have a problem. Even though there are multiple ways to become a preferred customer, the customer only becomes preferred once. They only get one gift basket. Also, you need to do two entirely different actions based on the same event – place a gift basket order and send an email to the sales rep. On top of all that, there’s another mess of weird requirements in the works. How will you handle this without turning your beautiful application in to a pile of ugly hacks? Domain events.

What’s is it?

The domain events pattern is the code equivalent of the office gossip network. Whenever something interesting happens, the office gossip tells anyone and everyone who might care about the news.

You have a requirement: “When X happens, do Y1, Y2, Y3, and Y4.” X is the event. The Y’s are the resulting actions that your application should take. There may be one action. There may be 15. There may be none. It doesn’t matter really. Let’s say the event is “John B. Customer just ordered 2 widgets and a sprocket.” Obviously, one of your actions will be “Ship 2 widgets and a sprocket to John B. Customer” You’ll also want to email him a receipt of his order. If this is his first order, you’ll want to mail him a catalog and add him to the mailing list.

How would you handle this? Well, you could have your customer’s PlaceOrder method call the services directly. That creates all sorts of tight coupling that you don’t want. Plus the reference is going the wrong direction. You could inject the services and program to an interface, but a lot of people consider that a bad idea. Even with an interface, your entities know more than they need to about your services.

You’ll probably want to use domain events for this. Domain events are especially helpful when X could happen in many different places, or the Y’s change a lot. They keep your coupling down by keeping your domain blissfully ignorant.

The checkout code of your ecommerce site doesn’t need to know how to ship products, email receipts, or anything else. It just needs to tell some central event dispatcher – the office gossip – “Hey. John B. Customer just placed an order for 2 sprockets and a widget.”

What’s the solution?

Basically, if you understand Pub/Sub service bus, toss out the queue and the transactional stuff. Now you understand the layout of domain events.

You have a dispatcher. So that it’s easy to access from anywhere in your application, it’s a static class.

You also have some service that wants to know when some event happens. It tells the central dispatcher “Tell me when [particular type of event] happens.” It registers as a handler of that particular type of event.

Your domain tells the dispatcher “Hey! This just happened.” The dispatcher looks up the handler or handlers for that event and passes along the message. The handler(s) do some work based on the details of that event.

Another Trio

All of the really cool patterns have 3 parts… and singleton has thick glasses and bad acne.

1. Events – CustomerBecomesPreferred, CustomerPlacedOrder, etc. Each of these classes will be immutable – they can’t be changed once they’re created. Even though we don’t have any common members, all of our domain events play a particular role in the application. Like Udi says in his post, define role explicitly. All of our event types will implement IDomainEvent.
2. Handlers – In Pub/Sub, these would be the subscribers. You can have multiple handlers for a single type of event. The order that these handlers execute is unknown. For this reason, your events should be immutable. Altering the state of an event during the execution of a handler could create unexpected side-effects in a subsequent handler, and the whole thing becomes a game of Chinese Whispers. Since handlers also play a role, we’ll have an interface for them. Each handler will implement IHandle<T> where T is the type of event to be handled. We’ll also define a void Handle(T Event); method so all our handlers have a common entry point for the dispatcher.
3. The dispatcher dispatches events to the various handlers. The internals can be implemented many different ways, but the result is the same. All of the event handlers are registered in the dispatcher. When the dispatcher raises an event, it gets all of the handlers for that type of event. One by one, it executes each handler’s Handle method. We’ll use a service locator internally. Ninject people will need to use v2.0.

Here’s the diagram:

Here’s the code:

namespace DomainEventsSample
{
    public interface IDomainEvent
    {
    }
}

namespace DomainEventsSample
{
    public interface IHandle<T> where T:IDomainEvent
    {
        void Handle(T Event);
    }
}

using Microsoft.Practices.ServiceLocation;

namespace DomainEventsSample
{
    public static class Dispatcher
    {

        public static void Initialize(IServiceLocator ServiceLocator)
        {
            serviceLocator = ServiceLocator;
        }

        private static IServiceLocator serviceLocator;

        static void Raise<T>(T Event) where T : IDomainEvent
        {
            var handlers = serviceLocator.GetAllInstances<IHandle<T>>();
            foreach (var handler in handlers)
            {
                handler.Handle(Event);
            }
        }

    }
}

In our main program, we bind some event handlers to their implementations, then make a customer order a bunch of stuff.

using System;
using Ninject;
using Microsoft.Practices.ServiceLocation;
using CommonServiceLocator.NinjectAdapter;

namespace DomainEventsSample
{
    class Program
    {
        static void Main(string[] args)
        {
            IKernel kernel = new StandardKernel();
            IServiceLocator sl = new NinjectServiceLocator(kernel);
            Dispatcher.Initialize(sl);

            kernel.Bind<IHandle<CustomerPlacedOrderEvent>>().To<ShipOrder>();
            kernel.Bind<IHandle<CustomerBecamePreferred>>().To<SendPreferredGiftBasket>();
            kernel.Bind<IHandle<CustomerBecamePreferred>>().To<SendMessageToSalesRep>();

            Customer c = new Customer();
            for (var i = 0; i < 22; i++)
            {
                Order newOrder = new Order(1000.00 + i);
                c.PlaceOrder(newOrder);
                Console.WriteLine("------------------------------------");
            }
            Console.ReadLine();

        }
    }
}

Our events are pretty simple. They just pass along the relevant information. When the event is about something that happened to a customer, we need to know which customer. When the event is about an order, we need to know which order.

namespace DomainEventsSample
{
    public class CustomerPlacedOrder:IDomainEvent
    {

        public CustomerPlacedOrder(Order Order)
        {
            order = Order;
        }

        private readonly Order order;

        public Order Order
        {
            get
            {
                return order;
            }
        }

    }
}

Here’s a handler. Of course, this is just a stub for some real action you would implement.

using System;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DomainEventsSample
{
    public class ShipOrder : IHandle<CustomerPlacedOrderEvent>
    {
        #region IHandle<CustomerPlacedOrderEvent> Members

        void IHandle<CustomerPlacedOrderEvent>.Handle(CustomerPlacedOrderEvent Event)
        {
            Console.WriteLine("Shipping order totalling {0:C}",Event.Order.Total);
        }

        #endregion
    }
}

Bidirectional Communication

This is the real reason I’m posting. I had a situation where a domain event handler called in to another system to do some action and the log of that action needed to make its way back to the UI.

Just like with Pub/Sub, domain events are one way. I didn’t want to give up domain events, but I had a requirement I couldn’t fill with the pattern. So, after hacking at it for an afternoon and getting nowhere, I sent out a call for help. Ayende wrote me back.

Publish a new event that the UI is listening to

He explains in 10 words what takes me 3 paragraphs. There’s your answer folks. When your handler needs to respond, publish a response event. Simple. Elegant.

Gimme the Code!

I’m putting all of the code for this blog in SVN at http://basiclyeverything.googlecode.com/svn/trunk/

The code for this particular post is in http://basiclyeverything.googlecode.com/svn/trunk/DomainEventsSample

What’s the alternative?

Blogs? They’re fine for keeping up with trends and knowing what the cool kids are doing, but if you’re trying to learn something from scratch, you’ve got to dig for the good stuff. The worst part about web 2.0 is that any idiot can start a blog about stuff he doesn’t really understand – even me.

Books? While I could recommend a few, the honest truth is that I’ve had a dozen queued up on my bookshelf for nearly a year. Even if you do take the time to read, the information is out of date before the book even hits your shopping cart. On top of that, you have no guarantee that the author is really an expert.

Here’s something different.

Rob Connery @robconery and James Avery @averyj started TekPub.com. That’s @tekpub on Twitter. It’s a library of professional screencast series by Rob, James, and other subject experts with names you’ll recognize. They’re not little 30 minute channel 9 interviews with obscure Microsoft PMs. First, the message is not corporate in any way, shape, or form. Second, the quality is amazing. The audio is clear. The text is crisp and easily readable. The images are both humorous and relevant to the topic.

They just launched. Here’s what’s out there already:

• Mastering NHibernate – Oren Eini aka Ayende Rahien and Rob integrate NHibernate with Kona, an MVC storefront app. When it comes to NHibernate, Oren is a true master.
• Building my own Blog – He’s Rob freakin’ Conery. He’s not setting up WordPress. He’s building a blog from scratch with MVC, lean techniques, AgileZen, BDD, MSpec, and a whole lot of other cool stuff – and taking you along for the ride.
• Mastering Git – A full 3 hours dedicated to the ins and outs of Git. This will be the first one I watch.
• Coder to Developer 2009 – Rob updates a classic book in screencast form. This one is free folks, so go watch.
• Concepts – The first one covers IoC and DI with Ninject. Again, this one is free. The basics – stuff you should know – are free. This should be pretty awesome for training the new college grad on your team. When you need a break from the training pace, stick him in front of the conference room flat screen with a bucket of popcorn.

Even if you’re cheap, the previews and free stuff are worth a look. Personally, I hope they get Udi Dahan to talk about NServiceBus.