Correct NHibernate Mapping For Stored Procedure?

Question

---

UPDATE: This is now resolved, see answer below.

---

I am having a bit of trouble trying to work out the correct way to write an NHibernate Mapping File (.hbm.xml) for a MSSQL Stored Procedure.

The Stored Procedure accepts two parameters and returns a single row result set containing several columns which have integer values.

My mapping file is as follows:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="SubsNetUk.DataAccess"
                   namespace="SubsNetUk.DataAccess.Models">

  <class name="MonthlyInstructionCount" lazy="true">
    <id name="Id" column="Month">
      <generator class="native" />
    </id>

    <property name="Month" />
    <property name="MapRequests" />
    <property name="Instructions" />
    <property name="DrainsLookSee" />
    <property name="DrainsFullCctv" />
    <property name="Soils" />
    <property name="Roots"/>
    <property name="Arb" />

    <loader query-ref="MI_MonthlyInstructionCount"/>
  </class>

  <sql-query name="MI_MonthlyInstructionCount">
    <return class ="MonthlyInstructionCount">
      <return-property name="Month" column="Month" />
      <return-property name="MapRequests" column="MapRequests" />
      <return-property name="Instructions" column="Instructions" />
      <return-property name="DrainsLookSee" column="DrainsLookSee" />
      <return-property name="DrainsFullCctv" column="DrainsFullCctv" />
      <return-property name="Soils" column="Soils" />
      <return-property name="Roots" column="Roots" />
      <return-property name="Arb" column="Arb" />
    </return>
    exec dbo.MI_MonthlyInstructionCount :StartDate :NumberOfMonths
  </sql-query>

</hibernate-mapping>

I have also tried the following, as suggested by someone on a forum for a similar problem another person was having relating to Stored Procedures...

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="SubsNetUk.DataAccess"
                   namespace="SubsNetUk.DataAccess.Models">

  <sql-query name="MI_MonthlyInstructionCount">
    <return class ="MonthlyInstructionCount" />
    exec dbo.MI_MonthlyInstructionCount :StartDate :NumberOfMonths
  </sql-query>

</hibernate-mapping>

Neither of which seem to work... I just get the following error:

The type initializer for 'SubsNetUk.DataAccess.Sql.NHibernateHelper' threw an exception.

{"Errors in named queries: {MI_MonthlyInstructionCount}"}

I can't see it being the Stored Procedure (though the error would suggest it is...) as it seems to run successfully in MSSQL Server Manager.

Any help is much appreciated! Cheers!

Answer 1

RESOLVED: I have now resolved this issue. First of all there was the issue of the ID property being set to "name=Id" instead of "name=Month". Secondly, I had to wrap the exec command in a CDATA and also put a comma seperator between the parameters. The full working mapping file is below for future reference to anyone experiencing a similar problem.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="SubsNetUk.DataAccess"
                   namespace="SubsNetUk.DataAccess.Models">

  <class name="MonthlyInstructionCount" lazy="true">
    <id name="Month">
      <generator class="native" />
    </id>

    <property name="MapRequests" />
    <property name="Instructions" />
    <property name="DrainsLookSee" />
    <property name="DrainsFullCctv" />
    <property name="Soils" />
    <property name="Roots"/>
    <property name="Arb" />

    <loader query-ref="MI_MonthlyInstructionCount"/>
  </class>

  <sql-query name="MI_MonthlyInstructionCount">
    <return class="MonthlyInstructionCount">
      <return-property name="Month" column="Month" />
      <return-property name="MapRequests" column="MapRequests" />
      <return-property name="Instructions" column="Instructions" />
      <return-property name="DrainsLookSee" column="DrainsLookSee" />
      <return-property name="DrainsFullCctv" column="DrainsFullCctv" />
      <return-property name="Soils" column="Soils" />
      <return-property name="Roots" column="Roots" />
      <return-property name="Arb" column="Arb" />
    </return>
    <![CDATA[ 
    exec MI_MonthlyInstructionCount :StartDate, :NumberOfMonths
    ]]>
  </sql-query>

</hibernate-mapping>

Cheers!