Dapper query with parameter not holding temp table in subsequent query [duplicate]

Question

This question already has answers here:

Using dapper, why is a temp table created in one use of a connection not available in a second use of the same connection (3 answers)

Sql Server temporary table disappears (9 answers)

Closed yesterday.

I have C# code that executes a Dapper Query, creates a temporary table, and uses a parameter to pass a value. In a subsequent query temp table is not available and will throw an error

Invalid object name #TempTable

Whereas if I do not use a parameterized query, then it works properly.

Below is my code -

public async void MyMethod()
{
 using(var connection = _unitOfWork.GetConnection())
 {
 connection.Open();
 var id = await connection.QueryAsync<int>(@"CREATE TABLE #TempTable(Id int); 
 INSERT INTO #TempTable SELECT TOP 1 ID from XYZ WHERE P_ID = @PID",
 new { PID = 12345 }).ConfigureAwait(false);
 await connection.ExecuteAsync("SELECT ID FROM #TempTable");
 )
 }
}

Here, #TempTable will not be available in the second query.

How to avoid this issue? I want to use a parameterized query (like @parameter)

I don't want to use param like $"Select ID from XYZ where P_ID = { pid }

• Guru Stron: Based on the syntax it seems that you are using SQL Server. From the comment to the accepted answer of the first duplicate - "When you include parameters in the command, .net wraps your sql with an "exec sp_executesql" call, which makes your temp table accessible only within that context. If you don't include parameters, your sql is executed directly, so it is available for subsequent commands."

Answer 1

Temp tables exist only for the single session, so temp table only exists in scope of the first query. When you execute second query, it's already different session and any temp table created in other query won't be accessible in your second query.

So in order for that to work, you need to include second query within the first one:

var id = await connection.QueryAsync<int>(
 @"CREATE TABLE #TempTable(Id int); 
 INSERT INTO #TempTable SELECT TOP 1 ID from [test].[dbo].[Items] WHERE ID = @PID;
 SELECT ID FROM #TempTable",
 new { PID = 6 }).ConfigureAwait(false);

• Guru Stron: That is not completely correct as far as I understand - please see the duplicates and comments.