C# – NHibernate use Stored Procedure OR mapping

c++, nhibernate

I have a mapping in NHibernate that works like so:

<?xml version="1.0" encoding="utf-8" ?><hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="BizEntities"    namespace="BizEntities"    default-lazy="false">  <class name="SubscriberQueueItem" table="SubscriberQueueItem">    <id name="SubscriberQueueItemId" column="Id" type="int" unsaved-value="0">      <generator class="identity" />    </id>    <property name="DateCreated" column="DateCreated"  type="DateTime" />    <property name="CMIId" column="CMIId" type="int" />    <property name="DateProcessed" column="DateProcessed"  type="DateTime" />    <property name="EventStatus" column="EventStatusId" type="QueueStatusTypeValues, BizEntities" />    <many-to-one  name="Subscription"  class="Subscription" column="SubscriptionId" />    <property name="ErrorDescription" column="ErrorDescription" type="string" />  </class></hibernate-mapping>

and it's retrieved with simple queries against the table.

Is it possible to also map this class to a stored procedure? I've got a procedure written that spits back a specific subsection of data that's difficult to write into an NHibernate query, but easy to write as a stored procedure.

Can I simply add a stored procedure mapping as answered here, and retrieve the object with direct mapping or stored proc based on my NHibernate query type, or does adding a stored procedure mapping to my hbm mean I can only retrieve based on that stored procedure?

Best Solution

Stored procedures work just fine in NHibernate, I'm using them no problem :)

You'll need to add a "named query" into your hibernate mapping, like so:

<sql-query name="spMyProcedure">    <!-- return type must be an NHibernate mapped entity -->    <return alias="SubscriberQueueItem" type="BizEntities.SubscriberQueueItem, BizEntities" />    exec spMyProcedure @Param1=:Param1, @Param2=:Param2</sql-query>

If the return type of your stored procedure does not match an already mapped entity, you'll need to create a new one.

To invoke the sp, you'll need to add the following code:

var query = session.GetNamedQuery("spMyProcedure");query.SetParameter("Param1", "hello");query.SetParameter("Param2", "byebye");SubscriberQueueItem result = query.UniqueResult<SubscriberQueueItem>();