unit1.pas

在自己的程序中完成SQK2K中的数据库的备份和恢复。虽然调用外部程序来完成

procedure TForm1.RTULVChange(Sender: TObject; Item: TListItem;
  Change: TItemChange);
begin
  if FetchingLV then Exit; //正在填充,不要处理

  if Item.SubItems[2]='' then //取表记录数
    Item.SubItems[2]:=IntToStr(RecCount(RTUDBName,Item.SubItems[1]));

end;

procedure TForm1.GetBakJobLstExecute(Sender: TObject);
var
  SR:TSearchRec;
begin
  BakJobLstBox.Clear;
  if not(DirectoryExists(BakDir)) then
  begin
    ForceDirectories(BakDir);
    BakJobLstBox.AddItem('( 备份不存在 )',nil);
    Exit;
  end;

  if FindFirst(BakDir+'\*.*', faDirectory, SR)<>0 then Exit; //出错,一个备份包都找不到
  repeat
    BakJobLstBox.Items.Add(SR.Name);
  until FindNext(SR) <> 0;
  FindClose(SR);

  //删去 . 和 .. 这两项
  BakJobLstBox.Items.Delete(0);
  BakJobLstBox.Items.Delete(0);

  if BakJobLstBox.Count=0 then
    BakJobLstBox.AddItem('( 备份不存在 )',nil);

end;

procedure TForm1.BakJobLstBoxClick(Sender: TObject);
begin
//  Caption:=BakJobLstBox.Items[BakJobLstBox.ItemIndex];
  ShowBakTblExecute(nil);
end;

procedure TForm1.BakRdoGrpClick(Sender: TObject);
begin
  //切换显示不同备份的LISTVIEW
  if BakRdoGrp.ItemIndex=0 then
  begin
    BakRTULV.Visible:=False;
    BakPVLV.Visible:=True;
  end
  else
  begin
    BakPVLV.Visible:=False;
    BakRTULV.Visible:=True;
  end;
end;

procedure TForm1.ShowBakTblExecute(Sender: TObject);
var
  SR:TSearchRec;
  JobName:string;
  PVFileLst,RTUFileLst:TStringList;
  PreFixLen,SufFixLen:Integer;
  I:Integer;
  TableCaptionLst:TStringList;
  FileHandle:HFILE;
  PVFileSizeLst,RTUFileSizeLst:FileSizeLst;
