Protect Data Operations With the SqlTransaction Class

XtremeData

LANGUAGES: C#

ASP.NET VERSIONS: 1.0/1.1

Protect Data Operations With the SqlTransaction Class

Use the SqlTransaction class in ADO.NET to ensure datastays in sync.

By Dan Wahlin

Using ADO.NET's many powerfulfeatures, developers can easily integrate data into .NET applications. In caseswhere data operations are sensitive, they can use ADO.NET classes to ensurethat data stays in sync. Consider the classic savings and checking accountanalogy where money is moved between accounts. If an application pulls $500from a savings account, it needs to guarantee that the checking account iscredited the same amount. If the debit is successful but the credit fails (orvice versa), both activities should fail and the two accounts should roll backto their original states. In this article, I'll demonstrate how to handle thistype of transaction using a class named SqlTransaction located in theSystem.Data.SqlClient namespace. I'll also touch on SQL Server transactions togive you an idea of how SQL Server transactions and ADO.NET transactionscompare.

If you're not familiar withtransactions, think of them as protective wrappers around sensitive operations.If one of the operations within the transaction wrapper fails, everything canbe rolled back to its original state. If all operations are successful, thetransaction can be committed. There are several ways to handle datatransactions, including SQL coding, COM+, and the SqlTransaction class.Although I'll focus specifically on the SqlTransaction class here, the other.NET-managed providers (OleDb, ODBC, and Oracle) have their own specializedtransaction classes as well. All of these transaction classes implement aninterface called IDbTransaction.

Before you use SqlTransaction inyour .NET applications, it's important to understand what it is good for andwhen it should be used (as well as when it should not be used). The mostimportant fact to consider is that SqlTransaction is good for data modificationoperations that occur over the same data connection. For example, if a savingstable and checking table exist in the same database, you can use theSqlTransaction class to ensure data is updated properly in both tables becausea single connection can be used. However, it's important to note that transactionsare expensive to perform and should only be used when appropriate. They arenormally not necessary for simple data operations such as single insert,update, or delete operations (although it certainly depends on the type ofoperation being performed). ADO.NET transactions should not be used inconjunction with SQL Server transactions.

If tables exist in separatedatabases, it's more than likely multiple database connections will be involved(unless you handle this communication through a stored procedure or othermeans). When this is the case, you'll want to consider using COM+ attributesavailable in the .NET Framework (explore the System.EnterpriseServicesnamespace) or database-specific transactional capabilities rather than ADO.NETtransactions.

Examine the Code

Before I jump into more details onhow to use the SqlTransaction class, take a moment to examine the code thatupdates the Savings table in the AcmeBanking database (see Figure 1) and thecode that updates the Checking table in the AcmeDatabase (see Figure 2).

//Credit/debit savings account

SqlConnection conn = new SqlConnection(connStr);

conn.Open();

//Create command object through AccountCommandManager

//CreditDebitSavings is the stored proc name

AccountCommandManager savings =

  new AccountCommandManager("CreditDebitSavings");

SqlCommand savingsCmd =

  savings.CreateAccountCommand(savingsNum,amount);

//Hook connection to command object

savingsCmd.Connection = conn;

savingsCmd.ExecuteNonQuery();

conn.Close();

Figure 1. This C#code handles updating the Savings table in the AcmeBanking database. I've alsoincluded the code for the AccountCommandManager class referenced here; seeFigure 3.

//Credit/debit checking account

SqlConnection conn = new SqlConnection(connStr);

conn.Open();

//Create command object through AccountCommandManager

//CreditDebitChecking is the stored proc name

AccountCommandManager checking =

  newAccountCommandManager("CreditDebitChecking");

SqlCommand checkingCmd =

  checking.CreateAccountCommand(checkingNum,amount);

//Hook connection to command object

checkingCmd.Connection = conn;

checkingCmd.ExecuteNonQuery();

conn.Close();

Figure 2. This C#code handles updating the Checking table in the AcmeBanking database.

As you look through the code in thefigures, you'll see that a connection is created to a database in order to calltwo different stored procedures that handle crediting and/or debiting savingsand checking accounts. A custom object named AccountCommandManager (see Figure3) is used to create the SqlCommand objects and add their associated parameterdata. While this code is fairly straightforward, a problem arises when the codein Figures 1 and 2 needs to execute together.

public class AccountCommandManager {

  private string_CommandText = null;

  publicAccountCommandManager() {}

  publicAccountCommandManager(string commandText) {

    _CommandText =commandText;

  }

  public SqlCommandCreateAccountCommand(int accountNum,

    decimal amount) {

    SqlCommand accountCmd= new SqlCommand();

    accountCmd.CommandText= _CommandText;

    accountCmd.CommandType= CommandType.StoredProcedure;

    //Add parameters

    SqlParameteraccountNumParam =

      accountCmd.Parameters.Add(

      newSqlParameter("@AccountNumber",SqlDbType.Int));

    accountNumParam.Value= accountNum;

    SqlParameter amountParam =

      accountCmd.Parameters.Add(

      newSqlParameter("@Amount",SqlDbType.Money));

    amountParam.Value =amount;

    SqlParameter newBalanceParam =

      accountCmd.Parameters.Add(

      newSqlParameter("@NewBalance",SqlDbType.Money));

    newBalanceParam.Direction = ParameterDirection.Output;

    return accountCmd;

  }

}

