Delphi多线程学习：多线程数据库查询（ADO）

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
.

程序运行结果如下：

可以看到三个线程同时执行。第一第三两个线程条件一样，查询的结果也一样。