Delphi之ADO操作Access

unit frm_Ado;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB;

type
  TForm_Ado = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    procedure FormCreate(Sender: TObject);
  private

    { Private declarations }
  public
    function AdoAddInfo(AppName: string; AppType: integer;AppPath: string): Boolean;
    function GetAppPath(AppName: string): string;
    function UpDateAppPath(AppName, mAppPath: string): Boolean;
    function DelAdoInfo(AppName: string): Boolean;
    function GetAppCount(AppName: string): integer;

    { Public declarations }
  end;

var
  Form_Ado: TForm_Ado;

implementation

{$R *.dfm}

procedure TForm_Ado.FormCreate(Sender: TObject);
begin
  // 联接access数据库
  ADOConnection1.ConnectionString :=
    'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + SysUtils.ExtractFilePath
    (application.ExeName) + 'AppPath.accdb;Persist Security Info=False';
  ADOConnection1.KeepConnection := true;
  ADOConnection1.LoginPrompt := false;
  ADOConnection1.Connected := true;
end;

function TForm_Ado.DelAdoInfo(AppName: string): Boolean;
var
  sqlStr: string;
begin
  result := false;
  sqlStr := 'delete FROM Tb_AppPath where(AppName=' + #39 + AppName + #39 + ')';
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add(sqlStr);
  ADOQuery1.ExecSQL;
  ADOQuery1.Close;
  result := true;
end;

function TForm_Ado.AdoAddInfo(AppName: string; AppType: integer;AppPath: string): Boolean;
var
  sqlStr: string;
  counter: integer;
begin
    result := false;
    sqlStr := 'INSERT INTO Tb_AppPath(AppName,AppType,AppPath) Values(' + #39 +
      AppName + #39 + ',' + inttostr(AppType) + ',' + #39 + AppPath + #39 + ')';
    ADOQuery1.Close;
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.Add(sqlStr);
    ADOQuery1.ExecSQL;
    ADOQuery1.Close;
    result := true;
end;

function TForm_Ado.UpDateAppPath(AppName: string; mAppPath: string): Boolean;
var
  sqlStr: string;
begin
  result := false;
  sqlStr := 'update Tb_AppPath Set AppPath=' + #39 + mAppPath + #39 +
    'where(AppName=' + #39 + AppName + #39 + ')';
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add(sqlStr);
  ADOQuery1.ExecSQL;
  ADOQuery1.Close;
  result := true;
end;

function TForm_Ado.GetAppPath(AppName: string): string;
var
  sqlStr: string;
begin
  result := '';
  sqlStr := 'select AppPath from Tb_AppPath where(AppName=' + #39 + AppName +
    #39 + ')';
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add(sqlStr);
  ADOQuery1.open;

  result := ADOQuery1.FieldByName('AppPath').AsString;
  ADOQuery1.Close;
end;

function TForm_Ado.GetAppCount(AppName: string): integer;
var
  sqlStr: string;
begin
  result := 0;
  sqlStr := 'select count(AppName) from Tb_AppPath where(AppName=' + #39 +
    AppName + #39 + ')';
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add(sqlStr);
  ADOQuery1.open;

  result := ADOQuery1.fields[0].Asinteger;
  ADOQuery1.Close;
end;

end.

//添加
procedure TForm_Ado.AdoSetNTPolcy(SvrAdmin, SvrPsw: string);
var
  sqlStr: string;
  counter: integer;
begin
  sqlStr := 'select Count(*) from tb_ntpolcy where(ntname=' + #39 + SvrAdmin + #39 + ')';
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add(sqlStr);
  ADOQuery1.open;
  counter := ADOQuery1.Fields[0].AsInteger;
  ADOQuery1.Close;

  if counter = 0 then
  begin
    sqlStr := 'INSERT INTO tb_ntpolcy(ntname,ntpsw) Values(' + #39 + SvrAdmin +
      #39 + ',' + #39 + SvrPsw + #39 + ')';
    ADOQuery1.Close;
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.Add(sqlStr);
    ADOQuery1.ExecSQL;
    ADOQuery1.Close;
  end;
end;

//遍历结果集
procedure TForm_Ado.AdoGetNTPolcy(var listNTPolcy: Tlist);
var
  sqlStr: string;
  pNtPolcy: PAccount;
begin
  sqlStr := 'select * from tb_ntpolcy';
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add(sqlStr);
  ADOQuery1.open;
  while not ADOQuery1.eof do
  begin
    New(pNtPolcy);
    pNtPolcy.ntname := ADOQuery1.FieldByname('ntname').AsString;
    pNtPolcy.ntpwd := ADOQuery1.FieldByname('ntpsw').AsString;
    if pNtPolcy.ntname <> '' then
    begin
      listNTPolcy.Add(pNtPolcy);
    end;
    ADOQuery1.Next;
  end;
  ADOQuery1.Close;
end;

控件属性设置：

TADOConnection/
TADOQuery/Connection:=ADOConnection1
TDataSource/DataSet:=ADOQuery1