Figure 3. TheAccountCommandManager class is responsible for creating a SqlCommand object andassigning specific SqlParameters to it as well as setting the CommandText andCommandType properties.

Although a try..catch block couldbe wrapped around the code to determine any errors that occur, if the checkingaccount code fails after the savings account code has already executedsuccessfully (to perform a debit from savings, for example), you'd have towrite custom code to "roll back" the savings account to its original state.This would, of course, require a lot of additional work and state tracking.

Fortunately, when you're using theSqlTransaction class, the amount of additional code that has to be written toaccommodate this type of scenario is greatly minimized. Let's take a look athow it works. First, a new SqlConnection object must be created and opened:

SqlConnection conn = new SqlConnection(connectionString);

conn.Open();

Review BeginTransaction'sOverloads

After the connection is opened, amethod on SqlConnection named BeginTransaction (which maps to BEGIN TRANSACTIONin SQL Server) is called. This method has several different overloads (seeFigure 4).

Figure 4. TheBeginTransaction method is overloaded and can accept the transaction name andthe isolation level.

This particular code fragmentassigns the IsolationLevel to ReadCommitted, which means that shared locks areheld while data is read to prevent any dirty reads from occurring during thetransaction lifetime:

SqlTransaction trans = null;

trans = conn.BeginTransaction(IsolationLevel.ReadCommitted,

  "CheckingSavingsTransaction");

