Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Configuring NHibernate to execute a Stored Procedure that returns a result set

, 28 Apr 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
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 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:

Id            MobileId              RadioAddress    DeactivationDate
3              MobileID_2        300034013417890             2020-12-26 00:00:00.000
4              MobileID_3        300034012356790             2020-12-26 00:00:00.000

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 you 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
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, much thanks to some good folks at StackOverflow for their help.


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Nji, Klaus

Canada Canada
No Biography provided

Comments and Discussions

 
GeneralIt is very much useful Pinmemberjeya prakash17-Sep-14 1:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141030.1 | Last Updated 28 Apr 2014
Article Copyright 2014 by Nji, Klaus
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid