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.