How to use legacy Stored Procedures in NHibernate

Stored procedure use in NHibernate is very specific to doing standard CRUD operations for a given entity. There are excellent resources out there which show how to do that. But sometimes, especially when trying to migrate a legecy environment to NHibernate, there may be situations where you need to work with stored procedures which have output parameters, a return parameter, or simply just need to ExecuteNonQuery. I am in this situtation, where I have hundreds of stored procedures to migrate, but I don't have enough time to completly migrate all procedures into an ORM structure before a release. I want to migrate them in small steps in next releases.

The NHibernate Documentation says that if you want to use these procedures you have to execute them via session.Connection. Taking hints from a post by Joshua Lockwood, I did it this way -


ISession session = sessionFactory.GetSession();

using(ITransaction transaction = session.BeginTransaction())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;

transaction.Enlist(command);

command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.SetUserInfo";

// Set input parameters
var parm = new SqlParameter("@UserID", SqlDbType.Int);
parm.Value = 12345;
command.Parameters.Add(parm);

// Set output parameter
var outputParameter = new SqlParameter("@Quantity", SqlDbType.Int);
outputParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(outputParameter);

// Set a return value
var returnParameter = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnParameter);

// Execute the stored procedure
command.ExecuteNonQuery();
}

Please read Joshua's post to note some of the caveats of using this approach.

2 comments:

Anonymous said...

The code as is didn't work for me. I had to add the line...

transaction.Commit();

Anonymous said...

Yes, I had to use the transaction.commit also and include the following;

using System.Data.SqlClient;

Also I had to define the length of the output parameter (if string) as follows;

var outputParameter = new SqlParameter("@vf1", SqlDbType.NVarChar);
outputParameter.Direction = ParameterDirection.Output;
outputParameter.Size = 15;

Post a Comment

I am a programmer based in Seattle, WA. This is a space where I put notes from my programming experience, reading and training. To browse the list of all articles on this site please go here. You can contact me at rohit.net@gmail.com.