Fluent NHibernate More Stored Procedures Without Mapping

I did a sample post on how Fluent NHibernate executes a stored procedure, but I didn’t dig into the subject and show some details. The sample I showed returned a list of tables that were already mapped and it made the whole sample easy, but not realistic. In most cases we want to execute a stored procedure to return some result that is not already mapped. So I’m going to show how this is done.

The Stored Procedure:

Use this code to generate an MS SQL stored procedure in your sampledata database (assuming you have one):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[ReadPersonNames]

AS
BEGIN
 SET NOCOUNT ON;

 SELECT first, last FROM person
END

The Stored Procedure Return Class

You’ll need to create a class to contain the data type that will be returned. Here’s what I created for this example:

public class PersonNameRecord
{
    public virtual string first { get; set; }
    public virtual string last { get; set; }
}

Call the Stored Procedure

Here’s my call to the stored procedure:

var personNameList = db.CreateSQLQuery("exec ReadPersonNames")
 .SetResultTransformer(Transformers.AliasToBean<PersonNameRecord>())
    .List<PersonNameRecord>()
    .ToList();

You’ll need to include a “using” statement at the top to make this work:

using NHibernate.Transform;

That’s it.  You can download the sample code here:

FluentNHibernateStoredProc2.zip