How to wrap IDbTransactions in a TransactionScope

Question

I have several code methods that look like this:

using (var connection = this.connectionFactory.GetConnection())
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        using (var command = connection.CreateCommand())
        {
            command.Transaction = transaction;
            command.CommandText = "foo";
            command.ExecuteNonQuery();
            transaction.Commit();
        }
    }
}

I now need to call several of these methods together inside an outer transaction, So I did this:

using (var transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
    method1();
    method2();
    method3();
}

but its doing:

The operation is not valid for the state of the transaction.
   at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
   at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()

Do I need to replace the IDbTransactions with TransactionScopes?

What TransactionScopeOption should I use for the outer an inner scopes? Im guessing i want RequiresNew for the outer and Required for the inners?

The methods will still be called individually (i.e. without an outer TransactionScope as well as together, so I still need them to be transactionally safe.

Thanks

Regent · Accepted Answer · 2015-06-17T10:15:06.303

7

I believe you are mixing technologies here and should avoid using TransactionScope and DbTransaction together because TransactionScope creates an implicit transaction.

So I would recommend to have your methods similar to:

using (var connection = this.connectionFactory.GetConnection())
{
    connection.Open();
    using (TransactionScope scope = new TransactionScope())
    {
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "foo";
            command.ExecuteNonQuery();
        }
        scope.Complete();
    }
}

Then you can call them together:

using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
    method1();
    method2();
    method3();

    scope.Complete();
}

and the methods you called will share the same transaction.

edited Jun 17 '15 at 10:15

answered Jun 08 '11 at 10:51

Regent

5,502
3
33
59

this was my plan, does this leave the top code transactionally safe? guess ill just write some test code to see! – Andrew Bullock Jun 08 '11 at 11:05
Re: the line `command.Transaction = transaction;` - Where does `transaction` come from? – Christoffer Lette Jun 16 '15 at 16:52
1

@ChristofferLette: my bad! You don't actually need to specify it explicitly as `TransactionScope` takes care of that - updated. – Regent Jun 17 '15 at 10:15

score 0 · Answer 2 · edited Aug 20 '13 at 10:26

0

I guess you are doing it right. The inner transaction is enrolled in the same scope of the outer one, and the whole thing will rollback. To have a new transactionscope you have specify "requiresnew".(which u have mentioned) for more clarity refer this post. http://web.archive.org/web/20091012162649/http://www.pluralsight.com/community/blogs/jimjohn/archive/2005/06/18/11451.aspx

edited Aug 20 '13 at 10:26

Alex Duggleby

7,948
5
37
44

answered Jun 08 '11 at 10:37

Aravind

4,125
1
28
39

that link not found , it's a pity – Kiquenet Apr 18 '12 at 09:52

score 0 · Answer 3 · answered Jun 08 '11 at 10:38

I am not sure of what I will say, but after reading this: http://msdn.microsoft.com/en-us/library/ms172152(v=vs.80).aspx#sectionSection3

I think you should use TransactionScope in your sub method, but we the option Required. So when they are called individually, you still have a transaction, and when you call them in the upper method, the transaction sill be integrated in the ambient transaction.

How to wrap IDbTransactions in a TransactionScope

3 Answers3

Linked