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 data stays in sync.
By Dan Wahlin
Using ADO.NET's many powerful features, developers can easily integrate data into .NET applications. In cases where data operations are sensitive, they can use ADO.NET classes to ensure that data stays in sync. Consider the classic savings and checking account analogy where money is moved between accounts. If an application pulls $500 from a savings account, it needs to guarantee that the checking account is credited the same amount. If the debit is successful but the credit fails (or vice versa), both activities should fail and the two accounts should roll back to their original states. In this article, I'll demonstrate how to handle this type of transaction using a class named SqlTransaction located in the System.Data.SqlClient namespace. I'll also touch on SQL Server transactions to give you an idea of how SQL Server transactions and ADO.NET transactions compare.
If you're not familiar with transactions, think of them as protective wrappers around sensitive operations. If one of the operations within the transaction wrapper fails, everything can be rolled back to its original state. If all operations are successful, the transaction can be committed. There are several ways to handle data transactions, 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 specialized transaction classes as well. All of these transaction classes implement an interface called IDbTransaction.
Before you use SqlTransaction in your .NET applications, it's important to understand what it is good for and when it should be used (as well as when it should not be used). The most important fact to consider is that SqlTransaction is good for data modification operations that occur over the same data connection. For example, if a savings table and checking table exist in the same database, you can use the SqlTransaction class to ensure data is updated properly in both tables because a single connection can be used. However, it's important to note that transactions are expensive to perform and should only be used when appropriate. They are normally not necessary for simple data operations such as single insert, update, or delete operations (although it certainly depends on the type of operation being performed). ADO.NET transactions should not be used in conjunction with SQL Server transactions.
If tables exist in separate databases, it's more than likely multiple database connections will be involved (unless you handle this communication through a stored procedure or other means). When this is the case, you'll want to consider using COM+ attributes available in the .NET Framework (explore the System.EnterpriseServices namespace) or database-specific transactional capabilities rather than ADO.NET transactions.
Examine the Code
Before I jump into more details on how to use the SqlTransaction class, take a moment to examine the code that updates the Savings table in the AcmeBanking database (see Figure 1) and the code 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 also included the code for the AccountCommandManager class referenced here; see Figure 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 =
new AccountCommandManager("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 the figures, you'll see that a connection is created to a database in order to call two different stored procedures that handle crediting and/or debiting savings and checking accounts. A custom object named AccountCommandManager (see Figure 3) is used to create the SqlCommand objects and add their associated parameter data. While this code is fairly straightforward, a problem arises when the code in Figures 1 and 2 needs to execute together.
public class AccountCommandManager {
private string _CommandText = null;
public AccountCommandManager() {}
public AccountCommandManager(string commandText) {
_CommandText = commandText;
}
public SqlCommand CreateAccountCommand(int accountNum,
decimal amount) {
SqlCommand accountCmd = new SqlCommand();
accountCmd.CommandText = _CommandText;
accountCmd.CommandType = CommandType.StoredProcedure;
//Add parameters
SqlParameter accountNumParam =
accountCmd.Parameters.Add(
new SqlParameter("@AccountNumber",SqlDbType.Int));
accountNumParam.Value = accountNum;
SqlParameter amountParam =
accountCmd.Parameters.Add(
new SqlParameter("@Amount",SqlDbType.Money));
amountParam.Value = amount;
SqlParameter newBalanceParam =
accountCmd.Parameters.Add(
new SqlParameter("@NewBalance",SqlDbType.Money));
newBalanceParam.Direction = ParameterDirection.Output;
return accountCmd;
}
}
Figure 3. The AccountCommandManager class is responsible for creating a SqlCommand object and assigning specific SqlParameters to it as well as setting the CommandText and CommandType properties.
Although a try..catch block could be wrapped around the code to determine any errors that occur, if the checking account code fails after the savings account code has already executed successfully (to perform a debit from savings, for example), you'd have to write 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 the SqlTransaction class, the amount of additional code that has to be written to accommodate this type of scenario is greatly minimized. Let's take a look at how it works. First, a new SqlConnection object must be created and opened:
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
Review BeginTransaction's Overloads
After the connection is opened, a method on SqlConnection named BeginTransaction (which maps to BEGIN TRANSACTION in SQL Server) is called. This method has several different overloads (see Figure 4).
|
BeginTransaction Overload |
Description |
|
BeginTransaction |
The default implementation of the method that accepts no parameters. |
|
BeginTransaction(IsolationLevel) |
The type of data isolation to perform during the transaction is passed to this overload. IsolationLevel enumeration members include Chaos, ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, and Unspecified. |
|
BeginTransaction(string) |
A string representing the transaction name can be passed to this overload. |
|
BeginTransaction(IsolationLevel, string) |
The type of IsolationLevel, as well as the transaction name, can be passed to this overload. |
Figure 4. The BeginTransaction method is overloaded and can accept the transaction name and the isolation level.
This particular code fragment assigns the IsolationLevel to ReadCommitted, which means that shared locks are held while data is read to prevent any dirty reads from occurring during the transaction lifetime:
SqlTransaction trans = null;
trans = conn.BeginTransaction(IsolationLevel.ReadCommitted,
"CheckingSavingsTransaction");
What's a dirty read and how does it occur? If a transaction such as the one I've just shown you (named "CheckingSavingsTransaction") makes a modification to a row, another transaction reads that row (we'll call this second transaction "Trans2"), and the CheckingSavingsTransaction performs a rollback to "undo" the modification, then the Trans2 transaction has accessed row data that doesn't really exist. By using ReadCommitted these types of dirty reads can be prevented.
After creating the SqlTransaction object instance through calling BeginTransaction on the SqlConnection object, the SqlTransaction must be assigned to the different command objects used to perform individual data operations. You accomplish this by using the SqlCommand class' Transaction property:
//Hook SqlTransaction to different command objects
savingsCmd.Transaction = trans;
checkingCmd.Transaction = trans;
Once the SqlTransaction object has been "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 a problem occurs in either operation, the error can be caught and the SqlTransaction object's Rollback method can be called. This restores data to its previous state. If the transaction succeeds without error, the SqlTransaction object's Commit method can be called and the data is committed permanently to the database.
I've included the code for a method named DebitCreditAccounts that handles creating a SqlTransaction object, assigning it to command objects, executing different data modification operations, 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("Unable to open db");
}
try {
//Credit or debit savings
AccountCommandManager savings =
new AccountCommandManager("CreditDebitSavings");
SqlCommand savingsCmd =
savings.CreateAccountCommand(savingsNum,amount);
savingsCmd.Connection = conn;
savingsCmd.Transaction = trans;
savingsCmd.ExecuteNonQuery();
newBalance =
Decimal.Parse(savingsCmd.Parameters[
"@NewBalance"].Value.ToString());
if (newBalance < 0) {
throw new ApplicationException
("Failed to move money to/from savings. " +
"Your new savings balance would have " +
"been: $" + newBalance.ToString());
}
//Credit or debit checking
AccountCommandManager checking =
new AccountCommandManager("CreditDebitChecking");
SqlCommand checkingCmd =
checking.CreateAccountCommand
(checkingNum, (amount * -1));
checkingCmd.Connection = conn;
checkingCmd.Transaction = trans;
checkingCmd.ExecuteNonQuery();
//Ensure debit/credit succeeded
newBalance =
Decimal.Parse(checkingCmd.Parameters[
"@NewBalance"].Value.ToString());
if (newBalance < 0) {
throw new ApplicationException
("Failed to move money to/from checking balance" +
". Your new checking balance " +
"would have been: $" + newBalance.ToString());
}
//No errors...so commit transaction
trans.Commit();
return true;
} catch (Exception exp) {
try {
//Experienced an error...rollback transaction
trans.Rollback();
} catch (SqlException sqlExp) {
throw new ApplicationException
("Transaction rollback failed");
}
throw exp;
} finally {
if (conn.State != ConnectionState.Closed)
conn.Close();
}
}
Figure 5. The DebitCreditAccounts accounts method demonstrates how to use the SqlTransaction class to wrap multiple data modification operations. Any errors that occur cause the transaction to roll back data to its initial state.
Take a look at a screenshot of the execution of the sample application included in this article's downloadable code (see Figure 6; for more on the downloadable code, see the Download box accompanying this article). In cases where an account falls below $0, an error is raised and the transaction object's RollBack method is called to restore the data to its initial state.
Figure 6. This screenshot shows the
sample application for this article in action. Clients are notified when
transactions fail because of improper credits or debits.
Consider SQL Server Transactions
You may look through the code in Figure 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 maps to the BEGIN TRANSACTION statement in SQL Server, which you can use with the ROLLBACK TRANSACTION and COMMIT TRANSACTION keywords.
Instead of wrapping the data operations shown in Figure 5 in an ADO.NET transaction, a stored procedure can be 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 into the individual savings and checking stored procedures; however, my goal was to keep things as simple as possible.
CREATE PROCEDURE CreditDebitAccounts
(
@SavingsAccountNumber int,
@CheckingAccountNumber int,
@Amount int,
@ErrorCode int = 0 OUTPUT
)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @SavingsBalance int
DECLARE @CheckingBalance int
DECLARE @CheckingAmount int
SET @CheckingAmount = @Amount * -1
--Credit/Debit Savings
EXEC CreditDebitSavings @SavingsAccountNumber,
@Amount,@NewBalance = @SavingsBalance OUTPUT
IF @SavingsBalance < 0 OR @@ERROR != 0
BEGIN
ROLLBACK TRANSACTION
SET @ErrorCode = -1
END
--Credit/Debit Checking
EXEC CreditDebitChecking @CheckingAccountNumber,
@CheckingAmount, @NewBalance = @CheckingBalance OUTPUT
IF @CheckingBalance < 0 OR @@ERROR != 0
BEGIN
ROLLBACK TRANSACTION
SET @ErrorCode = -1
END
COMMIT TRANSACTION
END
Figure 7. SQL Server allows transactions to be performed directly at the database level by using the BEGIN TRANSACTION keywords. You don't want to mix SQL transactions with ADO.NET transactions.
So which is better, ADO.NET transactions or SQL Server transactions? The answer is somewhat vague. Ultimately it depends on your unique environment, skill levels of the developers, and the types of applications involved.
Both ADO.NET and SQL Server transaction options get the job done when transactions are needed within a single database. The SQL Server route does offer more flexibility when more advanced transactions are needed, such as those involving distributed databases. Plus, by performing transactions at the database level, you'll see increased performance over doing them through ADO.NET because of fewer round trips to the database. Read "Performance Comparison: Transaction Control" by Priya Dhawan for more details on the performance of different transaction options: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/bdadotnetarch13.asp?frame.
Transactions can play an important role in any application where data operations may need to be rolled back to their previous state because of an error or other condition. In cases where operations within a single database need to be wrapped in a transaction, the SqlTransaction class (as well as other classes that implement IDbTransaction) can be used effectively to ensure that data is properly modified and committed to 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 best interest to research these options and see what fits best for your organization.
The sample code in this article is available for download.
Dan Wahlin (Microsoft Most Valuable Professional for ASP.NET and XML Web Services) is the president of Wahlin Consulting and founded the XML for ASP.NET Developers Web site (http://www.XMLforASP.NET), which focuses on using XML and Web Services in Microsoft's .NET platform. He's also a corporate trainer and speaker, and teaches XML and .NET training courses around the U.S. Dan coauthored Professional Windows DNA (Wrox, 2000) and ASP.NET: Tips, Tutorials and Code (Sams, 2001), and authored XML for ASP.NET Developers (Sams, 2001).
