使用临时表SQL Server不能使用上下文连接SqlDataAdapter.Fill在调用的存储

上帝的女婿

我想为任何存储过程（如当前用户）提供一些信息。根据此处指示的临时表方法，我尝试了以下操作：
1)打开连接时创建临时表
                              private void setConnectionContextInfo(SqlConnection connection)                                                                                                                                                                                                                                                                                                                                                                                                          if (!AllowInsertConnectionContextInfo)             return;          var username = HttpContext.Current?.User?.Identity?.Name ?? "";          var commandBuilder = new StringBuilder($@"CREATE TABLE #ConnectionContextInfo( AttributeName VARCHAR(64) PRIMARY KEY, AttributeValue VARCHAR(1024));INSERT INTO #ConnectionContextInfo VALUES('Username',@Username);");          using (var command = connection.CreateCommand())()))                                                                                        command.Parameters.AddWithValue("Username",username);             command.ExecuteNonQuery()；                            checks if current connection exists / is closed and creates / opens it if necessary    also takes care of the special authentication required by V3 by building a windows impersonation context          public override void EnsureConnection()                                                                                                                                                    try                                                 lock (connectionLock)                                                          if (Connection == null)                                                                Connection = new SqlConnection(ConnectionString);                      Connection.Open()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；setConnectionContextInfo(Connection);                                                                                                    if (Connection.State == ConnectionState.Closed)                                                                Connection.Open()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；setConnectionContextInfo(Connection);                                                                                        catch (Exception ex)                                                 if (Connection != null && Connection.State != ConnectionState.Open)                   Connection.Close()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；throw new ApplicationException("Could not open SQL Server Connection.",ex);          }       }2)用于填充以下函数DataTableusing测试过程SqlDataAdapter.Fill：
            public DataTable GetDataTable(String proc,Dictionary parameters,CommandType commandType)                                                                                                                                                                                                                EnsureConnection()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；using (var command = Connection.CreateCommand())                                                                                                                                                                                                                      if (Transaction != null)             command.Transaction = Transaction;          SqlDataAdapter adapter = new SqlDataAdapter(proc,Connection);          adapter.SelectCommand.CommandTimeout = CommonConstants.DataAccess.DefaultCommandTimeout;          adapter.SelectCommand.CommandType = commandType;          if (Transaction != null)             adapter.SelectCommand.Transaction = Transaction;          ConstructCommandParameters(adapter.SelectCommand,parameters);          DataTable dt = new DataTable()；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；try                                                 adapter.Fill(dt);             return dt;                                  catch (SqlException ex)                                                 var err = String.Format("Error executing stored procedure {0} - {1}.",proc,ex.Message);             throw new TptDataAccessException(err,ex);                3)尝试在调用过程中获取用户名，如下所示:
DECLARE @username VARCHAR(128) = (select AttributeValue FROM #ConnectionContextInfo where AttributeName = 'Username')但#ConnectionContextInfo不再可用于上下文。
我已经为数据库放置了一个SQL检查发生了什么：
使用特定的SPID临时表已成功创建
使用相同的SPID调用过程为什么临时表不能在过程范围内使用？
在T-SQL执行以下工作：
创建临时表
在特定临时表中调用数据的过程
在当前作用域结束后删除临时表谢谢。

解决方案:
                                                            小问题：我暂时假设问题中发布的代码不是正在运行的完整代码。不仅有一些我们看不到声明的变量（例如AllowInsertConnectionContextInfo），而且该setConnectionContextInfo方法中也有明显的遗漏:command但它创造了对象CommandText属性从未设置为commandBuilder.ToString()，所以它似乎是空的SQL批处理。我相信这其实是正确处理的，因为1)我相信提交空批处理会产生异常，2)这个问题确实提到了临时表的创建。SQL
