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