|
Keep Data Consistent With Transactions
Employ the transaction models supported by the .NET Framework to maintain consistent data in your applications.
by Paul Delcogliano
Posted March 15, 2004
Technology Toolbox: VB.NET, C#, SQL Server 2000, ASP.NET
Most of the applications you write probably deal with manipulating data one way or another, and changes to the data beyond simple updates are usually handled in transactions. Many business applications employ complex business rules, often combining multiple insert, update, and delete statements into one business object's method call. Such sophisticated data-handling applications can work wonders for a business, but it's also no wonder when they don't work. In particular, you as the developer face the challenge of maintaining data consistency—especially when one statement within the method call succeeds and another statement fails.
Transactions provide the solution to such problems. Transactions enable you to roll back the changes made to data when an error occurs, or commit the changes when all the data operations succeed. This ability keeps your application's data consistent day in and day out (see Figure 1).
I'll help you master transactions. I'll discuss what transactions are, describe the transaction models supported by the .NET Framework, and show you some handy techniques for adding transactional support to your applications.
A transaction consists of a set of related operations that either succeed or fail as a single unit. Transactions have been around as long as people have been doing business. For example, a transaction might involve transferring money between your savings and checking accounts. The bank first responds to your transaction request by verifying that you have enough money in your savings account to support the withdrawal. The bank then withdraws the requested amount of money from your savings account and deposits it into your checking account.
Both operations—withdrawing from your savings account and depositing into your checking account—must work for the transaction to succeed. The bank won't withdraw the money if the deposit fails. The bank wraps the transfer into a transaction, ensuring that your accounts maintain their balances in the event of a failure.
Successful transactions commit the changes made to your accounts to the bank's database. Failed transactions result in the bank aborting the operation and rolling your account balances back to their pre-transaction dollar amounts (except for penalties, of course).
The .NET Framework provides both manual and automatic models for implementing transactions. You can use either model in any application environment. However, each model has certain features that make it the right choice in a given situation.
The manual model works well in applications that use one data resource—such as SQL Server—on one server. The manual model provides measured control over the transaction and lets you nest transactions. It also works with the .NET Data Provider for SQL Server and the OLE DB .NET Data Provider.
Manual Model Gives More, Takes More
On the other hand, the manual model doesn't enlist data resources into the transaction automatically, nor does it coordinate transactions between data resources. This means you must handle enlistment and coordination. Furthermore, if you need to pass a transaction between objects, you must do so explicitly.
You create a transaction with the manual model and the .NET Data Provider for SQL Server with the SqlConnection and SqlTransaction objects. The SqlConnection object's BeginTransaction method creates and returns a SqlTransaction object:
Dim conn As New SqlClient.SqlConnection
Dim trans As SqlTransaction
conn.ConnectionString = _
connectionString
conn.Open()
' Return a transaction object from the
' opened connection
trans = conn.BeginTransaction()
You must open the connection before beginning your transaction (you'll receive an error later if you skip this step). Now mark the beginning of your transaction by calling the BeginTransaction method. Assign the SqlTransaction object returned from the BeginTransaction method call to a SqlCommand object's Transaction property. Doing so enlists the command in the transaction:
' Create the command
Dim orderCommand As New _
SqlCommand("InsertOrder_sp", conn)
' Enlist the command with the
' transaction
orderCommand.Transaction = trans
Back to top
|