WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
In database development, the concept of consistency dictates that the database is always in a valid state. Transactions exist to support consistency. The transaction is a mechanism by which you can treat multiple operations as an all-or-nothing whole: All the pieces work and all the changes are made or everything goes back to a prior known, consistent state. For example, for each customer deleted from a company's database, all of that customer's orders have to be deleted as well for consistency.
This article shows you how easy it is to use transactions with previous versions of .NET, and how you can use the TransactionScope object to auto-enlist database operations in a transaction. Other kinds of things, such as COM+ objects, can participate in transactions as well, but this article focuses on ADO.NET transactions. (If you are interested in other kinds of transactions, use the System.Transactions.TransactionScope class in .NET 2.0 as a point of further study.)
If you already know what transactions are and how to use them, skip ahead to the "Using the TransactionScope" section. If you are unsure what database transactions are and are unclear as to how to use them in past and present versions of .NET, or you just need a refresher, read straight through.
Using Transactions in .NET 1.1
Transactions were pretty easy to use in .NET versions prior to 2.0, as long as you knew what to look for and where to look for it. The basic process is the same for every single transaction:
- Create a Connection object.
- Open the connection.
- Request a transaction object from the connection.
- Notify all command objects of the transaction's existence.
- If there are no errors, commit the transaction.
- If there is an error, roll back the transaction.
- Finally, close the connection.
To ensure that a transaction could be rolled back—which means that all protected writes to the database are rolled back—use a try...catch exception handler. If the catch block is entered, you can assume that something went wrong and call Rollback in the catch block. This formula never changes and, when used correctly, will ensure that your database stays in a consistent state.
You don't need to protect a single write (but you can) or any number of reads with a transaction. That said, Listing 1 demonstrates how to enroll an insert SQL statement in a transaction. Rather then arbitrarily inserting and deleting a lot of data, one operation is enough to demonstrate the transaction behavior. Simply assume more than one command is executed in the try block.
Listing 1: Database Transactions for Every Version of .NET (Although the Sample Was Coded in VS 2005).
Public Sub UseTransaction() Const SQL As String = _ "INSERT INTO CUSTOMERS " + _ "(CustomerID, CompanyName, ContactName, ContactTitle, " + _ "Address, City, Region, PostalCode, Country, " + _ "Phone, Fax) VALUES (" + _ "'KimCo', 'Kimmel', 'Paul Kimmel', 'Dude', " + _ "'', '', '', '', 'USA', '', '')" Dim connectionString As String = _ "Data Source=EREWHON;Initial Catalog=Northwind;Integrated " + _ "Security=True" ' declare here because try and catch have different scope Dim trans As SqlTransaction = Nothing Dim connection As SqlConnection = _ New SqlConnection(connectionString) connection.Open() Try trans = connection.BeginTransaction Dim command As SqlCommand = New SqlCommand( _ SQL, connection, trans) command.ExecuteNonQuery() trans.Commit() Catch ex As Exception trans.Rollback() Console.WriteLine(ex.Message) Console.WriteLine("press enter") Console.ReadLine() ' nothing else you can do here, so re-throw exception Throw ex Finally ' called every time connection.Close() End Try
In all likelihood, you have seen code similar to this before. You can actually add and read the connection string from an App.config property by using the ConifgurationManager in .NET 2.0. The SqlTransaction is declared outside of the try block because try and catch have different scopes and you need to "see" the transaction object in both try and catch. The transaction is actually initialized right after the try block is entered; it is initialized from the connection object as shown. The SqlCommand is provided with the query, connection, and transaction. (Northwind has a primary key on the CustomerID column, so this query should fail the second time through.)
Assuming ExecuteNonQuery works, SqlTransaction.Commit is called. If ExecuteNonQuery fails, the code jumps to the catch block and the transaction is rolled back. The Console statements exist for informational purposes, and you re-throw the exception because there is no other useful handling you can do here. No matter what happens, the finally block is always executed.
Using the TransactionScope
The code for the TransactionScope fundamentally does the same thing as the code in Listing 1. They have two noteworthy differences: Listing 1 uses the new-to-Visual-Basic using statement, and the TransactionScope implicitly enrolls the connection and SQL command into a transaction.
The using statement is nothing to worry about. It has been around in C# for years, and it is just shorthand for try...finally. Any object that implements IDispose, such as SqlConnection or TransactionScope, can be created in a using block and the using block guarantees that the object's Dispose method will be called. For instance, the SqlConnection's Dipose checks to see whether the connection is open and, if so, closes it.
The TransactionScope class automatically runs a whole bunch of code (I checked using Reflector) that determines whether objects created in the scope support transactions. If they do (as the SQL Provider does), the TransactionScope's Dipose method determines whether the implicitly enrolled transaction has completed. If the transaction has completed, the changes are committed. If the transaction is not complete, the transaction is rolled back. You signify that the transaction is complete by calling TransactionScope.Complete. If you don't call Complete, the transaction is rolled back by TransactionScope when it is discarded.
Tip: You can download Reflector for free. Just Google for it by name or by the author Lutz Roeder's name.
For all intents and purposes, the code in Listing 2 functions exactly the same as the code in Listing 1, except that it uses the newly introduced using statement for Visual Basic .NET and the TransactionScope; this makes using transactions much easier.
Listing 2: Database Transactions Using the Using Statement for Visual Basic .NET and the TransactionScope
Public Sub UseTransactionScope() Const SQL As String = _ "INSERT INTO CUSTOMERS " + _ "(CustomerID, CompanyName, ContactName, ContactTitle, " + _ "Address, City, Region, PostalCode, Country, " + _ "Phone, Fax) VALUES (" + _ "'KimCo', 'Kimmel', 'Paul Kimmel', 'Dude', " + _ "'', '', '', '', 'USA', '', '')" Dim connectionString As String = _ "Data Source=EREWHON;Initial Catalog=Northwind;" + _ Integrated Security=True" Using scope As Transactions.TransactionScope = _ New Transactions.TransactionScope() Using connection As New SqlConnection(connectionString) connection.Open() Dim command As SqlCommand = New SqlCommand(SQL, connection) command.ExecuteNonQuery() scope.Complete() ' connection close is complicit in using statement End Using End Using End Sub
That's all there is to it, friends. Microsoft claims the TransactionScope applies to more than just database providers and there is no reason to doubt this, but how it works seems a little like magic.
Fun with Using and TransactionScope
The using statement helps ensure that finite resources like SqlConnections are cleaned up with an implicit try...finally block, and the TransactionScope makes it easier than ever to use transactions correctly. VB programmers can find many more new VB.NET features like this that make them more productive than ever. I will continue to provide more updates on .NET 2.0, so be sure to send me an e-mail if you have questions or comments.
About the Author
Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object oriented programming and .NET. Check out his new book UML DeMystified from McGraw-Hill/Osborne and his upcoming book, C# Express, from Addison Wesley (Spring 2006). Paul is an architect for Tri-State Hospital Supply Corporation. You may contact him for technology questions at firstname.lastname@example.org.
If you are interested in joining or sponsoring a .NET Users Group, check out www.glugnet.org.
Copyright © 2005 by Paul T. Kimmel. All Rights Reserved.