TransactionScope not working correctly with 2 layer database setup

Question

I have a single method that reaches out to 2 methods. One handles Inserts and the other handles Updates. I put a transaction scope around these 2 methods so if there is any issues it will roll back. I notice that it is putting the data in the database but on error it is not removing it. I have tried RequiresNew and Required options for the scope but that doesn't seem to make a difference.

Additional Information: These stored procedures are actually stored in a "Utility" database which is holding the stored procedures but they are actually modifying records in a different database.

This method will insert approx 700+ records into 40 different tables.

Main Method

using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
    foreach (var change in changes)
    {
        switch (change.ChangeType)
        {
            case ChangeTypeEnum.Insert:
                var result = DataAccess.InsertTableRow(sourceEnvironmentId, SuperClientVendorID,
                                                       DatabaseName, DataRouteName, change,
                                                       typeNamespace);
                postList.Add(result);
                break;
            case ChangeTypeEnum.Update:
                DataAccess.UpdateTableRow(sourceEnvironmentId, SuperClientVendorID, DatabaseName,
                                          DataRouteName, change, typeNamespace);
                postList.Add(change);
                break;
        }
    }

    scope.Complete();
}

Insert Method

var sproc = string.Format("Carma.usp_{0}_{1}_ins", databaseName, managedState.TypeName);
var connString = DataAccessManager.GetConnectionString(executionEnvironment, superClientVendorID, routeName, sproc, false);

using (var newConnection = new SqlConnection(connString))
{
    newConnection.Open();

    using (var newSqlCommand = new SqlCommand(sproc, newConnection))
    {
        newSqlCommand.CommandType = CommandType.StoredProcedure;
        newSqlCommand.CommandTimeout = Setup.TimeOut;
        newSqlCommand.Parameters.AddRange(GetParameters(managedState, typeNamespace, true));

        newSqlCommand.ExecuteNonQuery();

        return managedState;
    }
}

`they are actually modifying records in a different database` is they key here, I believe. The _other database_ is the one that needs to be enrolled in the transactions, and it will not be. — Oded, Oct 24 '12 at 19:06
I was thinking it might be the issue. Is there a way enroll the other database? I have a feeling this is going to be a flop. — Tony, Oct 24 '12 at 19:38
Not directly, not with this "hop" using an intermediary database. You might be able to stage the needed changes in your intermediary database to be transferred on a schedule (so only when the whole batch succeeds there will be stuff to transfer), but I don't know what the application requirements are so difficult to recommend anything. — Oded, Oct 24 '12 at 20:00

score 0 · Accepted Answer · answered Oct 24 '12 at 21:37

So a coworker was able to assist me with this.. here is the modified code hopefully it will help someone out there :)

Calling Method:

using (var scope = new TransactionScopeMS())
{
    foreach (var change in changes)
    {
        switch (change.ChangeType)
        {
             case ChangeTypeEnum.Insert:
                  var result = DataAccess.InsertTableRow(sourceEnvironmentId, SuperClientVendorID,
                                                               DatabaseName, DataRouteName, change,
                                                               typeNamespace);
                  postList.Add(result);
                  break;
             case ChangeTypeEnum.Update:
                  DataAccess.UpdateTableRow(sourceEnvironmentId, SuperClientVendorID, DatabaseName,
                                                  DataRouteName, change, typeNamespace);
                  postList.Add(change);
                  break;
        }
    }

    scope.Complete();
}

Insert Method:

using (IDbConnection newConnection = TransactionScopeMS.Current.GetConnection(connectionString))
        {
            using (IDbCommand newSqlCommand = newConnection.CreateCommand(sproc, CommandType.StoredProcedure, TransactionScopeMS.Current.GetTransaction(connectionString)))
            {

                foreach (var pram in GetParameters(managedState, typeNamespace, true))
                {
                    newSqlCommand.Parameters.Add(pram);
                }

                newSqlCommand.ExecuteNonQuery();

                return managedState;
            }
        }

TransactionScope not working correctly with 2 layer database setup

1 Answers1