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

比眉伴天荒 2022-07-12 10:18 228阅读 0赞

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
    .

程序运行结果如下：

![MultiThread8.gif][]

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

[MultiThread8.gif]: /images/20220710/7678a0d154eb482883ca6322f892f560.png