How to force extenal library to use SQL transaction

Question

I have an external library to which I pass an instance of System.Data.SqlClient.SqlConnection and I want to wrap everything that library does on that connection in a transaction. When I was working with php/doctrine I would simply do exactly that in such cases - start a transaction in my code, call stuff on the library which issues DB queries and then commit the transaction in my code. When I tried to use this approach in C#, I got the following exception:

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

So I took a look at the library code and it always uses SqlCommand without setting the Transaction property. Is it possible to achieve my goal somehow? (changing the library code isn't feasible)

Post the code. If you use a `TransactionScope` both the connection and commands will run under an implicit transaction. I suspect you use an explicit transaction by calling `SqlConnection.BeginTransaction` — Panagiotis Kanavos, May 09 '19 at 15:05
Ceck [Implementing an Implicit Transaction using Transaction Scope](https://learn.microsoft.com/en-us/dotnet/framework/data/transactions/implementing-an-implicit-transaction-using-transaction-scope) — Panagiotis Kanavos, May 09 '19 at 15:07

score 1 · Accepted Answer · answered May 09 '19 at 15:28

You haven't posted your code but I assume you tried to use an explicit transaction by calling SqlConnection.BeginTransaction().

You can use a TransactionScope to create an implicit transaction. Any connection, command created inside the TransactionScope's lifetime will be enlisted in a transaction automatically.

Copying from Implementing an Implicit Transaction using Transaction Scope's example:

    // Create the TransactionScope to execute the commands, guaranteeing
    // that both commands can commit or roll back as a single unit of work.
    using (TransactionScope scope = new TransactionScope())
    {
        using (SqlConnection connection1 = new SqlConnection(connectString1))
        {
            // Opening the connection automatically enlists it in the 
            // TransactionScope as a lightweight transaction.
            connection1.Open();

            // Create the SqlCommand object and execute the first command.
            SqlCommand command1 = new SqlCommand(commandText1, connection1);
            returnValue = command1.ExecuteNonQuery();
            writer.WriteLine("Rows to be affected by command1: {0}", returnValue);

            // If you get here, this means that command1 succeeded. By nesting
            // the using block for connection2 inside that of connection1, you
            // conserve server and network resources as connection2 is opened
            // only when there is a chance that the transaction can commit.   
            using (SqlConnection connection2 = new SqlConnection(connectString2))
            {
                // The transaction is escalated to a full distributed
                // transaction when connection2 is opened.
                connection2.Open();

                // Execute the second command in the second database.
                returnValue = 0;
                SqlCommand command2 = new SqlCommand(commandText2, connection2);
                returnValue = command2.ExecuteNonQuery();
                writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
            }
        }

        // The Complete method commits the transaction. If an exception has been thrown,
        // Complete is not  called and the transaction is rolled back.
        scope.Complete();

    }

The connection and both commands in this example run under a single transaction. Should an exception occur, the transaction will be rolled back.

Yes, I was using `BeginTransaction`. I'll try `TransactionScope`, seems like what I need. A small question - does this require that the connection is created inside the `TransactionScope`? Or will ones that were `new`ed before `new TransactionScope` also work? — Xymanek, May 09 '19 at 17:24
@Xymanek you *shouldn't* have to do that in the first place. Connections are meant to be short-lived. Locks acquired during a connection's lifetime are held until that connection closes. Creating new connections is cheap thanks to connection pooling. You gain nothing by creating the connection outside the transaction scope. You can use `SqlConnection.EnlistTransaction(Transaction.Current)` but you should try to avoid this. The answers [to this question](https://stackoverflow.com/questions/9257907/does-transactionscope-work-with-pre-existing-connections) explain both options — Panagiotis Kanavos, May 10 '19 at 06:43
The connections were short lived, it was only a matter of where they were created... I've reorganized the code to create them inside the `TransactionScope` and it works perfectly, thanks! (turns out this issue was even simpler than I expected) — Xymanek, May 10 '19 at 16:41

score 0 · Answer 2 · answered May 09 '19 at 15:27

In .NET you can use a TransationScope, and everything will happen in the same transaction:

using (TransactionScope scope = new TransactionScope())
{
    // Everything inside this block will be transactional:
    // Call the libraries which will use your SqlConnection here
}

Or you can use the BeginTransaction before calling the other library functions, and commit it after the function calls

How to force extenal library to use SQL transaction

2 Answers2