I've been reading all sorts of questions that are similar to what I'm trying to do, but different enough that they do not seem to apply. What I have is a C# project to create a CLR stored procedure. I'm in the process of improving performance on the CLR Stored Procedure by multi-threading it. (It has a set of nested loops, and on the inner-most loop I'm calling Parallel.ForEach to run them all on their own threads.) Well, sometimes the processing that's done needs to execute a query against the database. This is done using the context connection that launched the stored procedure in the first place. And here's my problem. SQL Server will not let you access the context connection from a child thread. If you're going to access the context connection, you must be executing on the main thread.
Since this is not a WinForms project, I can't make use of BeginInvoke. (And I know there's a similar command for WPF applications.) And I've seen several posts discussing the use of SynchronizationContext to do this. But my main thread does not have a SynchronizationContext to reference. (I think that's created by the first control placed on the thread?) I need to figure out how to marshal execution back onto the main thread just enough to access the context connection. I'm still somewhat new to working with multi-threaded applications. So I apologize if my terminology usage was poor or imprecise.
Thanks.
Edit:
So, based on the comments and answer, so far, I tried making some changes, but I'm afraid that either they're not working or I'm just not getting something. So I thought I'd post some simplified code as an example of what I'm currently doing. (Remember that this example does not work because the query execution is taking place on the child threads and only the main thread can make use of the context connection.)
public class MyDatabaseProject
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static int MyClrStoredProcedure(...)
{
ProcessingEngine engine = new ProcessingEngine();
engine.SomeQueryEvent += this.HandleSomeQueryEvent;
... // Gather up some data to process.
DataTable results = engine.Compute(...);
... // Save the computed results DataTable.
}
private static void HandleSomeQueryEvent(object Sender, MyEventArgs e)
{
SqlConnection contextConn = new SqlConnection("context connection=true");
contextConn.Open();
foreach (string query in e.QueriesToExecute)
{
// Use the contextConnection to execute the query and store the results in MyEventArgs.
}
contextConn.Close();
}
}
public class ProcessingEngine
{
public DataTable Compute(...)
{
... // Do stuff
foreach(var timingIndicator in SomeCollection)
{
... // Do stuff
Parallel.ForEach(FormulasToProcessNow, new ParallelOptions { MaxDegreeOfParallelism = this.ConcurrencyLevel }, r =>
{
... // Do stuff, including raising "SomeQueryEvent"
... // Do stuff with the results of the queries.
});
}
}
}
So what's confusing me is how to incorporate your suggestions (such as the ConcurrentQueue and AutoResetEvent) in a way that will work. Hopefully this code is helpful. Thanks, again.
Is there a reason why you need to use the Context Connection? Are you using any Session-specific functionality, such as being part of an existing transaction, or reading from existing temp tables, or using CONTEXT_INFO / SESSION_CONTEXT?
Is there a reason why you just don't use a regular / external connection?
(those questions were answered with the following quoted statements)
We want this to run with the user's security, connection settings, etc. Besides, if I attempt to open a connection from within the code, SQL Server raises an exception. So, to be honest, I haven't given that much thought. Opening another connection wasn't allowed, and we wanted to use the context connection, anyway. So that's what we did.
Running with the User's security is easy enough:
ConnectionString. This might be more difficult if there are a lot of Users that could be executing this code, though you could have the User pass in their credentials as input parameters to the SQLCLR stored procedure and build the Connection String from those.If the User is a Windows Login, implement Impersonation. You can get the Windows Security Principal (or whatever) from SqlContext. Then do:
using(impersonationContext = principal.Impersonate())
{
connection.Open();
...
impersonationContext.Undo();
}
That's not exact, but it's close.
Okay, so we actually ended up going with this. For these queries, we do not require the context connection. So we were able to get away with opening up new connections, instead of figuring out how to switch threads effectively.
Excellent! Glad to hear that it worked out after all 😺 . Since the Context Connection was not required, it seems like it would have taken more time / effort to figure out the thread-switching than it would have been worth. And, the code would like have been quite a bit more complicated (i.e. harder to maintain) than what you ended up with.
I suspect that if we had architected this from the beginning to run these queries on the main thread that it wouldn't have been such an issue. But this seems to get the job done.
True, it might have been less complicated had you planned that part out from the beginning. However, that would have introduced a point-of-contention on that singular connection, which would decrease performance (even if only slightly). Even with the Context Connection being faster to open/close than a regular connection, depending on how long it takes to get results back from the queries, the various threads could potentially be waiting around far longer than the few extra milliseconds eaten up by establishing regular connections. That, plus the increased complexity of the code, might well be worth it if you require session-specific functionality (i.e. working within an active transaction, working with local temporary objects, using CONTEXT_INFO and/or SESSION_CONTEXT, etc), but otherwise probably not.
For more info on working with SQLCLR in general, please visit: SQLCLR Info