Configuring NHibernate to Execute a Stored Procedure that Returns a Result Set

I recently spent about 5 hours trying to get NHibernate to successfully execute a stored procedure that returns a result set in SQL Server 2012. Given that it took me less than an hour to put all the pieces together for NHibernate to execute a similar stored procedure but one that returns a scalar, I thought this would be easy. Surely did not turn out that way.

This blog post explains exactly how you need to configure NHibernate to execute a stored procedure that returns a result set.

Tools

The tools I used were Visual Studio 2010, SQL Studio Management Express 2012 and Express Profiler v2.0. NHibernate version is 3.3.1.4000 pulled into the project via NuGet.

Requirement

Say, you have the following stored procedure:

if OBJECT_ID ( '[dbo].[sp_get_deactivation_list]', 'P' ) is not null
    drop procedure [dbo].[sp_get_deactivation_list];
go
create procedure [dbo].[sp_get_deactivation_list]
	@companyId int,
	@startDate DateTime,
	@endDate DateTime
as
begin
	select
   	        tblRadioinfo.ID as Id,
		tblRadioinfo.Mobile_ID as MobileId,
		tblRadioinfo.Radio_Address as RadioAddress,
		tblRadioinfo.Deactivation_Date as DeactivationDate
	from tblRadioinfo
	where tblRadioinfo.Radio_Type_ID in (2, 4, 7)
	and tblRadioinfo.Company_ID = @companyId
	and tblRadioinfo.Deactivation_Date <= @endDate
	and tblRadioinfo.Deactivation_Date >= @startDate
	and tblRadioinfo.Radio_Address in _
	(select IMEI from [airtime_cdrs] where  Effective_Date > @startDate and Effective_Date < @endDate)
<p>	ORDER BY tblRadioinfo.Deactivation_Date
end

which when executed using the following simple test in SQL Studio Management Express 2012.

declare @route_id_param as varchar(10), @start_time as datetime, _
@start_date as datetime, @end_date as datetime
set @start_time = GETDATE()
set @start_date = CONVERT(DATETIME,'10/26/2013',101)
set @end_date = CONVERT(DATETIME,'12/26/2020',101) 
<p>exec dbo.sp_get_deactivation_list @companyId=1, @startDate = @start_date, @endDate = @end_date;
select execution_time_in_ms = DATEDIFF(millisecond, @start_time, getdate())
GO

returns two results as follows:

Now, say you will like to use NHibernate to execute this stored procedure and return the same two records.

An Approach

Assume you have the following class and interface definitions:

public class HibernateStoredProcedureExecutor : IExecuteStoredProcedure
   {
       private readonly ISessionFactory _sessionFactory;

       public HibernateStoredProcedureExecutor(ISessionFactory sessionFactory)
       {
           _sessionFactory = sessionFactory;
       }

       public IEnumerable<TOut> ExecuteStoredProcedure<TOut>
              (string procedureName, IList<SqlParameter> parameters)
       {
           IEnumerable<TOut> result;

           using (var session = _sessionFactory.OpenSession())
           {
               var query = session.GetNamedQuery(procedureName);
               AddStoredProcedureParameters(query, parameters);
               result = query.List<TOut>();
           }

           return result;
       }

       public TOut ExecuteScalarStoredProcedure<TOut>
              (string procedureName, IList<SqlParameter> parameters)
       {
           TOut result;

           using (var session = _sessionFactory.OpenSession())
           {
               var query = session.GetNamedQuery(procedureName);
               AddStoredProcedureParameters(query, parameters);
               result = query.SetResultTransformer(Transformers.AliasToBean
                        (typeof(TOut))).UniqueResult<TOut>();
           }

           return result;
       }

       public static IQuery AddStoredProcedureParameters
                 (IQuery query, IEnumerable<SqlParameter> parameters)
       {
           foreach (var parameter in parameters)
           {
               query.SetParameter(parameter.ParameterName, parameter.Value);
           }

           return query;
       }
   }

 public interface IExecuteStoredProcedure
   {
       TOut ExecuteScalarStoredProcedure<TOut>(string procedureName, IList<SqlParameter> sqlParameters);
       IEnumerable<TOut> ExecuteStoredProcedure<TOut>
           (string procedureName, IList<SqlParameter> sqlParameters);
   }

