Clever Geek Handbook

Calling nHibernate stored procedure

I am new to nHibernate ORM. Can someone please help me understand / solve the scenario I have below?

I have a stored procedure called getSummaryReport

that expects @productID

as a parameter. This stored procedure concatenates multiple tables and summarizes the data.

I need to load the data returned from the above stored procedure using nhibernate. Can someone please help me how the stored procedure can be called via nHibernate?

Some of the questions I have now are:

  • Do I need xml mapping? If so, what happens to the xml display, as I understand there must be a physical table for each property. In the above case, the stored procedure generates a new object.

  • How do I call the above stored procedure from C #?

Thanks again.

+3


source to share


1 answer


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: -

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

Make sure that anything ever returned from your SP is defined in YOURCLASS

, keeping in mind that your column names and property names must match exactly as they are CASE

sensitive.

+5


source






More articles:


All Articles