c# - 用户取消查询时如何回滚SQL CLR存储过程？

Question

我正在尝试创建一个sql clr存储过程，它将创建一个表，将表名传递给一个服务，该服务将大容量插入一些数据，显示表的结果，然后清理表。
到目前为止我所做的：
使用SqlTransaction。取消事务是可行的，但它会将我的查询窗口置于无法继续处理的状态。
此会话中活动的事务已被另一个会话提交或中止
使用TransactionScope。与1相同的问题。
通过发出finallyDROP TABLE来手动清理SqlCommand子句中的表。虽然在发出命令之前我的SqlContext.Pipe.Send()会运行，但这似乎不会运行。它似乎与任何时间限制无关，因为如果在打印另一行之前发出Thread.Sleep(2000)，它仍然会打印第二行，而command.ExecuteNonQuery()会在打印第二行之前停止。
将手动清理代码放入cer或SafeHandle。这不起作用，因为拥有cer需要一些保证，包括不分配额外的内存或调用没有用ReliabilityContract修饰的方法。
我是不是遗漏了一些显而易见的东西？如何处理用户取消查询？
编辑：每个场景都有多个代码迭代，但所采取的一般操作遵循以下行：

    [SqlProcedure]
    public static void GetData(SqlString code)
    {
        Guid guid = Guid.NewGuid();
        using (var connection = new SqlConnection("context connection=true"))
        {
            connection.Open();

            try
            {
                SqlContext.Pipe?.Send("Constrain");

                SqlCommand command1 = new SqlCommand($"CREATE TABLE qb.{code}_{guid:N} (Id INT)", connection);
                command1.ExecuteNonQuery();
                SqlContext.Pipe?.Send($"Create: qb.{code}_{guid:N}");

                //emulate service call
                Thread.Sleep(TimeSpan.FromSeconds(10));

                SqlContext.Pipe?.Send($"Done: qb.{code}_{guid:N}");
            }
            finally
            {
                SqlContext.Pipe?.Send("1");
                //drop table here instead of sleep
                Thread.Sleep(2000);
                SqlContext.Pipe?.Send("2");
            }
        }
    }

Answer 1

不幸的是，sqlclr不能很好地处理查询取消。然而，考虑到错误消息，这似乎意味着取消操作本身会ROLLBACK。您是否尝试不在sqlclr代码中使用事务，而是从外部处理它？例如：
BEGIN TRAN;
EXEC SQLCLR_Stored_Procedure;
IF (@@TRANCOUNT > 0) ROLLBACK TRAN;
上述工作流程需要执行。这可以通过创建一个包装器t-sql存储过程来实现，该存储过程执行这三个步骤，并且只向包装器存储过程授予EXECUTE权限。如果sqlclr存储过程需要权限，则可以使用模块签名很容易地完成：
在与sqlclr存储过程相同的数据库中创建非对称密钥
从该非对称密钥创建用户
GRANT基于键的用户对sqlclr存储过程的权限
使用非对称密钥对包装器t-sql存储过程进行签名
这4个步骤允许t-sql包装程序执行sqlclr过程，而实际的应用程序登录只能执行t-sql包装程序：-）。而且，在执行EXECUTE之前取消操作中止执行的情况下，当连接关闭时，事务应自动回滚。
另外，您是否将ADD SIGNATURE设置为ROLLBACK或XACT_ABORT？更新：o.p.声明设置为ON，设置为OFF的行为似乎没有任何不同。
你试过检查OFF块中的连接状态吗？我很确定取消时ON是finally。您可以在SqlConnection块中尝试以下方法：
测试连接状态，如果Closed，请重新打开finally，然后执行non-query命令。
更新：O.P.声明连接仍处于打开状态。好吧，关上再打开怎么样？
更新2:o.p.已测试并发现无法重新打开连接。
由于上下文仍然可用，如打印命令所证明的，请使用类似Closed
更新：O.P.声明这不起作用。
或者，由于在代码中创建了保证唯一的表名，因此可以尝试将该表创建为全局临时表（即前缀为两个磅符号：SqlConnection），a）可用于批量导入过程，b）在连接完全关闭时清理自身。在这种方法中，技术上不需要执行任何手动清理。
当然，当连接池被启用时，只有在重新打开连接并执行第一个命令后才会进行自动清理。为了强制立即清除，必须在禁用连接池的情况下连接到SQL Server。在执行包含SqlContext.Pipe.ExecuteAndSend(new SqlCommand("DROP TABLE..;"));的存储过程时，是否可以使用不同的连接字符串？考虑到这个存储过程是如何使用的，仅仅在这个特定的调用上禁用连接池似乎不会导致任何明显的性能下降。为了更好地了解连接池（启用或禁用）如何影响临时对象的自动清理，请参阅我刚刚发布的博客文章，其中详细介绍了这种行为：
Sessions, Temporary Objects, and the Afterlife
这种方法可能是最好的整体，因为您可能无法保证##TableName将被执行（提到的第一种方法）或finally子句将被执行（假设您曾经让它起作用）。最后，未提交的事务将被回滚，但是如果有人通过ssms执行此操作，并且它在没有Pooling=false;的情况下中止，那么他们仍然处于打开的事务中，并且可能不知道它。另外，请考虑强制关闭连接、终止会话或关闭/重新启动服务器。在这些情况下，ROLLBACK是您的朋友，无论是使用全局临时表，还是至少在ROLLBACK中创建永久表，以便在下次启动SQL Server时自动将其删除（因为每次启动SQL Server服务时，tempdb都是作为tempdb的副本创建的）。