NHibernate Stored Procedures with Out Parameters
by David on Okt.19, 2009, under .NET, NHibernate, Software-Development
hi there,
Lately I wanted to set up a businesslogic layer with Nhibernate on our legacy database. In that database all the Identifiers of the colums are calculated by some stored procedure which has an Out Parameter. Parsing through some articles in some forum or other I found that this is currently not supported by Nhibernate.
The SQL Stored Procdure would look like:
Create procedure [dbo].[ups_GetNewId]
(
@dbid int=100,
@tableName varchar(512),
@incBy int = 0,
@id int out
) as
//// omitted for brevity
Since I can’t change the legacy database, I had to fall back to pure SQL use. But this was not an issue since I can easily get an ordinary connection out of the Nhibernate session. Having found that it became quite stright forward as one is used to as ADO programmer.
public int CalcNewId(string tableName)
{
using (ISession session = DBSessionManager.GetDBSessionController(Database).GetDBSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = “dbo.ups_GetNewId”; //this stored Procedure calculates the ID
// Set input parameters
var parm = new SqlParameter(”@tableName”, SqlDbType.VarChar);
parm.Value = tableName;
command.Parameters.Add(parm);
// Set output parameter
var outputParameter = new SqlParameter(”@id”, 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();
return (int)((SqlParameter)command.Parameters["@id"]).Value;
}
}
Having done this I just set the newly calculated Id to the Domainobject. So I could keep to the inherited method of identifier creation. Of course this is circumventing NHibernate, there will be no caching and tracking and all the rest of it. but in this case I can live (or have to because of the legacy DB) with it.
But I found that in this case I have to tell Nhibernate that I assign the Id myself. This implicates new issues to obey.
These will be described in another article coming up.
November 24th, 2009 on 16:49
I’m not entirely sure what problem you are trying to solve. NHibernate lets you specify custom id generators in your hibernate-mapping files for each class. Your custom generators should implement IIdentifierGenerator and IConfigurable and provide a much easier way of accessing the session object. You might find that return parameters are better than out parameters you just need to be careful of the order of the parameters in .Net.
Dezember 2nd, 2009 on 08:55
Thanks for the hint. I went with IIdentifierGenerator and IConfigurable
and it worked out well. Still my problem is the forced usage of the legacy database which only accepts the row Id precalculated by the stored procedure dbo.ups_GetNewId which has an Out parameter. The stored procedure is given and must not be changed.
I could not use the session.Connection in procedure Generate() since I call as advised by ayende rahien all the NH Access within an NH transaction and I cannot extract the IDbTransaction from some NH session.
public class IdGenerator : IIdentifierGenerator, IConfigurable{
private string TableName { get; set; }
#region IIdentifierGenerator Members
public object Generate(NHibernate.Engine.ISessionImplementor session, object obj)
{
using (IDbCommand command = new SqlCommand())
{
using (command.Connection = new SqlConnection(IpDbConnectionString))
{
command.Connection.Open();
//command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.ups_GetNewId";
// Set input parameters
var parm = new SqlParameter("@tableName", SqlDbType.VarChar);
parm.Value = TableName;
command.Parameters.Add(parm);
// Set output parameter
var outputParameter = new SqlParameter("@id", 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();
return (int)((SqlParameter)command.Parameters["@id"]).Value;
}
}
}
#endregion
#region IConfigurable Members
public void Configure(NHibernate.Type.IType type, IDictionary parms, NHibernate.Dialect.Dialect d)
{
TableName = parms["TableName"];
}
#endregion
}