How i can stop sql query execution from .net code?

Question

If you recommend use SqlCommand.Cancel(), please give a working example!

I need to show a wait form with a cancel button(if the user clicks this button, the query must stop running) during query execution.

My solution: (.net 4)

I pass to form constructor two parameters:

1. query execution task
2. cancelation action

Below is code of my loading form:

public partial class LoadingFrm : Form
{
     //task for execute query
     private Task execute;
     //action for cancelling task
     private Action cancel;

     public LoadingFrm(Task e, Action c)
     {
         execute = e;
         cancel = c;
         InitializeComponent();
         this.cancelBtn.Click += this.cancelBtn_Click;
         this.FormBorderStyle = FormBorderStyle.None;
         this.Load += (s, ea) =>
         {
             //start task
             this.execute.Start();
             //close form after execution of task
             this.execute.ContinueWith((t) =>
             {
                 if (this.InvokeRequired)
                 {
                     Invoke((MethodInvoker)this.Close);
                 }
                 else this.Close();
             });
         };
      }

      //event handler of cancel button
      private void cancelBtn_Click(object sender, EventArgs e)
      {
          cancel();
      }
}

Below code of my ExecuteHelper class:

public class ExecuteHelper
{
      public static SqlDataReader Execute(SqlCommand command)
      {
          var cts = new CancellationTokenSource();
          var cToken = cts.Token;
          cToken.Register(() => { command.Cancel(); });
          Task<SqlDataReader> executeQuery = new Task<SqlDataReader>(command.ExecuteReader, cToken);
          //create a form with execute task and action for cancel task if user click on button
          LoadingFrm _lFrm = new LoadingFrm(executeQuery, () => { cts.Cancel(); });
          _lFrm.ShowDialog();
          SqlDataReader r = null;
          try
          {
              //(1) here
              r = executeQuery.Result;
          }
          catch (AggregateException ae)
          {
          }
          catch (Exception ex)
          {
          }

          return r;
      }
}

But I can't stop execution of SqlCommand. After some time method that call ExecuteHelper.Execute(command) get a result (SqlDataReader) from sql server with data? Why? Anybody can help? How i can cancel execution of sqlcommand?

---

And i have another question. Why if i click a cancel button of my form and cts.Cancel() was been called //(1) here i get executeQuery.IsCanceled = false although executeQuery.Status = Faulted.

Answer 1

Instead of calling ExecuteReader call ExecuteReaderAsync and pass in the CancellationToken.

If you are on .net 4.0 you can write your own ExecuteReaderAsync using a TaskCompletionSource. I haven't tested this code, but it should roughly be this:

public static class Extensions
 {
        public static Task<SqlDataReader> ExecuteReaderAsync(this SqlCommand command, CancellationToken token)
        {   
            var tcs = new TaskCompletionSource<SqlDataReader>();

            // whne the token is cancelled, cancel the command
            token.Register( () => 
            {
                command.Cancel();
                tcs.SetCanceled();
            });

            command.BeginExecuteReader( (r) =>
            {
                try
                {
                    tcs.SetResult(command.EndExecuteReader(r));
                }
                catch(Exception ex)
                {
                    tcs.SetException(ex);
                }
            }, null);

            return tcs.Task;
        }
 }

You are using the SqlCommand.Cancel method to cancel any async operation in progress. Then you can use it like this:

 public static SqlDataReader Execute(SqlCommand command)
 {
     SqlDataReader r = null;
     var cts = new CancellationTokenSource();
     var cToken = cts.Token;
     var executeQuery = command.ExecuteReaderAsync(cToken).
                                                    .ContinueWith( t =>
                                                    {
                                                        if(t.IsCompleted)
                                                        {
                                                            r = t.Result;       
                                                        }       
                                                    }, TaskScheduler.Default);

     //create a form with execute task and action for cancel task if user click on button
     LoadingFrm _lFrm = new LoadingFrm(executeQuery, () => { cts.Cancel(); });

     // Assuming this is blocking and that the executeQuery will have finished by then, otheriwse
     // may need to call executeQuery.Wait().
     _lFrm.ShowDialog();                            

     return r;
 }

I modified the Execute method to use ContunueWith rather than r.Result because Result is a blocking property and you'll not show the dialog until the query is complete. As mentioned it is untested, but should be pretty close to what you need.