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. nHibernate requires two things to execute 1) config file and 2) Domain Class as per config file. I will separately expalin all the details about it. Also i would like inform you that I am describing this content on the Concept of Repositary pattern. Please have a look on Repository pattern from internet.

Let our SP name is "GetCustomerOrderHistory" and CustomerID as input parameters and returns ProductName, Total as out output parameters.

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>

public class CustomerOrderHistory
{
    public CustomerOrderHistory(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;
}

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 CustomerOrderHistoryDao 
{
    public List<CustomerOrderHistory> GetCustomerOrderHistory(string customerId) {
    IQuery query = NHibernateSession.GetNamedQuery("GetCustomerOrderHistory")
    query .SetString("CustomerID", customerId)
    query .SetResultTransformer(
    new NHibernate.Transform.AliasToBeanConstructorResultTransformer(
    typeof (CustomerOrderHistory).GetConstructors()[0]));
    return query.List<CustomerOrderHistory>() as List<CustomerOrderHistory>;
}
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.  If you need any help or assistance, then please let me know. Thank you.

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.