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.

4 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;

@williamhowf said...

Thanks, i find your way is correct. Here is the sample code.

decimal balance = 0m;
using (var session = new SessionDB().OpenSession())
{
#region INPUT/OUTPUT parameter
var parm = new SqlParameter("@CustomerID", SqlDbType.Int)
{
Value = customerId
};
var pAvailableBalance = new SqlParameter("@outAvailableBalance", SqlDbType.Decimal)
{
Direction = ParameterDirection.Output,
Precision = 26,
Scale = 8
};
#endregion
var command = new SqlCommand
{
CommandType = CommandType.StoredProcedure,
CommandText = "usp_Get_AvailableBalance"
};
command.Connection = (SqlConnection)session.Connection;
command.Parameters.Add(parm);
command.Parameters.Add(pAvailableBalance);
command.ExecuteNonQuery();
balance = Convert.ToDecimal(pAvailableBalance.SqlValue.ToString());
}

finddistance said...

I tried to execute the above. everything working fine with a small change.
Unstead of IDbCommand command = new SqlCommand(); I used DbCommand command = new SqlCommand();
because transaction.Enlist() is taking DbCommand as Parameter. This is ok for me if i use SQLSERVER. But for OracleSERVER OracleCommand class is extending IDbCommand interface.
IDBCommand command=new OraclCommand();
So i can't able to pass for transaction.Enlist() as parameter
Is There any solution? Pls help

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@rohit.cc.