unit Unit1;

{

Multithreaded Delphi Database Queries

http://delphi.about.com/od/kbthread/a/query_threading.htm

~Zarko Gajic


}

interface

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

type
  TCalcThread = class(TThread)
  private
    procedure RefreshCount;
  protected
    procedure Execute; override;
  public
    ConnStr : widestring;
    SQLString : widestring;
    ListBox : TListBox;
    Priority: TThreadPriority;
    TicksLabel : TLabel;

    Ticks : Cardinal;
  end;

  TADOThreadedForm = class(TForm)
    ADOConnection1: TADOConnection;
    lbCustomer1: TListBox;
    lbCustomer3: TListBox;
    lbCustomer2: TListBox;
    lblCustomer1: TLabel;
    lblCustomer2: TLabel;
    lblCustomer3: TLabel;
    Button1: TButton;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    Button2: TButton;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    ComboBox3: TComboBox;
    procedure Button2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    function RunThread(SQLString : widestring; LB:TListBox; Priority: TThreadPriority; lbl : TLabel): TCalcThread;
  public
    ct1, ct2, ct3 : TCalcThread;
    procedure ThreadTerminated(Sender:TObject);
  end;

var
  ADOThreadedForm: TADOThreadedForm;

implementation
{$R *.dfm}

procedure TADOThreadedForm.FormCreate(Sender: TObject);
begin
  with ADOQuery1 do
  begin
    Open;
    try
      while not EOF do
      begin
        ComboBox1.AddItem(ADOQuery1.Fields[1].AsString, TObject(ADOQuery1.Fields[0].AsInteger));
        Next;
      end;
    finally
      Close;
    end;
  end;

  //copy items
  ComboBox2.Items.Assign(ComboBox1.Items);
  ComboBox3.Items.Assign(ComboBox1.Items);

  //init to first
  ComboBox1.ItemIndex := 0;
  ComboBox2.ItemIndex := 0;
  ComboBox3.ItemIndex := 0;

end;

procedure TADOThreadedForm.Button1Click(Sender: TObject);
var
  s, sg: widestring;

  c1, c2, c3 : integer;
begin

  s := ' SELECT O.SaleDate, MAX(I.ItemNo) AS ItemCount  ' +
       ' FROM Customer C, Orders O,  Items I   ' +
       ' WHERE C.CustNo = O.CustNo AND I.OrderNo = O.OrderNo ' ;

  sg := ' GROUP BY O.SaleDate ';


  c1 := Integer(ComboBox1.Items.Objects[ComboBox1.ItemIndex]);
  c2 := Integer(ComboBox2.Items.Objects[ComboBox2.ItemIndex]);
  c3 := Integer(ComboBox3.Items.Objects[ComboBox3.ItemIndex]);


  Caption := '';

  ct1 := RunThread(Format('%s AND C.CustNo = %d %s',[s, c1, sg]), lbCustomer1, tpTimeCritical, lblCustomer1);

  ct2 := RunThread(Format('%s AND C.CustNo = %d %s',[s, c2, sg]), lbCustomer2, tpNormal,lblCustomer2);

  ct3 := RunThread(Format('%s AND C.CustNo = %d %s',[s, c3, sg]), lbCustomer3, tpLowest, lblCustomer3);

end;

function TADOThreadedForm.RunThread(SQLString: widestring; LB:TListBox; Priority: TThreadPriority; lbl : TLabel): TCalcThread;
var
  CalcThread : TCalcThread;
begin
  CalcThread := TCalcThread.Create(true);
  CalcThread.FreeOnTerminate := true;
  CalcThread.ConnStr := ADOConnection1.ConnectionString;
  CalcThread.SQLString := SQLString;
  CalcThread.ListBox := LB;
  CalcThread.Priority := Priority;
  CalcThread.TicksLabel := lbl;
  CalcThread.OnTerminate := ThreadTerminated;
  CalcThread.Resume;

  Result := CalcThread;
end;

procedure TADOThreadedForm.Button2Click(Sender: TObject);
begin
  ct2.Terminate;
end;

procedure TADOThreadedForm.ThreadTerminated(Sender: TObject);
begin
  Caption := Caption + (Sender as TCalcThread).ListBox.Name + ' - ';
end;


{ TCalcThread }

procedure TCalcThread.Execute;
var
  Qry : TADOQuery;
  i : integer;
begin
  inherited;

  ticks := GetTickCount();
  CoInitialize(nil); //CoInitialize was not called
  Qry := TADOQuery.Create(nil);
  try
//    Qry.Connection := Form1.ADOConnection1;     MUST USE OWN CONNECTION
    Qry.ConnectionString := ConnStr;
    Qry.CursorLocation := clUseServer;
    Qry.LockType := ltReadOnly;
    Qry.CursorType := ctOpenForwardOnly;
    Qry.SQL.Text := SQLString;


    ListBox.Clear;
    Qry.Open;
    for i:= 0 to 100 do
    begin
      while NOT Qry.Eof and NOT Terminated do
      begin
        ListBox.Items.Insert(0, Format('%s - %d', [Qry.Fields[0].asString,Qry.Fields[1].AsInteger]));

        //Canvas Does NOT Allow Drawing if not called through Synhronize
        Synchronize(RefreshCount);

        Qry.Next;
      end; //while
      if Terminated then break;
      Qry.First;
      ListBox.Items.Add('*---------*');
    end; // for
  finally
    Qry.Free;
  end;
  CoUninitialize();
  ticks := GetTickCount - ticks;
  TicksLabel.Caption := 'Ticks: ' + IntToStr(ticks);
end; //TCalcThread.Execute;


procedure TCalcThread.RefreshCount;
begin
  TicksLabel.Caption := IntToStr(ListBox.Items.Count);
end;


end.