Transactions behave so differently with SQL CLR that this topic warrants its own section.
So
far, we have discussed local transactions in SQL Server and ADO.NET, as
well as distributed transactions in ADO.NET, SQL Server, and the .NET
Framework in general. We noted that in PSPE, a distributed transaction
might not be distributed. Thus, the boundaries of what is distributed
and what is not are already blurry. Well in SQL CLR, they are not only
blurry, they are downright indistinct.
Here, you will build on the
same concepts and write a simple SQL CLR stored procedure to
demonstrate the behavior of SQL CLR objects in a surrounding
transaction. The stored procedure is simple; it accepts no parameters
and inserts a row in the TestTable table, as shown here:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertRow()
{
using (SqlConnection contextConn =
new SqlConnection("context connection = true"))
{
SqlCommand insertCmd = contextConn.CreateCommand();
insertCmd.CommandText =
"INSERT INTO TestTable(TestColumn) VALUES(100)";
contextConn.Open();
insertCmd.ExecuteNonQuery();
contextConn.Close();
}
}
As you can see, the code uses a context connection to execute a SqlCommand. The SqlCommand inserts a row into TestTable using a simple INSERT command. This SQL CLR stored procedure, once registered with SQL Server, can be executed using the following T-SQL command:
EXEC InsertRow
As you can see, if you have a matching TestTable in the appropriate database, executing the stored procedure will indeed insert a row.
But what if you wrap this line of T-SQL code inside a BEGIN TRANSACTION/ROLLBACK block?
BEGIN TRANSACTION
INSERT INTO TestTable(TestColumn) VALUES (200)
EXEC InsertRow
ROLLBACK
Interestingly, the row that the InsertRow stored procedure would have inserted is rolled back. Thus, the InsertRow stored procedure can successfully enlist within a calling transaction.
Important
A SQL CLR object automatically enlists within a current running transaction.
You can easily issue a rollback from T-SQL by using the ROLLBACK command. Can you do the same from a SQL CLR stored procedure? Luckily, due to the fantastic integration of System.Transactions with SQL Server transactions, the answer is “yes.” You can access the current running transaction right from within SQL CLR by using the Transaction.Current property and using the current transaction, issue a rollback as shown here:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertRow()
{
using (SqlConnection contextConn =
new SqlConnection("context connection = true"))
{
SqlCommand insertCmd = contextConn.CreateCommand();
insertCmd.CommandText =
"INSERT INTO TestTable(TestColumn) VALUES(100)";
contextConn.Open();
insertCmd.ExecuteNonQuery();
contextConn.Close();
}
Transaction.Current.Rollback();
}
Let’s modify the T-SQL block so that it will attempt to commit, not roll back, as follows:
BEGIN TRANSACTION
INSERT INTO TestTable(TestColumn) VALUES (200)
EXEC InsertRow
COMMIT
Now
when you attempt to execute this T-SQL code block, you’ll see an
ambiguous exception message resulting from the transaction that was
rolled back. You may want to wrap such an error in a custom BEGIN TRY…CATCH or try/catch/finally block and display a better error message than what the framework provides.
Important
A SQL CLR object is able to roll back a current running transaction with the help of System.Transactions integration.
So
far, you have been using a context connection to insert a row in the
database. Now change the connection string to the one shown here:
Data Source=RemoteMachine;Initial Catalog=OtherDB;Integrated Security=SSPI;
A
subtle difference is introduced in the preceding connection string. The
T-SQL code earlier connected to the local database . In contrast, the new
connection string connects to an entirely different database on an
entirely different server. Now remove the ROLLBACK
from the SQL CLR stored procedure and build and deploy it in the
original database. Then execute the following T-SQL code block:
BEGIN TRANSACTION
INSERT INTO TestTable(TestColumn) VALUES (200)
EXEC InsertRow
COMMIT
The transaction now spans two databases. In other words, SQL
CLR is smart enough not only to understand that you are calling the SQL
CLR object within a transaction, but also to promote that transaction
to a distributed
transaction because an external resource is involved. In fact, if you
tried connecting to an Oracle database, the SQL CLR function would
still be enlisted within the same transaction. If you want to change
this default behavior to not enlist within the same transaction, you
can add enlist=false to the fully qualified connection string.
Important
SQL CLR will attempt to enlist any external connections within the same transaction.
You have seen how to use System.Transactions.Transaction.Current and obtain a handle to the current transaction. The obvious next question is: What else could you use? Could you use SqlTransaction? Yes, you could definitely use SqlTransaction, and in that case, you would use SqlConnection.BeginTransaction in a manner identical to non–SQL CLR ADO.NET. (We therefore won’t cover it in depth here.)
The other approach, of course, is to use System.Transactions.TransactionScope, which is preferred because you don’t have to deal with the transaction. TransactionScope handles the details for you. In fact, in most scenarios, you probably don’t want to deal with the current transaction using Transaction.Current directly. The only situations in which you’ll want a direct handle on the transaction are the following:
You want to roll back the external transaction by calling Transaction.Current.Rollback.
You want to enlist resources that for some reason didn’t auto-enlist. You can do so by using Transaction.Current.EnlistVolatile or EnlistDurable.
You
want to manually enlist in the current running transaction or modify
the default behavior by explicitly listening for various callbacks in
the two-phase commit process.
As mentioned, in all other cases, you probably want a more transparent method of writing transactional code, such as wrapping it in a using block so that everything is handled automatically or by using TransactionScope. This is easy to do by modifying your SQL CLR stored procedure code, as shown here:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertRow()
{
using (var tsc = new TransactionScope())
{
using (SqlConnection contextConn =
new SqlConnection("context connection = true"))
{
SqlCommand insertCmd = contextConn.CreateCommand();
insertCmd.CommandText =
"INSERT INTO TestTable(TestColumn) VALUES(100)";
contextConn.Open();
insertCmd.ExecuteNonQuery();
contextConn.Close();
}
tsc.Complete();
}
}
Note that you are wrapping a context connection inside a TransactionScope. This is usually a bad practice with SQL CLR, and we’ll explain why in a moment. But if you have more than one database or RM involved in the transaction, TransactionScope
will take care of enlisting everything in one transaction. The good
part of this programming paradigm is that if there is already an active
transaction, TransactionScope
will take advantage of that transaction. If there is no active
transaction, it will simply start a new transaction. This level of
transparency helps you write more understandable and more manageable
code.
Now comment out the tsc.Complete()
statement from the SQL CLR stored procedure code, build and deploy the
stored procedure on the SQL Server database, and try running it in the
transactional T-SQL code. You’ll get the following error:
Msg 8520, Level 16, State 1, Line 4
Internal Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed to
commit: 0x8004d019(XACT_E_ABORTED).
This is as expected; because the TransactionScope
did not get marked as complete, the transaction aborted. What you may
find surprising here, though, is that the message is coming from MS
DTC. There is only one database connection, the context connection, and
yet the transaction was promoted. This raises another important point
about SQL CLR transactions: when working inside SQL CLR, the TransactionScope object will always cause the transaction to promote to MS DTC, even if you are using only context connections. For this reason, you should avoid using TransactionScope and stick with SqlTransaction or System.Transactions.Transaction.Current with SQL CLR if you’re only going to use context connections.
