I am quite new to NHibernate, and I need to add some code to a NH-enabled app. My db is mssqlserver 2000.

The application needs to trace the user modifications on the db, using table triggers that write a row on a log table whenever a row is inserted/updated/deleted. The application username can be retrieved by the triggers (using some sort of temptable, this is not the issue), but only if I can find a way to set the application username at connect time. To make things clear: I don't need the db-user, I need the user at the application level, a string that is not "known" by the db, unless I can tell it to the sql engine.

The usual way I obtain this behaviour, in other environments, is to call a sp after any open() of the connection, centralizing the connection process. But in NH the connection is embedded in the library code, and I can't find any after-open event that I can grab to add my sql code.

Is there any place where I can add some code that can be executed after any connection to the db is opened?

Thanks in advance
Pietro

I was able to find a solution to execute code after connection. the link to the forum is at: http://forum.hibernate.org/viewtopic.ph ... highlight=

It was posted by BillHawes:

You can obtain an IDbConnection object from the NHibernate session, and execute any commands using this. The following code assumes that the session object is an open NH session, and runs stored procedure MyStoredProc.

IDbConnection cn = session.Connection;
IDbCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "MyStoredProc";
cmd.ExecuteNonQuery();


What you can't currently do is run a stored procedure that returns data and map the data to persistent objects.

You also need to be aware that if your stored procedure amends the data in your tables, this will not be reflected in any persistent objects already loaded.

I hope this is what you're looking for.

Good Luck!

Thank you for your reply, eluxa, but this is not what I was looking for.

What I want to do is to find an event (a callback, or something else) that can enable me to catch the moment when NHibernate is executing a connection.open().

This could allow me to add some sql code after the connection with the db has been estabilished. To execute my sp I will certainly use your suggestion, but my problem is to know when to execute my sp!

Pietro

From what I see in the code there is no event getting launched when opening a connection.

You could get the source code and there's a ConnectionProvider(or something like that - just look for .Open() ) and bubble up an event from there. Don't think is going to be easy :).

What I need is a place where I can get the "real" Connection used by the NH Session. So I can grab its StateChange event.


The solution I'm trying now is to implement a ConnectionProvider (the NH class is abstract), and passing it to NH in my app.config with the property.

If I can force NH to use my connection object, I can attach my code to the event I need.

Thanks anyway.
p

Yes , you are right, I took a longer look at the code.
It did seem quite hard the first time I looked at it :).

I found a solution.

By implementing a ConnectionProvider, it's my code that prepares the connection objects used by NHibernate. After opening the connection I can issue sql commands.

I had many problems in setting up this code, mainly because I think that the doc is somewhat flawed: the idea of writing the class name in my app.config with the syntax "class,assembly" comes from some browsing of the source code and reflector; and again, the parameter is named connection.provider_class in many pages of the doc, but that name is ignored: you have to specify connection.provider.

Anyway, this is the skeleton of the class implemented:


To force nh to use my provider, you have to put this code in the app.config, under the <session-factory> node:

where MyNamespace is the namespace of the class, and AssemblyName is the name of the assembly.

In this example, my sql code executes directly after the Open(). An alternative is to attach an event handler to the StateChangeEvent, in case nh is closing and reopening the connection object.