Programming Microsoft® SQL Server® 2012

Putting It All Together

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

Because you are using a context connection in the SQL CLR stored procedure, you’ll follow the best practice of not using TransactionScope inside the SQL CLR stored procedure. This will prevent the transaction from unnecessarily being promoted to MS DTC. The SQL CLR stored procedure should look like this:

[Microsoft.SqlServer.Server.SqlProcedure] public ...