Stop SQL Query Execution from .Net Code

Stop SQL query execution from .net Code

Yes sqlcommand.cancel is your friend http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.cancel.aspx

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

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.

C# Cancel a list of tasks which are executing long running sql queries

You need to use CancellationToken inside of functions passed as Action instances to EnqueueTask method.

E.g. the following code shows how CancellationToken can be used for terminating execution of SQL commands:

    using (SqlConnection conn = new SqlConnection(sqlConnection))
    {
        conn.Open();
        var cmd = conn.CreateCommand();

        using (cancellationToken.Register(() => cmd.Cancel()))
        {
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }
    }

---

Related Topics