The System.Transactions namespace offers enhanced transactional support for managed code and makes it possible to handle transactions in a rather simple programming model. System.Transactions is designed to integrate well with SQL Server 2005 and offers automatic promotion of standard transactions to fully distributed transactions. Figure 10-1 shows the hierarchy of the Transaction class in the System.Transactions namespace. The TransactionScope class was added to the diagram to show that it is not related to the Transaction class but is used to create transactions.
Figure 10-1: The System.Transactions.Transaction class hierarchy
Creating a Transaction Using the TransactionScope Class
You can also create a transaction in your .NET code by using classes in the System.Transaction namespace. The most commonly used class is the TransactionScope class; it creates a standard transaction called a "local lightweight transaction" that is automatically promoted to a full-fledged distributed transaction if required. Note that this automatically promoted transaction is commonly referred to as an implicit transaction. This is certainly worth mentioning because it seems to give a somewhat different meaning to this term. You should note that even in this context, you are not explicitly creating the transaction for the work and explicitly issuing a commit or rollback. Instead, you are creating a scope where a transaction will exist and will automatically commit or roll back.
Visual Basic
Private Sub Button3_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Dim cnSetting As ConnectionStringSettings = _ ConfigurationManager.ConnectionStrings("NorthwindString") Using ts As TransactionScope = New TransactionScope() Using cn As New SqlConnection() cn.ConnectionString = cnSetting.ConnectionString cn.Open() 'work code here Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "SELECT count(*) FROM employees" Dim count As Integer = CInt(cmd.ExecuteScalar()) MessageBox.Show(count.ToString()) End Using 'if we made it this far, commit ts.Complete() End Using End Using End Sub
C#
private void button3_Click(object sender, EventArgs e) { ConnectionStringSettings cnSetting = ConfigurationManager.ConnectionStrings["NorthwindString"]; using (TransactionScope ts = new TransactionScope()) { using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = cnSetting.ConnectionString; cn.Open(); //work code here using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "SELECT count(*) FROM employees"; int count = (int)cmd.ExecuteScalar(); MessageBox.Show(count.ToString()); } //if we made it this far, commit ts.Complete(); } } }
This code starts by creating a TransactionScope object in a using block. If a connection is created, the TransactionScope object assigns a transaction to this connection, so you don't need to add anything to your code to enlist this connection into the transaction. Notice that the SqlCommand object doesn't need to have the Transaction property assigned, but the SqlCommand object joins the transaction. If an exception is thrown within the TransactionScope object's using block, the transaction aborts and all work is rolled back. The last line of the Transaction-Scope object's using block calls the Complete method to commit the transaction. This method sets an internal flag called Complete. The Complete method can be called only once. This is a design decision that ensures that you don't continue adding code after a call to Complete and then try calling Complete again. A second call to Complete throws an InvalidOperationException.
The scope of the transaction is limited to the code within the TransactionScope object's using block, which includes any and all connections created within the block, even if the connections are created in methods called within the block. You can see that the TransactionScope object offers more functionality than ADO.NET transactions and is easy to code.
Note
The code in this example performs as well as the previous examples showing ADO.NET transactions, so you should consider standardizing your code to use this programming model whenever you need transactional behavior, as long as you are using SQL Server 2005. If you are using SQL Server 2000, you should continue to use the existing ADO.NET transaction because SQL Server 2000 does not know how to create a local lightweight transaction.
Setting the Transaction Options
You can set the isolation level and the transaction's timeout period on the TransactionScope object by creating a TransactionOptions object. The TransactionOptions class has an IsolationLevel property that you can use to deviate from the default isolation level of Serializable and employ another isolation level (such as Read Committed). The isolation level is merely a suggestion (hint) to the database. Most database engines try to use the suggested level if possible. The TransactionOptions class also has a TimeOut property that can be used to deviate from the default of one minute.
The TransactionScope object's constructor also takes a TransactionScopeOption enumeration parameter. This parameter can be set to any of the following.
Required If your application has already started a transaction, this TransactionScope joins it. If there is no ongoing transaction, a new transaction is created. To help you understand the benefit of this setting, consider a BankAccount class that has Deposit, WithDraw, and Transfer methods. You want to create a TransactionScope in all three methods, but when you get to the Transfer method, it calls the WithDraw and the Deposit methods that already have a TransactionScope defined. This means that the Deposit and WithDraw will be nested within the Transfer. You can't remove the TransactionScope from the WithDraw and Deposit because you need the ability to call these methods directly, and you want these methods to execute within a transaction. With the Required setting, the WithDraw and Deposit join the Transfer transaction. This is the default setting.
RequiresNew Always starts a new transaction, even if there is an ongoing transaction. Expanding on the previous BankAccount scenario, you may want an audit record to be written, regardless of the outcome of the overall transaction. If you simply try to write an audit record and the transaction aborts, the audit record is rolled back as well. Use the RequiresNew setting to write the audit record regardless of the overall transaction state.
Suppress Suppresses any transaction activity in this block. This setting provides a way to do non-transactional work while there is an ongoing transaction. The execution of the Complete method is not required in this block and has no effect.
The following code creates and configures a TransactionOptions object, which is passed to the constructor of the TransactionScope object.
Visual Basic
Private Sub Button4_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button4.Click Dim cnSetting As ConnectionStringSettings = _ ConfigurationManager.ConnectionStrings("NorthwindString") Dim opt as New TransactionOptions() opt.IsolationLevel = IsolationLevel.Serializable Using ts As TransactionScope = _ New TransactionScope(TransactionScopeOption.Required, opt) Using cn As New SqlConnection() cn.ConnectionString = cnSetting.ConnectionString cn.Open() 'work code here Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "SELECT count(*) FROM employees" Dim count As Integer = CInt(cmd.ExecuteScalar()) MessageBox.Show(count.ToString()) End Using End Using 'if we made it this far, commit ts.Complete() End Using End Sub
C#
private void button4_Click(object sender, EventArgs e) { ConnectionStringSettings cnSetting = ConfigurationManager.ConnectionStrings["NorthwindString"]; TransactionOptions opt = new TransactionOptions(); opt.IsolationLevel = System.Transactions.IsolationLevel.Serializable; using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, opt)) { using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = cnSetting.ConnectionString; cn.Open(); //work code here using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "SELECT count(*) FROM employees"; int count = (int)cmd.ExecuteScalar(); MessageBox.Show(count.ToString()); } } //if we made it this far, commit ts.Complete(); }
Working with Distributed Transactions
Before the release of the classes in the System.Transactions namespace, developers had to create classes that inherited from the ServicedComponent class in the System.EnterpriseServices namespace to perform distributed transactions, as shown in the following snippet.
Visual Basic
Imports System.EnterpriseServices<Transaction> _Public Class MyClass Inherits ServicedComponent <AutoComplete()> _ Public Sub MyMethod() ' calls to other serviced components ' and resource managers like SQL Server End SubEnd Class
C#
using System.EnterpriseServices;[Transaction]public class MyClass : ServicedComponent{ [AutoComplete] public void MyMethod() { // calls to other serviced components // and resource managers like SQL Server }}
Notice the use of the Transaction and AutoComplete attributes to ensure that any method called within the class is in a transactional context. The use of the AutoComplete attribute makes it simple to commit a transaction in a declarative way, but you can also use the ContextUtil class for better control of the transaction from within your code.
The problem with this old approach is that you must inherit from the ServicedComponent class that is, you lose the flexibility of inheriting from a class that might be more appropriate to your internal application's class model. This also means that the DTC is always used, which is too resource intensive if you don't really need to execute a distributed transaction. Ideally, the DTC should be used only when necessary. This approach uses the COM+ hosting model, in which your component must be loaded into Component Services.
The System.Transactions namespace includes the Lightweight Transaction Manager (LTM) in addition to the DTC. The LTM is used to manage a single transaction to a durable resource manager, such as SQL Server 2005. Volatile resource managers, which are memory based, can also be enlisted in a single transaction. The transaction managers are intended to be invisible to the developer, who never needs to write code to access them.
Using the TransactionScope object and the same programming model that we used for single transactions, you can easily create a distributed transaction. When you access your first durable resource manager, a lightweight committable transaction is created to support the single transaction. When you access a second durable resource manager, the transaction is promoted to a distributed transaction. When a distributed transaction is executed, the DTC manages the two-phase commit protocol to commit or roll back the transaction.
The LTM and the DTC represent their transaction using the System.Transactions.Transaction class, which has a static (Visual Basic shared) property called Current that gives you access to the current transaction. The current transaction is known as the ambient transaction. This property is null if there is no ongoing transaction. You can access the Current property directly to change the transaction isolation level, roll back the transaction, or view the transaction status.
Promotion Details
When a transaction is first created, it always attempts to be a lightweight committable transaction, managed by the LTM. The LTM lets the underlying durable resource manager, such as SQL Server 2005, manage the transaction. A transaction that is managed by the underlying manager is known as a delegated transaction. The only thing the LTM does is monitor the transaction for a need to be promoted. If promotion is required, the LTM tells the durable resource manager to provide an object that is capable of performing a distributed transaction. To support the notification, the durable resource manager must implement the IPromotableSinglePhaseNotification interface. This interface, and its parent interface, the ITransactionPromoter, are shown here.
Visual Basic
Imports SystemNamespace System.Transactions Public Interface IPromotableSinglePhaseNotification Inherits ITransactionPromoter Sub Initialize() Sub Rollback(ByVal singlePhaseEnlistment As _ SinglePhaseEnlistment) Sub SinglePhaseCommit(ByVal singlePhaseEnlistment As _ SinglePhaseEnlistment) End Interface Public Interface ITransactionPromoter Function Promote() As Byte() End InterfaceEnd Namespace
C#
using System;namespace System.Transactions{ public interface IPromotableSinglePhaseNotification : ITransactionPromoter { void Initialize(); void Rollback(SinglePhaseEnlistment singlePhaseEnlistment); void SinglePhaseCommit(SinglePhaseEnlistment singlePhaseEnlistment); } public interface ITransactionPromoter { byte[] Promote(); }}
For example, the System.Data.dll assembly contains an internal class called SqlDelegatedTransaction that implements these interfaces for use with SQL Server 2005. SQL Server 2005 uses delegated transactions whenever possible. This means that on the SqlConnection.Open method, a local promotable transaction is created using the SqlDelegatedTransaction class, not a distributed transaction, and it remains a local transaction with its accompanying performance implications until you require a distributed transaction.
A transaction can be promoted from a lightweight committable transaction to a distributed transaction in the following three scenarios:
When a durable resource manager is used that doesn't implement the IPromotableSinglePhaseNotification interface, such as SQL Server 2000
When two durable resource managers are enlisted in the same transaction
When the transaction spans multiple application domains
Viewing Distributed Transactions
The DTC is available through Component Services (choose Start | Control Panel | Administrative Tools | Component Services | Computers | My Computer | Distributed Transaction Coordinator | Transaction Statistics). When Component Services starts, the Transaction Statistics node is blank, as shown in Figure 10-2. If you run any of the previous code examples, the screen will remain blank because the examples use standard transactions; when a transaction is promoted to a distributed transaction, however, you will see a change to the total transaction count as well as the other counters.
Figure 10-2: The DTC is used to monitor distributed transactions.
Creating a Distributed Transaction
To create a distributed transaction, you can use the same TransactionScope programming model that we used to create a standard transaction, but you add work to be performed on a different connection. The following code example uses two connections. Even though these connection objects use the same connection string, they are two different connection objects, which will cause the single transaction to be promoted to a distributed transaction.
Note
Before running this code, make sure that the DTC is running or you will get an exception stating that the MSDTC is not available.
Visual Basic
Private Sub Button5_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button5.Click Dim cnSetting As ConnectionStringSettings = _ ConfigurationManager.ConnectionStrings("NorthwindString") Using ts As TransactionScope = New TransactionScope() Using cn As New SqlConnection() cn.ConnectionString = cnSetting.ConnectionString cn.Open() 'work code here Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "SELECT count(*) FROM employees" Dim count As Integer = CInt(cmd.ExecuteScalar()) MessageBox.Show(count.ToString()) End Using End Using Using cn As New SqlConnection() cn.ConnectionString = cnSetting.ConnectionString cn.Open() 'work code here Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "SELECT count(*) FROM employees" Dim count As Integer = CInt(cmd.ExecuteScalar()) MessageBox.Show(count.ToString()) End Using End Using 'if we made it this far, commit ts.Complete() End Using End Sub
C#
private void button5_Click(object sender, EventArgs e) { ConnectionStringSettings cnSetting = ConfigurationManager.ConnectionStrings["NorthwindString"]; using (TransactionScope ts = new TransactionScope()) { using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = cnSetting.ConnectionString; cn.Open(); //work code here using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "SELECT count(*) FROM employees"; int count = (int)cmd.ExecuteScalar(); MessageBox.Show(count.ToString()); } } using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = cnSetting.ConnectionString; cn.Open(); //work code here using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "SELECT count(*) FROM employees"; int count = (int)cmd.ExecuteScalar(); MessageBox.Show(count.ToString()); } } //if we made it this far, commit ts.Complete(); } }
Because this code uses multiple connections, the connections appear to the LTM as requiring multiple durable resource managers, and the transaction that was originally delegated to SQL Server 2005 is promoted to a distributed transaction.
Building Your Own Transactional Resource Manager
You can build your own transactional resource manager classes that can participate in a transaction that is within the TransactionScope. Remember that your resource manager can be a volatile resource manager that is memory based or it can be a durable resource manager that is persisted to disk or other media. In your class, you must implement the IEnlistmentNotification interface, which has methods that are invoked on objects enlisted in a transaction, and you must also add code to enlist in the transaction.
We start by creating a simple Employee class that enlists in an ongoing transaction if one exists. If a change is made to one of the Employee properties while in a transaction, the value is stored in a working variable. If there is no ongoing transaction, the change is immediately stored to a committed variable.
Creating the Employee Class
The following code creates a simple Employee class containing EmployeeID, LastName, and FirstName properties. This class also contains private variables to hold the working and committed values for each of the properties. The ToString method is overridden to provide an easy means to get the state of the object, and internal methods are created for committing and rolling back the transaction.
Visual Basic
Imports System.Transactions Public Class Employee Private workingEmployeeId As Integer Private workingLastName As String Private workingFirstName As String Private committedEmployeeId As Integer Private committedLastName As String Private committedFirstName As String public Sub New(employeeId as Integer, lastName as String, _ firstName as String) Me.EmployeeID = employeeId Me.LastName = lastName Me.FirstName = firstName End Sub Public Property EmployeeID() As Integer Get Return workingEmployeeId End Get Set(ByVal value As Integer) workingEmployeeId = value End Set End Property Public Property LastName() As String Get Return workingLastName End Get Set(ByVal value As String) workingLastName = value End Set End Property Public Property FirstName() As String Get Return workingFirstName End Get Set(ByVal value As String) workingFirstName = value End Set End Property Public Overrides Function ToString() As String Return String.Format( _ " Employee WorkingVal {0} {1}, {2}" + vbCrLf + _ " CommittedVal {3} {4}, {5}", _ workingEmployeeId, workingLastName, workingFirstName, _ committedEmployeeId, committedLastName, committedFirstName) End Function Public Sub internalCommit() committedEmployeeId = workingEmployeeId committedFirstName = workingFirstName committedLastName = workingLastName End Sub Public Sub internalRollback() workingEmployeeId = committedEmployeeId workingFirstName = committedFirstName workingLastName = committedLastName End Sub End Class
This code makes use of the working values in the properties and in the internalRollback method. The committed values are set in the internalCommit method, and they are displayed in the ToString method. To test the class, add a button containing the following code to a form.
Visual Basic
Private Sub button6_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles button6.Click Dim e1 As New Employee(1, "JoeLast", "JoeFirst") Dim e2 As New Employee(2, "MaryLast", "MaryFirst") Try Using scope As New TransactionScope() e1.LastName = "JoeTranLast" e2.LastName = "MaryTranLast" scope.Complete() End Using Catch xcp As Exception System.Diagnostics.Debug.WriteLine("Exception:" + xcp.Message) End Try System.Diagnostics.Debug.WriteLine(_ "Final Answer:" + vbCrLf + e1.ToString()) System.Diagnostics.Debug.WriteLine(_ "Final Answer:" + vbCrLf + e2.ToString()) End Sub
C#
private void button6_Click(object sender, EventArgs e) { Employee e1 = new Employee(1, "JoeLast", "JoeFirst"); Employee e2 = new Employee(2, "MaryLast", "MaryFirst"); try { using (TransactionScope scope = new TransactionScope()) { e1.LastName = "JoeTranLast"; e2.LastName = "MaryTranLast"; scope.Complete(); } } catch (Exception xcp) { System.Diagnostics.Debug.WriteLine("Exception: " + xcp.Message); } System.Diagnostics.Debug.WriteLine( "Final Answer:\r\n" + e1.ToString()); System.Diagnostics.Debug.WriteLine( "Final Answer:\r\n" + e2.ToString()); }
The output window displays the following information when the button is clicked.
Notice that each employee displays the working and committed values, but the committed values have not been set because there is no code to commit the objects.
Implementing the IEnlistmentNotification Interface
You must implement the IEnlistmentNotification interface to get notification of changes to the transaction. This interface has the following methods that must be implemented.
Commit Executed by the transaction manager during the second phase of a transaction, when the transaction manager instructs participants to commit the transaction. This method is passed an Enlistment object, which needs to have its Done method executed if the commitment is okay.
Prepare Called by the transaction manager during the first phase of a transaction, when the transaction manager asks participants whether they can commit the transaction.
Rollback Called by the transaction manager if the transaction is rolled back.
InDoubt Called by the Lightweight Transaction Manager only on volatile resources when the transaction manager has invoked a single-phase commit operation to a single durable resource and then the connection to the durable resource is lost before the transaction result is obtained. At that point, the transaction outcome cannot be safely determined. The volatile resource that receives a call to its InDoubt method should perform whatever recovery or containment operation it understands on the affected data. This method is called to represent the final state of this object (volatile resource), so neither Commit nor Rollback will be called on this object.
In addition to adding the implementation of these methods, you must add code to enlist in any ongoing transaction. When a change is made to a property, if there is no ongoing transaction, the committed value is immediately updated. If there is an ongoing transaction, this object enlists in it and the committed value is not updated until the transaction is committed. The following code shows how the Employee class has been updated to add automatic enlistment and a simple implementation of the IEnlistmentNotification interface methods.
Visual Basic
Imports System.Transactions Public Class Employee Implements IEnlistmentNotification Private currentTransaction As Transaction Private workingEmployeeId As Integer Private workingLastName As String Private workingFirstName As String Private committedEmployeeId As Integer Private committedLastName As String Private committedFirstName As String Public Sub New(ByVal employeeId As Integer, ByVal lastName As String, _ ByVal firstName As String) Me.EmployeeID = employeeId Me.LastName = lastName Me.FirstName = firstName End Sub Public Property EmployeeID() As Integer Get Return workingEmployeeId End Get Set(ByVal value As Integer) workingEmployeeId = value If Not Enlist() Then committedEmployeeId = value End Set End Property Public Property LastName() As String Get Return workingLastName End Get Set(ByVal value As String) workingLastName = value If Not Enlist() Then committedLastName = value End Set End Property Public Property FirstName() As String Get Return workingFirstName End Get Set(ByVal value As String) workingFirstName = value If Not Enlist() Then committedFirstName = value End Set End Property Public Overrides Function ToString() As String Return String.Format(_ " Employee WorkingVal {0} {1}, {2}" + vbCrLf + _ " CommittedVal {3} {4}, {5}", _ workingEmployeeId, workingLastName, workingFirstName, _ committedEmployeeId, committedLastName, committedFirstName) End Function Public Sub internalCommit() committedEmployeeId = workingEmployeeId committedFirstName = workingFirstName committedLastName = workingLastName End Sub Public Sub internalRollback() workingEmployeeId = committedEmployeeId workingFirstName = committedFirstName workingLastName = committedLastName End Sub Public Function Enlist() As Boolean If Not (currentTransaction Is Nothing) Then Return True End If currentTransaction = Transaction.Current If currentTransaction Is Nothing Then Return False End If currentTransaction.EnlistVolatile(Me, EnlistmentOptions.None) Return True End Function Public Sub Commit(ByVal enlistment As System.Transactions.Enlistment) _ Implements System.Transactions.IEnlistmentNotification.Commit System.Diagnostics.Debug.WriteLine("Commit(before):" + vbCrLf _ + Me.ToString()) internalCommit() currentTransaction = Nothing enlistment.Done() End Sub Public Sub InDoubt(ByVal enlistment As System.Transactions.Enlistment) _ Implements System.Transactions.IEnlistmentNotification.InDoubt System.Diagnostics.Debug.WriteLine("InDoubt:" + vbCrLf + Me.ToString()) currentTransaction = Nothing Throw New TransactionAbortedException( _ "Commit results cannot be determined") End Sub Public Sub Prepare(ByVal preparingEnlistment As _ System.Transactions.PreparingEnlistment) _ Implements System.Transactions.IEnlistmentNotification.Prepare System.Diagnostics.Debug.WriteLine("Prepare(before):" + vbCrLf _ + Me.ToString()) preparingEnlistment.Prepared() End Sub Public Sub Rollback(ByVal enlistment As System.Transactions.Enlistment) _ Implements System.Transactions.IEnlistmentNotification.Rollback System.Diagnostics.Debug.WriteLine("Rollback(before):" + vbCrLf _ + Me.ToString()) currentTransaction = Nothing Me.internalRollback() End Sub End Class
C#
using System; using System.Transactions; namespace TransactionTest { class Employee : IEnlistmentNotification { private Transaction currentTransaction; private int workingEmployeeId; private string workingLastName; private string workingFirstName; private int committedEmployeeId; private string committedLastName; private string committedFirstName; public Employee(int employeeId, string lastName, string firstName) { EmployeeID = employeeId; LastName = lastName; FirstName = firstName; } public int EmployeeID { get{ return workingEmployeeId;} set { workingEmployeeId = value; if (!Enlist()) committedEmployeeId = value; } } public string LastName { get{ return workingLastName;} set { workingLastName = value; if (!Enlist()) committedLastName = value; } } public string FirstName { get{ return workingFirstName;} set { workingFirstName = value; if (!Enlist()) committedFirstName = value; } } public override string ToString() { return string.Format( " Employee WorkingVal {0} {1}, {2}\r\n" + " CommittedVal {3} {4}, {5}", workingEmployeeId, workingLastName, workingFirstName, committedEmployeeId, committedLastName, committedFirstName); } private void internalCommit() { committedEmployeeId = workingEmployeeId; committedFirstName = workingFirstName; committedLastName = workingLastName; } private void internalRollback() { workingEmployeeId = committedEmployeeId; workingFirstName = committedFirstName; workingLastName = committedLastName; } public bool Enlist() { if (currentTransaction != null) { return true; } currentTransaction = Transaction.Current; if (currentTransaction == null) { return false; } currentTransaction.EnlistVolatile(this, EnlistmentOptions.None); return true; } #region IEnlistmentNotification Members public void Commit(Enlistment enlistment) { System.Diagnostics.Debug.WriteLine("Commit(before):\r\n" + this.ToString()); internalCommit(); currentTransaction = null; enlistment.Done(); } public void InDoubt(Enlistment enlistment) { System.Diagnostics.Debug.WriteLine("InDoubt:\r\n" + this.ToString()); currentTransaction = null; throw new TransactionAbortedException( "Commit results cannot be determined"); } public void Prepare(PreparingEnlistment preparingEnlistment) { System.Diagnostics.Debug.WriteLine("Prepare(before):\r\n" + this.ToString()); preparingEnlistment.Prepared(); } public void Rollback(Enlistment enlistment) { System.Diagnostics.Debug.WriteLine("Rollback(before):\r\n" + this.ToString()); currentTransaction = null; this.internalRollback(); } #endregion } }
This code implements the interface along with its methods. Notice that we simply made calls to the internalCommit and internalRollback methods as required. We added a new field called currentTransaction to hold the transaction that this object is enlisted in. We also added the Enlist method, which returns true if this object is enlisted in a transaction and false if it is not. If this is the first time the Enlist method is entered after a transaction starts, the Enlist method sets the currentTransaction field and enlists in the current transaction. Additional calls to the Enlist method will quickly return true because the currentTransaction field is set.
The currentTransaction field is cleared when the transaction completes, which is when the Commit, RollBack , or InDoubt method is called.
Notice that every property setter contains a call to the Enlist method to ensure automatic enlistment. If the object is not in a transaction, the committed value is immediately set.
If you run the same test, the output window will look like the following. Notice that the Prepare method is called on both of the Employee objects before the Commit method is called on the Employee objects.
You can see that the initial creation of the Employee objects immediately commits the values because these objects were created prior to entering the transaction scope and there is no ongoing transaction. Notice that the working value and committed value are different in the Prepare and Commit output because the transaction has not committed yet. You can see that in the final answer output, the objects are committed, so their working and committed values have been updated properly.
Implementing the ISinglePhaseNotification Interface
The ISinglePhaseNotification interface provides a quick commit when only a single resource needs to be committed. It only has one method, called SinglePhaseCommit, which can be easily implemented. The following code shows the addition of this method to the previous code example.
Visual Basic
Public Class Employee Implements IEnlistmentNotification, ISinglePhaseNotification ' . . . Existing code Public Sub SinglePhaseCommit(ByVal singlePhaseEnlistment As _ System.Transactions.SinglePhaseEnlistment) Implements _ System.Transactions.ISinglePhaseNotification.SinglePhaseCommit System.Diagnostics.Debug.WriteLine("SinglePhaseCommit(before):" + vbCrLf _ + Me.ToString()) internalCommit() currentTransaction = Nothing singlePhaseEnlistment.Done() End Sub
If you run the code, you won't see a change to the output window because the previous examples have updated two volatile resource managers (two Employee objects). Comment out the code that changes the second employee, and run the example code. The output window will show that the prepare phase was skipped and the single-phase commit was executed as shown below.
You can see that although the code worked before the implementation of this interface, for greater efficiency when you are working with a single resource manager, you should always implement the ISinglePhaseNotification interface when you implement the IEnlistmentNotification interface.
Using System.Transactions with the SQLCLR
When we invoke managed stored procedures within a database transaction, we flow the transaction context down into the CLR code. The context connection is the same connection, so the same transaction applies and no extra overhead is involved.
If you're opening a connection to a remote server from within the SQLCLR, you use a new connection, so the existing database transaction that came with the context is promoted to a distributed transaction.
The notion of a "current transaction" that is available in the System.Transactions namespace is also available in the SQLCLR. If a transaction is active when your code enters the SQLCLR, you will see that Transaction.Current contains the transaction within the SQLCLR.
If you want to abort the external transaction from within your stored procedure or function, you can call the Transaction.Current.Rollback method.
If you use the TransactionScope object in the SQLCLR, you must use a distributed transaction. This means that if you are in a lightweight transaction, the use of the TransactionScope causes a promotion, which means additional overhead. If you will need a distributed transaction anyway, because you need to access remote servers from the SQLCLR, there is no difference in overhead.
