How to stop a method (triggered by button click) in WPF

Question

I have a private async void Button_Click method in my WPF which runs a very complicated SQL query which can run for several minutes. I wish the user can stop this method by clicking another button. My code is like this:

public partial class MainWindow : Window  
{
  private async void Button_Click(object sender, RoutedEventArgs e)
  {
     string SQL_Query= " a very long and complicated SQL query ... "
     SqlCommand SQL_Query_cmd = new SqlCommand(SQL_Query, conn);
    
     DataTable dt = new DataTable();
     await Task.Run(() => {
     using (SqlDataAdapter a = new SqlDataAdapter(SQL_Query_cmd))
     { a.Fill(dt);}
     });

  }
}

I read about BackgroundWorker in this link How to use WPF Background Worker. But didn't understand how to integrate it into my code. I think, my "filling datatable" code is already asynchronous but I don't know how to stop it. Assume that the button which is going to end this method is called stop_btn and its Click method is called cancelButton_Click.

Please please please write your answer in a post, rather than comments. I will be greatly thankful.

Answer 1

Here is how you could use the IDbCommand.Cancel method and a CancellationTokenSource, to perform cancellation both on the server side and on the client side.

private IDbCommand _activeSqlCommand;
private CancellationTokenSource _cts;

private async void btnExecute_Click(object sender, RoutedEventArgs e)
{
    // The _activeSqlCommand and _cts should be null here.
    // Otherwise, you may end up with multiple concurrent executions.
    Debug.Assert(_activeSqlCommand == null);
    Debug.Assert(_cts == null);
    var sqlQuery = "A very long and complicated SQL query...";
    var localSqlCommand = new SqlCommand(sqlQuery, _connection);
    var localCts = new CancellationTokenSource();
    _activeSqlCommand = localSqlCommand;
    _cts = localCts;
    btnExecute.IsEnabled = false;
    btnCancel.IsEnabled = true;
    try
    {
        DataTable dataTable = await AsCancelable(Task.Run(() =>
        {
            var dt = new DataTable();
            using (SqlDataAdapter a = new SqlDataAdapter(localSqlCommand))
                a.Fill(dt);
            return dt;
        }, localCts.Token), localCts.Token);
        // Here use the dataTable to update the UI
    }
    catch (OperationCanceledException) { } // Ignore
    catch (SqlException ex) when (ex.ErrorCode == CANCEL_ERROR_CODE) { } // Ignore
    finally
    {
        btnCancel.IsEnabled = false;
        btnExecute.IsEnabled = true;
        // The _activeSqlCommand and _cts should still have the local values here.
        Debug.Assert(_activeSqlCommand == localSqlCommand);
        Debug.Assert(_cts == localCts);
        _activeSqlCommand = null;
        _cts = null;
        localCts.Dispose();
    }
}

private void btnCancel_Click(object sender, RoutedEventArgs e)
{
    _activeSqlCommand?.Cancel();
    _cts?.Cancel();
}

private static Task<T> AsCancelable<T>(Task<T> task,
    CancellationToken cancellationToken)
{
    var cancelable = new Task<T>(() => default, cancellationToken);
    return Task.WhenAny(task, cancelable).Unwrap();
}

You'll have to figure out what kind of exception is thrown by the database server when the execution is canceled, and ignore this exception based on its ErrorCode or some other property.