fromtools.pas

批量修改约束关系, 只对sql server

UNIT FromTools;

INTERFACE

USES
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, DM,
  Dialogs, Menus, RzCommon, PrnDbgeh, ImgList, RzSplit, GridsEh, DBGridEh, RzPrgres,
  RzStatus, RzPanel, RzButton, ExtCtrls, DB, ADODB, PropFilerEh, FormBase;

TYPE
  TToolsFrom=CLASS(TBaseFrom)
    dsTable:TDataSource;
    dsDB:TDataSource;
    aqTable:TADOQuery;
    aqDB:TADOQuery;
    RzPanel1:TRzPanel;
    dsFK:TDataSource;
    GridDB:TDBGridEh;
    RzSizePanel1:TRzSizePanel;
    GridTable:TDBGridEh;
    GridFK:TDBGridEh;
    aqFK:TADOQuery;
    PROCEDURE FormCreate(Sender:TObject);
    PROCEDURE FormShow(Sender:TObject);
    PROCEDURE GridTableEnter(Sender:TObject);
    PROCEDURE GridTableExit(Sender:TObject);
    PROCEDURE GridFKEnter(Sender:TObject);
    PROCEDURE GridDBEnter(Sender:TObject);
    PROCEDURE GridDBDblClick(Sender:TObject);
    PROCEDURE GridTableDblClick(Sender:TObject);
    PROCEDURE BtnDeleteRecordClick(Sender:TObject);
    PROCEDURE BtnInsertRecordClick(Sender:TObject);
  PRIVATE
    { Private declarations }
    FCurOldInvCode, FCurNewInvCode:STRING; //业务功能
    sTempTable:STRING;
    SelectInvCCode:STRING;
    InvXHCount:integer;
    sDbName:STRING;
    sTableName:STRING;
    PROCEDURE SetBtnStat;
  PUBLIC
    { Public declarations }
  END;

VAR
  ToolsFrom:TToolsFrom;

CONST
  bLogTimeMark:Boolean=true;

IMPLEMENTATION

USES ADOFuncs, xfuncs, xfunc, PublicFunction;

{$R *.dfm}

{ TToolsFrom }

PROCEDURE TToolsFrom.FormShow(Sender:TObject);
VAR sSQL:STRING;
BEGIN
  FFormName:=Self.Name;
  sSQL:='SELECT [dbid], [name], [filename] '
    +' FROM [master].[dbo].[sysdatabases] '
    +' where name not in(''master'', ''model'', ''msdb'', ''tempdb'') '
    +' order by [name] ';
  OpenQuery(aqDB, sSQL);
  GridDBEnter(NIL);
END;

PROCEDURE TToolsFrom.SetBtnStat;
VAR bActive:Boolean;
BEGIN

END;

PROCEDURE TToolsFrom.GridTableEnter(Sender:TObject);
BEGIN
  INHERITED;
  AGrid:=GridTable;
  AData:=AGrid.DataSource.DataSet;
  Self.InitShow;

  IF AData<>NIL THEN
    TCustomADODataSet(AData).Requery();
END;

PROCEDURE TToolsFrom.FormCreate(Sender:TObject);
VAR FList:TStrings;
BEGIN
  INHERITED;
  sIniFile:=ChangeFileExt(ParamStr(0), '.ini');
  IF FileExists(sIniFile) THEN
  BEGIN
    FList:=TStringList.Create;
    TRY
      Application.Title:=FList.Values['caption'];
    FINALLY
      FList.Free;
    END;
  END;

  PBar.Visible:=false;
  Self.Caption:=Application.Title;
  //Application.OnException:=ErrorProc;
END;

PROCEDURE TToolsFrom.GridTableExit(Sender:TObject);
BEGIN
  INHERITED;
  Self.InitClose;
END;

PROCEDURE TToolsFrom.GridFKEnter(Sender:TObject);
BEGIN
  INHERITED;
  AGrid:=GridFK;
  AData:=AGrid.DataSource.DataSet;
  Self.InitShow;
END;

PROCEDURE TToolsFrom.GridDBEnter(Sender:TObject);
BEGIN
  INHERITED;
  AGrid:=GridDB;
  AData:=AGrid.DataSource.DataSet;
  Self.InitShow;
END;

PROCEDURE TToolsFrom.GridDBDblClick(Sender:TObject);
VAR sSQL:STRING;
BEGIN
  INHERITED;
  sDbName:=GridDB.DataSource.DataSet.FieldByName('name').AsString;
  ShowInfo(Stat1, sDbName);
  IF sDbName='' THEN exit;

  FDM.DBConn.Execute('use '+sDbName);

  sSQL:='SELECT [id], [name], [xtype] FROM [sysobjects] '
    +' where xtype in (''U'')'
    +' order by H1.[name] ';
  OpenQuery(aqTable, sSQL);
  GridTableEnter(NIL);
END;

PROCEDURE TToolsFrom.GridTableDblClick(Sender:TObject);
VAR sSQL:STRING;
BEGIN
  INHERITED;
  sTableName:=GridTable.DataSource.DataSet.FieldByName('name').AsString;
  ShowInfo(Stat2, sTableName);
  IF sTableName='' THEN exit;

  sSQL:=' select H1.[name] as fkname, H2.[name] as rtblName '
    +' from (SELECT * FROM [sysobjects] A, '
    +'          (SELECT * FROM [sysforeignkeys] '
    +'           where [rkeyid]=(SELECT [id] FROM sysobjects WHERE [name]='+QuotedStr(sTableName)+')) B '
    +'       where A.[id]=B.[constid])H1, '
    +'      (select * from sysobjects C, '
    +'          (SELECT * FROM [sysreferences] '
    +'           where rkeyid=(SELECT [id] FROM sysobjects WHERE [name]='+QuotedStr(sTableName)+')) D '
    +'       where C.[id]=D.[fkeyid])H2 '
    +' where H1.[constid]=H2.[constid] ';
  OpenQuery(aqFK, sSQL);
  GridFKEnter(NIL);
END;

PROCEDURE TToolsFrom.BtnDeleteRecordClick(Sender:TObject);
BEGIN
    IF MyConfirmation('真的要禁用表：'+sTableName+'的约束关系吗？') THEN
  BEGIN
    FDM.EnableInvFKs(sTableName, false);
    MyInformation('完成：'+sTableName);
  END;
END;

PROCEDURE TToolsFrom.BtnInsertRecordClick(Sender:TObject);
BEGIN
  IF MyConfirmation('真的要启用表：'+sTableName+'的约束关系吗？') THEN
  BEGIN
    FDM.EnableInvFKs(sTableName, true);
    MyInformation('完成：'+sTableName);
  END;
END;

END.