Detecting command cancel in asynchronous BeginExecuteReader

Question

I am having an asynchronous call to beingexecutereader method of sql command, that a user can cancel. When cancel happens, i cancel the sqlcommand object, and this would kill the executing job on sql server.

However as soon as i cancel it, the callback method specified in the BeginExecute query is called, and it errors out when trying to call endexecutequery. The result.Iscompleted is true, until the endexecute query is called, at that point it becomes false.

Is there any way to detect in the callback method that the command is canceled ? or do i have to keep track of that...

thanks

Answer 1

I've erased and restarted my whole answer: you've stumbled on a good case of contradictory documentation and warnings...

According to the .NET 2.0 MSDN Documentation for the SqlCommand.Cancel "The Cancel method cannot be used to cancel a pending asynchronous operation." However, the very same documentation for .NET 3.5 does not contain this warning--despite being otherwise identical to the 2.0 documentation.

I would advise following the conventions outlined in Calling Synchronous Methods Asynchronously which is targeted to Delegate.BeginInvoke (not Control.BeginInvoke!) and Delegate.EndInvoke, but is likely relevant in your case.

Methods named "Begin...Async" which return an IAsyncResult and have a corresponding "End..." method have the potential to create a ThreadPool leak. The easiest way to prevent a leak is to ensure that the corresponding "End..." method is always called.

The reason is that when Begin...Async is called it gets a thread from the ThreadPool and (typically) executes the synchronous version of the method on the worker thread; in your case ExecuteReader(). The return value of that call, or any exceptions that occur during that call, are not brought back to the main thread until End... is called--if you never call End then the results/exceptions are never returned and the ThreadPool thread is never freed.

So long story short, you are likely safe as long as you make sure to call EndExecuteReader() and handle the expected exception. Since the documentation is contradictory and vague I've started a discussion on the MSDN Feedback Forums.

Answer 2

Have a quick look at http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=98217 where exactly this behaviour has been reported.

So it looks like you should just catch this specific exception thrown in EndExecuteReader and ignore it.