How do I use a temp table across multiple c# calls

Question

I have a C# application, using ADO.Net to connect to MSSQL

I need to create the table (with a dynamic number of columns), then insert many records, then do a select back out of the table.

Each step must be a separate C# call, although I can keep a connection/transaction open for the duration.

Answer 1

The issue is that #Temp tables exist only within the Connection AND the Scope of the execution. When the first call from C# to SQL completes, control passes up to a higher level of scope.

This is just as if you had a T-SQL script that called two stored procedures. Each SP created a table named #MyTable. The second SP is referencing a completly different table than the first SP.
However, if the parent T-SQL code created the table, both SP's could see it, but they can't see each others.

The solution here is to use ##Temp tables. They cross scope and connections. The danger though is that if you use a hard coded name, then two instances of your program running at the same time could see the same table. So dynamically set the table name to something that will be always be unique.

Answer 2

There are two types of temp tables in SQL Server, local temp tables and global temp tables. From the BOL:

Prefix local temporary table names with single number sign (#tablename), and prefix global temporary table names with a double number sign (##tablename).

Local temp tables will live for just your current connection. Globals will be available for all connections. Thus, if you re-use (and you did say you could) the same connection across your related calls, you can just use a local temp table without worries of simultaneous processes interfering with each others' temp tables.

You can get more info on this from the BOL article, specifically under the "Temporary Tables" section about halfway down.