0
\$\begingroup\$

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 () =>
        {
            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+<Enter> 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 < @end
)
Select * From NumberSequence Option (MaxRecursion 0)
";
}
\$\endgroup\$
  • 2
    \$\begingroup\$ Hello Chris, could you give us more details regarding your code? \$\endgroup\$ – IEatBagels Nov 26 '19 at 19:21
0
\$\begingroup\$

1) You can listen for requests for your application to close without making your own loop, and without having to push the logic of your application into another task

var cts = new CancellationTokenSource();
AppDomain.CurrentDomain.ProcessExit += (sender, eventArgs) => cts.Cancel();
Console.CancelKeyPress += (sender, eventArgs) => { cts.Cancel(); eventArgs.Cancel = true; };

So you don't need Task.Run anymore. This is also a better, more consistent check for closing that is better implemented than anything you can hand roll in a main method.

(fyi. Unless you choose to leak these (and know what that means) you'll need to dispose cts and unregistered the handlers)

2) I almost certain passing a cancellation token to ExecuteReaderAsync is sufficient to cancel the query. You can verify this, but I think that cmd.Cancel for for people using ExecuteReader (non-async), which doesn't have a cancellationtoken.

3) Some other comments. It looks like you're not actually reading anything, is this code incomplete? A simple while loop is also probably not a great event loop (eg a delay would be good) but you don't need to worry about that anyway.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.