Solving the Mysteries of SQLCLR and System.Transactions

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

My previous three articles covered the nitty gritty of CLR integration in SQL Server 2005 (SQLCLR). In addition to discussing the correct usage, they also explored various SQLCLR features (such as context connections) and their uses and restrictions.

One topic in the previous article was the ability to work with an underlying database. It presented an example application that calculated prime numbers and used a database table to “remember” what it was doing. Obviously, when you are writing to an underlying table via a CLR database object, a number of questions come up:

1. Can you enlist SQLCLR objects in the current running transaction?
2. Can you choose to not enlist in a transaction?
3. Can a SQLCLR object cause the entire transaction (including client-side code, T-SQL, and the SQLCLR object itself) to roll back?
4. What if the transaction is started using a SqlTransaction object? Does that make a difference?
5. What if the transaction has been started using a “BEGIN TRANSACTION” T-SQL statement?

This article demystifies these and many other such questions. First, it runs through the setup of the data source and various other important objects for an example application.

Setting Up the Data Source

The data source is simply a database called Test that includes a table called Persons. You can easily set it up using the following script:

Create database test
go
use test
go

Create Table Persons
(
   PersonID Int Identity Primary Key,
   PersonName Varchar(50)
)
GO

Insert Into Persons (PersonName) Values ('Jimmy The Clown')
GO

Once you’ve set up the above data source, the next step is to get yourself a CLR object.

Creating the CLR Object: A Stored Procedure

Create a CLR stored procedure called RSSP.InsertPerson (Really Simple Stored Procedure). The following is the code for the stored procedure (you also will find it in the code download):

[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertPerson(string PersonName)
{
   using (SqlConnection contextConn =
      new SqlConnection("context connection=true"))
   {
      SqlCommand insertPersonCmd = contextConn.CreateCommand();
      insertPersonCmd.CommandText =
         "Insert Into Persons (PersonName) Values (@PersonName)";
      insertPersonCmd.Parameters.AddWithValue(
         "@PersonName", PersonName);
      contextConn.Open();
      insertPersonCmd.ExecuteNonQuery();
      contextConn.Close();
   }
}

As you can see, the stored procedure simply uses the context connection to insert a new row into the Persons table. If you need further CLR object details, refer to the previous article.

Using the CLR Stored Procedure in Transactions

Once you’ve deployed the stored procedure in SQL Server 2005, you can execute it simply by using the following T-SQL Syntax:

Exec InsertPerson 'Superman'

The line above simply inserts a new row into the Persons table. But what if you wanted to execute code that looked like this:

BEGIN TRANSACTION
Exec InsertPerson 'Batman'
ROLLBACK

It would not insert “Batman” into the Persons table. You’d notice that the SQLCLR stored procedure has the ability to enlist itself automatically in the current running transaction, and obey the ultimate issued rollback on the current running transaction. This, in fact, is made possible by the integration of System.Transactions—all the way into SQLCLR. After all, it is the same framework inside or outside SQL Server.

Now, modify the stored procedure code to as follows, and redeploy to SQL Server:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertPerson(string PersonName)
{
   using (SqlConnection contextConn =
      new SqlConnection("context connection=true"))
   {
      SqlCommand insertPersonCmd = contextConn.CreateCommand();
      insertPersonCmd.CommandText =
         "Insert Into Persons (PersonName) Values (@PersonName)";
      insertPersonCmd.Parameters.AddWithValue(
         "@PersonName", PersonName);
      contextConn.Open();
      insertPersonCmd.ExecuteNonQuery();
      contextConn.Close();
      // The line below will need a reference to System.Transactions
      System.Transactions.Transaction.Current.Rollback();
   }
}

Note the newly added line of code:

System.Transactions.Transaction.Current.Rollback();

If you were to execute a T-SQL code block as follows:

BEGIN TRANSACTION
Insert Into Persons(PersonName) Values ('Spiderman')
Exec InsertPerson 'Mr. Incredible'
COMMIT

You should get the following output:

(1 row(s) affected)
Msg 266, Level 16, State 2, Procedure InsertPerson, Line 0
Transaction count after EXECUTE indicates that a COMMIT or
ROLLBACK TRANSACTION statement is missing.
Previous count = 1, current count = 0.
Msg 3902, Level 16, State 1, Line 4
The COMMIT TRANSACTION request has no corresponding
BEGIN TRANSACTION.

Basically, SQL Server is informing you that the current running transaction has been rolled back by one of the participants (SQLCLR) within the transaction, so your commit cannot succeed.

You may be shocked at how simple this code looks. You simply latched on to the current running transaction using System.Transactions.Transaction.Current, and issued a rollback. However, you need to be wary of a few things when using System.Transactions—you should understand what is going on under the sheets to truly make good use of it. So, the following section offers a quick primer on what System.Transactions is, and what it brings to the table.