TransactionScope not rolling back with SqlDataAdapter.Update

Question

I'm using SqlDataAdapter.Update with DataTables to update two SQL tables in a single transaction. If either insert fails I want to roll back all data. This is my code:

using (var conn = new SqlConnection(_connectionString))
{
    conn.Open();

    using (var scope = new TransactionScope())
    {
        // Insert first table
        using (var command = conn.CreateCommand())
        {
            command.CommandText =
                @"INSERT INTO TableA(Id, Data)
                  VALUES(@id, @data)";

            command.CommandType = CommandType.Text;
            command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int) { SourceColumn = "Id" });
            command.Parameters.Add(new SqlParameter("@data", SqlDbType.Char) { SourceColumn = "Data" });

            var adapter = new SqlDataAdapter();
            adapter.InsertCommand = command;
            adapter.Update(tableADataTable);
        }

        // Insert second table
        using (var command = conn.CreateCommand())
        {
            command.CommandText =
                @"INSERT INTO TableB(Id, Data)
                  VALUES(@id, @data)";

            command.CommandType = CommandType.Text;
            command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int) { SourceColumn = "Id" });
            command.Parameters.Add(new SqlParameter("@data", SqlDbType.Char) { SourceColumn = "Data" });

            var adapter = new SqlDataAdapter();
            adapter.InsertCommand = command;
            adapter.Update(tableBDataTable);
        }

        scope.Complete();
    }
}

The problem I'm having is that if an exception is thrown during the second command execution, data from the first command is still commited. Do I need to explicitly roll back? Or is how TransactionScope should behave when using SqlDataAdapter.Update?

Something to note is that originally I had the SqlConnection creation within the TransactionScope using statement, but I moved it out as I was receiving errors that my DB server hadn't been configured correctly for distributed transactions. Is the fact that my SqlConnection creation is outside TransactionScope related?

Answer 1

Try placing your SqlConnection inside the TransactionScope, it should then automatically enlist in the transaction.

I think in your code, you need to manually enlist the connection into the transaction... review the examples in these links.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.enlistdistributedtransaction(v=VS.71).aspx

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

Sorry just caught your OP - perhaps it's because the connection wasn't configured to automatically enlist into existing transactions (a member of the connection string I think).

If you don't call Complete (or Commit on a SqlTransaction) it will automatically rollback.

Of course, in your current code sample - you can safely use a SqlTransaction object as you aren't involving multiple connections/databases.