How to create a temporary table that can be seen by different SqlCommands on the same connection?

The unexpected answer (that I learned the hard way) is: well, it depends on whether you have parameters on your command or not. The point being, if you execute a parameterless SqlCommand, the sql gets executed directly, the same way as if you entered it into the query analyzer. If you add a parameter, the things change in that a call to sp_execsql stored procedure gets inserted in the executed sql. The difference here is the scope: if you create a temporary table from within the sp_execsql, it's scope will be the stored procedure call and it will be dropped once the stored procedure finishes. In that case, you cannot use different commands to access it. If you execute a parameterless command, the temporary table will be connection-scoped and will be left alive for other commands to access. In that case, the other commands can have parameters because their sp_execsql call will be a child scope and will have access to parent scope's temporary table.

As to why they did it this way, I can't say I understand.