Transactions in CLR Code | Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET

Managed database objects (such as stored procedures and triggers) can participate in transactions in several ways including (but not limited to) the following:

A managed object can be enlisted (can participate) in a transaction that is initiated by a caller, no matter what type of caller that may be—client application, Transact-SQL batch, stored procedure, managed database object, etc.
A managed object can be designed to perform its operation outside of an existing transaction that is initiated by a caller.
A transaction can be rolled back inside the managed object (no matter where the transaction was initiated—client, Transact-SQL, managed database object, etc.).
A managed database object can initiate a transaction against a single database or multiple (distributed) databases.

For example, you do not need to do anything special to the code of a CLR stored procedure to participate in a transaction initiated by Begin Tran in a Transact-SQL batch (or initiated in any other way). However, if you want to test some condition and, based on it, roll back the entire transaction, your code must include a reference to System.Transactions and a call to System.Transactions.Transaction.Current.Rollback(). In the following C# example, a command object will be executed against a database. If the error is trapped, the code will perform some error handling and finally roll back the transaction (which was initiated by a caller such as Transact-SQL batch):

 try { Cmdl.ExecuteNonQuery(); } catch {    //some custom error handling    ...    System.Transactions.Transaction.Current.Rollback() }

Using TransactionScope Class

Code written using .NET Framework 1.0 and 1.1 had to use support for transactions from System.EnterpriseServices and SystemData.IDbTransaction. Working with it is somewhat similar to doing transactions in Transact-SQL. A developer has to:

Declare (create) a connection object.
Instantiate (open) the connection object.
Declare the transaction object.
Request (begin) a transaction object from the connection.
Create all command objects using the transaction.
Execute commands.
If there are no errors, commit the transaction.
If there is an error, roll back the transaction.
Close the connection.

.NET Framework 2.0 has introduced System.Transactions, which is easier to use and addresses some complexity issues.

By combining the TransactionScope class and a using (or Using in Visual Basic) statement, development of code that uses transactions is as simple as:

Creating an instance of the transaction in a Using statement via the TransactionScope() constructor
Creating an instance of the connection class in the Using statement
Executing Transact-SQL commands (one or more)
Completing the transaction

In Visual Basic .NET code, this would be something like the following:

 Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Transactions Partial Public Class StoredProcedures     <Microsoft.SqlServer.Server.SqlProcedure()> _     Public Shared Sub cp_LeasedAsset_Insert( _         ByVal EqId As Integer, ByVal LocId As Integer, _         ByVal StatusId As Integer, ByVal LeaseId As Integer, _         ByVal LeaseScheduleId As Integer, ByVal OwnerId As Integer, _         ByVal LeaseAmount As Decimal, ByVal AcqTypeId As Integer)         Dim Cmd1 As String = "insert Inventory(EqId, LocationId, " + _              "StatusId,            Leaseld, " + _              "LeaseScheduleId,     OwnerId, " + _              "Lease,               AcquisitionTypeID)" + _              "values (" + EqId.ToString() + ", " + LocId. ToString() + ", " + _              StatusId.ToString() + " , " + LeaseId.ToString() + " , " + _              LeaseScheduleId.ToString() + ", " + OwnerId.ToString() + ", " + _              LeaseAmount.ToString() + " , " + AcqTypeId.ToString() + ")"        Dim Cmd2 As String = "update dbo.LeaseSchedule " + _             "Set PeriodicTotalAmount = PeriodicTotalAmount + " _             + LeaseAmount.ToString() + _             "where LeaseId = " + LeaseId.ToString()      Using TxScope As TransactionScope = New TransactionScope()         Using Con As New SqlConnection ("Context Connection=true")           Con.Open()           Dim Cmd As SqlCommand = New SqlCommand(Cmd1, Con)           Cmd.ExecuteNonQuery()           'I will reuse the same connection and command object           'for next segment of operation.           Cmd.CommandText = Cmd2           Cmd.ExecuteNonQuery()               ' commit/complete transaction               TxScope.Complete()            End Using 'connection is implicitly closed        End Using 'transaction is implicitly closed or rolled back     End Sub End Class

The transaction is declared in a Using statement around a code block. The statement allows you to localize usage of an object (or a variable) to a code block and deallocate it with an implicit call to the Dispose() method of the object. Inside the code block, one or more Transact-SQL commands is being executed, and if everything is successfully completed, the operation (transaction) will be completed (committed). If an exception occurs, the code execution would leave the code block and the transaction would be considered discontinued (implicitly rolled back by calling TransactionScope.Dispose behind the scene).

Note

To use the described features, you must add a System.Transactions reference to your project and then reference it in an Import statement in Visual Basic .NET (or a Using statement in C#).

Transaction Options

The TransactionScope() constructor has several overloaded methods. They allow you to specify different combinations of transaction parameters.

Isolation Level

Of all transaction parameters, you will most often change the isolation level. By default, the isolation level of transactions is set to Serializable. As you may remember from Chapter 5, this would preserve all locks until the completion of the transaction. It would make the system too slow and it is typically not required.

To set some other isolation level, such as ReadCommitted, you must use an object of the TransactionOptions class:

 Dim options As TransactionOptions = New TransactionOptions() options.IsolationLevel = Transactions.IsolationLevel.ReadCommitted options.Timeout = TransactionManager.DefaultTimeout Using TxScope As TransactionScope = _     New TransactionScope(TransactionScopeOption.Required, options) End Using

Transaction Duratio

The TransactionOptions object also has a Timeout property. Its value determines when the engine will stop further execution of a transaction as too long. By default, it is set to 60 seconds.

Nested Transactions

The TransactionScope() constructor requires scopeOption as its first parameter. Its values are set using TransactionScopeOption enumeration. By default, the value of scopeOption is TransactionScopeOption.Required—the current scope (code block) requires a transaction. If the code block is executed inside of a transaction, the engine will not initiate a nested transaction. The engine will return the context of an outer transaction as the TransactionScope object—it will use (participate in) the existing (outer) transaction. If the code block has been executed without a transaction context (no transaction was in progress), a new transaction will be created for the code block.

Alternative values are RequiresNew and Suppress. RequiresNew forces the engine to create a new transaction whether the code block is executed inside of a transaction or not. Suppress is used to execute the current code block without an outer transaction. These options open up some interesting opportunities. For example, it is possible to log something in a table inside the code block. Then, even when the outer transaction is rolled back, the contents of the log table should be intact. It is also possible to develop something inside a code block, the success or failure of which would affect the outer transaction.

Note

A nested transaction can participate in an outer transaction only if they have the same isolation level; otherwise, an exception will he thrown.

Distributed Transactions

A resource manager (RM) is a transaction-aware component that performs some useful operation. An RM can participate (enlist) within a transaction and interact with a transaction manager. An example of a resource manager is a SqlConnection object that connects to a SQL Server 2005 database.

A transaction manager is a component that orchestrates the communication between various resource managers and preserves the integrity of the entire operation. Examples of transaction managers are Lightweight Transaction Manager (LTM), used for transactions on a single resource manager (SqlConnection object against SQL Server), and Microsoft Distributed Transaction Coordinator (MSDTC), which is used to communicate with multiple (distributed) resource managers (for example, different or even heterogeneous databases on different servers). MSDTC uses a two-phase commit (see Chapter 5) to maintain the integrity of the operation. Since MSDTC is very heavy for local operations (there is no need for a two-phase commit), it is better to use Lightweight Transaction Manager (LTM) for that situation.

You do not have to do anything special when your transaction is spanning more than one server. CLR will automatically promote a lightweight, local transaction into a distributed transaction involving MSDTC when you open a second connection against another server.

In the following example, the first connection is made to a local SQL Server 2005 database to execute an Insert statement against the Inventory table. The second connection is created against another database on another server. When the connection to the first database is opened, the transaction is created as a lightweight transaction. However, when the second SqlConnection is opened, the transaction is promoted to a distributed transaction:

 <Microsoft.SqlServer.Server.SqlProcedure()> _     Public Shared Sub cp_LeasedAsset_InsertDistributed( _         ByVal EqId As Integer, ByVal LocId As Integer, _         ByVal StatusId As Integer, ByVal LeaseId As Integer, _         ByVal LeaseScheduleId As Integer, ByVal OwnerId As Integer, _         ByVal LeaseAmount As Decimal, ByVal AcqTypeId As Integer)    Dim sCmd1 As String = "insert Inventory(EqId, LocationId, " + _         "StatusId,            LeaseId, " + _         "LeaseScheduleId,     OwnerId, " + _         "Lease,               AcquisitionTypeID)" + _         "values (" + EqId.ToString() + ", " + LocId.ToString() + ", " + _         StatusId.ToString() + " , " + LeaseId.ToString() + " , " + _         LeaseScheduleId.ToString() + ", " + OwnerId.ToString() + ", " + _         LeaseAmount.ToString() + " , " + AcqTypeId.ToString() + ")"    Dim sCmd2 As String = "update dbo.LeaseSum " + _        "Set PeriodicTotalAmount = PeriodicTotalAmount + " _        + LeaseAmount.ToString() + _        "where LeaseId = " + LeaseId.ToString()       Dim ConStringl As String = "Context Connection=true"       Dim ConString2 As String = _       "Data Source=lg/DW;Initial Catalog=Asset5DW;" + _       "Integrated Security=True"    Using TxScope As TransactionScope = New TransactionScope()       Using Conl As New SqlConnection(ConStringl)             Conl.Open()    Dim Cmd2 As SqlCommand = New SqlCommand(sCmd1, Conl)        Cmd2.ExecuteNonQuery()       End Using       Using Con2 As New SqlConnection(ConString2)      'Creation of second connection automaticaly promotes      'the transaction to distributed.      Con2.Open()    Dim Cmd2 As SqlCommand = New SqlCommand(sCmd2, Con2)        Cmd2.ExecuteNonQuery()        End Using 'connection is implicitly closed       'commit/complete transaction       TxScope.Complete()       End Using 'transaction is implicitly closed or rolled back End Sub

The Complete() method of the transaction scope object will finally commit the transaction.

Note

Automatic promotion from a lightweight transaction to a distributed transaction can occur only when the operation is executed against SQL Server 2005.

Explicit Transactions

It is still possible to use older transactional classes such as SqlTransaction to implement transactions explicitly in SQL CLR. The following procedure executes two Transact-SQL statements wrapped in SqlConnection.BeginTransaction and SqlConnection.Commit(). In the case of an error, SqlConnection.Rollback() is called:

 <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub cp_LeasedAsset_InsertDeclarative( _     ByVal EqId As Integer, ByVal LocId As Integer, _     ByVal StatusId As Integer, ByVal LeaseId As Integer, _     ByVal LeaseScheduleId As Integer, ByVal OwnerId As Integer, _     ByVal LeaseAmount As Decimal, ByVal AcqTypeId As Integer)   Dim sCmd1 As String = "insert Inventory(EqId, LocationId, " + _       "StatusId,            LeaseId, " + _       "LeaseScheduleId,     OwnerId, " + _       "Lease,               AcquisitionTypeID)" + _       "values (" + EqId.ToString() + ", " + LocId.ToString() + ", " + _       StatusId.ToString() + " , " + LeaseId.ToString() + " , " +       LeaseScheduleId.ToString() + ", " + OwnerId.ToString() + ", " + _       LeaseAmount.ToString() + " , " + AcqTypeId.ToString() + ")"    Dim sCmd2 As String = "update dbo.LeaseSum " + _       "Set PeriodicTotalAmount = PeriodicTotalAmount + " _       + LeaseAmount.ToString() + _       "where LeaseId = " + LeaseId.ToString()   ' declare here because try and catch have different scope   Dim trans As SqlTransaction = Nothing   Dim connection As SqlConnection = _   New SqlConnection("Context Connection=true")   connection.Open ()      Try           trans = connection.BeginTransaction      Dim command As SqlCommand = _          New SqlCommand(sCmd1, connection, trans)      command.ExecuteNonQuery()      command.CommandText = sCmd2      command.ExecuteNonQuery()      trans.Commit()    Catch ex As Exception        trans.Rollback()     'Todo: additional error hanndling      're-throw exception      Throw ex      Finally         'called every time         connection.Close()      End Try End Sub

Note

The SqlCommand constructor has a SqlTransaction object as a parameter to enlist itself under a transaction.

Benefits of New Transaction Paradigm

It is still possible to use older transactional classes to implement transactions in SQL CLR. However, the new model has several important benefits:

Application programming is decoupled from transaction managers—code that is written for the lightweight transaction manager will automatically be promoted and work without problems if the engine detects usage of another durable resource (database).
It is not required to enlist resources explicitly under a transaction. The engine will automatically make resource managers work as part of the transaction.
Nesting of transactions is easier to implement.
Nested code blocks or procedures can be written so that they do not participate in an outer transaction.
It is easier to handle exceptions and manage resources (disposal of objects is automatic).