How to build a mapping file for an insert stored procedure using NHibernate 2.1

Question

I'm looking for a way to map a simple insert stored procedure using NHibernate 2.1

Most of the examples I find are for getting data back from a sproc - but what I'm trying to do is insert an audit like record using a stored procedure. But I would prefer to build a mapping file for this

Also - to be clear I'm not looking to build an entity + mapping file like the below. Instead I wanted to see if I could simply map the params in a single xml file and call it via "CreateSQLQuery" using the session object

<class name="Staff, SampleApplication.Library" table="Staff">  
    <id name="Id" column="Id" type="Int32">  
      <generator class="native" />  
    </id>  
    <property name="LastName" column="LastName" type="String" length="255"/>  
    <property name="FirstName" column="FirstName" type="String" length="255"/>  
    <property name="EmailAddress" column="EmailAddress" type="String" length="512"/>  

    <sql-insert>EXEC InsertStaff ?,?,?</sql> 
</class>

Answer 1

As the name suggests, CreateSQLQuery is for queries, not for insert/updates/deletes. Invoking the kind of sproc you describe is possible, but not AFAIK via any NHibernate-supported query mechanism.

The sproc itself can be included in a mapping file via the <database-object> tag. The following example is for Sql Server :

<database-object>
  <create>
    <![CDATA[
      go
      create procedure dbo.SetFooBar
        @Foo nvarchar(32)
        @Bar nvarchar(32)
      as
      begin
        ...
      end
      go
    ]]>
  </create>
  <drop>
    <![CDATA[
      if object_id (N'dbo.SetFooBar', N'P') is not null
        drop procedure dbo.SetFooBar;
      go
    ]]>
  </drop>
</database-object>

Your code can then invoke the sproc. This is done by getting NHibernate to create an IDbCommand for you, filling it with parameters, and calling its ExecuteNonQuery method in the usual ADO.NET way :

private void SetFooBar(ISession session, string foo, string bar)
{
  var connection = session.Connection;
  var implementor = session.GetSessionImplementation();
  var driver = implementor.Factory.ConnectionProvider.Driver;
  var command = driver.GenerateCommand(CommandType.StoredProcedure, 
                  new global::NHibernate.SqlCommand.SqlString("SetFooBar"), 
                  new global::NHibernate.SqlTypes.SqlType[] {
                    global::NHibernate.SqlTypes.SqlTypeFactory.GetString(32),
                    global::NHibernate.SqlTypes.SqlTypeFactory.GetString(32),
                  });

  command.Connection = connection;

  SetParameter(command, 0, "@Foo", foo);
  SetParameter(command, 1, "@Bar", foo);

  command.ExecuteNonQuery();
}

private void SetParameter(IDbCommand cmd, int index, string name, object value)
{
  IDataParameter param = (IDataParameter) cmd.Parameters[index];

  param.ParameterName = name;
  param.Value = value;
}