NHibernate and Stored Procedures in C#
- by Jess Nickson
I was recently trying and failing to set up NHibernate (v1.2) in an ASP.NET project. The aim was to execute a stored procedure and return the results, but it took several iterations for me to end up with a working solution. In this post I am simply trying to put the required code in one place, in the hope that the snippets may be useful in guiding someone else through the same process. As it is kind’ve the first time I have had to play with NHibernate, there is a good chance that this solution is sub-optimal and, as such, I am open to suggestions on how it could be improved!
There are four code snippets that I required:
The stored procedure that I wanted to execute
The C# class representation of the results of the procedure
The XML mapping file that allows NHibernate to map from C# to the procedure and back again
The C# code used to run the stored procedure
The Stored Procedure
The procedure was designed to take a UserId and, from this, go and grab some profile data for that user. Simple, right? We just need to do a join first, because the user’s site ID (the one we have access to) is not the same as the user’s forum ID.
CREATE PROCEDURE [dbo].[GetForumProfileDetails] ( @userId INT )
AS
BEGIN
SELECT Users.UserID,
forumUsers.Twitter,
forumUsers.Facebook,
forumUsers.GooglePlus,
forumUsers.LinkedIn,
forumUsers.PublicEmailAddress
FROM Users
INNER JOIN Forum_Users forumUsers
ON forumUsers.UserSiteID = Users.UserID
WHERE Users.UserID = @userId
END
I’d like to make a shout out to Format SQL for its help with, well, formatting the above SQL!
The C# Class
This is just the class representation of the results we expect to get from the stored procedure. NHibernate requires a virtual property for each column of data, and these properties must be called the same as the column headers. You will also need to ensure that there is a public or protected parameterless constructor.
public class ForumProfile : IForumProfile
{
public virtual int UserID { get; set; }
public virtual string Twitter { get; set; }
public virtual string Facebook { get; set; }
public virtual string GooglePlus { get; set; }
public virtual string LinkedIn { get; set; }
public virtual string PublicEmailAddress { get; set; }
public ForumProfile()
{
}
}
The NHibernate Mapping File
This is the XML I wrote in order to make NHibernate a) aware of the stored procedure, and b) aware of the expected results of the procedure.
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="[namespace]" assembly="[assembly]">
<sql-query name="GetForumProfileDetails">
<return-scalar column="UserID" type="Int32"/>
<return-scalar column="Twitter" type="String"/>
<return-scalar column="Facebook" type="String"/>
<return-scalar column="GooglePlus" type="String"/>
<return-scalar column="LinkedIn" type="String"/>
<return-scalar column="PublicEmailAddress" type="String"/>
exec GetForumProfileDetails :UserID
</sql-query>
</hibernate-mapping>
Calling the Stored Procedure
Finally, to bring it all together, the C# code that I used in order to execute the stored procedure!
public IForumProfile GetForumUserProfile(IUser user)
{
return NHibernateHelper
.GetCurrentSession()
.GetNamedQuery("GetForumProfileDetails")
.SetInt32("UserID", user.UserID)
.SetResultTransformer(
Transformers.AliasToBean(typeof (ForumProfile)))
.UniqueResult<ForumProfile>();
}
There are a number of ‘Set’ methods (i.e. SetInt32) that allow you specify values for any parameters in the procedure. The AliasToBean method is then required to map the returned scalars (as specified in the XML) to the correct C# class.
