Stopping of Running DB Queries

From OpenPetra Wiki
Jump to navigation Jump to search

Overview and Warning

It is possible to request the stopping of the execution of a DB query when it is executed in a particular way.

If DB queries are not executed in that particular way it is not possible to stop their execution in a reliable way --- do not try to attempt that by running DB queries in another way and in a separate thread and then calling .Abort() on that thread when you want to stop it from executing - that would be a very bad way of trying to do this and can lead to all kinds of unforeseen problems!

Executing a DB Query in a Way That Allows it to be Stopped

  • Call the special DBAccess.GDBAccessObj.SelectUsingDataAdapter Method to execute the query. Not only will it return the number of Rows successfully added or refreshed in the DataTable passed in with the AFillDataTable Argument, but it will provide you with an instance of the TDataAdapterCanceller Class in the out Argument ADataAdapterCanceller.
    • Read the XML Documentation of the DBAccess.GDBAccessObj.SelectUsingDataAdapter Method and check where it is used so far for details on how to use this Method correctly!
  • Call the TDataAdapterCanceller.CancelFillOperation() Method on the instance that you were provided with to cancel the execution of the query. This needs to be done in a separate thread, as the thread that started the query will still be blocked because it is executing the query and that is a blocking operation!
    • It might take some time until the executing query is cancelled by the DB, but a call to the TDataAdapterCanceller.CancelFillOperation() Method returns immediately. Beware of executing another query while the other one is stopping - this leads to ADO.NET errors that state that a ADO.NET command is still executing!

Note: The Method DBAccess.GDBAccessObj.SelectUsingDataAdapter can also do other unique things:'Preparing' SQL Statements And Specifying SQL Statement Time-outs

Historic Information: How this was done before Co-ordinated DB Access got implemented

While it has previously been possible to request the stopping of the execution of a DB Query by first getting a DbDataAdapter from the TDataBase Class and then calling .SelectCommand.Cancel() on that Object, that option had to be removed as the calling of the DbDataAdapters' .Fill Method and the .SelectCommand.Cancel() Method were entirely outside of the scope in which the Co-ordinated DB Access could protect the integrity of the DB Access in the light of multi-threaded DB Access.


Questions?

In case you have questions regarding the feature that gets explained on this page please contact ChristianK, the developer of that feature.