Tuesday, July 29, 2008

Implement SQLCommand Cancel with Threading

Level: Advanced

Knowledge Required:
  • ADO.net
  • Threading
In a data driven application, sometimes we built queries which take time. In these type of scenarios usually developers put the query execution on different thread, so the User Interface keep responsive.

A more user friendly applications provide a flexibility to cancel the currently executing command. This can be achieved by using SQLCommand.Cancel() method.

So to implement it,
  • Create a Thread
  • Execute Query in that Thread
  • Meanwhile if user clicks the Cancel button we will call the SQLCommand.Cancel method
Cancel method actually tries to cancel the in-process query. When the attempt to cancel the query succeeds then an exception is occured at the same point where Command was executed i.e. SQLCommand.ExecuteReader (or other Execute method) was called. When the cancellation is failed then no exception occurs and command continues its execution. Therefore we also need to handle the Exception at the same point where we have executed the query. Here is an example:

Private Sub DoSomeWork()
    objCon = New SqlConnection("Data Source=.;Integrated Security=True")
    objCmd = New SqlCommand("WAITFOR DELAY '00:00:05';", objCon)

    Catch ex As SqlException
    End Try
End Sub

The above method just executes a WAITFOR DELAY query which simulates a very long query that takes 5 seconds to complete. Note that objCon and objCmd are Module Level variables. This method will be executed in a different thread using the following code,

Dim t As Threading.Thread
t = New Threading.Thread(AddressOf DoSomeWork)

While this query is being executed we will perform a query cancel on a button click event as,

Private Sub CancelButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
    If objCmd IsNot Nothing Then
    End If
End Sub

See Also:

Asynchronous Data Loading using TableAdapter with Cancel Feature


Michael said...

Very helpful, thanks. But when you cancel a command the connection state isn't necessarily cleaned up (transactions can be left open in particular). So you really want to clear the connection pool before closing the connection when you catch an exception. That ensures the connection is really closed.
See here and here

Anonymous said...

Is SqlCommand.Cancel thread-safe? According to the MSDN documentation, the instance members of SqlCommand are not thread-safe, doesn't this raise a concern about how you call the Cancel method from a different thread than the object was instantiated on?

Arsalan Tamiz said...

Yes you are right about thread-safety. Actually example is focusing on how we can do cancel. For the simple scenarios, the given example is fine. But it will be more safer if we use the proper way of cross-thread calling.

Code Corner said...

Very nice post! Hopefully you still respond to comments, even though it's been 7 years.

I have this problem: (http://stackoverflow.com/questions/29479543/cancel-long-running-iframes) so my question is - is there a way to implement your approach in an ASP.NET Webform application?


Arsalan Tamiz said...

@Code Comer, thanks for your comments. I am NOT very expert in web application asynchronous programming. However I did create a windows service for my ERP like Web Application, which performs all the heavy duty background processing, and this windows service exposes some API so that my Web Application can query the currently running processes, their progress and also can cancel them on user request. BTW I have posted a comment on your StackOverflow question.