main1.pas

SQL远程管理程序的源码,DELPHI做的.不需要控件

unit main1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Menus, ImgList, ExtCtrls, ComCtrls, DB, ADODB, Grids, DBGridEh;

type
  TfrmMain1 = class(TForm)
    tvw: TTreeView;
    Splitter1: TSplitter;
    Panel1: TPanel;
    ImageList1: TImageList;
    ListView1: TListView;
    PopupMenu1: TPopupMenu;
    PopupMenu2: TPopupMenu;
    PopupMenu3: TPopupMenu;
    PopupMenu4: TPopupMenu;
    OpenTable1: TMenuItem;
    DropTable1: TMenuItem;
    OpenView1: TMenuItem;
    DropView1: TMenuItem;
    ViewSQL2: TMenuItem;
    DropProcedure1: TMenuItem;
    ViewSQL3: TMenuItem;
    DropFunction1: TMenuItem;
    Qry1: TADOQuery;
    Qry2: TADOQuery;
    procedure tvwChange(Sender: TObject; Node: TTreeNode);
    procedure PopupMenu1Popup(Sender: TObject);
    procedure PopupMenu2Popup(Sender: TObject);
    procedure PopupMenu3Popup(Sender: TObject);
    procedure PopupMenu4Popup(Sender: TObject);
    procedure OpenTable1Click(Sender: TObject);
    procedure DropTable1Click(Sender: TObject);
    procedure OpenView1Click(Sender: TObject);
    procedure DropView1Click(Sender: TObject);
    procedure DropProcedure1Click(Sender: TObject);
    procedure ViewSQL2Click(Sender: TObject);
    procedure DropFunction1Click(Sender: TObject);
    procedure ViewSQL3Click(Sender: TObject);
  private
    { Private declarations }

  public
    { Public declarations }
    procedure DrawTree;
  end;

var
  frmMain1: TfrmMain1;

implementation

uses Connect, ViewData, ViewSQL;

{$R *.dfm}
procedure OpenSQL(Aquery: TADOQuery; Command: string);
begin
  with AQuery do
  begin
    Close;
    SQL.Clear;
    SQL.Add(Command);
    Open;
  end;
end;

procedure ExecSQL(Aquery: TADOQuery; Command: string);
begin
  with AQuery do
  begin
    Close;
    SQL.Clear;
    SQL.Add(Command);
    ExecSQL;
  end;
end;

{ TfrmMain1 }

procedure TfrmMain1.DrawTree;
var
  q1,q2,q3: TADOQuery;
  n0, n1, n2, n3, n4, n5, n6: TTreeNode;
  db: string;
  c_db: string;
  srv: string;
begin
  try
  q1 := TADOQuery.Create(nil);
  q2 := TADOQuery.Create(nil);
  q3 := TADOQuery.Create(nil);
  db := Trim(frmConnect.Edit4.Text);
  try
    q1.Connection := frmConnect.ADOConnection;
    q2.Connection := frmConnect.ADOConnection;
    q2.Connection := frmConnect.ADOConnection;
    tvw.Items.Clear;
    if Trim(frmConnect.Edit1.Text) = '' then
      srv := 'Local Host'
    else
      srv := frmConnect.Edit1.Text;
    n0 := tvw.Items.Add(nil, frmConnect.Edit2.Text + '@' + srv);
    n0.ImageIndex := 6;
    n0.SelectedIndex := 6;
    OpenSQL(q1, 'SELECT name FROM MASTER.DBO.SYSDATABASES WHERE NAME LIKE' + QuotedStr('%'+db+'%'));
    while not q1.Eof do  //抓数据库
    begin
      c_db := q1.Fields[0].AsString;
      n1 := tvw.Items.AddChild(n0, c_db);  //抓DB
      n1.ImageIndex := 0;
      n1.SelectedIndex := 0;
      n2 := tvw.Items.AddChild(n1, 'Tables'); //表
      n2.ImageIndex := 1;
      n2.SelectedIndex := 1;
      n3 := tvw.Items.AddChild(n1, 'Views'); //视图
      n3.ImageIndex := 2;
      n3.SelectedIndex := 2;
      n4 := tvw.Items.AddChild(n1, 'Stored procedures');
      n4.ImageIndex := 9;
      n4.SelectedIndex := 9;
      n5 := tvw.Items.AddChild(n1, 'User defined functions');
      n5.ImageIndex := 18;
      n5.SelectedIndex := 18;
      Q1.Next;
    end;
    //tvw.FullExpand;
  finally
    q1.Free;
    q2.Free;
    q3.Free;
  end;
  except
  end;
