Q:

dapper: #tempDB created but not available in next query

In my program, I want to select some bookIDs into a tempDB for later queries like this (using Dapper extension):
using (var conn = new SqlConnection(connStr)) {
    conn.Execute("SELECT bookID INTO #tempdb WHERE ... FROM Books");
    int count = conn.ExecuteScalar<int>("SELECT COUNT(*) FROM #tempdb");
    var authors = conn.Query("SELECT * FROM #tempdb LEFT JOIN BookAuthors ON ...");
}

However when I execute the page, I get following exception: 

Invalid object name '#tempdb'.

It seems that life-cycle of #tempdb is only valid in first query ?

A:

It looks like you're using the implicit connection opening/closing. This will indeed cause problems with transient objects. If you need temp tables between queries, you will need to manually open the connection before you execute any such queries. This should then work fine, and many examples in the test suite make use of temp tables in the way.
However, from a practical standpoint, making use of temporary tables to transfer state between queries is ... awkward. In addition to being brittle, it isn't good for the plan cache, as #foo has a different meaning between all uses on different connection (including reset but reused connections).