What's a dirty read and how does itoccur? If a transaction such as the one I've just shown you (named"CheckingSavingsTransaction") makes a modification to a row, another transactionreads that row (we'll call this second transaction "Trans2"), and theCheckingSavingsTransaction performs a rollback to "undo" the modification, thenthe Trans2 transaction has accessed row data that doesn't really exist. Byusing ReadCommitted these types of dirty reads can be prevented.

After creating the SqlTransactionobject instance through calling BeginTransaction on the SqlConnection object,the SqlTransaction must be assigned to the different command objects used toperform individual data operations. You accomplish this by using the SqlCommandclass' Transaction property:

//Hook SqlTransaction to different command objects

savingsCmd.Transaction = trans;

checkingCmd.Transaction = trans;

Once the SqlTransaction object hasbeen "hooked" to the appropriate SqlCommand objects, different data operations,such as crediting or debiting checking and savings accounts, can be performed.Refer back to Figures 1 and 2 to see the code that will be executed. If aproblem occurs in either operation, the error can be caught and theSqlTransaction object's Rollback method can be called. This restores data toits previous state. If the transaction succeeds without error, theSqlTransaction object's Commit method can be called and the data is committedpermanently to the database.

I've included the code for a methodnamed DebitCreditAccounts that handles creating a SqlTransaction object,assigning it to command objects, executing different data modificationoperations, and then rolling back or committing the transaction (see Figure 5).

public static bool DebitCreditAccounts(int savingsNum,

   int checkingNum,decimal amount) {

    string connStr =

      ConfigurationSettings.AppSettings["DBConnString"];

    SqlConnection conn =new SqlConnection(connStr);

    SqlTransaction trans =null;

    decimal newBalance;

    try {

        conn.Open();

        trans =conn.BeginTransaction

             (IsolationLevel.ReadCommitted,

             "CheckingSavingsTransaction");

    }

    catch {

       throw new ApplicationException("Unableto open db");

    }

    try {

        //Credit or debitsavings

        AccountCommandManager savings =

          newAccountCommandManager("CreditDebitSavings");

        SqlCommandsavingsCmd =

        savings.CreateAccountCommand(savingsNum,amount);

        savingsCmd.Connection = conn;

        savingsCmd.Transaction = trans;

        savingsCmd.ExecuteNonQuery();

        newBalance =

          Decimal.Parse(savingsCmd.Parameters[

          "@NewBalance"].Value.ToString());

         if (newBalance < 0) {

          throw newApplicationException

            ("Failedto move money to/from savings. " +

            "Your newsavings balance would have " +

            "been:$" + newBalance.ToString());

        }

        //Credit or debitchecking

        AccountCommandManager checking =

          newAccountCommandManager("CreditDebitChecking");

        SqlCommand checkingCmd =

          checking.CreateAccountCommand

           (checkingNum,(amount * -1));

        checkingCmd.Connection= conn;

        checkingCmd.Transaction = trans;

        checkingCmd.ExecuteNonQuery();

        //Ensuredebit/credit succeeded

        newBalance =

            Decimal.Parse(checkingCmd.Parameters[

            "@NewBalance"].Value.ToString());

         if (newBalance < 0) {

         throw newApplicationException

          ("Failed tomove money to/from checking balance" +

          ". Your newchecking balance " +

          "would havebeen: $" + newBalance.ToString());

        }

        //No errors...socommit transaction

        trans.Commit();

        return true;

    } catch (Exceptionexp) {

        try {

          //Experienced anerror...rollback transaction

          trans.Rollback();

        } catch(SqlException sqlExp) {

          throw newApplicationException

             ("Transaction rollback failed");

        }

        throw exp;

    } finally {

         if (conn.State !=ConnectionState.Closed)

           conn.Close();

    }

}

Figure 5. TheDebitCreditAccounts accounts method demonstrates how to use the SqlTransactionclass to wrap multiple data modification operations. Any errors that occurcause the transaction to roll back data to its initial state.

Take a look at a screenshot of theexecution of the sample application included in this article's downloadablecode (see Figure 6; for more on the downloadable code, see the Download boxaccompanying this article). In cases where an account falls below $0, an erroris raised and the transaction object's RollBack method is called to restore thedata to its initial state.

Figure 6. This screenshot shows thesample application for this article in action. Clients are notified whentransactions fail because of improper credits or debits.

Consider SQL Server Transactions

You may look through the code inFigure 5 and say, "I could do all of this directly within a stored procedure!"and you'd be correct. I mentioned earlier that the BeginTransaction method mapsto the BEGIN TRANSACTION statement in SQL Server, which you can use with the ROLLBACKTRANSACTION and COMMIT TRANSACTION keywords.

Instead of wrapping the dataoperations shown in Figure 5 in an ADO.NET transaction, a stored procedure canbe written that calls the two procedures and wraps a transaction around them.See Figure 7 for a sample stored procedure that demonstrates this technique.Some of the logic shown in the stored procedure could certainly be moved intothe individual savings and checking stored procedures; however, my goal was tokeep things as simple as possible.

CREATE PROCEDURE CreditDebitAccounts

   (

    @SavingsAccountNumberint,

    @CheckingAccountNumberint,

    @Amount int,

    @ErrorCode int = 0OUTPUT

  )

AS

BEGIN

  BEGIN TRANSACTION

    DECLARE @SavingsBalance int

    DECLARE@CheckingBalance int

    DECLARE@CheckingAmount int

    SET @CheckingAmount =@Amount * -1

    --Credit/Debit Savings

    EXECCreditDebitSavings @SavingsAccountNumber,

      @Amount,@NewBalance= @SavingsBalance OUTPUT

    IF @SavingsBalance< 0 OR @@ERROR != 0

    BEGIN

      ROLLBACK TRANSACTION

      SET @ErrorCode = -1

    END

    --Credit/DebitChecking

    EXECCreditDebitChecking @CheckingAccountNumber,

     @CheckingAmount,@NewBalance = @CheckingBalance OUTPUT

      IF @CheckingBalance< 0 OR @@ERROR != 0

      BEGIN

        ROLLBACKTRANSACTION

        SET @ErrorCode =-1

      END

    COMMIT TRANSACTION

  END

Figure 7. SQLServer allows transactions to be performed directly at the database level byusing the BEGIN TRANSACTION keywords. You don't want to mix SQL transactionswith ADO.NET transactions.

So which is better, ADO.NETtransactions or SQL Server transactions? The answer is somewhat vague.Ultimately it depends on your unique environment, skill levels of thedevelopers, and the types of applications involved.

Both ADO.NET and SQL Servertransaction options get the job done when transactions are needed within asingle database. The SQL Server route does offer more flexibility when moreadvanced transactions are needed, such as those involving distributeddatabases. Plus, by performing transactions at the database level, you'll seeincreased performance over doing them through ADO.NET because of fewer roundtrips to the database. Read "Performance Comparison: Transaction Control" byPriya Dhawan for more details on the performance of different transactionoptions: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/bdadotnetarch13.asp?frame.

Transactions can play an importantrole in any application where data operations may need to be rolled back totheir previous state because of an error or other condition. In cases whereoperations within a single database need to be wrapped in a transaction, theSqlTransaction class (as well as other classes that implement IDbTransaction)can be used effectively to ensure that data is properly modified and committedto the database. In addition to ADO.NET transactions, other options do exist,such as COM+ and SQL Server (plus many other databases), so it is in your bestinterest to research these options and see what fits best for yourorganization.

The sample codein this article is available for download.

Dan Wahlin(Microsoft Most Valuable Professional for ASP.NET and XML Web Services) is thepresident of Wahlin Consulting and founded the XML for ASP.NET Developers Website (http://www.XMLforASP.NET), whichfocuses on using XML and Web Services in Microsoft's .NET platform. He's also acorporate trainer and speaker, and teaches XML and .NET training courses aroundthe U.S. Dan coauthored Professional Windows DNA (Wrox, 2000) and ASP.NET: Tips, Tutorialsand Code (Sams, 2001), and authored XML for ASP.NET Developers (Sams, 2001).