Improving Distributed Transactions
When you're working in .NET and connecting to SQL Server in pre-2005 versions of Visual Studio, if you want a transaction that spans multiple data sources (aka an automatic transaction), you need to put your .NET components in the Enterprise Services .NET namespace, then manually integrate your .NET assemblies with COM+. Visual Studio 2005 simplifies this manual process by introducing a new namespace called System.Transactions. (You can find documentation of this namespace at http://msdn2.microsoft.com/library/system.transactions.aspx or in the .NET Framework Class Library section of MSDN.) The System.Transactions namespace has three main classes that developers will use to manage transactions: Transaction, TransactionScope, and TransactionManager.
In .NET 1.1, you can use the Connection object to call the BeginTransaction method. When you use this method on a SQL Server connection, the Connection object returns a System.Data.SqlClient.SqlTransaction object. You can then associate that object with each of the commands that will participate in the transaction. Recall that SQL Server provides implicit transactions, so you need a transaction object only to encompass the scope of multiple commands.
When you call the BeginTransaction method, ADO.NET uses your connection to call SQL Server and execute the T-SQL Begin Transaction command. Thus, to use the BeginTransaction method, you must sequentially use the same connection object for each command that will participate in the transaction—and this type of transaction isn't compatible with COM+ transactions.
The first thing to know about using the System.Transactions namespace is that you'll still be able to create a manual transaction on SQL Server without using the BeginTransaction method. The transaction you create can still be considered manual because you code the start and finish of the transaction as well as associating the transaction with each command that will participate.
Visual Studio 2005's System.Transactions.Transaction object provides a way for you to manually enlist a command into a transaction. The code at callout B in Listing 1 contains an example of how to manually create a System.Transactions.CommittableTransaction object. (The CommittableTransaction object is one of two implementations of the virtual Transaction base class.) The code creates a Connection object and opens the connection. Once you have an open connection to the database, the connection object uses your CommitableTransaction object to enlist a transaction. This is the start of your transaction, and you can then execute one or more database commands that will be part of this transaction. Finally, when you finish accessing the database, you can either commit or roll back your transaction.
Although the ability to manually associate your new transaction object with a connection is similar to existing transaction logic, behind the scenes, you've actually changed the transaction paradigm. The code at callout B actually creates a distributed transaction, which in Visual Studio 2003 would require Enterprise Services. Notice that this project doesn't reference the System.EnterpriseServices namespace. In Visual Studio 2005, you need to include only the System.Transactions library as part of your project references to create transactions that span multiple data sources. Not needing to use Enterprise Services means that your code is simpler and that you don't need to integrate your assembly with COM+. Now you can create within .NET a distributed transaction that spans multiple connections. Remember, too, that your connection object must remain open—although dormant—until the transaction is complete, as shown by the order of the commands in the sample code.
Although the System.Transactions.Transaction object simplifies managing distributed transactions, Visual Studio 2005 goes further and provides the tools to automate distributed transactions. The TransactionScope object provides a way to automatically associate with the current transaction each connection object created within its scope. As the code at callout C in Listing 1 shows, the code for using the TransactionScope object is in some ways even simpler than the code for manually creating a System.Transaction.Transaction object. The code at callout C starts by using a new Visual Basic language element, the Using command. You should always create the TransactionScope object within the context of a Using command to ensure that you've explicitly defined the scope of the transaction. After doing so, you can create and open as many connections as you want, and each command that executes within the context of your active transaction scope is automatically associated with your current transaction.
The code at callout C could create multiple transactions, but because the example uses only one data source, I left only one connection active. The code opens the connection and executes an Update command. The next step is to set the transaction's status. Note that setting the status is different from committing your transaction. When you work with the TransactionScope object, the transaction isn't committed until the End Using statement. The Complete or Dispose method calls don't actually commit or roll back your transaction. Instead, they indicate what should happen when the TransactionScope reaches its end. When you use a TransactionScope object instead of a Transaction object, you no longer need to associate the various connections with your transaction; instead, .NET automatically creates the association.
The third primary object in the System.Transactions namespace, TransactionManager, isn't actually designed for processing transactions so much as it is available to provide your developers an interface to the distributed-transaction coordinator. Working with this object is beyond the scope of this article, but the purpose of the object is to provide a set of static methods that you can use to access one or more transaction managers. Thus, developers can register transaction-management utilities other than the default managers that .NET provides, which means you can extend your transaction coordination beyond COM+.
Get Ready to Go
This article has introduced only three of the features that developers can use in SQL Server 2005 and Visual Studio 2005. Many more new features—such as the ability to work with XML, create truly asynchronous database queries, and improve error handling—will further enhance the applications you build. The key is that regardless of whether you're a developer or an administrator, now is the time to start working with these products. You can use the final beta versions of SQL Server 2005 and Visual Studio 2005 as the basis for new development, so you'll have 2005-ready applications to use as soon after release as possible.