end;

procedure TfrmMain1.tvwChange(Sender: TObject; Node: TTreeNode);
  procedure DrawListView(AQuery: TADOQuery);
  var
    AnItem: TListItem;
  begin
    ListView1.Items.Clear;
    while not Aquery.Eof do
    begin
      AnItem := ListView1.Items.Add;
      AnItem.Caption := AQuery.Fields[0].AsString;
      AnItem.SubItems.Add(AQuery.Fields[1].AsString);
      AQuery.Next;
    end;
  end;
var
  IIndex: integer;
  Lvl: integer;
  tmp: TADOQuery;
  DB: string;
begin
  tmp := TADOQuery.Create(nil);
  try
    tmp.Connection := frmConnect.ADOConnection;
    Lvl := Tvw.Selected.Level;

    if Lvl = 2 then
    begin
      db := Tvw.Selected.Parent.Text;
      ListView1.Visible := True;
      IIndex := Node.ImageIndex;
      case IIndex of
      1:  begin     //表
            ListView1.PopupMenu := PopupMenu1;
            OpenSQL(tmp, 'SELECT name, crdate FROM '+db+'..SYSOBJECTS WHERE TYPE=''S'' OR TYPE=''U'' '
              +' ORDER BY type desc, name asc');
            DrawListView(tmp);
          end;      //视图
      2:  begin
            ListView1.PopupMenu := PopupMenu2;
            OpenSQL(tmp, 'SELECT name, crdate FROM '+db+'..SYSOBJECTS WHERE TYPE=''V'' '
              +' ORDER BY type desc, name asc');
            DrawListView(tmp);
          end;
      9:  begin    //存储过程
            ListView1.PopupMenu := PopupMenu3;
            OpenSQL(tmp, 'SELECT name, crdate FROM '+db+'..SYSOBJECTS WHERE TYPE=''P'' '
              +' ORDER BY type desc, name asc');
            DrawListView(tmp);
          end;
      18: begin    //函数
            ListView1.PopupMenu := PopupMenu4;
            OpenSQL(tmp, 'SELECT name, crdate FROM '+db+'..SYSOBJECTS WHERE TYPE=''FN'' OR TYPE=''TF'' '
              +' ORDER BY type desc, name asc');
            DrawListView(tmp);
          end;
      end;
    end
    else
      ListView1.Visible := False;
  finally
    tmp.Free;
  end;
end;

procedure TfrmMain1.PopupMenu1Popup(Sender: TObject);
begin
  if ListView1.SelCount = 0 then
  begin
    OpenTable1.Enabled := False;
    DropTable1.Enabled := False;
  end else
  begin
    OpenTable1.Enabled := True;
    DropTable1.Enabled := True;
  end;
end;

procedure TfrmMain1.PopupMenu2Popup(Sender: TObject);
begin
  if ListView1.SelCount = 0 then
  begin
    OpenView1.Enabled := False;
    DropView1.Enabled := False;
  end else
  begin
    OpenView1.Enabled := True;
    DropView1.Enabled := True;
  end;
end;

procedure TfrmMain1.PopupMenu3Popup(Sender: TObject);
begin
  if ListView1.SelCount = 0 then
  begin
    ViewSQL2.Enabled := False;
    DropProcedure1.Enabled := False;
  end else
  begin
    ViewSQL2.Enabled := True;
    DropProcedure1.Enabled := True;
  end;
end;

procedure TfrmMain1.PopupMenu4Popup(Sender: TObject);
begin
  if ListView1.SelCount = 0 then
  begin
    ViewSQL3.Enabled := False;
    DropFunction1.Enabled := False;
  end else
  begin
    ViewSQL3.Enabled := True;
    DropFunction1.Enabled := True;
  end;
end;

procedure TfrmMain1.OpenTable1Click(Sender: TObject);
var
  DB, TBL: string;
  I: integer;
begin
  DB := Tvw.Selected.Parent.Text;
  TBL := '[' + ListView1.Selected.Caption + ']';
  with TfrmViewData.Create(Application) do
  begin
    Caption := 'View data of table: ' + DB + '.DBO.' + TBL;
    data.Active := False;
    data.CommandText := 'SELECT * FROM ' + DB + '..' + TBL;
    data.Active := True;
    for I := 0 to data.FieldCount - 1 do
    DBGridEh1.Columns[I].Width := 80;
  end;
end;

procedure TfrmMain1.DropTable1Click(Sender: TObject);
var
  DB, TBL: string;
