title: Using dapper, why is a temp table created in one use of a connection not available in a second use of the same connection
body: <p>I'm trying to perform a series of SQL*Server steps using dapper from C#.  One step creates a temp table and populates it.  Following steps query data from the temp table.  The create/populate seems to run successfully, but the first query from the temp table fails saying:</p>
  
  <blockquote>
    <p>"Invalid object name '#GetPageOfGlobalUsers'."</p>
  </blockquote>
  
  <pre><code>        using (SqlConnection connection = DBConnectionProvider.CreateConnection())
          {
              ... misc setup stuff...
  
              connection.Execute(@"
                  create table #GetPageOfGlobalUsers(row int, EmailAddress nvarchar(max), LastName nvarchar(max), FirstName nvarchar(max), Id uniqueidentifier)
                  insert into #GetPageOfGlobalUsers
                  SELECT ROW_NUMBER() OVER (order by LastName, FirstName, EmailAddress) row,
                      EmailAddress, LastName, FirstName, Id 
                      FROM Users 
                      WHERE LastName like @search or FirstName like @search or EmailAddress like @search
              ", new { search = search }
              );
  
              int count = connection.Query&lt;int&gt;(@"
                  SELECT count(*) from tempdb..#GetPageOfGlobalUsers
              ").Single&lt;int&gt;();
  
  ... more queries from the temp table follow
  </code></pre>
  
  <p>Above, the Execute works, but the Query fails with the error I mentioned above.  (Note that I get the same error whether or not I use the "tempdb.." prefix.)  If I create a permanent table instead (i.e. if I remove the leading hash) or if I make it a global temp table (i.e. prefix the name with two hashes) everything works fine.</p>
  
  <p>My understanding is that temp tables named with a single hash are scoped by the duration of the connection, so I don't know what's going on.  But I'm sure someone can tell me!</p>
  
  <p>(BTW, I would appreciate it if no one tells me "don't do it this way" unless it simply can't be done.)</p>