SQLException when Canceling Async Query

Question

Using the new .Net 4.5 Async/Await functionality with a CancellationToken, I get a SQLException when I cancel a ExecuteNonQueryAsync call, instead of a OperationCanceledException (or some other exception that is specific to canceling an operation). The SQLException does say Operation cancelled by user at the end of the message. I expected a more specific exception to be thrown when canceling the operation. Additionally, how would I create the appropriate Try/Catch handlers to deal with this expected scenario? I normally would have the SQLException as more general failure block, but now I would have to tease out the text of the message to see if this just the user clicking the Cancel button!? I must be missing something.

Here's a simple VB WinForm app that has two buttons, one to do the Async call, and the other to cancel. The Try/Catch in the 1st button shows the SQLException that is hit when the 2nd button calls the Cancel method.

Dim _cts As CancellationTokenSource
Private Async Sub btnLocalTest_Click(sender As Object, e As EventArgs) Handles btnLocalTest.Click
    _cts = New CancellationTokenSource()
    Dim CancelToken As CancellationToken = _cts.Token
    Using sconn As New SqlConnection("server=(local);database=MyDB;user id=MyUser;password=MyPassword")
        sconn.Open()
        Dim SQL As String = some long running SELECT or INSERT statement
        Try
            Using scmd As New SqlCommand(SQL, sconn)
                scmd.CommandTimeout = 300
                Dim i As Integer = Await scmd.ExecuteNonQueryAsync(CancelToken)
            End Using
        Catch exCancel As OperationCanceledException
            LogEvent("Query canceled Exception.") ' This error is *not* thrown on Cancel.  
        Catch ex As SqlClient.SqlException
            LogEvent("Error with query. " & ex.Message)  ' This error *is* thrown on Cancel.  Message includes text 'Canceled by user.'
        End Try
        sconn.Close()
        _cts = Nothing
    End Using
End Sub

Private Sub btnLocalTestCancel_Click(sender As Object, e As EventArgs) Handles btnLocalTestCancel.Click
    If _cts IsNot Nothing Then
        _cts.Cancel()
    End If
End Sub

UPDATE: I created a different Async test with the HttpClient.GetAsync method that supports cancellation. When you cancel that task, you can the OperationCanceledException exception that I originally expected above. So the question remains: what exception should you get when you cancel an Async task? Or is that dependent upon each method and it's implementation?

Answer 1

It is not always as simple as catching an SqlException. If you use Task.Wait() on the asynchronous task then the SqlException will be wrapped inside an AggregateException.

A code example that demonstrates this can be found in the ADO.NET section on MSDN, in the article Asynchronous Programming (section "Cancelling an Asynchronous Operation").

Documentation for a similar behaviour that is general for the Task class is buried in the MSDN article Task Cancellation (part of the "Task Parallel Library" documentation), although here the AggregateException wraps a TaskCanceledException (which is derived from OperationCanceledException).

Here is a somewhat simplified piece of C# code that shows how I am currently handling cancellation requests with SqlClient:

class MyDataProcessor
{
    void ReadSomething(CancellationToken cancellationToken)
    {
        try
        {
            // Get the command from somewhere
            DbCommand dbCommand = [...]

            // We don't use await, we manage the Task ourselves
            Task<DbDataReader> task = dbCommand.ExecuteReaderAsync(cancellationToken)
            // If cancellation is requested this throws an AggregateException
            task.Wait();

            // Task status should probably be checked here,
            // but this is just sample code
            DbDataReader dbDataReader = task.Result;

            // If cancellation is requested, this throws a straight SqlException
            while (dbDataReader.Read())
            {
                // do something with the data

                // Be nice and check the token. ADO.NET data providers
                // other than SqlClient might not check the token.
                cancellationToken.ThrowIfCancellationRequested();
            }
        }
        catch (System.Exception exception)
        {
            // If it's a cancellation request, transform the SqlException
            // into an OperationCanceledException
            ThrowIfSqlClientCancellationRequested(
                cancellationToken, exception);

            // Re-throw if it's a genuine error
            throw;
        }
    }

    void ThrowIfSqlClientCancellationRequested(
        CancellationToken cancellationToken,
        Exception exception)
    {
        // Check the CancellationToken, as suggested by Anton S in his answer
        if (!cancellationToken.IsCancellationRequested)
            return;
        System.Data.SqlClient.SqlException sqlException =
            exception as System.Data.SqlClient.SqlException;
        if (null == sqlException)
        {
            AggregateException aggregateException = exception as AggregateException;
            if (null != aggregateException)
                sqlException = aggregateException.InnerException as System.Data.SqlClient.SqlException;
            if (null == sqlException)
                return;
        }
        // Assume that if it's a "real" problem (e.g. the query is malformed),
        // then this will be a number != 0, typically from the "sysmessages"
        // system table 
        if (sqlException.Number != 0)
            return;
        throw new OperationCanceledException();
    }
}

I am not very happy with this, it looks brittle, but lacking any official documentation it's the best I could come up with at the moment. The big questions are:

• Will future versions of SqlClient change their cancellation behaviour?
• Are there additional behaviours not covered by the code above?

Answer 2

I "solved" this issue by checking CancelToken.IsCancellationRequested in the Catch ex As SqlClient.SqlException block.