Programming Microsoft® SQL Server™ 2008

Putting It All Together

Let’s end this chapter with one example that sums up the whole SQL CLR transaction story. We’ll take the SQL CLR stored procedure we wrote and call it from a console application. Inside the console application, we will wrap the SQL CLR stored procedure and the RM we wrote earlier in this chapter in a single transaction, bound together with a single System.Transactions.TransactionScope.

Because we are using only a context connection in our SQL CLR stored procedure, let’s follow the best practice of not using TransactionScope inside the SQL CLR stored procedure. The SQL CLR stored procedure should look like this:

[Microsoft.SqlServer.Server.SqlProcedure] public static void InsertRow() { using (SqlConnection contextConn = new ...