multithreading - 线程化 Delphi ADO 查询

Question

我有一个查询代码，每次需要从数据库中获取数据时都可以调用它，并且我希望它是线程化的。不知道如何在线程中实现此代码，以便我可以重用此代码，基本上，我希望此代码在线程中。我知道如何在线程内创建一个简单的数据库查询，但想要一些可以重用的东西。谁能指出我在哪里可以找到这方面的示例，或者是否可以提供一个示例？

这是我的示例数据库查询:

function TDBConnection.SQLOpen(const SQLStr: String): TDataSet;
var
  i: Integer
begin
  try
    Result := TADOQuery.Create(DBConnect.FDatabaseConection);
    TADOQuery(Result).Connection:=DBConnect.FDatabaseConnection;
    TADOQuery(Result).CommandTimeOut:=30;
    TADOQuery(Result).SQL.Text := SQLStr;
    TADOQuery(Result).Open;
  except

  end;
end;

这是我如何调用上述函数的示例:

function TDBConnection.GetUserInfo: Boolean;
var
  sqlStr: String;
  Database: TDataset;
begin
  sqlStr:= 'SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1';
  try
    Dataset := SQLOpen(sqlStr);
    if not Dataset.IsEmpty then
    begin
      //pass result to StringGrid
    end;
  finally
    FreeAndNil(SQLParams);
    FreeAndNil(Dataset);
  end;
end;

Answer 1

为了可重用性，使用带有参数信息的数组。
每个线程都使用自己的 Connection 创建一个自己的 Adodataset。
Recordset可用于线程终止后的显示和编辑。
对于真正的应用程序，必须添加线程实例的处理。

unit ThreadedAdoDataset;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB, Grids, DBGrids;

type

  TFieldInfoRecord = Record // as far as sometimes parametertypes can not be detected by
    DataType: TFieldType; // Ado on his own, provide all needed informations
    Name: String;
    Size: Integer;
    Value: Variant;
  End;

  TFieldInfoArray = Array of TFieldInfoRecord;

  TDBThread = Class(TThread)
    Constructor Create(Const ConnectionString, SQL: String;
      FDArray: TFieldInfoArray);
  private
    FConnectionString, FSQL: String;
    FFDArray: TFieldInfoArray;
    FRecordSet: _RecordSet;
  Protected
    Procedure Execute; override;
  public
    Property RecordSet: _RecordSet read FRecordSet;
  End;

  TForm7 = class(TForm)
    ADOConnection1: TADOConnection;
    Button1: TButton;
    ADODataSet1: TADODataSet;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    procedure ThreadTerminate(Sender: TObject);
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form7: TForm7;

implementation

uses ActiveX;
{$R *.dfm}

procedure TForm7.Button1Click(Sender: TObject);
var
  FDArray: TFieldInfoArray;
  I: Integer;
begin
  // prepare parameterinformations
  SetLength(FDArray, 1);
  FDArray[0].Name := 'cn';
  FDArray[0].DataType := ftString;
  FDArray[0].Size := 20;
  FDArray[0].Value := '%ue%';

  for I := 0 to 10 do // testrun with 11 threads

    With TDBThread.Create(ADOConnection1.ConnectionString,
      'select * from Composition where Componame like :cn', FDArray) do
    begin
      FreeOnTerminate := true;
      // assign the wished procedure to ba called on terminate
      OnTerminate := ThreadTerminate;
    end;

end;

procedure TForm7.ThreadTerminate(Sender: TObject);
begin
  // example of assigning the recordset of the thread for displaying and editing
  // NOTE for editing the connection of ADODataSet1 has to be fitting to the threadcall
  ADODataSet1.RecordSet := TDBThread(Sender).RecordSet;
end;

procedure TForm7.FormCreate(Sender: TObject);
begin
  ReportMemoryLeaksOnShutDown := true;
end;


{ TDBThread }

constructor TDBThread.Create(const ConnectionString, SQL: String;
  FDArray: TFieldInfoArray);
var
  I: Integer;
begin
  inherited Create(false);
  FConnectionString := ConnectionString;
  FSQL := SQL;
  SetLength(FFDArray, Length(FDArray));
  for I := 0 to High(FDArray) do
  begin
    FFDArray[I].DataType := FDArray[I].DataType;
    FFDArray[I].Size := FDArray[I].Size;
    FFDArray[I].Name := FDArray[I].Name;
    FFDArray[I].Value := FDArray[I].Value;
  end;
end;

procedure TDBThread.Execute;
var
  I: Integer;
begin
  inherited;
  CoInitialize(nil);
  try
    With TADODataSet.Create(nil) do
      try
        CommandTimeOut := 600;
        ConnectionString := FConnectionString;
        // use own connection for the dataset
        // will requite a conncetionsstring including all
        // information for loggon
        Commandtext := FSQL;
        Parameters.ParseSQL(FSQL, true); // extract parameters
        for I := Low(FFDArray) to High(FFDArray) do // set parametervalues
        begin
          Parameters.ParamByName(FFDArray[I].Name).DataType := FFDArray[I]
            .DataType;
          Parameters.ParamByName(FFDArray[I].Name).Size := FFDArray[I].Size;
          Parameters.ParamByName(FFDArray[I].Name).Value := FFDArray[I].Value;
        end;
        Open;
        FRecordSet := RecordSet; // keep recordset
      finally
        Free;
      end;
  finally
    CoUnInitialize;
  end;
end;

end.