NHibernate and Stored Procedures in C#

Posted by Jess Nickson on Simple Talk See other posts from Simple Talk or by Jess Nickson
Published on Fri, 27 Sep 2013 12:35:32 +0000 Indexed on 2013/10/17 16:19 UTC
Read the original article Hit count: 311

Filed under:

Uncategorized

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.

Developer IT