Click here to Skip to main content
Click here to Skip to main content

Execute Stored Procedure in SQL Server using nHibernate

, 20 Aug 2009
Rate this:
Please Sign up or sign in to vote.
Useful tips to excecute stored procedure 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.

License

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

Share

About the Author

ziaulh
Web Developer
India India
want to contribute more on myself growth.

Comments and Discussions

 
Questionuse stored proc in nhibernate Pinmemberswemme1810-May-12 17:58 
Question[My vote of 2] SetResultTransformer Pinmemberabellix25-Aug-11 1:29 
AnswerRe: [My vote of 2] SetResultTransformer Pinmemberziaulh27-Aug-11 22:55 
QuestionHow the column map to property work? Pinmembertola.chhoeun9-Oct-10 7:55 
AnswerRe: How the column map to property work? Pinmemberziaulh18-Nov-10 18:23 
GeneralSample Code PinmemberSougandh29-Mar-10 20:04 
GeneralRe: Sample Code Pinmemberziaulh18-Nov-10 18:27 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 20 Aug 2009
Article Copyright 2009 by ziaulh
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid