unit1.~pas

如何自由访问局域网中的所有 SQL Server 服务器的数据

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, OleCtrls, ComObj,SQLMERGXLib_TLB, StdCtrls, DB, ADODB, Buttons,
  DBTables, Grids, DBGrids, ComCtrls,StrUtils, ExtCtrls;

type
  TForm1 = class(TForm)
    adoqDataBae: TADOQuery;
    ADOConnection1: TADOConnection;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    TreeView1: TTreeView;
    ADOQuery1: TADOQuery;
    Splitter1: TSplitter;
    procedure FormCreate(Sender: TObject);
    procedure TreeView1Change(Sender: TObject; Node: TTreeNode);
  private
    { Private declarations }
  public
    { Public declarations }
  end;
type //保存信息的指针类形
  ppoint = ^pp;
  pp = string[50];
  
var
  Form1: TForm1;
  tablestring:ppoint;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
var
  SQLServer:Variant;   //用于创建SQL
  ServerList:Variant;     //存放sqlsever服务器名列表
  i,j:integer;
  thisnode1,thisnode2,thisnode3,thisnode4: ttreenode;//用于初始化treeview目录树
  SqlConnStr:string; //用于存放ado连接字符串
  SL: TStrings;
begin

  treeview1.Selected := treeview1.Items.Add(nil,'全部');//初始化初的结点即一级结点
  treeview1.Selected.ImageIndex := 0;
  treeview1.Selected.SelectedIndex := 1;
  thisnode1:=treeview1.selected;
  SQLServer := CreateOleObject('SQLDMO.Application');//创建sqldemo对象
  ServerList:= SQLServer.ListAvailableSQLServers; //读取服务器名列表


  if serverlist.count<1 then
    begin //如果服务器不存在，则退出系统
      showmessage('     服务器不存在，请检测服务器是否已经开机或者服务是否打开了。');
      application.Terminate;
    end
  else
    begin
      //初始化目录树的第二级节点
      for i:=1 to serverlist.count do
      begin
        thisnode2 := treeview1.Items.AddChild(thisnode1, serverlist.item(i));
        thisnode2.ImageIndex := 2;
        thisnode2.SelectedIndex := 3;
        adoqDataBae.Active :=false;
        SqlConnStr:='Provider=SQLOLEDB.1;'+'Integrated Security=SSPI;Persist Security Info=False;'
                            +';Data Source='+serverlist.item(i);
        adoqDataBae.ConnectionString:=SqlConnStr;
        with  adoqDataBae  do                                 //获得数据库名称列表
        begin
          //Active :=false;
          sql.Clear ;
          sql.Add('select name from master.dbo.sysdatabases');
          open;
        end;
        while  not  adoqDataBae.Eof  do                       //添加数据库列表进目录树的第三级节点
        begin
          thisnode3 := treeview1.Items.AddChild(thisnode2, adoqDataBae.fieldbyname('name').AsString );
          {with  adoqDataBae1  do                                 //获得数据库名称列表
          begin
            close;
            sql.Clear ;
            sql.text:=format('select * from %s',[cmbDataBase.Items.Strings[cmbDataBase.itemindex]+'.dbo.'+listbox1.Items.Strings[ListBox1.itemindex]]);
            open;
          end; }
          SL := TStringList.Create;  //创建字符串列表

          SqlConnStr:='Provider=SQLOLEDB.1;'+'Integrated Security=SSPI;'+'Persist Security Info=False;'+
         'User ID=sysdba;'+'Initial Catalog=hyshyzchgl;'+serverlist.item(i);
          ADOConnection1.Connected :=false;
          ADOConnection1.ConnectionString:=sqlconnstr;
          ADOConnection1.Connected :=true;
          ADOConnection1.DefaultDatabase :=adoqDataBae.fieldbyname('name').AsString;
          //ADOConnection1.Connected :=true;
          ADOConnection1.GetTableNames(SL, true);//读取数据库的数据表
          //初始化目录树的第四级节点
          for j:=0 to sl.count-1 do
          begin
            thisnode4 := treeview1.Items.AddChild(thisnode3, sl.strings[j]);
            new(tablestring);
            tablestring^:=serverlist.item(i)+':'+adoqDataBae.fieldbyname('name').AsString+':'+sl.strings[j];
            thisnode4.Data := tablestring;//在第四节点的数据指针上存放服务器名、数据库名、数据表名
          end;
          sl.Free;
          adoqDataBae.Next ;
       end;
    end;
  end;
end;

procedure TForm1.TreeView1Change(Sender: TObject; Node: TTreeNode);
var
  i:integer;
  string1,serverstring,dbasestring,tablestring:string;
  tnode1:ttreenode;
  SqlConnStr:string;
begin
  inherited;
   i:=0;
   tnode1:=TreeView1.Selected;
   while tnode1  <>nil do    //判定是否是选定的节点
   begin
      tnode1 :=tnode1.Parent ;
      i:=i+1
   end;
  if i=4 then   //读节点数据
   begin
    string1:=ppoint(TreeView1.Selected.Data )^;
    serverstring:=copy(string1,1,pos(':',string1)-1); //读取服务器名
    string1:=RightStr(string1,length(string1)-pos(':',string1));

    dbasestring:=copy(string1,1,pos(':',string1)-1);  //读取数据库名
    tablestring:=RightStr(string1,length(string1)-pos(':',string1));//读取数据表名
    SqlConnStr:='Provider=SQLOLEDB.1;'+'Integrated Security=SSPI;Persist Security Info=False;'
                            +';Data Source='+serverstring;
      ADOQuery1.ConnectionString:=SqlConnStr;
      //读取表内的数据
      with  ADOQuery1  do
        begin
          close;
          sql.Clear ;
          sql.text:=format('select * from %s',[dbasestring+'.dbo.'+tablestring]);
          open;
        end;
  end;
end;
end.