begin
//  Caption:=IntToStr(BakJobLstBox.ItemIndex);
  if BakJobLstBox.ItemIndex<0 then Exit; //无有效项目

  JobName:=BakJobLstBox.Items[BakJobLstBox.ItemIndex]; //根据项目名称取备份目录
  BakJobDir:=BakDir+'\'+JobName;

  PVFileLst:=TStringList.Create;
  RTUFileLst:=TStringList.Create;

  SufFixLen:=Length('.'+TblFileExtName); //文件名后缀长度

  //找出包里的PV库的表备份文件
  PreFixLen:=Length(PVDBName+'.'); //前缀长度
  I:=0;
  if FindFirst(BakJobDir+'\'+PVDBName+'*.'+TblFileExtName, faAnyFile, SR)=0 then //成功，逐个加
  begin
  repeat
    //取文件长度
    FileHandle:=CreateFile(PChar(BakJobDir+'\'+SR.Name),GENERIC_READ,0,
                                   nil,OPEN_EXISTING,FILE_ATTRIBUTE_NORMAL,0);

    PVFileSizeLst[I]:=GetFileSize(FileHandle,nil);
    Inc(I);
    CloseHandle(FileHandle);
    PVFileLst.Add(Copy(SR.Name,PreFixLen+1,Length(SR.Name)-PreFixLen-SufFixLen));//截取出有效表名
  until FindNext(SR) <> 0;
  FindClose(SR);
  end;

  //找出包里的RTU库的表备份文件
  PreFixLen:=Length(RTUDBName+'.'); //前缀长度
  I:=0;
  if FindFirst(BakJobDir+'\'+RTUDBName+'*.'+TblFileExtName, faAnyFile, SR)=0 then //成功，逐个加
  begin
    repeat
    //取文件长度
      FileHandle:=CreateFile(PChar(BakJobDir+'\'+SR.Name),GENERIC_READ,0,nil,OPEN_EXISTING,FILE_ATTRIBUTE_NORMAL,0);
      RTUFileSizeLst[I]:=GetFileSize(FileHandle,nil);
      Inc(I);
      CloseHandle(FileHandle);
      RTUFileLst.Add(Copy(SR.Name,PreFixLen+1,Length(SR.Name)-PreFixLen-SufFixLen));//截取出有效表名
    until FindNext(SR) <> 0;
    FindClose(SR);
  end;

  BakPVLV.Clear; //在LISTVIEW中显示
  if PVFileLst.Count>0 then
  begin
    TableCaptionLst:=GetTableCaption(PVFileLst,TStringList(PVTableNameMemo.Lines),TStringList(PVTableCaptionMemo.Lines));
    for I:=0 to PVFileLst.Count-1 do
    begin
      BakPVLV.AddItem(TableCaptionLst[I],nil);//表CAPTION
      BakPVLV.Items[I].SubItems.Add(''); //这栏不用
      BakPVLV.Items[I].SubItems.Add(PVFileLst[I]);//表NAME
      BakPVLV.Items[I].SubItems.Add(IntToStr(PVFileSizeLst[I]));//文件长度
    end;
  end;

  BakRTULV.Clear;//在LISTVIEW中显示
  TableCaptionLst:=GetTableCaption(RTUFileLst,TStringList(RTUTableNameMemo.Lines),TStringList(RTUTableCaptionMemo.Lines));
  if RTUFileLst.Count>0 then
  begin
    for I:=0 to RTUFileLst.Count-1 do
    begin
      BakRTULV.AddItem(TableCaptionLst[I],nil);//表CAPTION
      BakRTULV.Items[I].SubItems.Add(''); //这栏不用
      BakRTULV.Items[I].SubItems.Add(RTUFileLst[I]);//表NAME
      BakRTULV.Items[I].SubItems.Add(IntToStr(RTUFileSizeLst[I]));//文件长度
    end;
  end;

end;

//恢复处理
procedure TForm1.ProcessRestoreExecute(Sender: TObject);
var
  I:Integer;
  SumFileSize:Integer;
  TableName,FullName:string;
begin

  SumFileSize:=0;//统计记录数
  for I:=0 to BakPVLV.Items.Count-1 do
  begin
    if BakPVLV.Items[I].Checked then
      SumFileSize:=SumFileSize+StrToInt(BakPVLV.Items[I].SubItems[2]);
  end;

  for I:=0 to BakRTULV.Items.Count-1 do
  begin
    if BakRTULV.Items[I].Checked then
      SumFileSize:=SumFileSize+StrToInt(BakRTULV.Items[I].SubItems[2]);
  end;

  if SumFileSize=0 then //用户没有选表或表没有记录
  begin
    ShowMessage('需要恢复的数据长度为0');
    Exit;
  end;

  BcpOutputMemo.Clear;//清空BCP曾有的显示内容
  TabSheet3.TabVisible:=True;
  PageControl1.ActivePage:=TabSheet3;  //切换到BCP输出显示页

  //先处理PV
  EnableDataBaseConstraint(PVDBName,False); //禁用所有约束
  ProgressBar1.Max:=SumFileSize;
  for I:=0 to BakPVLV.Items.Count-1 do
  begin
    if BakPVLV.Items[I].Checked then
    begin
      TableName:=BakPVLV.Items[I].SubItems[1];
      FullName:=BakJobDir+'\'+PVDBName+'.'+TableName+'.'+TblFileExtName;

      ADOQuery1.Close;
      ADOQuery1.SQL.Text:='TRUNCATE TABLE '+TableName; //删除所有记录，以防不能启用主键约束
      ADOQuery1.Prepared:=True;
      ADOQuery1.ExecSQL;

      BcpRestore(PVDBName, TableName, '"'+FullName+'"'); //调用BCP

      ProgressBar1.Position:=ProgressBar1.Position+StrToInt(BakPVLV.Items[I].SubItems[2]);
      ProgressBar1.Refresh;
    end;
  end;
  EnableDataBaseConstraint(PVDBName,True); //启用所有约束


  //再处理RTU
  EnableDataBaseConstraint(RTUDBName,False); //禁用所有约束
  for I:=0 to BakRTULV.Items.Count-1 do
  begin
    if BakRTULV.Items[I].Checked then
    begin
      TableName:=BakRTULV.Items[I].SubItems[1];
      FullName:=BakJobDir+'\'+RTUDBName+'.'+TableName+'.'+TblFileExtName;

      ADOQuery1.Close;
      ADOQuery1.SQL.Text:='TRUNCATE TABLE '+TableName; //删除所有记录，以防不能启用主键约束
      ADOQuery1.Prepared:=True;
      ADOQuery1.ExecSQL;

      BcpRestore(RTUDBName, TableName, '"'+FullName+'"');

      ProgressBar1.Position:=ProgressBar1.Position+StrToInt(BakRTULV.Items[I].SubItems[2]);
      ProgressBar1.Refresh;
    end;
  end;
  EnableDataBaseConstraint(RTUDBName,True); //启用所有约束

    PageControl1.ActivePage:=TabSheet2; //回到本页
//  TabSheet3.TabVisible:=False; //隐藏BCP输出页
end;

{ TODO : bcp.exe是否已注册在SharedDlls中，如果是，将完整的文件名取出来。}
procedure TForm1.ValidateBCPExecute(Sender: TObject);
var
  Reg:TRegistry;
  ValueNameLst:TStringList;
  I:Integer;
begin
  { TODO : 存在[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\SharedDLLs]吗？ }
  Reg:=TRegistry.Create;
  Reg.RootKey:=HKEY_LOCAL_MACHINE;
  if not(Reg.OpenKeyReadOnly('\SOFTWARE\Microsoft\Windows\CurrentVersion\SharedDLLs')) then
  begin
    MessageDlg('因注册表读取操作失败，不能进行数据库维护。', mtError, [mbOK], 0);
    Close;
  end;

  { TODO : 逐个搜索，把BCP找出来 }
  ValueNameLst:=TStringList.Create;
  Reg.GetValueNames(ValueNameLst);
  for I:=0 to ValueNameLst.Count-1 do
  begin
    if LowerCase(RightStr(ValueNameLst[I],7))='bcp.exe' then //SQL的实用工具已安装
    begin
      BCPFullName:=ValueNameLst[I]; //复制路径
      Exit; //圆满，退出
    end;
  end;

  MessageDlg('SQL SERVER 2000的相关附件及实用工具没有安装完整，不能进行数据库维护。', mtError, [mbOK], 0);
  Close;

end;

procedure TForm1.BcpBackup(DBName, TblName, TblFilename: string);
var
  Parameter,QueryStr,WhereClause:string;
begin
  QueryStr:='select * from '+DBName+'..'+TblName;//查询的对像，
  WhereClause:=' where 1=1'; //过滤（功能扩展用）
  Parameter:=' "'+QueryStr+WhereClause+'"'; //完整的查询语名要用双引号括起来
  Parameter:=Parameter+' queryout '+TblFilename+' -S"." -T -n'; //指定为查询输出，本地主机，信任联接，原始数据格式
  BcpOutputMemo.Lines.Add('处理对像：［'+DBName+'..'+TblName+'］');
  RunBcp(Parameter);
end;


procedure TForm1.RunBcp(Parameter: string);
var
  hReadPipe, hWritePipe: THandle;
  si: STARTUPINFO;
  lsa: SECURITY_ATTRIBUTES;
  pi: PROCESS_INFORMATION;
//  mDosScreen: string;
  cchReadBuffer: DWORD;
  ph: PChar;
  fname: PChar;
//  i, j: integer;
begin
  fname := allocmem(255);
  ph := AllocMem(5000);
  lsa.nLength := sizeof(SECURITY_ATTRIBUTES);
  lsa.lpSecurityDescriptor := nil;
  lsa.bInheritHandle := True;

  if CreatePipe(hReadPipe, hWritePipe, @lsa, 0) = false then
  begin
    //Can not create pipe!
    MessageDlg('无法从操作系统获得所需资源', mtError, [mbOK], 0);
    Close;
  end;

  fillchar(si, sizeof(STARTUPINFO), 0);
  si.cb := sizeof(STARTUPINFO);
  si.dwFlags := (STARTF_USESTDHANDLES or STARTF_USESHOWWINDOW);
//  si.wShowWindow := SW_SHOW;
  si.wShowWindow:=SW_HIDE; //隐藏BCP的控制台窗口
  si.hStdOutput := hWritePipe; //使用已分配的管道HANDLE
  StrPCopy(fname, BCPFullName);

  if CreateProcess(fname, PChar(Parameter), nil, nil, true, 0, nil, nil, si, pi) = False then
  begin
    //ShowMessage('can not create process');
    MessageDlg('试图运行 BCP 时出错', mtError, [mbOK], 0);
    FreeMem(ph);
    FreeMem(fname);
    Close;
  end;

  while (true) do
  begin

    if not PeekNamedPipe(hReadPipe, ph, 1, @cchReadBuffer, nil, nil) then break;

    if cchReadBuffer <> 0 then
    begin
      if ReadFile(hReadPipe, ph^, 4096, cchReadBuffer, nil) = false then break;
      ph[cchReadbuffer] := chr(0);
      BcpOutputMemo.Lines.Add(ph);
    end

    else
      if (WaitForSingleObject(pi.hProcess, 0) = WAIT_OBJECT_0) then break; //BCP已结束
    Sleep(10);
  end;

    ph[cchReadBuffer] := chr(0);
    BcpOutputMemo.Lines.Add(ph);
    CloseHandle(hReadPipe);
    CloseHandle(pi.hThread);
    CloseHandle(pi.hProcess);
    CloseHandle(hWritePipe);
    FreeMem(ph);
    FreeMem(fname);


end;

procedure TForm1.BcpRestore(DBName, TblName, TblFilename: string);
var
  Parameter:string;
begin
  Parameter:=' "'+DBName+'..'+TblName+'"';//查询的对像，
  Parameter:=Parameter+' in '+TblFilename+' -E -S"." -T -n'; //指定为查询输出，本地主机，信任联接，原始数据格式
  BcpOutputMemo.Lines.Add('处理对像：［'+DBName+'..'+TblName+'］');
  RunBcp(Parameter);
end;

//切换数据库中所有的用户表的约束状态
procedure TForm1.EnableDataBaseConstraint(DBName: string; Enable: Boolean);
var
  TblLst:TStringList;
  I:Integer;
  SwitchClause:string;
begin

  TblLst:=TStringList.Create;
  ADOConnection1.DefaultDatabase:=DBName;
  ADOConnection1.GetTableNames(TblLst,False);

  if Enable then
    SwitchClause:=' CHECK CONSTRAINT ALL'
  else
    SwitchClause:=' NOCHECK CONSTRAINT ALL';

  for I:=0 to TblLst.Count-1 do
  begin
    ADOQuery1.Close;
    ADOQuery1.SQL.Text:='ALTER TABLE '+TblLst[I]+SwitchClause;
    ADOQuery1.Prepared:=True;
    ADOQuery1.ExecSQL;
  end;

  TblLst.Free;
end;

end.