unit1.pas

仿真SQL Server查询分析器

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, ExtCtrls, StdCtrls, Buttons, DB, ADODB;

type
  TForm1 = class(TForm)
    Panel1: TPanel;
    StatusBar1: TStatusBar;
    bbtnConn: TBitBtn;
    Label1: TLabel;
    EditIP: TEdit;
    Label2: TLabel;
    EditUser: TEdit;
    bbtnExit: TBitBtn;
    Panel2: TPanel;
    Label3: TLabel;
    EditPWD: TEdit;
    ADOConnGetDB: TADOConnection;
    Panel3: TPanel;
    Label5: TLabel;
    ADOQExecSQL: TADOQuery;
    Panel4: TPanel;
    Panel6: TPanel;
    Panel7: TPanel;
    Memo1: TMemo;
    bbtnOpen: TBitBtn;
    bbtnExecSQL: TBitBtn;
    Label4: TLabel;
    Edit1: TEdit;
    OpenDialog1: TOpenDialog;
    ADOQGetDB: TADOQuery;
    ADOConneExecSQL: TADOConnection;
    ListBox1: TListBox;
    bbtnClear: TBitBtn;
    Splitter1: TSplitter;
    procedure bbtnExitClick(Sender: TObject);
    procedure bbtnConnClick(Sender: TObject);
    procedure bbtnExecSQLClick(Sender: TObject);
    procedure ListBox1Click(Sender: TObject);
    procedure bbtnOpenClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure bbtnClearClick(Sender: TObject);
    procedure WriteLog(ErrStr:String);
    procedure ADOQExecSQLPostError(DataSet: TDataSet; E: EDatabaseError;
      var Action: TDataAction);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}
procedure TForm1.WriteLog(ErrStr:String);
var
  LogFilename: String;
  LogFile: TextFile;
begin
  LogFilename:=ExtractFilePath(ParamStr(0)) + 'LOG_' + FormatDateTime('yyyymmdd',Now) + '.LOG';
  AssignFile(LogFile, LogFilename);
  if FileExists(LogFilename) then Append(LogFile)
  else Rewrite(LogFile);
  Writeln(Logfile,DateTimeToStr(now)+': '+ErrStr);
  CloseFile(LogFile);
end;

procedure TForm1.bbtnExitClick(Sender: TObject);
begin
  Close;
end;

procedure TForm1.bbtnConnClick(Sender: TObject);
var
  i : integer;
begin
  try
  StatusBar1.Panels[0].Text := '';

  ListBox1.Clear;
  
  if ADOConnGetDB.Connected then
    ADOConnGetDB.Connected := False;

  ADOConnGetDB.ConnectionString := 'Provider=SQLOLEDB.1;Persist Security Info=True;;'+
                                   'Password=' + Trim(EditPWD.Text) +
                                   ';User ID=' + Trim(EditUser.Text) +
                                   //';Initial Catalog=' + Form1.database3 +
                                   ';Data Source=' + Trim(EditIP.Text);

  ADOConnGetDB.Connected := True;

  with ADOQGetDB do
    begin
      Close;
      SQL.Clear;
      SQL.Text := 'select name from master..sysdatabases order by name';
      Open;
    end;
    
  for i := 1 to ADOQGetDB.RecordCount do
    begin
      ListBox1.Items.Add(ADOQGetDB.FieldByName('name').AsString);
      ADOQGetDB.Next;
    end;
  StatusBar1.Panels[0].Text := '获得数据库列表!';
  except
    StatusBar1.Panels[0].Text := '获得数据库列表 失败!';
    MessageBox(Handle,Pchar('获得数据库列表 失败!'),'提示',MB_OK);
  end;
  {
  ADOConnXNHHos.ConnectionString      := 'Provider=SQLOLEDB.1;Persist Security Info=True;;Password=' + Form1.password3 +
  ';User ID=' + Form1.user3 +
  ';Initial Catalog=' + Form1.database3 +
  ';Data Source='+Form1.server3;
   }
end;

procedure TForm1.bbtnExecSQLClick(Sender: TObject);
var
  s:string;
  sqltext : string;
  sqlfile : TextFile;
