Execute Stored Procedure in SQL Server using nHibernate

Introduction

This article shows how to execute a stored procedure (SP) in Microsoft Visual Studio (C#) using nHibernate.

Background

I was facing a lot of challenges to complete this task. As I was new to nHibernate technology, Googling also did not help me much on this front and I spent a lot of time to get the actual result. Here, I am sharing with you some tips while doing this type of activity.

Useful Tips

Hibernate Config File

Points to remember while writing nHibernate configuration file (*.hbm.xml).

All the returned parameters in the stored procedure will be as "return-scalar" with column name and type.
Use db aliases instead of db column name in the stored procedure.
If your SP has input parameters, then use comma separated ? marks for each.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<sql-query name="GetCustomerOrderHistory"> //code name of your SP
<return-scalar column="ProductName" type="String" /> //all the return parameters 
<return-scalar column="Total" type="Int32" />
exec CustOrderHist :? 
</sql-query>
</hibernate-mapping>

Hibernate Model Class

The following are very important while writing a model class for your hibernate configuration for SP:

There will be a property for each returned parameter.
There will be only one parametric constructor in your model class and you need to set all private variables with a specific one.

public class HistoricalOrderSummary
{
    public HistoricalOrderSummary(string productName, int totalQuantity) {
    _productName = productName;
    _totalQuantity = totalQuantity;
}

public string ProductName {
    get { return _productName; }
}

public int TotalQuantity {
    get { return _totalQuantity; }
}

private string _productName;

private int _totalQuantity;
}

Hibernate Repository Class

I assume that you are already aware how to initialize/create/mapped nHibernate session.

To execute a stored procedure, hibernate uses the GetNamedQuery method.

public class HistoricalOrderSummaryDao 
{
    public List<HistoricalOrderSummary> GetCustomerOrderHistoryFor(string customerId) {
    IQuery query = NHibernateSession.GetNamedQuery("GetCustomerOrderHistory")
    query .SetString("CustomerID", customerId)
    query .SetResultTransformer(
    new NHibernate.Transform.AliasToBeanConstructorResultTransformer(
    typeof (HistoricalOrderSummary).GetConstructors()[0]));
    return query.List<HistoricalOrderSummary>() as List<HistoricalOrderSummary>;
}
private ISession NHibernateSession {
    get {
        return NHibernateSessionManager.Instance.GetSession();
        }
    }
}

Conclusion

It is not a difficult job to find the solution, but it is important to remember some tips. Please share your inputs or thoughts regarding the same. This article helped me a lot to complete my task. If you need any help or assistance, then please let me know. Thank you.