I am attempting to create an Interceptor in order to do some auditing (similar to the example shown in 'Hibernate in Action'.

I have statement similar to this:

ISession auditSession = NHibUtil.SessionFactory.OpenSession(session.Connection);


Where session is the main outer session which I pass in when instantiating the interceptor. I am using SQL Server.

However, when I come to use the auditSession (such as auditSession.Save(anObject) for example). I get the following exception:

System.InvalidOperationException: ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

I suspect this is because the auditSession does not have a reference to the outer sessions transaction.

What am I doing wrong? Has anybody got a solution to this problem?

Thanks in advance ......

You have to open a new connection for the new session as well, using the same connection won't work.

Ok ... thanks.

Is it not possible to use the underlying session connection at all then i.e. if I wanted to use ADO.NET in the same transaction as the session?

It is possible if that's what you want. Your example didn't work because it essentially tried to do two transactions on one connection simultaneously and ADO.NET doesn't support that.

How do I get the underlying ADO.NET transaction from the session so that my subsequent ADO.NET command can execute in the same transaction?

ISession.Connection contains the IDbConnection object that the session is using. If you're using a transaction and are executing an external command you'll need to enlist the command in the connection's transaction using ITransaction.Enlist(command).

Cheers,

Symon.