dbconverter.pas

不同数据库的转换 用delphi实现access excel sql server 等数据库的转换

        //连接到源Access
        FConnectString := ReplaceAccessConnectStr(FSourceDBPara.AccessPara);
        FConnectTargetString := ReplaceFoxproConnectStr(FTargetDBPara.FoxproPara);

        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable;
        FExecuteSQLString := FExecuteSQLString + ' IN ''' + FTargetDBPara.FoxproPara.Path + ''' ''dbase 5.0;''';
        FExecuteSQLString := FExecuteSQLString + ' From ' + FConvertTablePara.SourceTable;
      end;
    dbParadox: begin //Access-->Paradox        -------------------->测试通过
        //连接到源Access
        FConnectString := ReplaceAccessConnectStr(FSourceDBPara.AccessPara);
        FConnectTargetString := ReplaceParadoxConnectStr(FTargetDBPara.ParadoxPara);

        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable;
        FExecuteSQLString := FExecuteSQLString + ' IN ''' + FTargetDBPara.ParadoxPara.Path + ''' ''Paradox 5.x;''';
        FExecuteSQLString := FExecuteSQLString + ' From ' + FConvertTablePara.SourceTable;
      end;
    dbSqlServer: begin //Access-->SqlServer     -------------------->测试通过
        //连接到源Access
        FConnectString := ReplaceAccessConnectStr(FSourceDBPara.AccessPara);
        FConnectTargetString := ReplaceSqlServerConnectStr(FTargetDBPara.SqlServerPara); //目标数据库连接串

        S := ReplaceSqlServerODBCStr(FTargetDBPara.SqlServerPara);
        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable + ' ';
        FExecuteSQLString := FExecuteSQLString + S + ' FROM ' + FConvertTablePara.SourceTable;
      end;

  end;

  StartConvert;
end;

procedure TDBConverter.ConvertExcel;
var
  S: string;
begin
  {
  Foxpro,Paradox均不支持类似Access中的SQL语句中的FRom部分的格式
  }
  case FTargetDBPara.DBType of
    dbAccess: begin //Excel-->Access         ----------->测试通过
        //连接到目标Access
        FConnectString := ReplaceAccessConnectStr(FTargetDBPara.AccessPara);
        FConnectTargetString := FConnectString;

        FExecuteSQLString := 'Select * into ' + FConvertTablePara.TargetTable;
        FExecuteSQLString := FExecuteSQLString + ' from [Excel 8.0;database=' + FSourceDBPara.ExcelPara.Path;
        FExecuteSQLString := FExecuteSQLString + '].' + FConvertTablePara.SourceTable;
      end;
    dbFoxpro: begin //Excel-->Foxpro     ------------------>测试通过
        //连接到源Excel
        FConnectString := ReplaceExcelConnectStr(FSourceDBPara.ExcelPara);
        FConnectTargetString := ReplaceFoxproConnectStr(FTargetDBPara.FoxproPara);

        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable;
        FExecuteSQLString := FExecuteSQLString + ' IN ''' + FTargetDBPara.FoxproPara.Path + ''' ''dbase 5.0;''';
        FExecuteSQLString := FExecuteSQLString + ' FROM ' + FConvertTablePara.SourceTable;
      end;
    dbParadox: begin //Excel-->Paradox           ----------------->测试通过
        //连接到源Excel
        FConnectString := ReplaceExcelConnectStr(FSourceDBPara.ExcelPara);
        FConnectTargetString := ReplaceParadoxConnectStr(FTargetDBPara.ParadoxPara);

        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable;
        FExecuteSQLString := FExecuteSQLString + ' IN ''' + FTargetDBPara.ParadoxPara.Path + ''' ''Paradox 5.x;''';
        FExecuteSQLString := FExecuteSQLString + ' FROM ' + FConvertTablePara.SourceTable;
      end;
    dbSqlServer: begin //Excel-->SqlServer        ------------------>测试成功
        //连接到源Excel
        FConnectString := ReplaceExcelConnectStr(FSourceDBPara.ExcelPara);
        FConnectTargetString := ReplaceSqlServerConnectStr(FTargetDBPara.SqlServerPara);

        S := ReplaceSqlServerODBCStr(FTargetDBPara.SqlServerPara);
        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable + ' ';
        FExecuteSQLString := FExecuteSQLString + S + ' FROM ' + FConvertTablePara.SourceTable;
      end;

  end;

   StartConvert;
end;

procedure TDBConverter.ConvertFoxpro; //Foxpro支持的SQL非常有限，因此一般不连接DBF
var
  S: string;
