C# : Cancelling MySqlCommand using CancellationToken giving NULLReferenceException

Question

I was trying to cancel a MySqlCommand using a CancellationToken. The query executes successfully when cancellation is not requested.

public async Task<int> ExecuteNonQueryAsync(string connectionString, string query, 
       CancellationToken cancellationToken)
{
    int affectedRowsCount = 0;
    await Task.Run(() =>
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            using (MySqlCommand command = new MySqlCommand())
            {
                connection.Open();
                command.Connection = connection;
                cancellationToken.Register(() => command.Cancel());

                command.CommandText = query;
                command.CommandTimeout = 0;

                affectedRowsCount = command.ExecuteNonQuery();
                connection.Close();
             }
         }
     });

     return affectedRowsCount;
}

But when cancellation is requested it is producing NullReferenceException. Can't figure out what is NULL.

I am calling the above method by

deletedRowsInLastIteration = await 
    mySqlHelperService.ExecuteNonQueryAsync(
       connectionString,
       query, 
       cancellationToken);

if I try

cancellationToken.ThrowIfCancellationRequested();

before calling the ExecuteNonQueryAsync() method, it works. But the cancel of MySqlCommand is not working.

This is the stack trace

System.NullReferenceException HResult=0x80004003 Message=Object reference not set to an instance of an object. Source=MySql.Data
StackTrace: at MySql.Data.MySqlClient.MySqlConnection.CancelQuery(Int32 timeout)
at MySql.Data.MySqlClient.MySqlCommand.Cancel() at ProjectName.Common.MySqlHelperService.<>c__DisplayClass1_1.b__1() in C:\Users\username\source\repos\ProjectName\Applications\ProjectName.Common\MySqlHelperService.cs:line 55 at System.Threading.CancellationToken.ActionToActionObjShunt(Object obj) at System.Threading.CancellationCallbackInfo.ExecutionContextCallback(Object obj) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.CancellationCallbackInfo.ExecuteCallback() at System.Threading.CancellationTokenSource.CancellationCallbackCoreWork(CancellationCallbackCoreWorkArguments args) at System.Threading.CancellationTokenSource.ExecuteCallbackHandlers(Boolean throwOnFirstException)

Answer 1

You shouldn't use Task.Run to convert synchronous methods to asynchronous ones. At best, this wastes a thread just waiting for some IO operation to complete.

MySqlCommand has an ExecuteNonQueryAsync method that accepts a cancellation token. MySqlConnection itself has an OpenAsync method. You should change your code to :

public async Task<int> ExecuteNonQueryAsync(string connectionString, string query, 
       CancellationToken cancellationToken)
{
    using (MySqlConnection connection = new MySqlConnection(connectionString))
    {
        using (MySqlCommand command = new MySqlCommand(query,connection))
        {
            await connection.OpenAsync();
            command.CommandTimeout = 0;

            var affectedRowsCount = await command.ExecuteNonQuery(cancellationToken);
         }
    }

    return affectedRowsCount;
}

Answer 2

How are you creating your cancellation Token and what is his value?

Also here is a solution how to cancel a sql command with a cancellation token

private CancellationTokenSource cts;
private async void TestSqlServerCancelSprocExecution()
{
cts = new CancellationTokenSource();
try
{
    await Task.Run(() =>
    {
        using (SqlConnection conn = new SqlConnection("connStr"))
        {
            conn.InfoMessage += conn_InfoMessage;
            conn.FireInfoMessageEventOnUserErrors = true;
            conn.Open();

            var cmd = conn.CreateCommand();
            cts.Token.Register(() => cmd.Cancel());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "dbo.[CancelSprocTest]";
            cmd.ExecuteNonQuery();
        }
   });
}
catch (SqlException)
{
    // sproc was cancelled
}

}

The code above is from this question, which had kinda the same problem, that the cancellation token won't cancel the sql command.