begin
  {
  if ADOConneExecSQL.Connected then
    ADOConneExecSQL.Connected := False;

  ADOConneExecSQL.Connected := True;
  }

  if (Trim(Edit1.Text) = '') or (not FileExists(Trim(Edit1.Text))) then
    begin
      MessageBox(Handle,Pchar('请选择要执行的SQL脚本!'),'提示',MB_OK);
      Exit;
    end;

  if not ADOConneExecSQL.Connected then
    begin
      MessageBox(Handle,Pchar('请选择要连接的数据库!'),'提示',MB_OK);
      Exit;
    end;
  if Edit1.Text <> '' then
  begin
    //以下代码参考网上资源
    AssignFile(sqlfile, Edit1.Text);
    FileMode := 0;
    Reset(sqlfile);
    try
      ADOConneExecSQL.BeginTrans;
      while not eof(sqlfile) do
      begin
        Readln(sqlfile, s);
        sqltext := s;
        while (not eof(sqlfile)) and
         (uppercase(trim(s))<>'GO') do
        begin
          Readln(sqlfile, s);
          if (uppercase(trim(s))<>'GO') then
            sqltext := sqltext+' '+s;
        end;
        ADOQExecSQL.Close;
        ADOQExecSQL.SQL.Clear;
        ADOQExecSQL.SQL.Add(sqltext);
        ADOQExecSQL.ExecSQL;
      end;
      CloseFile(sqlfile);
      ADOConneExecSQL.CommitTrans;
      application.MessageBox('SQL脚本完成！','提示',MB_OK+MB_ICONINFORMATION);
    except
      CloseFile(sqlfile);
      raise exception.Create('SQL脚本执行失败!');
      ADOConneExecSQL.RollbackTrans;
    end;
  end;
end;

procedure TForm1.ListBox1Click(Sender: TObject);
var
  dbname : String;
begin
  if ListBox1.ItemIndex > -1 then
  begin
    dbname := ListBox1.Items[ListBox1.ItemIndex];
    try
    StatusBar1.Panels[0].Text := '';
    if ADOConneExecSQL.Connected then
      ADOConneExecSQL.Connected := False;

    ADOConneExecSQL.ConnectionString := 'Provider=SQLOLEDB.1;Persist Security Info=True;;'+
                                        'Password=' + Trim(EditPWD.Text) +
                                        ';User ID=' + Trim(EditUser.Text) +
                                        ';Initial Catalog=' + dbname +
                                        ';Data Source=' + Trim(EditIP.Text);
    ADOConneExecSQL.Connected := True;
    StatusBar1.Panels[0].Text := '连接数据库成功!';
    
    except
      StatusBar1.Panels[0].Text := '连接数据库 ' + dbname +' 失败!';
      MessageBox(Handle,Pchar('连接数据库 ' + dbname +' 失败!'),'提示',MB_OK);
    end
  end
end;

procedure TForm1.bbtnOpenClick(Sender: TObject);
var
  SQLFile : String;
begin
  if (OpenDialog1.Execute) and (OpenDialog1.FileName <> '') then
    begin
      SQLFile := Uppercase(OpenDialog1.FileName);

      if Copy(SQLFile,Length(SQLFile) - 4 + 1,4)  = '.SQL'then
        begin
          Edit1.Text := OpenDialog1.FileName;
          Memo1.Lines.LoadFromFile(OpenDialog1.FileName);
        end
      else
        begin
          MessageBox(Handle,Pchar('不是 SQL 文件!'),'提示',MB_OK);
          Edit1.Clear;
          Memo1.Clear;
          Exit;
        end
    end
  else
    begin
      Edit1.Clear;
      Memo1.Clear;
    end
end;

procedure TForm1.FormShow(Sender: TObject);
begin
  Form1.Caption := '执行SQL';
  Edit1.Clear;
  Memo1.Clear;
  EditPWD.Clear;
end;

procedure TForm1.bbtnClearClick(Sender: TObject);
begin
  EditIP.Clear;
  EditUser.Clear;
  EditPWD.Clear;
  Edit1.Clear;
  Memo1.Clear;
  ListBox1.Clear;
  StatusBar1.Panels[0].Text := '';
  
  if ADOConnGetDB.Connected then
    ADOConnGetDB.Connected := False;

  if ADOConneExecSQL.Connected then
    ADOConneExecSQL.Connected := False;

end;

procedure TForm1.ADOQExecSQLPostError(DataSet: TDataSet; E: EDatabaseError;
  var Action: TDataAction);
{var
  adoerrors:errors;
  adoerror:error;
  icount:integer;
}
begin
{  showmessage('aa');

  adoerrors := ADOConneExecSQL.Errors;
  for icount:=0 to adoerrors.count-1 do
    begin
      adoerror:=adoerrors.item[icount];

      // listbox1.Items.add('Error Number : ' + inttostr(adoerror.number));
      // listbox1.items.add('Error Source : ' + adoError.source);
      // listbox1.items.add('Error Description : '+ adoerror.description);
      // listbox1.items.add('Error HelpFile : ' + adoerror.helpfile);
      // listbox1.items.add('Error SWLState : ' + adoerror.sqlstate);
      // listbox1.Items.add('Error NativeError : '+ inttostr(adoerror.nativeError));

      WriteLog('Error Number : ' + inttostr(adoerror.number));
    end;
}
end;

end.