begin
  case FTargetDBPara.DBType of
    dbAccess: begin //Foxpro-->access       ---------------->测试通过
        //连接到目标Access
        FConnectString := ReplaceAccessConnectStr(FTargetDBPara.AccessPara);
        FConnectTargetString := FConnectString;

        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable;
        FExecuteSQLString := FExecuteSQLString + ' From ' + FConvertTablePara.SourceTable;
        FExecuteSQLString := FExecuteSQLString + ' IN ''' + FSourceDBPara.FoxproPara.Path + ''' ''dbase 5.0;''';
      end;
    dbExcel: begin //Foxpro-->Excel             ---------------->测试通过
        //连接到目标Excel,虽然目标Excel不存在，但执行仍会通过，会自动创建Excel
        FConnectString := ReplaceExcelConnectStr(FTargetDBPara.ExcelPara);
        //目标Excel不使用FConnectTargetString

        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable;
        FExecuteSQLString := FExecuteSQLString + ' FROM ' + FConvertTablePara.SourceTable + ' IN ''';
        FExecuteSQLString := FExecuteSQLString + FSourceDBPara.FoxproPara.Path + ''' ''dbase 5.0;''';
      end;
    dbSqlServer: begin //Foxpro-->SqlServer          ------------------->测试成功
         //连接到源Foxpro
        FConnectString := ReplaceFoxproConnectStr(FSourceDBPara.FoxproPara);
        FConnectTargetString := ReplaceSqlServerConnectStr(FTargetDBPara.SqlServerPara); //目标数据库连接串

        S := ReplaceSqlServerODBCStr(FTargetDBPara.SqlServerPara);
        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable + ' ';
        FExecuteSQLString := FExecuteSQLString + S + ' FROM ' + FConvertTablePara.SourceTable;
      end;

  end;

  StartConvert;
end;

procedure TDBConverter.ConvertParadox;
var
  S: string;
begin
  case FTargetDBPara.DBType of
    dbAccess: begin //Paradox-->Access       ---------------->测试通过
        //连接到目标Access
        FConnectString := ReplaceAccessConnectStr(FTargetDBPara.AccessPara);
        FConnectTargetString := FConnectString;

        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable;
        FExecuteSQLString := FExecuteSQLString + ' From ' + FConvertTablePara.SourceTable;
        FExecuteSQLString := FExecuteSQLString + ' IN ''' + FSourceDBPara.ParadoxPara.Path + ''' ''Paradox 5.x;''';
      end;
    dbExcel: begin //Paradox-->Excel         --------------------->测试通过
          //连接到目标Excel,虽然目标Excel不存在，但执行仍会通过，会自动创建Excel
        FConnectString := ReplaceExcelConnectStr(FTargetDBPara.ExcelPara);
        //目标Excel不使用FConnectTargetString

        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable;
        FExecuteSQLString := FExecuteSQLString + ' FROM ' + FConvertTablePara.SourceTable + ' IN ''';
        FExecuteSQLString := FExecuteSQLString + FSourceDBPara.ParadoxPara.Path + ''' ''Paradox 5.x;''';
      end;
    dbSqlServer: begin //Paradox-->SqlServer       -----------------测试通过
        //连接到源Paradox
        FConnectString := ReplaceParadoxConnectStr(FSourceDBPara.ParadoxPara);
        FConnectTargetString := ReplaceSqlServerConnectStr(FTargetDBPara.SqlServerPara); //目标数据库连接串

        S := ReplaceSqlServerODBCStr(FTargetDBPara.SqlServerPara);
        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable + ' ';
        FExecuteSQLString := FExecuteSQLString + S + ' From ' + FConvertTablePara.SourceTable;
      end;

  end;

  StartConvert;
end;

procedure TDBConverter.ConvertSqlServer;
var
  S: string;
