Calling a Stored Procedure as part of a NHibernate transaction


The scenario

Today I had the need to call a stored procedure after some domain objects got updated. Those domain objects are persisted via NHibernate. The question here is not whether it’s good or not to have calls to sprocs mixed with NHibernate persistence because this can be argued but sometimes you have no choice when dealing with legacy code.

This is all in the context of an asp.net application with a SQL Server database.

The problem

The root of the problem I faced was that we are using a DAO pattern where a NHibernate session is started at the beginning of a request and closed at the end of the request.

My first approach was to do my objects updates and then call a method in my data access layer executing the stored procedure through ADO.NET. The problem with this was that the changes done to the objects are not persisted into the database until the session closed and the transaction committed. As a result, the stored procedure was run immediately and my update statements issued afterward.

I then thought that all I had to do was flush my NHibernate session before I exec the sproc to commit my changes to the database. However, for some reason that created some lock or pending session somewhere so my ado.net connection attempt timed out.

The solution

The idea of flushing the session and then executing the sproc was close but the last missing piece was to call the sproc via NHibernate using the same session as opposed to ADO.NET. My code ended up looking like this:


// flush the NHibernate session to commit changes
Session.Flush();

// execute the stored procedure
IQuery query = Session.CreateSQLQuery("exec MySprocName @param=:param1");
query.SetString("param1", data);
query.ExecuteUpdate();

Hope this helps

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: