Mimer SQL Data Provider help 11.0
Transaction Scope
Mimer SQL Data Provider > Overview > Working with transactions > Transaction Scope

The System.Transactions.TransactionScope class provides a framework for managing transactions.

We have several more options to consider when looking at transactions initiated through a TransactionScope. This class is typically used when application servers are used. It is then possible to write methods that perform a single change toward the database using a TransactionScope. If we want several of these simple operations to be in a single, larger, transaction we use an outer transaction scope. It is thus allowed to nest transaction scopes which makes them ideal when we want the code to be structured. For example:

using (TransactionScope ts = new TransactionScope())
{
      if (functionX() && functionY())
      {
         ts.SetComplete()
      }
}

In the previous section detailing explicit transaction handling we looked at an  example where two updates were made. We build on this example and show how this can translate to a distributed transaction.

In this example we will move money from one account to another through two SQL statements:

UPDATE ACCOUNTS_BANK1 SET BALANCE=BALANCE-10 WHERE ACCOUNTID = 1203 AND BALANCE > 10
UPDATE ACCOUNTS_BANK2 SET BALANCE=BALANCE+10 WHERE ACCOUNTID = 132

In an actual application we would use host variables for the amount of money we are transferring and also for the account numbers.

//
// Start a transaction by creating a TransactionScope instance
//
int rows1 = 0;
int rows2 = 0;
using (TransactionScope ts = new TransactionScope())
{
    using (MimerConnection conn1 = new MimerConnection(connectionString1))
    {
         //
   
      // The following method call automatically enlist in the transaction. This will be a local Mimer transaction
         //
         conn1.Open();
         using (MimerCommand c1 = new MimerCommand("UPDATE ACCOUNTS_BANK1 SET BALANCE=BALANCE-10 " +
                                                                                             "WHERE ACCOUNTID = 1203 AND BALANCE >= 10'
"
, conn1))
         {
             //
   
          // The following method call withdraws the money from the first account
             //
        
     rows1 = c1.ExecuteNonQuery();
         }
    }
    if (rows1 == 1)
    {

        using (MimerConnection conn2 = new MimerConnection(connectionString2))
        {
             //
   
          // The following method call automatically enlists the second connection in the transaction. The
             // transaction will be promoted to a distributed transaction that will use MSDTC (Microsoft Distributed
             // Transaction Coordinator)

             //
             conn1.Open();
          
  using (MimerCommand c2 = new MimerCommand("UPDATE ACCOUNTS_BANK2 SET BALANCE=BALANCE+10 " +
                                                                                               "WHERE ACCOUNTID = 1203'
"
, conn2))
            {
                
//
           
      // The following method call transfers the money to the second account
                 //
                rows2 = c2.ExecuteNonQuery();
             }
        }
        if  (rows2 == 1)
        {
            //
           
// We modified exactly two accounts, commit the changes to both databases
            //
            ts.SetComplete();
        }
    }
}

In the above code we modify two accounts. If we are able to subtract the amount from one account and add it to another we commit the transaction by calling SetComplete on the TransactionScope. (The code should, of course, also check that we have money in the account). If we are not happy with the changes made the transaction is rolled back. This is done by not calling SetComplete. This will also happen if there is an exception in which case the transaction is automatically rolled back when the TransactionScope object goes out of scope.

 In the example the connection strings would reference two different databases. Actually, the same code would work against a single database and the result would be a local transaction that would not involve the distributed transaction coordinator.

See Also