Click here to Skip to main content
15,506,675 members
Articles / Programming Languages / C#
Posted 18 Jun 2009


33 bookmarked

Execute Stored Procedure in SQL Server using nHibernate

Rate me:
Please Sign up or sign in to vote.
4.29/5 (6 votes)
20 Aug 2009CPOL1 min read
Useful tips to excecute stored procedure using nHibernate


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


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

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


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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Web Developer
India India
want to contribute more on myself growth.

Comments and Discussions

Questionuse stored proc in nhibernate Pin
swemme1810-May-12 18:58
swemme1810-May-12 18:58 
Question[My vote of 2] SetResultTransformer Pin
abellix25-Aug-11 2:29
abellix25-Aug-11 2:29 
AnswerRe: [My vote of 2] SetResultTransformer Pin
ziaulh27-Aug-11 23:55
ziaulh27-Aug-11 23:55 
QuestionHow the column map to property work? Pin
tola.chhoeun9-Oct-10 8:55
tola.chhoeun9-Oct-10 8:55 
AnswerRe: How the column map to property work? Pin
ziaulh18-Nov-10 19:23
ziaulh18-Nov-10 19:23 
GeneralSample Code Pin
Sougandh29-Mar-10 21:04
Sougandh29-Mar-10 21:04 
GeneralRe: Sample Code Pin
ziaulh18-Nov-10 19:27
ziaulh18-Nov-10 19:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.