and somewhere within your project, you are invoking an instance of this class like this:

public IEnumerable<Activation> GetDeactivationList(int companyId, DateTime startDate, DateTime endDate)
      {
           var sessionFactory = BuildSessionFactory();
          var executor = new HibernateStoredProcedureExecutor(sessionFactory);
          var deactivations = executor.ExecuteStoredProcedure<Activation>(
            "GetDeactivationList",
            new[]
              {
                  new SqlParameter("companyId", companyId),
                  new SqlParameter("startDate", startDate),
                  new SqlParameter("endDate", endDate),
              });

          return deactivations;
      }

To get this all working, you will need a mapping for your stored procedure and one for the return type of your stored procedure, if your stored procedure is returning a result set of complex types. The mapping file for the stored procedure looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping 
  xmlns="urn:nhibernate-mapping-2.2" 
  assembly="HGT.IridiumAirtime.Service" 
  namespace="HGT.IridiumAirtime.Service.Model">
  <sql-query name="GetDeactivationList" callable="true">
    <query-param name="companyId" type="int"/>
    <query-param name="startDate" type="DateTime"/>
    <query-param name="endDate" type="DateTime"/>
    <return class="Activation">
      <return-property column="Id" name="Id" />
      <return-property column="MobileId" name="MobileId" />
      <return-property column="RadioAddress" name="RadioAddress" />
      <return-property column="DeactivationDate" name="DeactivationDate" />
    </return>
    exec [sp_get_deactivation_list] @companyId=:companyId, @startDate=:startDate, @endDate=:endDate
  </sql-query>
</hibernate-mapping>

and for the return type of our stored procedure, which is not really associated with any table in the database, is listed below:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping 
  xmlns="urn:nhibernate-mapping-2.2" 
  assembly="HGT.IridiumAirtime.Service" 
  namespace="HGT.IridiumAirtime.Service.Model">
  <class name="Activation" >
    <id name="Id">
      <generator class="identity"/>
    </id>
    <property column="MobileId" name="MobileId" />
    <property column="RadioAddress" name="RadioAddress" />
    <property column="DeactivationDate" name="DeactivationDate" />
  </class>
</hibernate-mapping>

Once you have all of the aforementioned in place, you should be able to write and execute successfully a unit test against the GetDeactivationList method. If you use the same input parameters as those used earlier, you should get the same two records.

The Gotchas

Bold lines in the aforementioned code sections were stumbling points and are worth mentioning. These were missing in my original iteration and were arrived at after numerous consultations against the document and StackOverflow.

Since one cannot define a Hibernate mapping file without the Id column, you must add one, even if it means nothing to your entity.

<id name="Id">
     <generator class="identity"/>
<id/>

An Id column defined by the above Hibernate mapping mandates its inclusion in the return type definition of your stored procedure, hence the reason of this entry in the stored procedure’s mapping file:

<return-property column="Id" name="Id" />

Otherwise, you will get an exception similar to this:

NHibernate.Exceptions.GenericADOException: could not execute query
[ exec [sp_get_deactivation_list] @companyId=@p0, @startDate=@p1, @endDate=@p2 ]
Name:companyId – Value:1 Name:startDate – Value:10/26/2013 12:00:00 AM 
Name:endDate – Value:12/26/2020 12:00:00 AM
[SQL: exec [sp_get_deactivation_list] @companyId=@p0, @startDate=@p1, 
@endDate=@p2] —> System.IndexOutOfRangeException: Id2_0_

An Id column defined in a Hibernate mapping file also mandates a corresponding public virtual property defined on the class. So we need this:

public virtual int Id { get; set; }

in the Activation class.

Finally, since your mapping has instructed NHibernate to expect an Id column in the result set, you must ensure one is returned by the stored procedure, hence the reason for this line:

assMobileRadio.ID as Id

In Conclusion

It has been relatively painful getting here but it now works. Hopefully, this helps out others running into a similar problem. If any of my statements are technically inaccurate, please let me know. Also, many thanks to some good folks at StackOverflow for their help.