Profiler输出。尽管如此，我还是指出了这一点，因为这意味着可能有其他与问题中未显示的行为相关的代码，这使得更难给出准确的答案。
正如@Vladimir的一个很好的回答中所述，由于查询在子流程（即sp_executesql）在运行过程中，当地临时对象(表和存储过程)无法在子过程完成后幸免于难。用于父亲的上下文。
子流程完成后，全球临时对象和永久/非临时对象将幸存下来，但这两种选项的典型用法都带来了并发问题：在尝试创建表格之前，您需要测试它们是否存在，和
你需要一种方法来区分不同的过程。所以这些都不是真正的好选择，至少在他们的典型用法上也不是这样(稍后会详细介绍)。
假设你不能将任何值传递到存储过程中(否则你可以username按照他的答案@Vladimir有几种选择：
[ol]给出当前代码，最简单的解决方案是创建本地临时表INSERT命令分开(也在)@Vladimir答案中提到)。如前所述，您遇到的问题是由于运行查询sp_executesql。而之所以sp_executesql使用该参数处理该参数@Username。因此，此修复程序可以像将当前代码更改为以下简单：
string _Command = @" CREATE TABLE #ConnectionContextInfo( AttributeName VARCHAR(64) PRIMARY KEY, AttributeValue VARCHAR(1024) );";using (var command = connection.CreateCommand())
{
            command.CommandText = _Command;
            command.ExecuteNonQuery();
}
_Command = @”
                  INSERT INTO #ConnectionContextInfo VALUES (‘Username’,@Username);
