dts_cls.pas

来自「一个电力企业的后台管理程序」· PAS 代码 · 共 208 行

unit DTS_Cls;

interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Db, DBTables;

type DTS = class
private
 //m_sCondition,m_OTable,m_DTable: String;
 //m_bDelRec,m_bIsImport: Boolean;
 ODataBase: TDataBase;
 procedure ImportDB(OTableName,DTableName,IsDel,Cond:String;IsImport:boolean);
public
 SqlStr: String;
 m_arrItem: Array of array[0..3] of String;
 function ConDB(ServerName,DbName,UserName,Pws: String):Boolean;
 procedure GetTableName(IsOutPort: Boolean; List: TStrings);
 procedure GetFieldName(TableName : String ;List:TStrings);
 function GetSqlStr(OTableName,NTableName : String): String;
 procedure DoExe(IsImPort: Boolean);
 constructor Create;

end;
{ DTS }
implementation
uses SystemDM,SystemPH;

{ DTS }

procedure DTS.GetTableName(IsOutPort: Boolean; List: TStrings);
begin

 if IsOutPort then
  SysDM.DBMain.Session.GetTableNames(SysDM.DBMain.DatabaseName,'',False,False,List)
 else
  begin
   if ODataBase <> nil then
    ODataBase.Session.GetTableNames(ODataBase.DataBaseName,'',False,False,List)
   else
    begin
     Application.MessageBox('请先连接数据库！','信息提示',MB_OK+MB_ICONINFORMATION);
     Exit;
    end;
  end
end;

procedure DTS.GetFieldName(TableName : String;List: TStrings);
begin
 with TQuery.Create(nil)do
  try
   DataBaseName := SysDM.DBMain.DatabaseName;
   Sql.Text := Format('Select * from %s',[TableName]);
   Open;
   Fields.GetFieldNames(List);

  finally
   Free;
  end;
end;

function DTS.GetSqlStr(OTableName, NTableName: String): String;
var
 I: Integer;
 TypeStr,SqlStr : String;

begin
   SqlStr := Format('Create Table  %s(',[NTableName]);
   with TQuery.Create(nil)do
   try
    DataBaseName := SysDM.DBMain.DatabaseName;
    Sql.Text := Format('Select Top 1 * From %s',[OTableName]);
    Open;
    for I := 0 to Fields.Count - 1 do
     begin
      case Fields[I].DataType of
       ftBoolean : TypeStr := Fields[I].FullName +' Char(' + IntToStr(Fields[I].DataSize - 1)+') null';
       ftDate : TypeStr := Fields[I].FullName +' DateTime null';
       ftDateTime: TypeStr := Fields[I].FullName +' DateTime null';
       ftInteger : TypeStr := Fields[I].FullName +' Int null';
       ftSmallint : TypeStr := Fields[I].FullName +' Int null';
       ftFloat	: TypeStr := Fields[I].FullName +' Numeric(10,2) null';
       ftString : TypeStr := Fields[I].FullName +' Varchar('+IntToStr(Fields[I].DataSize - 1)+') null';
       ftUnknown : TypeStr := Fields[I].FullName +' Varchar(100) null';
       ftMemo : TypeStr := Fields[I].FullName +' Text null';
       ftGraphic :  TypeStr := Fields[I].FullName + 'image null';
      end;
      SqlStr := SqlStr + TypeStr + ',';
     end;
    SqlStr := Copy(SqlStr,1,Length(SqlStr) - 1);
    SqlStr := SqlStr + ')';
    Result := SqlStr;
   finally
    Free;
   end;
end;

constructor DTS.Create;
begin
 ODataBase := TDataBase.Create(nil);
end;

function DTS.ConDB(ServerName,DbName, UserName, Pws: String): Boolean;
begin

 try
  ODataBase.Close;
  ODataBase.AliasName := SystemConst_AliasName;//'Ecsc_Bak';
  ODataBase.DatabaseName := 'DbBak';
  ODataBase.Params.Clear;
  ODataBase.Params.Values['DATABASE NAME']:=DbName;
  ODataBase.Params.Values['SERVER NAME']:=ServerName;
  ODataBase.Params.Values['USER NAME']:=UserName;
  ODataBase.Params.Values['PASSWORD']:=Pws;
  ODataBase.LoginPrompt := False;
  ODataBase.Open;
  Result := ODataBase.Connected;
 except
  on E: Exception do
   begin
    ShowMessage(E.Message);
    Result := False;
   end;
 end;
end;

procedure DTS.ImportDB(OTableName, DTableName, IsDel, Cond: String;IsImport:boolean);
var
 OQuery,DeQuery: TQuery;
 DeTable: TTable;
 DbTr: TBatchMove;
 TableStr: String;
begin
 OQuery := TQuery.Create(nil);
 DeQuery := TQuery.Create(nil);
 DeTable := TTable.Create(nil);
 DbTr := TBatchMove.Create(nil);
 if not IsImport then
  begin
   try
     OQuery.DatabaseName := SysDM.DBMain.DatabaseName;
     if Cond <> '' then
      OQuery.SQL.Text := Format('Select * From %s where %s',[OTableName,Cond])
     else
      OQuery.SQL.Text := Format('Select * From %s',[OTableName]);
     OQuery.Open;
     TableStr := GetSqlStr(OTableName,DTableName);
     DeQuery.DatabaseName := ODataBase.DatabaseName;
     DeQuery.SQL.Text := TableStr;
     DeQuery.ExecSQL;
     DeTable.DatabaseName := ODataBase.DatabaseName;
     DeTable.TableName := DTableName;
     DeTable.Active := True;
     DbTr.Destination := DeTable;
     DbTr.Source := OQuery;
     DbTr.Execute ;
     if IsDel <> '' then
      begin
       OQuery.Close;
       if Cond <> '' then
        OQuery.SQL.Text := Format('Delete %s where %s',[OTableName,Cond])
       else
        OQuery.SQL.Text := Format('Delete %s',[OTableName]);
       OQuery.ExecSQL;
      end;
   finally
    OQuery.Free;
    DeQuery.Free;
    DeTable.Free;
    DbTr.Free;
   end;
  end
 else
  begin
   try
     OQuery.DatabaseName :=  ODataBase.DatabaseName;
     if Cond <> '' then
      OQuery.SQL.Text := Format('Select * From %s where %s',[OTableName,Cond])
     else
      OQuery.SQL.Text := Format('Select * From %s',[OTableName]);
     OQuery.Open;
     DeTable.DatabaseName := SysDM.DBMain.DatabaseName;
     DeTable.TableName := DTableName;
     DeTable.Active := True;
     DbTr.Destination := DeTable;
     DbTr.Source := OQuery;
     DbTr.Execute ;
   finally
    OQuery.Free;
    DeQuery.Free;
    DeTable.Free;
    DbTr.Free;
   end;
  end;
end;

procedure DTS.DoExe(IsImPort: Boolean);
var
 I: Integer;
begin
 For I := 0 To  High(m_arrItem) do
  begin
   ImportDB(m_arrItem[I,0],m_arrItem[I,1],m_arrItem[I,2],m_arrItem[I,3],IsImport);
  end;
end;

end.