I am trying to do a many different queries on a result set which has a very large creation time. To get performance gains I wish to use a temp table and just do many queries on this temp table.
Seems pretty standard. Yet I am struggling to share this temp table in dynamic sql. As I understand it, each SqlCommand object executes in its own thread and so the temp table is in a different scope - thus making it inaccessible from the query thread.
I tried using a global temporary table and that works great, but not ideal?
How can I share a local temporary table between dynamic SQL queries?
My intent:
using (var conn = new SqlClient.SqlConnection("..."))
{
// Creation involes many table joins in reality
String creationScript = "SELECT * FROM FooTable INTO #MyTemp";
SqlCommand createTempTbl = new SqlCommand(creationScript, conn);
createTempTbl.ExecuteNonQuery();
String query1 = "SELECT * FROM #MyTemp where id=@id";
SqlCommand query1Comm = new SqlCommand(query1, conn);
query1Comm.Parameters.Add("@id", ...);
String query2 = "SELECT * FROM #MyTemp where name=@name";
SqlCommand query2Comm = new SqlCommand(query2, conn);
query2Comm.Parameters.Add("@name", ...);
// And so on the queries go
} // Now want #MyTemp to be destroyed