Better cancellation support for ADO.NET

[vsfeedback]

This issue has been moved from a ticket on Developer Community.

---

Hello,

I'm using asynchronous ADO.NET operations to make some database calls. In our code we need to distinguish between retryable errors (such as deadlocks) and non-retryable ones.

A CancellationToken is passed along, but whenever this token is triggered, a SqlException is thrown. This SQL exception has SQL error number 0 and it doesn't contain an inner exception. That means I have no means of detecting whether the operation was canceled by investigating the exception. I cannot use the error code, because 0 is too generic. I cannot find an OperationCanceledException nested within. And finally the error message cannot be used either, because it could be different depending on operating system culture. So basically the SqlException doesn't tell me what's going on exactly.

I would expect an OperationCanceledException to be thrown instead. Or an OperationCanceledException nested within a SqlException, Or perhaps even an AggregateException with an OperationCanceledException and a SqlException nested within. In any way, I would expect an OperationCanceledException (or derived TaskCanceledException) at least somewhere.

I already found a way to work around this myself. It's possible to check the cancellation token within the SqlException handling block. But this just doesn't feel right. It seems to me that ADO.NET should throw an OperationCanceledException instead of wrapping the cancellation in a SqlException, which essentially hides the actual cancellation. Which is very misleading. It took me a long time to figure out what was really wrong because I was focussed on SQL error number 0 situations.

The MSDN documentation clearly statest that ThrowCancellationRequested should be used, in order to throw an OperationCanceledException:
https://docs.microsoft.com/en-us/dotnet/standard/parallel-programming/task-cancellation
And it seems to me that ADO.NET should do the same.

I'd really like to hear your thoughts about this.
Regards,
Stefan Adriaenssen

Here's some code to simulate the error:

static void Main(string[] args)
{
    var ct = new CancellationTokenSource();
    var task = Run(ct. Token);
    ct. CancelAfter(1000);
    task. Wait();
    Console.ReadLine();
}
static async Task Run(CancellationToken token)

{

using (var connection = new SqlConnection(@"server=localhost;database=tempdb;Integrated Security=true"))

{

connection. Open();
using (var cmd = new SqlCommand("waitfor delay '00:05:00'", connection))

{

try

{

await cmd.ExecuteNonQueryAsync(token);

}

catch(AggregateException)

{

Console.WriteLine("AggregateException caught");

}

catch (OperationCanceledException)

{

Console.WriteLine("OperationCanceledException caught");

}

catch(SqlException ex)

{

Console.WriteLine("SqlException caught");

Console.WriteLine();

Console.WriteLine($"Error Number: {ex. Number}");

Console.WriteLine($"Error Code: {ex. ErrorCode}");

Console.WriteLine($"Message: {ex. Message}");

Console.WriteLine($"Inner exception: {(ex. InnerException?. ToString() ?? "<NULL>")}");

Console.WriteLine();

// Only the token's IsCancellationRequested can be used, as a workaround. (An OperationCanceledException could be thrown from here)

Console.WriteLine($"Cancellation requested by token: {token. IsCancellationRequested}");

}

}

}

}

---

Original Comments

Feedback Bot on 1/4/2021, 10:24 PM:

Thank you for taking the time to provide your suggestion. We will do some preliminary checks to make sure we can proceed further. We'll provide an update once the issue has been triaged by the product team.

[Dotnet-GitSync-Bot]

I couldn't figure out the best area label to add to this issue. If you have write-permissions please help me learn by adding exactly one area label.

[ajcvickers]

/cc @roji

[roji]

Duplicate of dotnet/SqlClient#26

[roji]

This question seems specific to the SqlClient provider, and so belongs in https://github.com/dotnet/SqlClient. dotnet/SqlClient#26 already tracks having SqlClient throw OperationCancelledException instead of SqlException, and also contains some help on how to identify cancellation from the current SqlException. If there are any difficulties identifying cancellation, it's recommended to open a new issue on the SqlClient repo.