unitmain.pas

数据倒入导出

unit UnitMain;

interface

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

type
  TFormData = class(TForm)
    Panel1: TPanel;
    BitBtn2: TBitBtn;
    Panel2: TPanel;
    GroupBoxRestort: TGroupBox;
    ShellTreeViewRestore: TShellTreeView;
    Panel3: TPanel;
    Label1: TLabel;
    Edit_DataServer: TEdit;
    Label2: TLabel;
    EditDataBaseUser: TEdit;
    Label3: TLabel;
    Edit_DataBasePass: TEdit;
    ADOConnection1: TADOConnection;
    BitBtnBZ: TBitBtn;
    ComboBoxDataBase: TComboBox;
    Label4: TLabel;
    GroupBox1: TGroupBox;
    CheckListBoxTable: TCheckListBox;
    BitBtnConn: TBitBtn;
    BitBtn1: TBitBtn;
    ButtonAllTable: TButton;
    ButtonNoTable: TButton;
    BitBtnDel: TBitBtn;
    procedure BitBtn2Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure BitBtnBZClick(Sender: TObject);
    procedure BitBtnConnClick(Sender: TObject);
    procedure CheckListBoxTableClickCheck(Sender: TObject);
    procedure ButtonAllTableClick(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure ButtonNoTableClick(Sender: TObject);
    procedure BitBtnDelClick(Sender: TObject);
    procedure ShellTreeViewRestoreChange(Sender: TObject; Node: TTreeNode);
  private
    { Private declarations }
    OptionMode :string;
    TableNames :TStringList;

  public
    { Public declarations }

    procedure SetOptionMode(setOption:string);
    procedure SetRestoreFilePath(SetReDataFile,SetReDataLogFile:string);
    function SetDataServerConnStr(DataBaseName:string):string;
    function CreateBcpStr(SqlStr,ServerName,ExportFile,DataMode,UserID,Pass: string):string;
    Function CreateDataBcpFile(ServarName,DataBaseName,DataMode:string;TableList:TStringList):string;
    function DelData(TableList:TStringList):integer;
    procedure OpenBcpFile(BcpFile:string);
    function SetDataBase(DataIndex:integer):string;
     
  end;

var
  FormData: TFormData;

implementation



{$R *.dfm}

{ TFormData }



procedure TFormData.BitBtn2Click(Sender: TObject);
begin
  close;
end;

procedure TFormData.SetOptionMode(setOption: string);
begin
  self.OptionMode:=setOption;
end;

procedure TFormData.FormCreate(Sender: TObject);
begin
  self.OptionMode:='out';
end;

procedure TFormData.FormShow(Sender: TObject);
begin
  //ConnString:=MainForm.GetConnString;
  TableNames:=TstringList.Create;
  {case OptionMode  of
    0:begin
        self.Caption:='导入数据';
        self.GroupBoxRestort.Visible:=false;

      end;
    1:begin
        self.Caption:='导出数据';
        self.GroupBoxRestort.Visible:=true;

      end;
  end;      }
end;

procedure TFormData.SetRestoreFilePath(SetReDataFile,
  SetReDataLogFile: string);
begin

end;

function TFormData.SetDataServerConnStr(DataBaseName:string): string;
var
   ConnStr:string;
   ServerName ,DataBaseUser,DataBassPass:string;
begin
   ServerName:=self.Edit_DataServer.Text;
   DataBaseUser:=self.EditDataBaseUser.Text;
   DataBassPass:=self.Edit_DataBasePass.Text;
 
   if DataBassPass='' then
   begin
      ConnStr:='Provider=SQLOLEDB.1;Persist Security Info=False;User ID='+DataBaseUser+';Initial Catalog='+DataBaseName+';Data Source='+ServerName;
   end else
   begin
      ConnStr:=' Provider=SQLOLEDB.1;Password='+DataBassPass+';Persist Security Info=True;User ID='+DataBaseUser+';Initial Catalog='+DataBaseName+';Data Source='+ServerName;
   end;
   result:=ConnStr;
end;

procedure TFormData.FormClose(Sender: TObject; var Action: TCloseAction);
begin
   TableNames.Free;
end;
function TFormData.CreateBcpStr(SqlStr,ServerName,ExportFile,DataMode,UserID,Pass: string):string;
var BcpStr:string;
begin
   SqlStr:='Select * from jwbz.dbo.t_jwbzUnitCode';
   BcpStr:=' bcp "'+SqlStr+'"  '+DataMode+' "'+ExportFile+'" -c -q  -S"'+ServerName+'" -U"'+UserID+'" -P"'+Pass+'" ';
   result:=BcpStr;
end;
function TFormData.CreateDataBcpFile(ServarName,DataBaseName,DataMode: string;TableList:TStringList): string;
var BcpFileName:string;
   DataFileName:string;
   TableName:string;
   AppPath,DataFilePath:string;
   BcpStrList:TStringList;
   i:integer;
   BcpFileHandle:Integer;
   BcpExeStr:String;
begin 
   BcpStrList:=TstringList.Create;
   
   BcpStrList.Clear;

   AppPath:=extractfilepath(Application.ExeName);
   DataFilePath:=self.ShellTreeViewRestore.Path;
   if Copy(AppPath,Length(AppPath),1)='\' then
   begin
      BcpFileName:=AppPath+'Bcp'+DataBaseName+'.bat';
   end
   else
   begin
      BcpFileName:=AppPath+'Bcp\'+DataBaseName+'.bat';
   end ;
   for i:=0 to TableList.Count-1 do
   begin
      TableName:=TableList.Strings[i];
      if Copy(AppPath,Length(DataFilePath),1)='\' then
      begin
         DataFileName:=DataFilePath+'Export'+TableName+'.txt';
      end
      else
      begin
         DataFileName:=DataFilePath+'\Export'+TableName+'.txt';
      end;
      BcpExeStr:=CreateBcpStr(DataBaseName+'.dbo.'+TableName,ServarName,DataFileName,DataMode,self.EditDataBaseUser.Text,self.Edit_DataBasePass.Text);
      BcpStrList.Append(BcpExeStr);
   end;
   BcpStrList.SaveToFile(BcpFileName);
   BcpStrList.Free;
   result:= BcpFileName;
end;

procedure TFormData.BitBtnBZClick(Sender: TObject);
var
    BcpFileName:string;
begin
   if self.Edit_DataServer.Text<>'' then
   begin
      BcpFileName:=self.CreateDataBcpFile(self.Edit_DataServer.Text,SetDataBase(self.ComboBoxDataBase.ItemIndex),'out',TableNames);
      //if OptionMode='in' then
      //DelData(TableNames);
      OpenBcpFile(BcpFileName);
      Showmessage('导出数据完毕');
   end else ShowMessage('需要服务器名称！');
end;

function TFormData.DelData(TableList: TStringList): integer;
var
   AdoDel:TADOQuery;
   SqlStr:string;
   i:integer;
begin
   for i:=0 to TableList.Count-1 do
   begin
      SqlStr:=SqlStr+'  delete from '+TableList.Strings[i];
   end;
   AdoDel:=TADOQuery.Create(self);
   AdoDel.Connection:=self.ADOConnection1;
   AdoDel.SQL.Add(SqlStr);
   result:=AdoDel.ExecSQL;
   AdoDel.Free;
end;

procedure TFormData.OpenBcpFile(BcpFile: string);
begin
   ShellExecute( Handle, 'open', PChar(BcpFile), nil, nil,SW_SHOWMAXIMIZED);
   //deleteFile(BcpFile);
end;

procedure TFormData.BitBtnConnClick(Sender: TObject);
begin
   if self.Edit_DataServer.text<>'' then
   begin
      self.ADOConnection1.Close;
      self.ADOConnection1.ConnectionString:=self.SetDataServerConnStr(SetDataBase(self.ComboBoxDataBase.ItemIndex));
      self.ADOConnection1.Open;
      ADOConnection1.GetTableNames(self.CheckListBoxTable.Items);
      TableNames.Clear;
      self.ButtonAllTable.OnClick(Sender);
   end else ShowMessage('需要服务器名称！');
end;

function TFormData.SetDataBase(DataIndex: integer): string;
var DataBaseName:string;
begin
   case DataIndex of
      0:  DataBaseName:='jwbz';
      1:  DataBaseName:='jwbyb';
      2:  DataBaseName:='jwbyw';
      3:  DataBaseName:='jwdw';
      4:  DataBaseName:='jwsl';
      5:  DataBaseName:='jwxt';
   end;
   result:=DataBaseName;
end;

procedure TFormData.CheckListBoxTableClickCheck(Sender: TObject);
var TableName :string;
begin
    TableName:=self.CheckListBoxTable.Items.Strings[self.CheckListBoxTable.ItemIndex];
   if self.CheckListBoxTable.Checked[self.CheckListBoxTable.ItemIndex] then
   begin
      TableNames.Append(TableName);
   end  else
   begin
      if TableNames.IndexOf(TableName)>-1 then
      begin
         TableNames.Delete(TableNames.IndexOf(TableName));
      end;
   end;
end;

procedure TFormData.ButtonAllTableClick(Sender: TObject);
var
   i:integer;
begin
   TableNames.Clear;
   for i:=0 to CheckListBoxTable.Items.Count-1 do
   begin
      self.CheckListBoxTable.Checked[i]:=true;
      TableNames.Append(CheckListBoxTable.Items.Strings[i]);
   end
end;

procedure TFormData.BitBtn1Click(Sender: TObject);
var
    BcpFileName:string;
begin
  if self.Edit_DataServer.Text<>'' then
   begin
   if Application.MessageBox('导入数据将覆盖数据库的现有数据！','提示',1)=IDOK	 then
   begin
   BcpFileName:=self.CreateDataBcpFile(self.Edit_DataServer.Text,SetDataBase(self.ComboBoxDataBase.ItemIndex),'in',TableNames);
   //if OptionMode='in' then
      DelData(TableNames);
      OpenBcpFile(BcpFileName);
      Showmessage('导入数据完毕');
   end;
   end else ShowMessage('需要服务器名称！');
end;

procedure TFormData.ButtonNoTableClick(Sender: TObject);
var
   i:integer;
begin
   TableNames.Clear;
   for i:=0 to CheckListBoxTable.Items.Count-1 do
   begin
      self.CheckListBoxTable.Checked[i]:=false;
   end
end;

procedure TFormData.BitBtnDelClick(Sender: TObject);
begin
   if self.Edit_DataServer.Text<>'' then
   begin
      if DelData(TableNames)>-1 then ShowMessage('删除成功');
   end else  ShowMessage('需要服务器名称！');
   
end;

procedure TFormData.ShellTreeViewRestoreChange(Sender: TObject;
  Node: TTreeNode);
begin
   self.GroupBoxRestort.Caption:='数据文件位置:'+self.ShellTreeViewRestore.Path;
end;

end.