1

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.

Improve this question
3
  • @Liath, Because the link does not help me in resolving my issue Commented Jul 2, 2014 at 10:22
  • @Liath, you can give working code, that stop executing query by Cancel method of SqlCommand? Commented Jul 2, 2014 at 10:23
  • I tried this solution, unsuccessfully. Commented Jul 2, 2014 at 10:27

1 Answer 1

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

.net 4 has not ExecuteReaderAsync
only BeginExecuteReader and EndExecuteReader
You can make one yourself using Task.FromAsync. I'll post some code.
I've updated my answer hope it helps.
try changing the type of the executeQuery variable to var
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.