Using stored procedures with NHibernate

For some reason newbies to NHibernate struggle to find answers on Google or S.O when trying to use Stored Procedures with NHibernate. I have posted this here as a guide to help.

First define in a mapping file that is marked as embedded resource, call it StoredProcs.hbm.xml (the name doesn't matter but the extension .hbm.xml does) :-

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

The stored procedure returns the following SQL columns:-

int ProductId
nvarchar(75) ProductName
decimal(18,2) SalesTotal
bit IsActive 

First we define our c# class:-

public class SummaryReport {
  public virtual int ProductId { get; set; }
  public virtual string ProductName { get; set; }
  public virtual decimal SalesTotal { get; set; }
  public virtual boolen IsActive { get; set; }
}

NOTE: The column names returned from the stored procedure MUST match exactly (case sensitive) the property names on your c# class.

Now for the calling code, as you can see we make use of the method GetNamedQuery and SetResultTransformer:-

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

Note: we call the stored procedure with a parameter :productId. The colon tells NHibernate that this is a parameter. In our calling code we have .SetInt32("productId", productId);, notice that the colon is not required.

Simple isn't it?