Execute Stored Procedure in SQL Server using nHibernate
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.