Title: Cancel an Asynchronous ADO Query
Question: Have you ever had a huge query running inside an application but you would like the user to be able to cancel it? Well with asynchronous queries and a little trick you can.
Answer:
Follow these simple step by step instructions
1) First you will need an AdoQuery and an AdoConnection.
2) Set the connection string on AdoConnection and set the AdoQuery property of Connection to AdoConnection
3) Then set the following AdoQuery Properties:
ExecuteOptions
eoAsyncExecute True
eoAsyncFetch True
eoAsyncFetchNonBlock True
4) Write your SQL statement in AdoQuery.
5) Create a new class like this:
type
TDataSetWrapper = class(TDataSet);
6) Create a public ResultDataSet:TAdoDataSet;
7) Write this code for the FetchComplete event of the AdoQuery:
ResultDataSet:=DataSet;
AdoConnection.RollbackTrans;
8) Write this code to open your query:
if not AdoConnection.Connected then
begin
AdoConnection.Open;
AdoConnection.BeginTrans;
ADOQuery.Open;
end;
9) This function will stop the query:
procedure CancelAsyncQuery(qry:TADODataSet);
var
fCommand: _Command;
begin
if not Assigned(qry.RecordSet) then exit;
if stFetching in qry.RecordsetState then begin
fCommand := _Command(qry.RecordSet.ActiveCommand);
fCommand.Cancel;
if Assigned(qry.RecordSet ) then qry.RecordSet.Cancel;
TDataSetWrapper(qry).SetState(dsInactive);
TDataSetWrapper(qry).CloseCursor;
Qry.Connection.RollbackTrans;
Qry.Connection.Close;
end;
if Assigned(qry.RecordSet) then qry.RecordSet.Cancel;
end;
10) Just call the above function this way:
CancelAsyncQuery(TAdoDataSet(AdoQuery));