unit_data_module.pas

连接本地access数据文件

unit Unit_Data_Module;

interface

uses
  Variants, SysUtils, Classes, DB, ADODB, IdGlobal, StrUtils, Dialogs;

type
  TExamine_Data = class(TDataModule)
    myConn1: TADOConnection;
    myCmd: TADOCommand;
    myDispQuery: TADOQuery;
    myDispDataSource: TDataSource;
    myQueryMaxNo: TADOQuery;
    ADOQueryTemp: TADOQuery;
    procedure DataModuleCreate(Sender: TObject);
    procedure DataModuleDestroy(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    function myExecuteCmd(strCmd: String):Boolean;
    function myDataExist(strFirst,strSecond,strOperator: String):Boolean;
    procedure RefreshDisp();
    function myDispQueryData(strQuery: String;intInterval: Integer):LongInt;
    function myGetMaxNo():Integer;
  end;

var
  Examine_Data: TExamine_Data;
  myAppPath: String;
  StringTable: String = 'String_Item_T';

implementation

{$R *.dfm}

//Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Examine\Examine.mdb;Persist Security Info=False

procedure TExamine_Data.DataModuleCreate(Sender: TObject);
begin
  //
  myConn1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+myAppPath+'String.mdb;Persist Security Info=False';
  myConn1.Open();
end;

procedure TExamine_Data.DataModuleDestroy(Sender: TObject);
begin
  myConn1.Close();
end;

function TExamine_Data.myExecuteCmd(strCmd: String):Boolean;
begin
  myCmd.CommandText := strCmd;
  try
    myCmd.Execute();
    myExecuteCmd := true;
//    myConn1.CommitTrans();
  except
    myExecuteCmd := false;
//    myConn1.RollbackTrans();
  end
end;

procedure TExamine_Data.RefreshDisp();
begin
  if myDispQuery.Active then myDispQuery.Close();
  myDispQuery.Open();
//  myDispQuery.Close();
end;

function TExamine_Data.myDataExist(strFirst,strSecond,strOperator: String):Boolean;
begin
  myCmd.CommandText := 'select * from ' + StringTable + ' where FirstOperator = '+strFirst+' and SecondOperator = ' + strSecond + ' and OperatorSign =''' + strOperator + '''';
  try
    if (myCmd.Execute().RecordCount > 0) then
      myDataExist := true
    else
      myDataExist := false;
//    myConn1.CommitTrans();
  except
    myDataExist := false;
//    myConn1.RollbackTrans();
  end
end;

function TExamine_Data.myDispQueryData(strQuery: String;intInterval: Integer):LongInt;
var
    intCycle:Integer;
    intAddCount:Integer;
    myQueryStr:TStringList;
begin
    myQueryStr:=TStringList.Create;
    intAddCount := -1;

    //条件
    for intCycle := 1 to Length(strQuery) do
        if IsNumeric(strQuery[intCycle]) then
        begin
            intAddCount := intAddCount + 1;
            if intCycle = 1 then
                myQueryStr.Append('where a.String_Item like ''%'+ strQuery[intCycle] + '%''')
            else
                myQueryStr.Append('and exists(select * from String_Item_T where String_NO = a.String_NO + ' + IntToStr((intInterval+1)*intAddCount) + ' and String_Item like ''%'+ strQuery[intCycle] + '%'')')
        end;
try
    with myDispQuery do begin
        if Active then Close;
        //当查询串为空或只有一个条件时直接查询出全部数据
        if (trim(strQuery) = '') or (intAddCount <= 0) then
        begin
            with SQL do begin
                Clear;
                Append('select a.String_NO as String_NO,a.String_Item as String_Item from String_Item_T a');
                for intCycle := 1 to myQueryStr.Count do
                    Append(myQueryStr.Strings[intCycle - 1]);
                Append('order by a.String_NO');
            end;
            Open;
            myDispQueryData := RecordCount;
        end
        else
        begin
            //取符合条件的第一条数据，
            if ADOQueryTemp.Active then ADOQueryTemp.Close;
            with ADOQueryTemp.SQL do begin
                Clear;
                Append('select a.String_NO as String_NO,a.String_Item as String_Item from String_Item_T a');
                for intCycle := 1 to myQueryStr.Count do
                    Append(myQueryStr.Strings[intCycle - 1]);
                Append('order by a.String_NO');
            end;
            ADOQueryTemp.Open;

            //如果有查询串，则只显示查询出来的结果
            if (ADOQueryTemp.RecordCount > 0) then
            begin
                //RecordSet.MoveFirst;
                myQueryStr.Clear;
                while not ADOQueryTemp.RecordSet.EOF do
                begin
                    myQueryStr.Append(' (String_No >= ' + IntToStr(ADOQueryTemp.FieldByName('String_NO').AsInteger) + ' and String_No <= ' + IntToStr(ADOQueryTemp.FieldByName('String_NO').AsInteger + (intInterval+1)*intAddCount) + ')');
                    ADOQueryTemp.MoveBy(1);
                end;
                with SQL do begin
                    Clear;
                    Append('select String_NO, String_Item from String_Item_T where');
                    for intCycle := 1 to myQueryStr.Count do
                        if intCycle = 1 then
                            Append(myQueryStr.Strings[intCycle - 1])
                        else
                            Append(' or ' + myQueryStr.Strings[intCycle - 1]);
                    Append('order by String_NO');
                end;
                Open;
                myDispQueryData := RecordCount;
            end
            else
                myDispQueryData := 0;
        end;
    end;
except
    myDispQueryData := -1;
end;
    myQueryStr.Destroy;
end;

function TExamine_Data.myGetMaxNo():Integer;
begin
try
    with myQueryMaxNo do begin
        if Active then Close;
        with SQL do begin
            Clear;
            Add('select max(string_no) + 1 as MAX_NO from String_Item_T');
        end;
        Open;
        RecordSet.MoveFirst;
        myGetMaxNo := FieldByName('MAX_NO').AsInteger;
    end;
except
    myGetMaxNo := 0;
end;
end;


end.