C# How is the CancellationToken used with SqlConnection.OpenAsync(token)?

Question

I'm attempting to use the CancellationToken with SqlConnection.OpenAsync() to limit the amount of time that the OpenAsync function takes.

I create a new CancellationToken and set it to cancel after say 200 milliseconds. I then pass it to OpenAsync(token). However this function still can take a few seconds to run.

Looking at the documentation I cant really see what I'm doing wrong. https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.openasync?view=netframework-4.7.2

Here is the code I'm using:

    private async void btnTestConnection_Click(object sender, EventArgs e)
    {
        SqlConnection connection = new SqlConnection(SQLConnectionString);
        Task.Run(() => QuickConnectionTest(connection)).Wait();
    }

    public async Task QuickConnectionTest(SqlConnection connection)
    {
        CancellationTokenSource source = new CancellationTokenSource();
        CancellationToken token = source.Token;
        source.CancelAfter(200);

        ConnectionOK = false;

        try
        {
            using (connection)
            {
                await connection.OpenAsync(token);

                if (connection.State == System.Data.ConnectionState.Open)
                {
                    ConnectionOK = true;
                }

            }
        }
        catch (Exception ex)
        {
            ErrorMessage = ex.ToString();
        }
    }

I was expecting OpenAsync() to end early when the CancellationToken to throw a OperationCanceledException when 200ms had passed but it just waits.

To replicate this I do the following:

Run the code: Result = Connection OK

Stop the SQL Service

Run the code: hangs for the length of connection.Timeout

user_122718 · Answer

Your code seems correct to me. If this does not perform cancellation as expected then SqlConnection.OpenAsync does not support reliable cancellation. Cancellation is cooperative. If it's not properly supported, or if there is a bug, then you have no guarantees.

Try upgrading to the latest .NET Framework version. You can also try .NET Core which seems to lead the .NET Framework. I follow the GitHub repositories and have seen multiple ADO.NET bugs related to async.

You can try calling SqlConnection.Dispose() after the timeout passes. Maybe that works. You also can try using the synchronous API (Open). Maybe that uses a different code path. I believe it does not, but it is worth a try.

In case that does not work either you need to write your code so that your logic proceeds even if the connection task has not completed. Pretend that it has completed and let it linger in the background until it completes by itself. This can cause increased resource usage but it might be fine.

In any case consider opening an issue in the GitHub corefx repository with a minimal repro (like 5 lines connecting to example.com). This should just work.

user_3930378 · Answer

The CancelationToken works the same for everyone else that it does for you in your own code.
Example:

class Program
{
    static async Task Main(string[] args)
    {
        Console.WriteLine("main");
        var cts = new CancellationTokenSource();
        var task = SomethingAsync(cts.Token);
        cts.Cancel();
        await task;
        Console.WriteLine("Complete");
        Console.ReadKey();
    }

    static async Task SomethingAsync(CancellationToken token)
    {
        Console.WriteLine("Started");
        while (!token.IsCancellationRequested)
        {
            await Task.Delay(2000); //didn't pass token here because we want to simulate some work.
        }
        Console.WriteLine("Canceled");
    }
}

//**Outputs:**
//main
//Started
//… then ~2 seconds later <- this isn't output
//Canceled
//Complete

The OpenAsync method may require some optimization but don't expect a call to Cancel() to immediately cancel any Task. It's just a marshalled flag to let the unit of work within the Task know that the caller wants to cancel it. The work inside the Task gets to choose how and when to cancel. If it's busy when you set that flag then you just have to wait and trust the Task is doing what it needs to do to wrap things up.

C# How is the CancellationToken used with SqlConnection.OpenAsync(token)?

2 Answers