Is there a way to use TransactionScope with an existing connection?

Question

I have some code that works like the advised use of TransactionScope, but has an ambient connection instead of an ambient transaction.

Is there a way to use a TransactionScope object with an existing connection, or is there an alternative in the .Net framework for this purpose?

score 32 · Accepted Answer · answered Jul 01 '10 at 16:11

32

In fact, there is one way.

connection.EnlistTransaction(Transaction.Current)

It works and it doesnt promote transaction to distributed if not necessary (contrary to what documentation says)

HTH

answered Jul 01 '10 at 16:11

Michal Levý

24,601
3
44
68

what is `connection` ? – Muflix Feb 05 '20 at 17:55
1

Just any class derived from [DbConnection](https://docs.microsoft.com/en-us/dotnet/api/system.data.common.dbconnection?view=netframework-4.8#methods) – Michal Levý Feb 05 '20 at 20:42

Mitch Wheat · Answer 2 · 2009-06-01T12:08:47.523

5

To enlist a connection into a TransactionScope, you need to specify 'Enlist=true' in its connection string and open the connection in the scope of that TransactionScope object.

You can use SqlConnection.BeginTransaction on an existing connection.

Update: Can you use BeginTransaction like this:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    SqlCommand command = connection.CreateCommand();
    SqlTransaction transaction;

    // Start a local transaction.
    transaction = connection.BeginTransaction("SampleTransaction");

    // Must assign both transaction object and connection
    // to Command object for a pending local transaction
    command.Connection = connection;
    command.Transaction = transaction;

    ...
    ...

}

edited Jun 01 '09 at 12:08

answered Jun 01 '09 at 10:55

Mitch Wheat

288,400
42
452
532

The thing is, the connection is opened in advance of the TransactionScope instantiation; hence the reason for my question. – Nathan Ridley Jun 01 '09 at 11:03
@Mitch, Doesn't the connection.BeginTransaction("SampleTransaction") promote to a Distributed Transaction (DTC) in any scenario? If it does, then what are those scenarios? – Baig Jul 28 '11 at 13:24

score 2 · Answer 3 · answered Jun 10 '09 at 08:57

2

After more research, the answer to my question turned out to be:

No, the connection needs to be opened after the TransactionScope object is instantiated.

answered Jun 10 '09 at 08:57

Nathan Ridley

32,688
32
118
193

Is there a way to use TransactionScope with an existing connection?

3 Answers3

Linked