Canceling SQL Server query with CancellationToken

Question

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…

Accepted Answer

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.

Canceling SQL Server query with CancellationToken

Solution

Problem