Cancel Long Running Query using Cancellation Token AND SqlCommand.Cancel() I need to be able to cancel a long running query programmatically through our application.  The code below will kick off a long running query and give control back to the main thread.  At any arbitrary time, a user could choose to cancel the query.  It both cancels the Task as well as cancels the query in SQL Server. I can check the status of the query in SQL Server with select * from sys.query_store_runtime_stats to verify that the query was in fact aborted.  This is important as I need to make sure it's not just canceled in the app but in the database as well.

The code below does this, but I'm hung up on the line where I Register the cmd.Cancel method with the cancellationToken, cancellationToken.Register(cmd.Cancel); Is there a potential issue since I'm referencing a variable created inside the scope of the Task.Run from the Main method(outside the Task.Run scope)?

Also, I'm including the cmd.Cancel bit so that the SQL query actually gets cancelled and not just the task that kicked it off.  Is there a better way to ensure that the long running SQL query currently being executed in the database is aborted? (again, the code below does this, just wondering if there is a better solution for cancelling a SQL query from the c# code that initiated the SQL query)

I'm using .NET Core 3.1 preview 3 and SQL Server 2017 with the code posted below.

using Microsoft.Data.SqlClient;
using System;
using System.Threading;
using System.Threading.Tasks;

public class Program
{
    public static void Main(string[] args)
    {
        var tokenSource = new CancellationTokenSource();
        var cancellationToken = tokenSource.Token;

        Task.Run(async () =&gt;
        {
            await using var cn = new SqlConnection(CONNECTION_STRING);
            await using var cmd = new SqlCommand(LONG_RUNNING_SQL, cn);
            cancellationToken.Register(cmd.Cancel); // is this bad??
            await cn.OpenAsync(cancellationToken);
            await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
            while (await reader.ReadAsync())
            {
            }
        }, cancellationToken);

        Console.WriteLine("Press z+&lt;Enter&gt; to Stop");
        while (true)
        {
            if (Console.Read() == 'z')
            {
                tokenSource.Cancel();
                break;
            }

        }
    }

    private const string CONNECTION_STRING = "server=.;database=scratch;trusted_connection=true;";
    private const string LONG_RUNNING_SQL = @"
Declare @start bigint, @end bigint
Select @start=1, @end=999999
;With NumberSequence( Number ) as
(
    Select @start as Number
        union all
    Select Number + 1
        from NumberSequence
        where Number &lt; @end
)
Select * From NumberSequence Option (MaxRecursion 0)
";
}
It both cancels the Task as well as cancels the query in SQL Server. I can check the status of the query in SQL Server with select * from sys.query_store_runtime_stats to verify that the query was in fact aborted. This is important as I need to make sure it's not just canceled in the app but in the database as well.

Baaaack up a few steps. Your strategy to rely on proof of query completion by looking in a secondary table seems strange to me. If your long-running query were an update, insert etc., you would want to consider

Running your query in a transaction that does not have any kind of auto-commit enabled
Only committing at the end, once you're sure that there will be no desire for cancellation
If cancellation occurs before the commit, then you can be sure that the query was cancelled with no effect on the database

But that's not required, because your long-running query is only a select. Is there a specific reason that you don't trust Cancel to do what it's designed to do? Doing the query_store_runtime_stats check manually as a part of development testing is fine, as long as you aren't baking it in programmatically.