Recently (ok, today) I needed to drop down into ADO to call a procedure in our code.  I was not too happy with it, but either my lack of knowledge or lack of will to figure it out mandated it.  What I needed to do was create a build-insert process and using NHibernate was not the right solution for performance reasons.

When I first created my code, seen below my tests worked fine.

public void AssignNewDestinationToPatients( Destination destination )
{
    var connection = (SqlConnection) Session.Connection;
    
    var sqlCommand = new SqlCommand
                         {
                             CommandType = CommandType.StoredProcedure,
                             CommandText = StoredProcedures.DO_SOMETHING,
                             Connection = connection,                                     
                         };

    sqlCommand.Parameters.Add( new SqlParameter( "DestinationID", destination.DestinationID ) );

    sqlCommand.ExecuteNonQuery()    ;
}

However, once this call was wrapped around an NH transaction i would receive the following error:

{"ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized."}

Of course the first I tried to do was cast the NH transaction to a sql transaction and provide that to the command object.  However, that is NOT possible.  The great news for me was the NH has the capacity to enlist a sql commmand and add it to its transaction.  The code below shows how to do this.

public void AssignNewDestinationToPatients( Destination destination )
{
    var connection = (SqlConnection) Session.Connection;
    
    var sqlCommand = new SqlCommand
                         {
                             CommandType = CommandType.StoredProcedure,
                             CommandText = StoredProcedures.DO_SOMETHING,
                             Connection = connection,                                     
                         };

    // this is the magic code
    if ( Session.Transaction != null && Session.Transaction.IsActive )
    {
        Session.Transaction.Enlist( sqlCommand );
    }

    sqlCommand.Parameters.Add( new SqlParameter( "DestinationID", destination.DestinationID ) );

    sqlCommand.ExecuteNonQuery()    ;
}

So, if you need to drop down to ADO from NHibernate and you need to use transactions, this solution should work out well for you.

Till next time,

P.S. If there is a better, simpler way please let me know.