使用dapper，为什么在一次使用连接时创建的临时表在第二次使用同一连接时不可用 [英] Using dapper, why is a temp table created in one use of a connection not available in a second use of the same connection

问题描述

我正在尝试使用C＃中的dapper执行一系列SQL * Server步骤。第一步将创建一个临时表并将其填充。以下步骤从临时表中查询数据。创建/填充似乎运行成功，但是临时表中的第一个查询失败：

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:

无效的对象名称'#GetPageOfGlobalUsers' 。

"Invalid object name '#GetPageOfGlobalUsers'."

        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<int>(@"
                SELECT count(*) from tempdb..#GetPageOfGlobalUsers
            ").Single<int>();

... more queries from the temp table follow

执行工作，但查询失败，出现我上面提到的错误。 （请注意，无论是否使用 tempdb ..前缀，我都会遇到相同的错误。）如果我改为创建一个永久表（即，如果删除前导的哈希），或者将其设为全局临时表（即，前缀）

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.

我的理解是，用单个哈希命名的临时表受连接持续时间的限制，所以我不知道发生了什么事。但是我敢肯定有人会告诉我！

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!

（顺便说一句，如果没有人告诉我不要那样做，除非能做到，否则我将不胜感激。

(BTW, I would appreciate it if no one tells me "don't do it this way" unless it simply can't be done.)

推荐答案

我不完全了解发生了什么，但是我可以解决此问题通过在自己的Execute中创建临时表，而不是在既创建表又填充表的Execute中创建临时表，如我的问题所示的代码。

I don't understand exactly what's going on, but I am able to work around the problem by creating the temp table in an Execute of its own, as opposed to in an Execute that both creates the table and populates it, as in the code shown in my question.

即完成以下工作：

            connection.Execute(@"
                create table #PagesOfUsers(row int, 
                                           EmailAddress nvarchar(max), 
                                           LastName nvarchar(max), 
                                           FirstName nvarchar(max), 
                                           Id uniqueidentifier)"
                );

            connection.Execute(@"
                insert into #PagesOfUsers
                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<int>(@"
                SELECT count(*) from #PagesOfUsers
            ").Single<int>();

这并不可怕，但是不方便。值得注意的是，我完全不必明确地创建临时表。确实，我最初将创建/填充操作编码为SELECT INTO，因此不必逐项列出临时表的列。但这在随后的查询中也遇到了无效对象错误，因此我尝试使用显式的CREATE TABLE来查看它是否有所作为，并在发现问题后在此处张贴了我的问题。

This isn't horrible, but it is inconvenient. It's worth noting that I'd rather not have to explicitly create the temp table at all. Indeed, I'd originally coded the create/populate operation as a SELECT INTO so I didn't have to itemize the temp table's columns. But that also ran into the "invalid object" error on the subsequent query, so I tried the explicit CREATE TABLE to see if it made a difference and posted my question here after finding that it didn't.

我看到的行为是，当在同一Execute中创建并填充临时表时，表面看来成功执行后，它实际上不在tempdb中。这让我想知道原始代码中的Execute是否在做任何事情！据我所知，这相当于没有操作。

The behavior I'm seeing is that when the temp table is created and populated in the same Execute, it really isn't in tempdb after the Execute ends, ostensibly successfully. That leaves me to wonder if the Execute in my original code was doing anything at all! For all I can tell, it amounted to a NOOP.

这篇关于使用dapper，为什么在一次使用连接时创建的临时表在第二次使用同一连接时不可用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！