Fluent NHibernate Execute Stored Procedure

Summary

I’ve demonstrated stored procedures a couple of times on this blog, but this time I’m going to show you how to execute a stored procedure that doesn’t need to return a value. You can use the same syntax to execute an update, insert or delete query (in cases where you might want to make bulk changes).

The Setup

I’m going to use my facultydata database from previous blog posts. Here’s the ERD:

My stored procedure will look like this:

USE [facultydata]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pSetClassForTeacher]
@TeacherId int,
@ClassId int
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE class 
    SET teacherid=@TeacherId 
    WHERE id=@ClassId

END

This is just a simple example. It’s not very useful, but easy to understand.

The Project

First, I’m not going to do any mappings or table classes. I’m going to create a console application with a session factory and just call the stored procedure. Here’s the complete code (minus the session factory):

using (var db = MSSQLSessionFactory.OpenSession())
{
    db.CreateSQLQuery("exec pSetClassForTeacher 
        @TeacherId=:TeacherId, @ClassId=:ClassId")
        .SetParameter("TeacherId", 3)
        .SetParameter("ClassId", 84)
        .ExecuteUpdate();
}

Don’t forget that SQL Server uses a comma between variables.

This stored procedure will update the class table and put a 3 in the teacher id field (make sure you’re using valid values. My database has records for class with an id of 84 and a teacher id of 3).

If you have a long-running stored procedure and you need to set the timeout longer, you can use the SetTimeout() method:

using (var db = MSSQLSessionFactory.OpenSession())
{
    db.CreateSQLQuery("exec pSetClassForTeacher 
        @TeacherId=:TeacherId, @ClassId=:ClassId")
        .SetParameter("TeacherId", 3)
        .SetParameter("ClassId", 84)
        .SetTimeout(100)
        .ExecuteUpdate();
}

Keep in mind that the set timeout method is measured in seconds.

Executing SQL Queries

You can use the same command to execute a query directly. Maybe you want to delete all records of a given foreign key, or perform the same update that my stored procedure performed, without creating a stored procedure. You can do it using this syntax:

using (var db = MSSQLSessionFactory.OpenSession())
{
    db.CreateSQLQuery("UPDATE facultydata..class SET teacherid=:TeacherId WHERE id=:ClassId")
        .SetParameter("TeacherId", 3)
        .SetParameter("ClassId", 84)
        .SetTimeout(100)
        .ExecuteUpdate();
}

You can download the sample project here:

FluentNHibernateStoredProcedure.zip