- ADO多线程数据库查询通常会出现3个问题:
- 1、CoInitialize 没有调用 (CoInitialize was not called);所以,在使用任何dbGo对象前,必须手 调用CoInitialize和CoUninitialize。调用CoInitialize失败会产生"CoInitialize was not called"例外。
- 2、画布不允许绘画 (Canvas does not allow drawing);所以,必须通过Synchronize过程来通知主线程访问主窗体上的任何控件。
- 3、不能使用主ADO连接 (Main TADoConnection cannot be used!);所以,线程中不能使用主线程中TADOConnection对象,每个线程必须创建自己的数据库连接。
- Delphi2007安装后在X:/Program Files/Common Files/CodeGear Shared/Data目录下有一个dbdemos.mdb文件,用来作为测试的例子。dbdemos.mdb中的customer表保存了客户信 息,orders表中保存了订单信息。
- 测试程序流程大致是这样的:在主窗体上放TADOConnection和TQuery控件,启动时这个TQuery从Customer表中查出客户编码 CustNo和公司名称Company,放到三个Combox框中,分别在三个列表框中选定客户公司名称,按照公司名称所对应的客户代码建立三个线程同时 在orders表中查询销售日期SaleDate分别填入ListBox中
- unit
- Main;
- interface
- uses
- Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs,
- DB, ADODB, StdCtrls;
- type
- TForm2 =class (TForm)
- ComboBox1: TComboBox;
- ComboBox2: TComboBox;
- ComboBox3: TComboBox;
- ListBox1: TListBox;
- ListBox2: TListBox;
- ListBox3: TListBox;
- Button1: TButton;
- ADOConnection1: TADOConnection;
- ADOQuery1: TADOQuery;
- Label1: TLabel;
- Label2: TLabel;
- Label3: TLabel;
- procedure FormCreate(Sender: TObject) ;
- procedure Button1Click(Sender: TObject) ;
- private
- { Private declarations }
- public
- { Public declarations }
- end ;
- var
- Form2: TForm2;
- implementation
- uses
- ADOThread;
- {$R *.dfm}
- procedure TForm2.Button1Click(Sender: TObject);
- const
- SQL_CONST= 'Select SaleDate from orders where CustNo = %d' ;
- var
- c1,c2,c3: Integer ;
- s1, s2,s3: string ;
- begin
- //取得三个选择框客户的编码
- c1:= Integer(ComboBox1.Items.Objects[ComboBox1.ItemIndex]);
- c2:= Integer(ComboBox2.Items.Objects[ComboBox2.ItemIndex]);
- c3:= Integer(ComboBox3.Items.Objects[ComboBox3.ItemIndex]);
- //生成SQL 查询语句
- s1:=Format(SQL_CONST,[c1]);
- s2:=Format(SQL_CONST,[c2]);
- s3:=Format(SQL_CONST,[c3]);
- //三个线程同时查询
- TADOThread.Create(s1,ListBox1,Label1) ;
- TADOThread.Create(s2,ListBox2,Label2);
- TADOThread.Create(s3,ListBox3,Label3);
- end ;
- procedure TForm2.FormCreate(Sender: TObject);
- var
- strSQL:string ;
- begin
- strSQL:='SELECT CustNo,Company FROM customer';
- ADOQuery1.Close;
- ADOQuery1.SQL.Clear;
- ADOQuery1.SQL.Add(strSQL);
- ADOQuery1.Open;
- ComboBox1.Clear;
- ComboBox2.Clear;
- ComboBox3.Clear;
- //将客户Company和相关CustNo填到ComboBox中
- while not ADOQuery1.Eof do
- begin
- ComboBox1.AddItem(ADOQuery1.Fields[1].asString, TObject(ADOQuery1.Fields[0].AsInteger));
- ADOQuery1.Next;
- end ;
- ComboBox2.Items.Assign(ComboBox1.Items);
- ComboBox3.Items.Assign(ComboBox1.Items);
- // 默认选中第一个
- ComboBox1.ItemIndex := 0;
- ComboBox2.ItemIndex := 0;
- ComboBox3.ItemIndex := 0;
- end ;
- end.
- {ADO查询多线程单元}
- unit
- ADOThread;
- interface
- uses
- Classes,StdCtrls,ADODB;
- type
- TADOThread = class(TThread)
- private
- { Private declarations }
- FListBox:TListBox;
- FLabel:TLabel;
- ConnString:WideString;
- FSQLString:string;
- procedure UpdateCount;
- protected
- procedure Execute; override;
- public
- constructor Create(SQL:string;LB:TListBox;Lab:TLabel);
- end ;
- implementation
- uses Main,SysUtils,ActiveX;
- { TADOThread }
- constructor TADOThread.Create(SQL: string; LB: TListBox;Lab:TLabel);
- begin
- ConnString:=Form2.ADOConnection1.ConnectionString;
- FListBox:=LB;
- FLabel:=Lab;
- FSQLString:=SQL;
- Inherited Create(False);
- end ;
- procedure TADOThread.Execute;
- var
- Qry:TADOQuery;
- i:Integer;
- begin
- { Place thread code here }
- FreeOnTerminate:=True;
- CoInitialize(nil);
- //必须调用(需Uses ActiveX)
- Qry:=TADOQuery.Create(nil);
- try
- Qry.ConnectionString:=ConnString; //必须有自己的连接
- Qry.Close;
- Qry.SQL.Clear;
- Qry.SQL.Add(FSQLString);
- Qry.Open;
- FListBox.Clear;
- for i := 0 to 100 do //为了执行久点重复历遍数据集101次
- begin
- while not Qry.Eof And not Terminated do
- begin
- FListBox.AddItem(Qry.Fields[0].asstring,nil);
- //如果不调用Synchronize,会出现Canvas Does NOT Allow Drawing
- Synchronize(UpdateCount);
- Qry.Next;
- end ;
- Qry.First;
- FListBox.AddItem('*******',nil);
- end ;
- finally
- Qry.Free;
- end ;
- CoUninitialize;
- end ;
- procedure TADOThread.UpdateCount;
- begin
- FLabel.Caption:=IntToStr(FListBox.Items.Count);
- end ;
- end.