UPDATE: this is now allowed, see answer below.
I'm having trouble trying to work out the correct way to write the NHibernate mapping file (.hbm.xml) for the MSSQL stored procedure.
A stored procedure takes two parameters and returns a single row row result set containing multiple columns that 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="TestApp.DataAccess"
namespace="TestApp.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 also tried the following, as suggested by someone on the forum for a similar problem that another person had to do with stored procedures ...
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="TestApp.DataAccess"
namespace="TestApp.DataAccess.Models">
<sql-query name="MI_MonthlyInstructionCount">
<return class ="MonthlyInstructionCount" />
exec dbo.MI_MonthlyInstructionCount :StartDate :NumberOfMonths
</sql-query>
</hibernate-mapping>
None of them work ... I just get the following error:
The type initializer for 'TestApp.DataAccess.Sql.NHibernateHelper' threw an exception.
{ " : {MI_MonthlyInstructionCount}" }
, ( , ...), , , MSSQL.
! !