Executing Stored Procedure With NHibernate

There are two reasons why you might want to execute a stored procedure using NHibernate.  The first would be for your normal CRUD activities which can be found here, Populating Entities From Stored Procedures With NHibernate.  I’d like to focus on the other scenario you might find yourself needing to execute a procedure, like running a complex report or some query that is better performed in a procedure.  The real benefit of NHibernate is that you’re not coding to an implementation but to a set of interfaces.  This buys you the blissful ignorance of the underlying database vendor, see also my post on NHibernate And Paging Results.

Executing a procedure requires two main items, a connection and a command object.  The connection comes from the session, and the command comes from the connection’s method CreateCommand.  Below shows a very straightforward way of executing a procedure.  Notice that the connection and command objects are just interfaces from System.Data namespace.

List<SomeDto> list = new List<SomeDto>();

using (ISession session = NHSessionManager.OpenSession())
{
    // use the ISession to get the database connection
    IDbConnection conn = session.Connection;

    // use the connection object to get the database command object
    IDbCommand cmd = conn.CreateCommand();

    // set the command text to the procedure's name and set the command type
    cmd.CommandText = "dbo.MyAwesomeProcdure";
    cmd.CommandType = CommandType.StoredProcedure;

    // using the command object you can create a parameter, setting it's properties accordingly
    IDbDataParameter p;
    p = cmd.CreateParameter();
    p.DbType = DbType.Int32;
    p.ParameterName = "@someParam";
    p.Value = someParamValue;
    cmd.Parameters.Add(p);

    // data read to capture results
    IDataReader reader = null;
    try
    {
        reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
        while (reader.Read())
            list.Add(new SomeDto
            {
                Name = reader.GetString(0),
                Age = reader.GetInt32(1),
                ...
            });
    }
    finally
    {
        if (reader != null)
            reader.Close();
    }
}

return list;