Creating temp tables from C# application and then using them

[Susan]

Because of the constraints with the size VARCHAR and the number of elements
allowed in a WHERE IN clause, I need to create a temp_table that I insert ids
into and then within my stored procedure select from that temp_table.
Currently, my stored procedure sends in an associative array of the ids,
inserts the ids into a global temp table and then SELECT * FROM prod_table
WHERE prod_ids IN (SELECT prod_ids FROM temp_table). I guess my question is,
if I create the temp_table from within my application, will the select from
the stored procedure still work? Will it still be the same "session" or
whatever as long as I use the same connection? How do I create a temp table
from within my C# application? Unfortunately, I have to get rid of the
associate array because of an Oracle bug.

 

[Scott Roberts]

You can send multiple SQL commands to the server as a single batch by
separating them with ';'.

SqlCommand cmd = new SqlCommand("command1; command2; command3");

So just figure out what series of commands works for you (using SQL
Worksheet) then translate that into code.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

IIRC the temp tables exist in the connection scope, so you have to reuse the
same connection among all the commands.

If this is not possible you can use a psedo temp table, this is a table with
a column that is used as a "session key" for example a GUID, your app
generate the GUID insert all the data you need in this table with all the
rows having the same GUID and finally pass this GUID to the SP that perform
the action, later on you can delete all the rows.