Execute Stored Procedure in SQL Server using nHibernate

Posted by in Design Pattern & Practices category on for Beginner level | Views : 30044 red flag

I was facing a lot of challenges to execute Stored Procedure through nHibernate. 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.

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

Hibernate Model 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 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();

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.


Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Ziaul Haque
Member Level: Starter
Member Status: Member
Member Since: 12/14/2009 7:15:17 AM
Country: India

Microsoft technology Specialist

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)