begin
  DB := Tvw.Selected.Parent.Text;
  TBL := '[' + ListView1.Selected.Caption + ']';
  if Application.MessageBox(Pchar('Really drop table ' + ListView1.Selected.Caption), 'Confirm',
       MB_YESNO+MB_ICONQUESTION+MB_DEFBUTTON2) = IDYES then
  begin
    ExecSQL(Qry1, 'DROP TABLE ' + DB+'..'+TBL);
    ListView1.DeleteSelected;
  end;
end;

procedure TfrmMain1.OpenView1Click(Sender: TObject);
var
  DB, TBL: string;
  I: integer;
begin
  DB := Tvw.Selected.Parent.Text;
  TBL := '[' + ListView1.Selected.Caption + ']';
  with TfrmViewData.Create(Application) do
  begin
    Caption := 'View data of view: ' + DB + '.DBO.' + TBL;
    data.Active := False;
    data.CommandText := 'SELECT * FROM ' + DB + '..' + TBL;
    data.Active := True;
    for I := 0 to data.FieldCount - 1 do
    DBGridEh1.Columns[I].Width := 80;
  end;
end;

procedure TfrmMain1.DropView1Click(Sender: TObject);
var
  DB, TBL: string;
begin
  DB := Tvw.Selected.Parent.Text;
  TBL := '[' + ListView1.Selected.Caption + ']';
  if Application.MessageBox(Pchar('Really drop view ' + ListView1.Selected.Caption), 'Confirm',
       MB_YESNO+MB_ICONQUESTION+MB_DEFBUTTON2) = IDYES then
  begin
    ExecSQL(Qry1, 'DROP view ' + DB+'..'+TBL);
    ListView1.DeleteSelected;
  end;
end;

procedure TfrmMain1.DropProcedure1Click(Sender: TObject);
var
  DB, PROC: string;
begin
  DB := Tvw.Selected.Parent.Text;
  PROC := ListView1.Selected.Caption;
  ExecSQL(Qry2, 'USE ' + DB);
  if Application.MessageBox(Pchar('Really drop procedure: ' + PROC), 'Confirm'
    , MB_YESNO + MB_ICONQUESTION + MB_DEFBUTTON2) = IDYES then
    begin
      ExecSQL(Qry2, 'DROP PROC ' + PROC);
      ListView1.DeleteSelected;
    end;

end;

procedure TfrmMain1.ViewSQL2Click(Sender: TObject);
var
  DB, PROC: string;
begin
  DB := Tvw.Selected.Parent.Text;
  PROC := ListView1.Selected.Caption;
  ExecSQL(Qry2, 'USE ' + DB);
  with TfrmViewSQL.Create(Application) do
  begin
    OpenSQL(Qry2, 'SP_HELPTEXT ' + PROC);
    while not Qry2.Eof do
    begin
      Memo1.Text := Memo1.Text + Qry2.Fields[0].AsString;
      //Memo1.Lines.Add(Qry2.Fields[0].AsString);
      Qry2.Next;
    end;
    Caption := 'View SQL of Stored procedure: ' + PROC;
    Show;
  end;
end;

procedure TfrmMain1.DropFunction1Click(Sender: TObject);
var
  DB, PROC: string;
begin
  DB := Tvw.Selected.Parent.Text;
  PROC := ListView1.Selected.Caption;
  ExecSQL(Qry2, 'USE ' + DB);
  if Application.MessageBox(Pchar('Really drop Function: ' + PROC), 'Confirm'
    , MB_YESNO + MB_ICONQUESTION + MB_DEFBUTTON2) = IDYES then
    begin
      ExecSQL(Qry2, 'DROP PROC ' + PROC);
      ListView1.DeleteSelected;
    end;

end;

procedure TfrmMain1.ViewSQL3Click(Sender: TObject);
var
  DB, PROC: string;
begin
  DB := Tvw.Selected.Parent.Text;
  PROC := ListView1.Selected.Caption;
  ExecSQL(Qry2, 'USE ' + DB);
  with TfrmViewSQL.Create(Application) do
  begin
    OpenSQL(Qry2, 'SP_HELPTEXT ' + PROC);
    while not Qry2.Eof do
    begin
      Memo1.Text := Memo1.Text + Qry2.Fields[0].AsString;
      //Memo1.Lines.Add(Qry2.Fields[0].AsString);
      Qry2.Next;
    end;
    Caption := 'View SQL of Function: ' + PROC;
    Show;
  end;
end;

end.