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?

Answer 1

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;

    ...
    ...

}

Answer 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.

Answer 3

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