Call to Session.CreateSQLQuery ExecuteUpdate fails in NHibernate

Question

I have a stored procedure that is used to synchronize data from another system through a linked server. I execute this call using NHibernate in a Windows Service that runs tasks using Quartz.net. One of these tasks is the synchronize data task and it is really just the sproc call:

using(var tx = Session.BeginTransaction())  {   
    Session
        .CreateSQLQuery("exec dbo.spSyncData")
        .ExecuteUpdate();
    tx.Commit();
}

This stored procedure takes no parameters and returns no results. When this call is completed, I then load the data that was synchronized like this,,,

return Session.CreateCriteria(typeof(MyData))
    .Add(Restrictions.Eq("Status", Status.Waiting))
    .List<MyData>();

However, this call fails with a ADOException with the message "Invalid attempt to call Read when reader is closed."

I discovered that the sproc was set to SET NOCOUNT ON so I changed that and now I am getting a different exception...

"There is already an open DataReader associated with this Command which must be closed first."

This error occurs when the sproc call is committed.

Any ideas? Thanks, Steve

UPDATE: Some of the issues I discovered to be related to access of the session object across multiple threads, which I had not expected. I was able to clean this up, but haven't had a chance to try the recommendation again of using the Session.Connection object to execute a IDbCommand. That had failed before, but I think that was related to the threading issue as well. I hope to get back to that attempt soon.

Answer 1

I'd try executing the sproc via session.Connection (which is a IDbConnection) instead of CreateSQLQuery

ExecuteUpdate() was meant for bulk operations (see this post by Dario Quintana, one of the NHibernate devs).