0

If I make a CancellationTokenSource and pass it down to a place where I perform a query like this:

await command.Connection.OpenAsync();
dataReader = await command.ExecuteReaderAsync(_cancellationToken);

If immediately below this I add the following:

_cancellationToken.ThrowIfCancellationRequested();
resultTable.Load(dataReader);

If on a different thread _cancellationTokenSource.Cancel() is called, will the query be cancelled appropriately?

endyourif
  • 2,186
  • 19
  • 33
  • Why don't you check yourself? Not sure I understand the point of this question. – 3615 Aug 26 '16 at 13:00
  • I tried, it doesn't cancel until after the datatable is loaded. Another process is polling for a cancellation request and the end goal is try and cancel a potentially long running query. – endyourif Aug 26 '16 at 13:02
  • Ah, so the real question is: why ThrowIfCancellationRequested doesn't interrupt current method execution? – 3615 Aug 26 '16 at 13:04
  • Perhaps, I still feel like I'm misunderstanding why if I pass the cancellation token to the ExecuteReaderAsync that it doesn't internally detect that it has been cancelled. – endyourif Aug 26 '16 at 13:12
  • Well, it definitely should cancel the request, I'm trying to reproduce your issue. Btw, if you would provide a ready-to-go reproduction, it would be much to help. – 3615 Aug 26 '16 at 13:20
  • Look at this [fiddle](https://dotnetfiddle.net/o8c20l). I cannot reproduce your issue, so I think the problem is in your code. I gave 5 sec timeout and it worked as expected. In your case you should call Cancell on the cancellation source. Hope it helps.. – 3615 Aug 26 '16 at 13:39
  • That's the problem, I am calling Cancel on the cancellation source, but it is not stopping the query. – endyourif Aug 26 '16 at 13:43
  • Ok, changed [fiddle](https://dotnetfiddle.net/Rehtx1) to call Cancel after 5 seconds, and it's still works. I cannot help you by guessing, I can't see your real code and what causes the issue. ExecuteReaderAsync is working properly as shown in fiddles. – 3615 Aug 26 '16 at 13:48
  • That might be the difference. In my scenario the cancel and cancellation token happen outside the async process. Like this [fiddle](https://dotnetfiddle.net/Nmj2h8) – endyourif Aug 26 '16 at 13:58
  • 1
    In your fiddle the problem occurs because timer object is disposed before is acutally called, so ts.Cancel() is never called. If you would block the thread by `worker.Do(ts).Wait();` and prevent timer beeing collected untill Do method is executed, everything should work as before. – 3615 Aug 26 '16 at 14:06
  • Btw, you might want to try cancell your query by calling cancel on command, like in this [question](http://stackoverflow.com/q/4779679/5246145) – 3615 Aug 26 '16 at 14:09
  • Changing Do to return Task and adding .Wait definitely worked as expected. I think I need to rework something in my code to try that. – endyourif Aug 26 '16 at 14:11
  • Think I have a snag in my code. I need to wrap my sproc call in a transaction scope because the sproc is set to isolation level of snapshot. We are not using 4.5.1 which appears to allow transaction scope to be async, but not 4.5. – endyourif Aug 26 '16 at 14:21
  • Well, you can always run it sync mode on ThreadPool and await the resulting task. – 3615 Aug 26 '16 at 14:28
  • Passing the cancellation token to the new task and that will cancel the inner sql if the source is cancelled? – endyourif Aug 26 '16 at 14:31

1 Answers1

2

Was able to find a decent solution to the problem. Given the cancellation token and a SQLCommand, wrapping the loading to a data table as follows successfully cancels the db query:

using (CancellationTokenRegistration ctr = cancellationToken.Register(() => cmd.Cancel()))
{
     using (var reader = cmd.ExecuteReaderAsync(cancellationToken))
     {
         dataTable.Load(reader.Result);
     }
}

So when the cancellationToken is cancelled in a different thread, the cmd.Cancel is called automatically. This will throw a SqlException that I had to handle and perform my cleanup.

endyourif
  • 2,186
  • 19
  • 33