Hi,
I'm trying to make OdbcCommands running in the background interruptible from a UI button.
According to the documentation the OdbcCommand.Cancel() method does just that but the problem is how to invoke it, since it can only be called from the thread the command is running on and that by definition this thread is busy running the command.
I tried outside a backgroundworker, using the example code supplied by MS :
public void CreateMyOdbcCommand(string mySelectQuery, string myConnectionString)
{
OdbcConnection myConnection = new OdbcConnection(myConnectionString);
OdbcCommand myCommand = new OdbcCommand(mySelectQuery, myConnection);
myCommand.Connection.Open();
myCommand.ExecuteReader();
myCommand.Cancel();
}
and it doesn't work for me, the control only passes to the cancel line after the executereader is finished (not very surprising...).
For a background worker, the problem is even worse since to interrupt your worker you're supposed to call myWorker.CancelAsync() which raises the PendingCancellation property which can be checked by your code... except that if your thread is busy running an OdbcCommand it can't be checking to cancel it anyway.
Clues would be appreciated ;-)
MetaMeta
Cancelling an OdbcCommand in a backgroundworker
BobGomersall
Sarah,
You're right, the OdbcCommand.Cancel() worked beautifully when called from the main thread whereas my query are running in the backgroundworker.
Thank you!
MM
SeekTechHelp
Hello MM,
I've tried the examples but nothing seems to work but as you've noted in previous postings you still can't issue a cancel until the command is finished. I've also had problems trying to read the datareader after the query did finish running.
Can you provide me with an example of how you made this work... I haven't had any luck as of yet and I would really like for my users to be able to cancel a query during it's excecution!
Please!!
Thanks
-jms-
dinks
You are correct that you can't just call ExecuteReader and then Cancel, and expect it to really do much as far as cancelling the query. What you need to do is to call OdbcCommand.Cancel from a separate thread. Of course, this means that you will have to share the OdbcCommand instance across those threads. I want to point out that while OdbcCommand is not thread-safe for most operations, this is one scenario that is intended to work across threads, because otherwise it doesn't provide much value.
One thing you should consider is whether Cancel is really what you want to use. If you are concerned about long running queries, something like CommandTimeout could be used instead. Keep in mind that not all ODBC drivers support this value though (ODBC attribute is SQL_ATTR_QUERY_TIMEOUT). Furthermore, even with drivers that do support this functionality or the Cancel behavior, in some cases they are not able to actually cancel a query. Usually the cancellation would be done through an asynchronous tranmission to the server, separate from the query you are trying to cancel. The server may not recognize the request in time to actually be able to cancel the query. These are just things to keep in mind whenever you are trying to write an application with the ability to cancel queries in the middle of execution.
I don't see any reason why you couldn't cancel a query that's running in a BackgroundWorker thread. You just have to have access to the same OdbcCommand that you used to execute the query.
Thanks,
Sarah
paulmig
Fafnir (nice name! you into mythology or are you breathing fire )
Believe it or not I actually researched the doc before asking the question ;-)
I asked the question becuase there are several things I do not understand :
1- Re the OdbcCommand.Cancel() method. When you execute an OdbcCommand the thread it is executing in is waiting for the command to have finished executing before going to the next command. So I have a hard time imagining any scenario where you could actually cancel the command, unless maybe you can reference the command in 2 different threads, one for the execution, one for the cancellation.
The basic example (in a normal main thread) found on msdn didn't work for me.
2- Re Cancellation of backgroundworker
I understand how that can work if the 'timeconsumingoperation' performs some kind of loop where it can check the status of the cancellationpending for each iteration.
What I do not know/understand is if it is possible to 'kill' a single time consuming operation (like one big sql query gone mad) this way or any other way.
In theory it seems it would be a question of calling OdbcCommand.Cancel() in the right place but that's where I get all fuzzy on which thread you'd do that from and is it doable when the query is running within a BackgroundWorker.DoWork().
Please illuminate me.
MM
Gazeth
1. Make sure your operation can be properly cancelled (see #6 on
http://msdn2.microsoft.com/en-us/library/ms233672(VS.80).aspx
)
[Also, see the whole article ;-) ]
2. In the main thread, call CancelAsynch on the backgroundWorker object:
http://msdn2.microsoft.com/en-us/library/system.componentmodel.backgroundworker.cancelasync(VS.80).aspx