To circumvent this limitation (feature), I have implemented a data session object used to pass an open SqlConnection object around my biz logic and data access logic. Last week, when implementing a unit test for some logic involving a method in the call stack using the .Suppress option, I noted that changes made to the data was not rolled back when the error I was debugging, occurred.
Some experimentation with nested transaction scopes lead me to conclude that I needed to open the common, shared connection within a TransactionScopeOption.Required using block before passing it around to get the correct behavior (rollback on error, etc). It seemed that opening the connection in a .Suppress block would suppress transactions even in nested 'required' scopes.
I posted my findings at forums.microsoft.com and got an "expected behavior" from Alazel Acheson of the ADO.NET team. The explanation is that the connection will enlist in a transaction only when opened, thus when opened in a suppress block, the connection will never use a transaction. To ensure that a connection gets enlisted in a transaction, you need to actually open the connection within each using TransactionScope block.The need to stay LTM is a very common requirement in the System.Transactions community, and Acheson has implemented a DbConnectionScope class that simplifies the process of using a common connection in nested transaction scopes. The DbConnectionScope class has been further refined to include nesting options to provide detailed connection reuse control. Good work, Alazel!
I recommend anyone who has implemented their own LTM data session object to switch to the DbConnectionScope. I strongly advice never to roll your own transaction management mechanism, the chances are that you will not be able to make it 100% bulletproof.
2 comments:
That's interesting but seems a bit messy or a lot just to code this simple code.
Yea I know this is like the only work around based on posts I've found but what if you need to add a parameter to your command?
You wouldn't want to do this in the InnerMethod as the params are specific to each Query call.
If you're calling 2 queries or procs inside a transaction scope, half the time you're gonna need to to add parameters to the command object between these 2 lines in the InnerMethod:
SqlCommand cmd = conn.CreateCommand();
//add parames here but for what query call!! bad
cmd.ExecuteNonQuery();
Maybe I'm missing it but you should be able to add these params to your SqlCommand object inside the OuterMethod.
I don't see how SqlCommand params relates to having a shared, opened SqlConnection used across all the SqlCommand objects.
You can use as many different SqlCommand objects as you like, just remember to attach the shared SqlCommand/DbConnectionScope to the commands to stay LTM.
Post a Comment