Save data in multiple databases using a single transaction (multiple connection strings)

Question

I have a server with multiple databases, each database will be connected using a different user ID pswd. I need to update/insert/delete the records in tables in any of the DBs. If any error occurs - rollback all changes to all databases in the current transaction.

My code looks as below:

string connStrTest1 = "connectionstring to connect to DB1";
string connStrTest2 = "connectionstring to connect to DB2";
string connStrTest3 = "connectionstring to connect to DB3";

//For an example I have created 3 DBs which have the same tables and columns.

string InsertPerson = "insert into Person (Id, Name, City) VALUES (123, 'Jon' , 'England' )";
string InsertPhones = "insert into Phones (Id, Number, SrvcPrvdr) VALUES (123, '+442345678' , 'Some')";
string InsertWork = "INSERT INTO WorkPlace (Id, Office, Address) VALUES (123, 'Soem', 'England' )";
string FailInsertWork = "INSERT INTO WorkPlace (Id, Office, Address) VALUES (999, 'some', 'Australia' )";


static void Main()
{
    using (var connTest1 = new SqlConnection(connStrTest1))
    {
        connTest1.Open();
        var transaction = connTest1.BeginTransaction();
        try
        {
            //Update 1st DB here.....
            var command = new SqlCommand(InsertPerson, connTest1, transaction);
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = InsertPerson;
            command.ExecuteNonQuery();

            command.CommandText = InsertPhones;
            command.ExecuteNonQuery();

            command.CommandText = InsertWork;
            command.ExecuteNonQuery();

            //updating DBs 2 & 3 here
            updateRecords();

            transaction.Commit();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            throw ex;
        }
    }
}


private static void updateRecords()
{
    //Updating tables in 2nd Test DB
    using (var conn = new SqlConnection(connStrTest2))
    {
        conn.Open();
        try
        {
            var command = new SqlCommand(InsertPerson, conn);
            command.CommandType = System.Data.CommandType.Text;
            command.ExecuteNonQuery();

            command.CommandText = InsertPhones;
            command.ExecuteNonQuery();

            command.CommandText = InsertWork;
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }


    //Updating tables in 3rd Test DB
    using (var conn = new SqlConnection(connStrTest3))
    {
        conn.Open();
        try
        {
            var command = new SqlCommand(InsertPerson, conn);
            command.CommandType = System.Data.CommandType.Text;
            command.ExecuteNonQuery();

            command.CommandText = InsertPhones;
            command.ExecuteNonQuery();

            if (fail)
            {
                command.CommandText = FailInsertWork;
                command.ExecuteNonQuery();
            }
            else
            {
                command.CommandText = InsertWork;
                command.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}

In the query FailInsertWork, I have written the query so that it will raise an exception as the Foreign key violation will occur.

Now, I want my program to function as when the update to 3rd DB fails, all the inserts happened previously for the 1st and 2nd DB also should be rolled back.

You can pass the connectionString or an instance of the connection or an instance of the transaction to the updateRecords method.

FYI - I DO NOT WANT TO USE TransactionScope/DTC/System.Transactions.Transaction.

Any other solutions apart from this are highly appreciated.

Answer 1

If I understand correctly you're trying to make your .net code manage multiple recipient databases.

One approach would be to switch to .Net generated Ids. I suggest guid.

Then create a managing database, so that you may fix/correct the recipient databases in the event of an application interruption.

Transaction information should be inserted into the managing database before it is executed against recipient databases and should be deleted once all databases successfully executed the transaction. You could even place a bit column for each database.

In the event of interruption you need only check the started transactions in the managing database and decide how to correct the incomplete transactions.

Depending on how robust the managing database is, you could even create a windows service that catches up a recipient database that was offline once it comes back online.

Answer 2

You must use from TransactionScope. TransactionScope is a very special and important class in the .NET Framework. Supporting transactions from a code block is the main responsibility of this class.it's easy to use like :

// This function takes arguments for 2 connection strings and commands to create a transaction  
// involving two SQL Servers. It returns a value > 0 if the transaction is committed, 0 if the  
// transaction is rolled back. To test this code, you can connect to two different databases  
// on the same server by altering the connection string, or to another 3rd party RDBMS by  
// altering the code in the connection2 code block. 
static public int CreateTransactionScope(
    string connectString1, string connectString2,
    string commandText1, string commandText2)
{
    // Initialize the return value to zero and create a StringWriter to display results. 
    int returnValue = 0;
    System.IO.StringWriter writer = new System.IO.StringWriter();

    try
    {
        // 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();

        }

    }
    catch (TransactionAbortedException ex)
    {
        writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
    }
    catch (ApplicationException ex)
    {
        writer.WriteLine("ApplicationException Message: {0}", ex.Message);
    }

    // Display messages.
    Console.WriteLine(writer.ToString());

    return returnValue;
}

Check this on MSDN