begin
  case FTargetDBPara.DBType of
    dbAccess: begin //SqlServer-->Access    ------------------->测试通过
        //连接到目标Access
        FConnectString := ReplaceAccessConnectStr(FTargetDBPara.AccessPara);
        FConnectTargetString := FConnectString;

        S := ReplaceSqlServerODBCStr(FSourceDBPara.SqlServerPara);
        FExecuteSQLString := 'Select * into ' + FConvertTablePara.TargetTable;
        FExecuteSQLString := FExecuteSQLString + ' from ' + FConvertTablePara.SourceTable + ' ';
        FExecuteSQLString := FExecuteSQLString + S;
      end;
    dbExcel: begin //SqlServer-->Excel    ------------------->测试通过
        //连接到目标excel
        FConnectString := ReplaceExcelConnectStr(FTargetDBPara.ExcelPara);

        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable;
        FExecuteSQLString := FExecuteSQLString + ' FROM ' + FConvertTablePara.SourceTable + ' ';
        FExecuteSQLString := FExecuteSQLString + ReplaceSqlServerODBCStr(FSourceDBPara.SqlServerPara);
      end;
    dbParadox: begin //SqlServer---->Paradox 7       ------------------>测试通过
         //连接到目标Paradox
        FConnectString := ReplaceParadoxConnectStr(FTargetDBPara.ParadoxPara);
        FConnectTargetString := FConnectString;

        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable;
        FExecuteSQLString := FExecuteSQLString + ' FROM ' + FConvertTablePara.SourceTable + ' ';
        FExecuteSQLString := FExecuteSQLString + ReplaceSqlServerODBCStr(FSourceDBPara.SqlServerPara);
      end;
    dbFoxpro: begin //sqlServer---->Foxpro         ------------------->测试通过
        //连接到目标Foxpro
        FConnectString := ReplaceFoxproConnectStr(FTargetDBPara.FoxproPara);
        FConnectTargetString := FConnectString;

        FExecuteSQLString := 'Select * INTO ' + FConvertTablePara.TargetTable;
        FExecuteSQLString := FExecuteSQLString + ' FROM ' + FConvertTablePara.SourceTable + ' ';
        FExecuteSQLString := FExecuteSQLString + ReplaceSqlServerODBCStr(FSourceDBPara.SqlServerPara);
      end;
    dbSqlServer: begin //SqlServer-->SqlServer  ------------------>测试通过，但OpenRowSet有时会遇到由于权限问题而不能从源库取得数据出错，要求一定要有权限
        //连接到目标SQLServer库
        FConnectString := ReplaceSqlServerConnectStr(FTargetDBPara.SqlServerPara);
        FConnectTargetString := FConnectString; //目标数据库连接串

        S := 'SELECT * into ' + FConvertTablePara.TargetTable + ' from OpenRowSet(''SQLOLEDB.1'',''';
        S := S + FSourceDBPara.SqlServerPara.Server + ''';''' + FSourceDBPara.SqlServerPara.UserName + ''';''';
        S := S + FSourceDBPara.SqlServerPara.Password + ''',' + FSourceDBPara.SqlServerPara.DBName + '.dbo.[' + FConvertTablePara.SourceTable + '])';
        FExecuteSQLString := S;
      end;

  end;

  StartConvert;
end;

function TDBConverter.ReplaceAccessConnectStr(
  AccessPara: TAccessPara): string;
var
  S: string;
begin
  S := StringReplace(conAccessConnectStr, 'd%', AccessPara.Path, [rfIgnoreCase]);
  S := StringReplace(S, 'p%', AccessPara.Password, [rfIgnoreCase]);
  Result := S;
end;

function TDBConverter.ReplaceExcelConnectStr(
  ExcelPara: TExcelPara): string;
begin
  Result := StringReplace(conExcelConnectStr, 'd%', ExcelPara.Path, [rfIgnoreCase]);
end;

function TDBConverter.ReplaceFoxproConnectStr(
  FoxproPara: TFoxproPara): string;
begin
  Result := StringReplace(conFoxproConnectStr, 'd%', FoxproPara.Path, [rfIgnoreCase]);
end;

function TDBConverter.ReplaceParadoxConnectStr(
  ParadoxPara: TParadoxPara): string;
begin
  Result := StringReplace(conParadoxConnectStr, 'd%', ParadoxPara.DSN, [rfIgnoreCase]);
end;

function TDBConverter.ReplaceSqlServerConnectStr(
  SqlServerPara: TSqlServerPara): string;
var
  S: string;
begin
  S := StringReplace(conSqlServerConnectStr, 'u%', SqlServerPara.UserName, [rfIgnoreCase]);
  S := StringReplace(S, 'p%', SqlServerPara.Password, [rfIgnoreCase]);
  S := StringReplace(S, 'd%', SqlServerPara.DBName, [rfIgnoreCase]);
  S := StringReplace(S, 's%', SqlServerPara.Server, [rfIgnoreCase]);
  Result := S;
end;


function TDBConverter.ReplaceSqlServerODBCStr(
  SqlServerPara: TSqlServerPara): string;
var
  S: string;
begin
  S := StringReplace(conSqlServerODBCStr, 'u%', SqlServerPara.UserName, [rfIgnoreCase]);
  S := StringReplace(S, 'p%', SqlServerPara.Password, [rfIgnoreCase]);
  S := StringReplace(S, 's%', SqlServerPara.Server, [rfIgnoreCase]);
  S := StringReplace(S, 'd%', SqlServerPara.DBName, [rfIgnoreCase]);
  Result := S;
end;


function TDBConverter.CheckConvertAvailable(SourceDBIndex,
  TargetDBIndex: integer): Boolean;
var
  iFlag: integer;
begin
  iFlag := SourceDBIndex * 10 + TargetDBIndex;
  Result := iFlag in AvailalbeDBConvert;
end;

end.