harriyott.com

SQL CLR - System.InvalidOperationException: The context connection is already in use.

I'm now writing stored procedures in C#, on the SQL CLR, as I have some complex data mangling to do, which I've already solved in C# elsewhere. Instead of converting the code to a long T-SQL stored procedure, I decided to stick it in as is.

Anyway, I got an error message "System.InvalidOperationException: The context connection is already in use." when calling the stored procedure from a trigger. In the trigger, I defined a new connection, did some stuff and called the stored procedure:
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
int stuffID = GetStuffIdFromDatabase(cn);
StoredProcedures.usp_GetMyStuff(stuffID);
}

and in the stored procedure, I ran an update statement:

SqlContext.Pipe.ExecuteAndSend(new SqlCommand("UPDATE Stuff SET [Description] = 'Updated from trigger' WHERE ID = " + stuffID));

I googled around for a while without much luck, but eventually realised that the SqlContext.Pipe.ExecuteAndSend must be creating its own connection. I moved the stored procedure call out of the using block, and it all worked fine.

n.b. I posted this for the benefit of other people who will, in future, google for the error message. If you are one of these people, I hope it helps you, because my regular readers have undoubtedly moved on by now...
21 January 2008