In SQL CLR, best practice for queries is to use a context connection that reuses the already existing connection to the db. There's also a restriction that you can only have one open context connection at a time per execution environment (i.e. per
sql clr stored proc / function that's running; a nested sql clr function called from within another sql clr function has its own separate execution environment, hence has its own open context connection).
If I have a relatively long-running sql clr trigger:
- Is it best practice to create it in a singleton wrapper and keep it throughout execution, or to create a new SqlConnection for each use in a using statement?
- Is it best practice to explicitly close the context connection after each call, or to leave it open and just have a check for whether it's already open before opening another one? Note: If I create a new SqlConnection each time in a using statement
and open it and leave it open, and then there's a nested using statement inside the main one, I can't open the connection or it will fail, so I must ALWAYS have a check for whether the connection is open before opening it, even though this looks odd because
I just created it as a new SqlConnection)
View Complete Post