“);
using (var command = connection.CreateCommand())
{
            command.CommandText = _Command;
// do not use AddWithValue()!SqlParameter _UserName = new SqlParameter("@Username",SqlDbType.NVarChar,128);_UserName.Value = username;command.Parameters.Add(_UserName);command.ExecuteNonQuery();}
[/ol]请注意，不能T-SQL在用户定义函数或表值函数中访问临时对象数或表值函数。
[ol]使用更好的解决方案(最有可能)CONTEXT_INFO，它本质上是会话内存。这是一个VARBINARY(128)值，但它的变化可以在任何子过程中保留，因为它不是对象。这不仅消除了你目前面临的麻烦，也消除了你目前面临的麻烦tempdb考虑到创建和删除每个操作过程中的临时表并执行它INSERT，而且还减少了I / O ，所有这三个操作都写在磁盘中两次：首先在事务日志中，然后在数据文件中。您可以使用以下方法：
string _Command = @"DECLARE @User VARBINARY(128) = CONVERT(VARBINARY(128),@Username);SET CONTEXT_INFO @User; ";using (var command = connection.CreateCommand())
{
            command.CommandText = _Command;
// do not use AddWithValue()!SqlParameter _UserName = new SqlParameter("@Username",SqlDbType.NVarChar,128);_UserName.Value = username;command.Parameters.Add(_UserName);command.ExecuteNonQuery();}
[/ol]在存储过程/用户定义函数/表值函数/触发器中，您可以通过以下方式获得值：
            DECLARE @Username NVARCHAR(128) = CONVERT(NVARCHAR(128),CONTEXT_INFO());这对单个值很有用，但如果你需要多个值，或者如果你已经把它放在一边CONTEXT_INFO对于其他用途，您需要返回此处介绍的其他方法之一，或者如果使用它SQL
Server 可用于2016(或更高版本)SESSION_CONTEXT，它类似于CONTEXT_INFOHashTable /键值对。
这种方法的另一个好处是CONTEXT_INFO(至少我还没试过(至少我还没试过)SESSION_CONTEXT）在T-SQL可用于用户定义函数和表值函数。
[ol]最后，另一个选择是创建一个全球临时表。如上所述，全球对象具有幸存子流程的优势，但它们也具有复杂并发的缺点。没有缺点的好处是给临时对象一个唯一的基于会话的名称，而不是添加一列来保存唯一基于会话的值。使用会话的唯一名称可以消除所有并发问题，并允许您在关闭连接后自动清除一个对象（因此，无需担心在完成前创建全球临时表），并使用永久表，或至少在开始时检查）。[/ol]请记住，我们不能将任何值传递给存储过程的限制。我们需要使用数据层存在的值。要使用的值是session_id/
SPID。当然，该值在应用程序层中并不存在，因此必须重新获得，但在这个方向上没有限制。
            int _SessionId;using (var command = connection.CreateCommand(){    command.CommandText = @"SET @SessionID = @@SPID;"; SqlParameter _paramSessionID = new SqlParameter("@SessionID",SqlDbType.Int); _paramSessionID.Direction = ParameterDirection.Output; command.Parameters.Add(_UserName); command.ExecuteNonQuery()；    _SessionId = (int)_paramSessionID.Value;}string _Command = String.Format(@"  CREATE TABLE ##ConnectionContextInfo_{0}(    AttributeName VARCHAR(64) PRIMARY KEY,             AttributeValue VARCHAR  INSERT INTO ##ConnectionContextInfo_{0} VALUES('Username',@Username);",_SessionId);using (var command = connection.CreateCommand(){    command.CommandText = _Command; SqlParameter _UserName = new SqlParameter("@Username",SqlDbType.NVarChar,128)； _UserName.Value = username; command.Parameters.Add(_UserName); command.ExecuteNonQuery();}然后，您可以通过以下方式在存储过程/触发器中获得值：
            DECLARE @Username NVARCHAR(128)       @UsernameQuery NVARCHAR(4000);SET @UsernameQuery = CONCAT(N'SELECT @tmpUserName = [AttributeValue]    FROM ##ConnectionContextInfo_',@@SPID,N' WHERE [AttributeName] = ''UsernameEXEC sp_executesql  @UsernameQuery, N'@tmpUserName NVARCHAR(128) OUTPUT', @Username OUTPUT;请注意，不能T-SQL在用户定义函数或表值函数中访问临时对象数或表值函数。
[ol]最后，您可以使用真实/永久（即非临时）表，如果您包括一列，以保存当前会话中特定的值。此附加列将允许并发正常工作。[/ol]您可以在中间创建表格tempdb(是的，你可以把它拿走tempdb用作常规数据库而不仅仅是#或开头的临时对象##）。使用的优势tempdb这张桌子不影响所有其他内容(毕竟只是临时值，不需要还原，所以tempdb使用SIMPLE恢复模型是完美的选择)，并在例子中重新启动（FYI：tempdb在modelSQL
Server作为副本创建每次启动)。
就像上面的选项3一样，我们可以再次使用它session_id/
SPID因为这是值Connection所有操作都是通用的(只要Connection保持打开状态）。但是，与选项＃3不同的应用程序代码不需要SPID值:可以用默认约束自动插入每一行。这稍微简化了操作。
这里的概念是首先检查永久表是否tempdb存在。如果是这样，请确保目前SPID手表中没有条目。如果没有，创建手表。因为它是一个永久的手表，即使在当前的过程中关闭它的连接，它也会继续存在。最后，插入@Username参数，SPID自动填充值。
            // assume _Connection is already openusing (SqlCommand _Command = _Connection.CreateCommand(){    _Command.CommandText = @"    IF (OBJECT_ID(N'tempdb.dbo.Usernames') IS NOT NULL)    BEGIN       IF (EXISTS(SELECT *                   FROM [tempdb].[dbo].[Usernames]                   WHERE  [SessionID] = @@SPID          BEGIN          DELETE FROM [tempdb].[dbo].[Usernames]          WHERE  [SessionID] = @@SPID;       END;    END;    ELSE    BEGIN       CREATE TABLE [tempdb].[dbo].[Usernames]       [SessionID]  INT NOT NULL                         CONSTRAINT [PK_Usernames] PRIMARY KEY                         CONSTRAINT [DF_Usernames_SessionID] DEFAULT (@@SPID),                                              [Username] NVARCHAR(128) NULL,          [InsertTime] DATETIME NOT NULL                         CONSTRAINT [DF_Usernames_InsertTime] DEFAULT (GETDATE()       )END;    INSERT INTO [tempdb].[dbo].[Usernames] ([Username]) VALUES (@UserName);          "; SqlParameter _UserName = new SqlParameter("@Username",SqlDbType.NVarChar,128)； _UserName.Value = username; command.Parameters.Add(_UserName); _Command.ExecuteNonQuery();}在存储过程/用户定义函数/表值函数/触发器中，您可以通过以下方式获得值：
            SELECT [Username]FROM [tempdb].[dbo].[Usernames]WHERE  [SessionID] = @@SPID;这种方法的另一个优点是可以T-SQL访问用户定义函数和表值函数中的永久表。

使用临时表SQL Server不能使用上下文连接SqlDataAdapter.Fill在调用的存储

上帝的女婿 LV1