Fluent NHibernate Stored Procedure

Summary

This week I’m going to give a very simple example of how to execute a stored procedure.  The stored procedure will only do a select query, but I’ll pass a variable to demonstrate how it’s done and I’ll leave it up to the reader to alter the code to execute other types of stored procedures.  I’m going to build on my last post, so I’ll use all the same code except for the main method of the console application.  If you haven’t read my previous post on NHibernate go here: Fluent NHibernate using Multiple Databases and setup your database to match the example.

The Stored Procedure

Copy this SQL command into your query window of MS SQL server, and execute it. It should generate a stored procedure in your database. Make sure this stored procedure is in the facultydata database.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[pSelectClassPerTeacher]
@TeacherId int
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM class WHERE teacherid = @TeacherId 
END

The Code

The only change I made to the Main method of program.cs is this:

using (ISession session = NHibernateHelper.OpenSession())
{
    var query = session.CreateSQLQuery("exec pSelectClassPerTeacher :TeacherId")
        .AddEntity(typeof(Class))
        .SetParameter("TeacherId", 2)
        .List<Class>();

    foreach (var item in query)
    {
        Console.WriteLine("ClassName=" + item.Name.Trim() + " TeacherId=" + item.TeacherId.ToString());
    }

    Console.ReadKey();
}

That’s it. Now run it. It should produce a list of class names with teacher number 2. One of the reasons why my example was so simple is that I selected all records from the class table. This produced the same output as the “Class” object that defines that table. You can do any fancy query that produces other types of output, followed up by defining a special return type class that has all the expected fields. Also, each parameter must be accounted for inside the string and it must have a matching “.SetParameter()” method call.

I know, this is a short post, but this turns out to be an easy subject.

5 thoughts on “Fluent NHibernate Stored Procedure

  1. Hi Frank,

    I accidently came to your blog via searching something and I found it quite usefull. You are doing great job!!.

    In this particular post I have found that link your previous blog post "Fluent Nhibernate with Multiple databases" is broken. It is pointing to some IP address instead of your blog post.

    Keep sharing!!

    Thanks,
    Jalpesh Vadgama

  2. Wow, thanks for pointing out the broken link. I fixed it. I'm also happy to see that other people are getting value from knowledge that I post on my blog. I spent a lot of years of my life figuring stuff like this out on my own, only to never have the opportunity to share. It's always good to find a blog post that fixes a problem right away. Thanks.

  3. That's same vision I have for my blog when I write my blog. It's always about giving something back to community and share experience so that it can help other people to achieve result faster.

Leave a Reply