dapper: #tempDB created but not available in next query

Question

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 ?

@2kay I added conn.Open() explicitly before 1st query, and it still throws "Invalid object name #tempdb" exception — ineztia, Jun 09 '16 at 10:20
Temporary tables `#tmp` exist only in the batch they were created. You can combine all three queries and run `ExecuteReader` which will contain 2 result sets. — Alex Kudryashev, Jun 09 '16 at 18:12

score 3 · Answer 1 · answered Jun 09 '16 at 17:58

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).

Thanks for the advice. I added conn.Open() but still get failure on #tempdb reference. Does `Dapper` handles connection state automatically no matter I open it or not? — ineztia, Jun 10 '16 at 02:41

score 0 · Answer 2 · edited May 23 '17 at 12:31

0

I found a previous poster who met the same problem and his solution.

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

The post indicates that you have to "CREATE TABLE #tempdb" explicitly in your SQL first and everything goes fine. Even the poster himself don't know why such style of coding works.

edited May 23 '17 at 12:31

Community

1
1

answered Jun 10 '16 at 05:20

ineztia

815
1
13
29

dapper: #tempDB created but not available in next query

2 Answers2