CODEWITHSUNDEEP
c#nhibernatestored-procedures
Nil Pun
Nil Pun

Reputation: 17373

nHibernate stored procedure call

I'm quite new to nHibernate ORM. Could someone please help me to understand/resolve the scenario I've below?

I have a stored procedure called getSummaryReport which expects @productID as parameter. This stored procedure joins multiple tables and gives the summary data.

I need to load the data returned from the above stored procedure using nhibernate. Could someone please help me what how it's possible to call stored procedure via nHibernate?

Some of the questions running through my head right now is:

  1. Do I need a mapping xml? If yes what goes on to the mapping xml as I understand there should be physical table for each property. In above case stored procedure is generating brand new object.

  2. How do I call the above stored procedure from within C#?

Thank you again.

Upvotes: 3

Views: 3363

Answers (1)

Rippo
Rippo

Reputation: 22424

Try this, your mapping:-

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <sql-query name="SummaryReport">
    exec getSummaryReport  :productId
  </sql-query>
</hibernate-mapping>

and then use SetResultTransformer...

var results = Session
      .GetNamedQuery("SummaryReport")
      .SetInt32("productId", productId);
      .SetResultTransformer(new AliasToBeanResultTransformer(typeof(YOURCLASS)));
return results.List<YOURCLASS>();

and YOURCLASS is:-

public class YOURCLASS 
{
    public virtual int ProductId { get; set; }
    public virtual string Column1Returned { get; set; }
    public virtual int Column2Returned { get; set; }
            etc..
}

Make sure what ever is returned from your SP is defined in YOURCLASS remembering that your column names and property names have to match exactly as they are CASE sensitive.

Upvotes: 5

Related Questions