delphi7 TADOConnection TADODataSet 简单应用

unit DBOperation;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, StdCtrls, DB, ADODB, MemoExt;
{
INSERT INTO [testDB].[dbo].[stu]([ID],[name],[score])VALUES(3,'ccc',30)
SELECT [ID],[name],[score] FROM [testDB].[dbo].[stu]
}
type
  TForm1 = class(TForm)
    grp1: TGroupBox;
    grp2: TGroupBox;
    statDBConnction: TStatusBar; //在Object Inspector中的panel属性页中添加两个 panel ,在第一个panel用来标志数据是否连接
    btnConnectDB: TButton;
    conConnectionSqlServer: TADOConnection; //在ADO组件页 非可视化控件 拖到窗体 用于数据库的连接
    btnexecutesql: TButton;
    edtSql: TEdit;
    edtExecuteSql: TEdit;
    btnExecutesqlReturnSet: TButton;
    mmoMessage1: TMemoExt;
    procedure btnConnectDBClick(Sender: TObject);
    procedure ExecuteSql(const sql :string);                           //可以执行没有返回记录的sql语句
    function ExecutesqlReturnRecord(const sql :string):TADODataSet;    //可以执行  有返回记录的sql语句
    procedure btnexecutesqlClick(Sender: TObject);
    procedure btnExecutesqlReturnSetClick(Sender: TObject);

  private
    { Private declarations }
    bDBisConnected:Boolean;
  public
    { Public declarations }
    function  DbConnect:Boolean;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}
function  TForm1.DbConnect:Boolean;//封装一个数据库连接函数
begin
  Result := false;
  if not bDBisConnected then
  begin
    with conConnectionSqlServer do
    begin
      Connected := False;
      //TADOConnection 属性1 设置连接字符串参数
      ConnectionString  := 'Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=testDB;password =123456;Data Source=LJF-PC\LJF';
      try
       ConnectionTimeout := 20;//TADOConnection 属性2 连接超时等待时间
       CommandTimeout := 30 ;//TADOConnection 属性3 执行命令超时时间
       Connected:=True;   //TADOConnection 属性4 设置连接也可以用于判断是否连接
   //    ConnectOptions :=coConnectUnspecified;//TADOConnection 属性 5 用来设置同步或异步连接 默认同步，连接数据库很慢可以用异步coConnectUnspecified, coAsyncConnect
       Result := True;
       bDBisConnected := True;
      except
        Result :=False;
        bDBisConnected := False;
        ShowMessage('not connected');  //
        raise;
      //  Exit;
      end;
    end;
  end else
  Result := True;
end;
procedure TForm1.btnConnectDBClick(Sender: TObject);
begin
    if DbConnect then
      statDBConnction.Panels[0].Text := '数据库已连接!'
    else
      statDBConnction.Panels[0].Text := '数据库未连接!';
end;



procedure TForm1.ExecuteSql(const sql :string); //可以执行没有返回记录的sql语句
var
  lCursor: TCursor;
begin
  if bDBisConnected then
  begin
    with conConnectionSqlServer do
    begin
      lCursor := Screen.Cursor;
      try
        try
          //ZWH 2006-01-06 modify
          Screen.Cursor := crSQLWait;
          Execute(sql);//执行
          Screen.Cursor := lCursor;
        except
          on E: Exception do
          begin
            ShowMessage(E.Message);
            Raise;
            Exit;
          end;
        end;
      finally
        Screen.Cursor := lCursor;
        end;
      end;

  end;
end;

procedure TForm1.btnexecutesqlClick(Sender: TObject);
begin
    ExecuteSql(edtSql.Text);//
end;

function TForm1.ExecutesqlReturnRecord(const sql :string):TADODataSet;
var
  lCursor: TCurSor;
begin
   Result := nil;
  try
    Result := TAdoDataset.Create(nil);
    Result.Connection := Form1.conConnectionSqlServer;
    Result.LockType := ltUnspecified;    //用来设置或取得Dataset打开时，记录如何锁定  1 ltUnspecified不指定锁定类型 ltReadOnly只读 ltBatchOptimistic 批量优化锁定....
    Result.CommandTimeout := 1800;
    if bDBisConnected then
    begin
      Result.Close;
      Result.CommandText := sql;
      lCursor := Screen.Cursor;
      Screen.Cursor := crSQLWait;
      try
        Result.Open;//Derived from TDataSet //open相当于将active属性设置为true,Dataset状态为打开，也将触发一些相应的事件 ，数据集状态为dsBrowse
        Screen.Cursor := crArrow;
      except
        on E: Exception do
        begin
          ShowMessage(E.Message);
          raise;
        end;
      end;
    end;
;
  except
    on E: Exception do
    begin
      ShowMessage(E.Message);
      raise;
    end;
  end;
end;
procedure TForm1.btnExecutesqlReturnSetClick(Sender: TObject);
var
  dsRecord:TADODataSet;
  i:Integer;
begin
  dsRecord := ExecutesqlReturnRecord(edtExecuteSql.Text);

  with dsRecord do
  begin
    First;
      for i:= 1 to RecordCount - 1 do
      begin
        mmoMessage1.Lines.Add(FieldByName('ID').AsString+  FieldByName('name').AsString+ FieldByName('score').AsString + #13#10);
        Next;
      end;
  end
end;

end.

继承关系

运行后