多线程执行数据库查询

多线程执行数据库查询 Two requirements must be met in order to perform a threaded query. First, the query to be threaded must be contained within its own session by using a separate TSession component. Therefore, you would place a TSession component on your form and assign it's name to the SessonName property of the TQuery component to be used in the thread. You must use a separate TSession component for each TQuery component to be used in a thread. If you are also using a TDataBase component, a separate TDataBase must be used for each threaded query as well. The second requirement is that the threaded TQuery component must not be connected to a TDataSource in the context of the thread in which it will be executed. This must be done in the context of the primary thread. The code example below illustrates this process. This unit shows a form which contains two each of the following comopnents: TSession, TDatabase, TQuery, TDataSource and TDBGrid. These components have the following property settings: Session1 Active True; SessionName "Ses1" DataBase1 AliasName "IBLOCAL" DatabaseName "DB1" SessionName "Ses1" Query1 DataBaseName "DB1" SessionName "Ses1" SQL.Strings "Select * from employee" DataSource1 DataSet "" DBGrid1 DataSource DataSource1 Session2 Active True; SessionName "Ses2" DataBase2 AliasName "IBLOCAL" DatabaseName "DB2" SessionName "Ses2" Query2 DataBaseName "DB2" SessionName "Ses2" SQL.Strings "Select * from customer" DataSource2 DataSet "" DBGrid1 DataSource DataSource2 Notice that the DataSet property for both TDataSource components do not refer to anything initially. This will be set at run-time as illustrated in the code. unit Unit1; interface uses Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls, Grids, DBGrids, DB, DBTables; type TForm1 = class(TForm) Session1: TSession; Session2: TSession; Database1: TDatabase; Database2: TDatabase; Query1: TQuery; Query2: TQuery; DataSource1: TDataSource; DataSource2: TDataSource; DBGrid1: TDBGrid; DBGrid2: TDBGrid; GoBtn1: TButton; procedure GoBtn1Click(Sender: TObject); end; TQueryThread = class(TThread) private FSession: TSession; FDatabase: TDataBase; FQuery: TQuery; FDatasource: TDatasource; FQueryException: Exception; procedure ConnectDataSource; procedure ShowQryError; protected procedure Execute; override; public constructor Create(Session: TSession; DataBase: TDatabase; Query: TQuery; DataSource: TDataSource); virtual; end; var Form1: TForm1; implementation constructor TQueryThread.Create(Session: TSession; DataBase: TDatabase; Query: TQuery; Datasource: TDataSource); begin inherited Create(True); // Create thread in a suspendend state FSession := Session; // connect all private fields FDatabase := DataBase; FQuery := Query; FDataSource := Datasource; FreeOnTerminate := True; // Have thread object free itself when terminated Resume; // Resume thread execution end; procedure TQueryThread.Execute; begin try { Run the query and connect the datasource to the TQuery component by calling ConnectDataSource from main thread (Synchronize used for this purpose)} FQuery.Open; Synchronize(ConnectDataSource); except { Capture exception, if one occurs, and handle it in the context of the main thread (Synchonize used for this purpose. } FQueryException := ExceptObject as Exception; Synchronize(ShowQryError); end; end; procedure TQueryThread.ConnectDataSource; begin FDataSource.DataSet := FQuery; // Connect the DataSource to the TQuery end; procedure TQueryThread.ShowQryError; begin Application.ShowException(FQueryException); // Handle the exception end; procedure RunBackgroundQuery(Session: TSession; DataBase: TDataBase; Query: TQuery; DataSource: TDataSource); begin { Create a TThread instance with the various parameters. } TQueryThread.Create(Session, Database, Query, DataSource); end; {$R *.DFM} procedure TForm1.GoBtn1Click(Sender: TObject); begin { Run two separate queries, each in their own thread } RunBackgroundQuery(Session1, DataBase1, Query1, Datasource1); RunBackgroundQuery(Session2, DataBase2, Query2, Datasource2); end; end. The TForm1.GoBtn1Click method is an event handle for a button click event. This event handler calls the RunBackgroundQuery procedure twice, each time passing a different set of database components. RunBackgroundQuery creates a separate instance of the TQueryThread class, passing the various database components to its constructor which in turn assigns them to the appropriate TQueryThread private data fields. The TQueryThread contains two user-defined procedures: ConnectDataSource and ShowQryError. ConnectDataSource connects FDataSource.DataSet to FQuery. However, it does this in the primary thread by using the TThread.Synchronize method. ShowQryError handles the exception in the context of the primary thread, again by using the Synchronize method. The Create constructor and Execute method are explained in the code's comments.