取消SQL Server查询用的CancellationToken [英] Canceling SQL Server query with CancellationToken
问题描述
我在SQL Server中的长时间运行的存储过程，我的用户需要能够取消。我已经写了一个小的测试应用程序如下演示了 SqlCommand.Cancel（）方法效果相当不错：
私人的SqlCommand CMD;
私人无效TestSqlServerCancelSprocExecution（）
{
TaskFactory F =新TaskFactory（）;
f.StartNew（（）=＆GT;
{
使用（SqlConnection的康恩=新的SqlConnection（connStr））
{
conn.InfoMessage + = conn_InfoMessage;
conn.FireInfoMessageEventOnUserErrors = TRUE;
conn.Open（）; CMD = conn.CreateCommand（）;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText =DBO [CancelSprocTest];
cmd.ExecuteNonQuery（）;
}
}）;
} 私人无效cancelButton_Click（对象发件人，EventArgs的发送）
{
如果（CMD！= NULL）
{
cmd.Cancel（）;
}
}
在调用 cmd.Cancel（），我可以验证底层存储过程基本上停止立即执行。鉴于我在我的应用相当大量使用异步/的await模式，我希望在的SqlCommand 称取的CancellationToken 参数也同样有效。不幸的是，我发现，调用取消（）在的CancellationToken 引起 InfoMessage 事件处理程序不再被调用，但底层的存储过程继续执行。我的测试code为异步版本如下：
私人的SqlCommand CMD;
私人CancellationTokenSource CTS;
私人异步无效TestSqlServerCancelSprocExecution（）
{
CTS =新CancellationTokenSource（）;
使用（SqlConnection的康恩=新的SqlConnection（connStr））
{
conn.InfoMessage + = conn_InfoMessage;
conn.FireInfoMessageEventOnUserErrors = TRUE;
conn.Open（）; CMD = conn.CreateCommand（）;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText =DBO [CancelSprocTest];
等待cmd.ExecuteNonQueryAsync（cts.Token）;
}
} 私人无效cancelButton_Click（对象发件人，EventArgs的发送）
{
cts.Cancel（）;
}
我缺少的的CancellationToken 应该如何工作的事情？我在这情况下，重要的是.NET 4.5.1和SQL Server 2012。
编辑：我改写了测试程序作为一个控制台应用程序的情况下，同步方面是一个因素，我看到相同的行为 - 的调用CancellationTokenSource.Cancel（）不会停止底层的存储过程的执行。
编辑：这里是存储过程我打电话，重要案件的机构。它插入记录并打印结果在一秒钟的时间间隔，可以很容易地看到取消尝试是否生效及时。
WHILE（@loop＆LT; = 40）
开始 DECLARE @msg为varchar（80）='迭代'+ CONVERT（VARCHAR（15），@loop）;
RAISERROR（@味精，0,1）WITH NOWAIT;
插入到foo VALUES（@loop）;
WAITFOR DELAY '00：00：01.01'; SET @loop = @环+ 1;
结束;
在看你的存储过程是这样做后，看来，它以某种方式阻止取消。
如果您更改
RAISERROR（@味精，0,1）WITH NOWAIT;
要删除 WITH NOWAIT 子句，则取消按预期工作。然而，这$ P $实时从发射pvents的 InfoMessage 事件。
您可以跟踪长时间运行的进程存储过程的一些其他方式或注册标记取消并调用 cmd.Cancel（），因为你知道的作品。
另外有一点要注意，与.NET 4.5，你可以使用 Task.Run 而不是实例化一个 TaskFactory 。
所以这里有一个有效的解决方案：
私人CancellationTokenSource CTS;
私人异步无效TestSqlServerCancelSprocExecution（）
{
CTS =新CancellationTokenSource（）;
尝试
{
等待Task.Run（（）=＆GT;
{
使用（SqlConnection的康恩=新的SqlConnection（connStr））
{
conn.InfoMessage + = conn_InfoMessage;
conn.FireInfoMessageEventOnUserErrors = TRUE;
conn.Open（）; 变种CMD = conn.CreateCommand（）;
cts.Token.Register（（）=＆GT; cmd.Cancel（））;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText =DBO [CancelSprocTest];
cmd.ExecuteNonQuery（）;
}
}）;
}
赶上（SQLEXCEPTION）
{
//存储过程被取消
}
}私人无效cancelButton_Click（对象发件人，EventArgs的发送）
{
cts.Cancel（）;
}
在我的这个测试，我不得不为包装的ExecuteNonQuery 在工作为 cmd.Cancel（）工作。如果我用 ExecuteNonQueryAsync ，即使没有传递一个令牌，则系统会阻止对 cmd.Cancel（）。我不知道为什么是这样的话，但在任务包的同步方法提供了类似的用法。
I have a long-running stored procedure in SQL Server that my users need to be able to cancel. I have written a small test app as follows that demonstrates that the SqlCommand.Cancel() method works quite nicely:
private SqlCommand cmd;
private void TestSqlServerCancelSprocExecution()
{
TaskFactory f = new TaskFactory();
f.StartNew(() =>
{
using (SqlConnection conn = new SqlConnection("connStr"))
{
conn.InfoMessage += conn_InfoMessage;
conn.FireInfoMessageEventOnUserErrors = true;
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.[CancelSprocTest]";
cmd.ExecuteNonQuery();
}
});
}
private void cancelButton_Click(object sender, EventArgs e)
{
if (cmd != null)
{
cmd.Cancel();
}
}
Upon calling cmd.Cancel(), I can verify that the underlying stored procedure stops executing essentially immediately. Given that I use the async/await pattern quite heavily in my application, I was hoping that the async methods on SqlCommand that take CancellationToken parameters would work equally well. Unfortunately, I found that calling Cancel() on the CancellationToken caused the InfoMessage event handler to no longer be called, but the underlying stored procedure continued to execute. My test code for the async version follows:
private SqlCommand cmd;
private CancellationTokenSource cts;
private async void TestSqlServerCancelSprocExecution()
{
cts = new CancellationTokenSource();
using (SqlConnection conn = new SqlConnection("connStr"))
{
conn.InfoMessage += conn_InfoMessage;
conn.FireInfoMessageEventOnUserErrors = true;
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.[CancelSprocTest]";
await cmd.ExecuteNonQueryAsync(cts.Token);
}
}
private void cancelButton_Click(object sender, EventArgs e)
{
cts.Cancel();
}
Am I missing something in how the CancellationToken is supposed to work? I'm on .NET 4.5.1 and SQL Server 2012 in case it matters.
EDIT: I rewrote the test app as a console app in case the synchronization context was a factor and I see the same behavior -- the invocation of CancellationTokenSource.Cancel() does not stop the execution of the underlying stored procedure.
EDIT: Here's the body of the stored procedure I'm calling in case that matters. It inserts records and prints results at one-second intervals to make it easy to see whether cancellation attempts took effect promptly.
WHILE (@loop <= 40)
BEGIN
DECLARE @msg AS VARCHAR(80) = 'Iteration ' + CONVERT(VARCHAR(15), @loop);
RAISERROR (@msg,0,1) WITH NOWAIT;
INSERT INTO foo VALUES (@loop);
WAITFOR DELAY '00:00:01.01';
SET @loop = @loop+1;
END;
After looking at what your stored procedure is doing, it appears that it is somehow blocking the cancellation.
If you change
RAISERROR (@msg,0,1) WITH NOWAIT;
to remove the WITH NOWAIT clause, then the cancellation works as expected. However, this prevents the InfoMessage events from firing in real time.
You could track progress of the long running stored procedure some other way or register for the token cancellation and call cmd.Cancel() since you know that works.
One other thing to note, with .NET 4.5, you can just use Task.Run instead of instantiating a TaskFactory.
So here's a working solution:
private CancellationTokenSource cts;
private async void TestSqlServerCancelSprocExecution()
{
cts = new CancellationTokenSource();
try
{
await Task.Run(() =>
{
using (SqlConnection conn = new SqlConnection("connStr"))
{
conn.InfoMessage += conn_InfoMessage;
conn.FireInfoMessageEventOnUserErrors = true;
conn.Open();
var cmd = conn.CreateCommand();
cts.Token.Register(() => cmd.Cancel());
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.[CancelSprocTest]";
cmd.ExecuteNonQuery();
}
});
}
catch (SqlException)
{
// sproc was cancelled
}
}
private void cancelButton_Click(object sender, EventArgs e)
{
cts.Cancel();
}
In my testing of this, I had to wrap ExecuteNonQuery in a Task in order for cmd.Cancel() to work. If I used ExecuteNonQueryAsync, even without passing it a token, then the system would block on cmd.Cancel(). I'm not sure why that's the case, but wrapping the synchronous method in a Task provides a similar usage.
这篇关于取消SQL Server查询用的CancellationToken的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
