Derik Whittaker

Syndication

News


Joining NHibernate Transaction with ADO SqlTransaction

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.


Posted 06-15-2009 11:35 AM by Derik Whittaker
Filed under: ,

[Advertisement]

Comments

Artur wrote re: Joining NHibernate Transaction with ADO SqlTransaction
on 06-15-2009 3:50 PM

Don't know about the NH part, but there is no need to cast connection to specifc type. IDbConnection has CreateCommand() (or something like that, I'm writing from memory) method that you should use to create a compatible command.

chrissie1 wrote re: Joining NHibernate Transaction with ADO SqlTransaction
on 06-15-2009 3:55 PM

Why not use the connection.createcommand and all the other create things with all the interfaces. That way it will use whatever you have nhibernate use at that moment. Very short post here blogs.lessthandot.com/.../ado-net-use-the-interfaces

var connection = Session.Connection;  

    var sqlCommand =connectio.CreateCommand;  

                            sqlCommand . CommandType = CommandType.StoredProcedure ;                            sqlCommand . CommandText = StoredProcedures.DO_SOMETHING;  

                            sqlCommand . Connection =

I'm not sure what that will do with the transaction though.

Derik Whittaker wrote re: Joining NHibernate Transaction with ADO SqlTransaction
on 06-15-2009 3:59 PM

@Chrissie,

The issue is that NH uses ITransaction which cannot be cast to SqlTransation or IDBTransaction.

Also, the final code does include the call in the transaction

DotNetShoutout wrote Joining NHibernate Transaction with ADO SqlTransaction - Derik Whittaker - Devlicio.us
on 06-16-2009 10:23 AM

Thank you for submitting this cool story - Trackback from DotNetShoutout

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)