How to force extenal library to use SQL transaction

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)

Answer

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.