u_export.pas

orcl与sql的数据转移

unit U_Export;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, StdCtrls, Buttons, ADODB, DB,ShellAPI,jpeg,Registry,FileCtrl,
  ComCtrls, Grids, DBGrids, BaseGrid, AdvGrid, DBGridEh;

type
  TFrm_Export = class(TForm)
    Ads: TADODataSet;
    AdoCnn: TADOConnection;
    grp1: TGroupBox;
    pnl1: TPanel;
    lbl1: TLabel;
    lbl2: TLabel;
    lbl3: TLabel;
    lbl5: TLabel;
    Ed_ServerName: TEdit;
    Ed_UserName: TEdit;
    Ed_UserPass: TEdit;
    CB_Table: TComboBox;
    Btn_Test: TButton;
    Btn_Cancel: TButton;
    grp2: TGroupBox;
    pnl2: TPanel;
    lbl6: TLabel;
    lbl7: TLabel;
    lbl8: TLabel;
    lbl10: TLabel;
    Ed_DBName: TEdit;
    lbl4: TLabel;
    pnl3: TPanel;
    dbgrid: TDBGrid;
    DS: TDataSource;
    QY: TADOQuery;
    Ed_SName: TEdit;
    Ed_UName: TEdit;
    Ed_UPass: TEdit;
    Btn_Oracle: TButton;
    CB_OraTable: TComboBox;
    pnl4: TPanel;
    dbgrid_data: TDBGrid;
    SpdBtn_GetServerList: TSpeedButton;
    Btn_Del: TButton;
    Btn_Num: TButton;
    Btn_Chaxun: TButton;
    Btn_Send: TButton;
    Button1: TButton;
    Ed_Num: TEdit;
    Btn_Export: TButton;
    StrGrd_Data: TAdvStringGrid;
    Button2: TButton;
    Button4: TButton;
    procedure Btn_TestClick(Sender: TObject);
    procedure Btn_CancelClick(Sender: TObject);
    procedure CB_TableChange(Sender: TObject);
    procedure Btn_OracleClick(Sender: TObject);
    procedure CB_OraTableChange(Sender: TObject);
    procedure SpdBtn_GetServerListClick(Sender: TObject);
    procedure Btn_DelClick(Sender: TObject);
    procedure Btn_NumClick(Sender: TObject);
    procedure Btn_ChaxunClick(Sender: TObject);
    procedure Btn_SendClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Btn_ExportClick(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
  private
    { Private declarations }
    {sqlserver测试连接}
    function ConnectDB(DBConStr:string):Boolean;
     {取得SQlSERVER数据表列表}
    procedure GetTable;
    {取得Oracle数据表列表}
    procedure GetDataBase;
    //SQLSERVER通用查询语句
    procedure ExecSql1(Qy:TAdoQuery;aStr:String;Flag:Boolean);
  public
    { Public declarations }
    DBConStr: string;
    FMacineName: string;
    OMacineName: string;
    FDBName: string;
    FPwd: string;
    OPwd: string;
    FConnUser: string;
    OConnUser: string;
    isServer: Boolean;
    srcMdf:string;
    srcLdf:string;
    aStr:string;
  end;

var
  Frm_Export: TFrm_Export;

implementation
 uses  U_DM;
{$R *.dfm}
var
 _Msg:pChar;




//SQLSERVER通用查询语句
procedure TFrm_Export.ExecSql1(Qy:TAdoQuery;aStr:String;Flag:Boolean);
begin
  with Qy do
       begin
         Close;
         Sql.Clear;
         Sql.Add(aStr);
         Prepared;
         if Flag then
            Open
         else
           ExecSql;
       end;
end;

{sqlserver测试连接服务器}
function TFrm_Export.ConnectDB(DBConStr:string):Boolean;
begin
  try
    Adocnn.Connected:=False;
    Adocnn.ConnectionString:=DBConStr;
    Adocnn.Connected:=True;
    Result:=True;
  except
    Result:=False;
    Adocnn.Connected:=False;
  end;
end;



{取得数据表列表}
procedure TFrm_Export.GetTable;
begin
  Ads.Close;
  CB_Table.Clear;
  Ads.CommandText:='select   name   from   sysobjects   where   xtype='+'''u''';
  Ads.Open;
  While Not(Ads.Eof) Do
        Begin
          CB_Table.Items.Add(Ads.Fields[0].AsString);
          Ads.Next;
        End;
  if CB_Table.Items.Count>0 then
     CB_Table.ItemIndex:=0;
  Ads.Close;
end;

{取得数据表列表}
procedure TFrm_Export.GetDataBase;
begin
  dbgrid_Data.DataSource   :=   DM.DS1;
  DM.DS1.DataSet   :=   DM.QY1;
  DM.QY1.sql.text:=   'select   *   from  '+Trim(CB_OraTable.Text);
  DM.QY1.open;
end;

procedure TFrm_Export.Btn_TestClick(Sender: TObject);
begin
  if Trim(Ed_ServerName.Text)='' then
     begin
       _MSG:='请选择或输入SQL SERVER服务器名称';
       Application.MessageBox(_MSG,'系统提示',MB_OK+MB_ICONINFORMATION);
       Ed_ServerName.SetFocus;
       Exit;
     end;
  if Trim(Ed_UserName.Text)='' then
     begin
       _MSG:='请输入SQL SERVER用户名称，默认为sa';
       Application.MessageBox(_MSG,'系统提示',MB_OK+MB_ICONINFORMATION);
       Ed_UserName.SetFocus;
       Exit;
     end;
  FMacineName:=Ed_ServerName.Text;
  FDBName := Ed_DBName.Text;
  FConnUser := Ed_UserName.Text;
  FPwd := Trim(Ed_UserPass.Text);
  if FPwd = '' then
  //Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=ZhiNeng;Data Source=60.211.253.136
     DBConStr:='Provider=SQLOLEDB.1;Persist Security Info=False;' +
          'User ID=' + FConnUser + ';Initial Catalog=' + FDBName + ';Data Source=' + FMacineName
  else
     DBConStr :='Provider=SQLOLEDB.1;Password=' + FPwd + ';Persist Security Info=True;' +
            'User ID=' + FConnUser + ';Initial Catalog=' + FDBName + ';Data Source=' + FMacineName;
  Screen.Cursor:=crSQLWait;
  try
    if ConnectDB(DBConStr) then  //连接数据库成功则
       begin
          //Button4Click(Sender);
         _Msg:='恭喜，MS SQL Server服务器连接成功。';
         Application.MessageBox(_Msg,'信息提示',MB_OK+MB_ICONINFORMATION);
         GetTable;
       end
    else  //连接数据库不成功则
       Begin
         _Msg:='测试连接失败,请重新测试或选用其他的服务器';
         Application.MessageBox(_Msg,'信息提示',MB_OK+MB_ICONINFORMATION)
       End;
  finally
    Screen.Cursor:=crDefault;
  end;
end;

procedure TFrm_Export.Btn_CancelClick(Sender: TObject);
begin
  Close;
end;

procedure TFrm_Export.CB_TableChange(Sender: TObject);
begin
 { dbgrid.DataSource   :=   DS;
  DS.DataSet   :=   QY;
  QY.sql.text:=   'select   *   from  '+Trim(CB_Table.Text);
 // QY.sql.text:=   'select   *   from  oemrep.person';
  QY.open;       }

  Button2Click(Sender);
end;

procedure TFrm_Export.Btn_OracleClick(Sender: TObject);
begin
  if Trim(Ed_SName.Text)='' then
     begin
       _MSG:='请选择或输入Oracle服务器名称';
       Application.MessageBox(_MSG,'系统提示',MB_OK+MB_ICONINFORMATION);
       Ed_SName.SetFocus;
       Exit;
     end;
  if Trim(Ed_UName.Text)='' then
     begin
       _MSG:='请输入Oracle用户名称';
       Application.MessageBox(_MSG,'系统提示',MB_OK+MB_ICONINFORMATION);
       Ed_UName.SetFocus;
       Exit;
     end;
  OMacineName:=Trim(Ed_SName.Text);
  OConnUser := Trim(Ed_UName.Text);
  OPwd := Trim(Ed_UPass.Text);
  if OPwd = '' then
  //"Provider=OraOLEDB.Oracle;Data Source=oemrep;User ID=system;PASSWORD=manager;Persist Security Info=True"
   //DBConStr :='Provider=MSDAORA.1;Password=' + OPwd + ';Persist Security Info=True;' +
         //   'User ID=' + OConnUser + ';Initial Catalog=' + OConnUser + ';Data Source=' + OMacineName;
     ODBConStr:='Provider=OraOLEDB.Oracle;Persist Security Info=False;' +
          'User ID=' + OConnUser + ';Data Source=' + OMacineName
  else
     ODBConStr :='Provider=OraOLEDB.Oracle;Password=' + OPwd + ';Persist Security Info=True;' +
            'User ID=' + OConnUser + ';Data Source=' + OMacineName;
  Screen.Cursor:=crSQLWait;
  try
    if DM.OConnectDB(ODBConStr) then  //连接数据库成功则
       begin
         _Msg:='恭喜，Oracle服务器连接成功。';
         Application.MessageBox(_Msg,'信息提示',MB_OK+MB_ICONINFORMATION);
         //GetDataBase;
       end
    else  //连接数据库不成功则
       Begin