dbtxtimp.pas

从文本文件象Oracle数据库导入数据

unit DBTxtImp;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Grids, DBGrids, Mask, enterctl, ToolEdit, Db, OracleData,
  ExtCtrls, RxMemDS, RXSpin;

type
  TfrmDBTxtImp = class(TForm)
    OraDsFields: TOracleDataSet;
    dsFields: TDataSource;
    Panel1: TPanel;
    GroupBox1: TGroupBox;
    rbTab: TRadioButton;
    rbSep: TRadioButton;
    rbOther: TRadioButton;
    rbComma: TRadioButton;
    edtSeparator: TEdit;
    rbSpace: TRadioButton;
    GroupBox2: TGroupBox;
    rbFirstData: TRadioButton;
    rbFirstName: TRadioButton;
    Label1: TLabel;
    cmbedtFileName: TComboEdit;
    Label8: TLabel;
    Button1: TButton;
    dgrdFields: TDBGrid;
    rxmtblFields: TRxMemoryData;
    rxmtblFieldsTABLE_NAME: TStringField;
    rxmtblFieldsCOLUMN_NAME: TStringField;
    rxmtblFieldsCOLUMN_ID: TFloatField;
    rxmtblFieldsDATA_LENGTH: TFloatField;
    rxmtblFieldsNULLABLE: TStringField;
    rxmtblFieldsADJUST_TYPE: TStringField;
    rxmtblFieldsUPDATE_MODE: TStringField;
    rxmtblFieldsDATA_FROM: TStringField;
    rxmtblFieldsDEFAULT_VALUE: TStringField;
    rxmtblFieldsFILE_INDEX: TFloatField;
    cmbedtTableName: TComboEdit;
    OpenDialog1: TOpenDialog;
    Label2: TLabel;
    spinCmtCnt: TRxSpinEdit;
    cbIgnoreCoute: TCheckBox;
    Label3: TLabel;
    edtDateFormat: TEdit;
    cbForceDefFields: TCheckBox;
    rxmtblFieldsIGNORE_COUTE: TStringField;
    rxmtblFieldsDATE_FORMAT: TStringField;
    cbAdjustFlag: TCheckBox;
    procedure rxmtblFieldsADJUST_TYPEGetText(Sender: TField;
      var Text: String; DisplayText: Boolean);
    procedure rxmtblFieldsADJUST_TYPESetText(Sender: TField;
      const Text: String);
    procedure rxmtblFieldsUPDATE_MODEGetText(Sender: TField;
      var Text: String; DisplayText: Boolean);
    procedure rxmtblFieldsUPDATE_MODESetText(Sender: TField;
      const Text: String);
    procedure rxmtblFieldsDATA_FROMGetText(Sender: TField;
      var Text: String; DisplayText: Boolean);
    procedure rxmtblFieldsDATA_FROMSetText(Sender: TField;
      const Text: String);
    procedure cmbedtFileNameButtonClick(Sender: TObject);
    procedure cmbedtTableNameButtonClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure cmbedtTableNameKeyPress(Sender: TObject; var Key: Char);
    procedure Button1Click(Sender: TObject);
    procedure rxmtblFieldsIGNORE_COUTEGetText(Sender: TField;
      var Text: String; DisplayText: Boolean);
    procedure rxmtblFieldsIGNORE_COUTESetText(Sender: TField;
      const Text: String);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure DoImpData(ATableName, AFileName: string);
  end;

var
  frmDBTxtImp: TfrmDBTxtImp;

implementation

{$R *.DFM}
uses dData, uFileFieldDefines, Msg;

procedure TfrmDBTxtImp.rxmtblFieldsADJUST_TYPEGetText(Sender: TField;
  var Text: String; DisplayText: Boolean);
begin
  if (Sender.AsString = '0') or (Sender.AsString = 'f') or (Sender.AsString = 'F') then
    Text := '不校验'
  else if (Sender.AsString = '1') or (Sender.AsString = 't') or (Sender.AsString = 'T') then
    Text := '主键校验';
end;

procedure TfrmDBTxtImp.rxmtblFieldsADJUST_TYPESetText(Sender: TField;
  const Text: String);
begin
  if (Text = '0') or (Text = 'f') or (Text = 'F') or (Text = '否') or (Text = '不校验') then
    Sender.AsString := '0'
  else if (Text = '1') or (Text = 't') or (Text = 'T') or (Text = '是') or (Text = '主键校验') then
    Sender.AsString := '1'
end;

procedure TfrmDBTxtImp.rxmtblFieldsUPDATE_MODEGetText(Sender: TField;
  var Text: String; DisplayText: Boolean);
begin
  if Sender.AsString = '0' then
    Text := '不更新'
  else if Sender.AsString = '1' then
    Text := '替换'
  else if Sender.AsString = '2' then
    Text := '累加'
  else if Sender.AsString = '3' then
    Text := '合并';
end;

procedure TfrmDBTxtImp.rxmtblFieldsUPDATE_MODESetText(Sender: TField;
  const Text: String);
begin
  if Text = '不更新' then
    Sender.AsString := '0'
  else if Text = '替换' then
    Sender.AsString := '1'
  else if Text = '累加' then
    Sender.AsString := '2'
  else if Text = '合并' then
    Sender.AsString := '3';
end;

procedure TfrmDBTxtImp.rxmtblFieldsDATA_FROMGetText(Sender: TField;
  var Text: String; DisplayText: Boolean);
begin
  if Sender.AsString = '0' then
    Text := '文件导入'
  else if Sender.AsString = '1' then
    Text := '系统默认'
  else if Sender.AsString = '2' then
    Text := '自增字段'
  else
    Text := Sender.AsString;
end;

procedure TfrmDBTxtImp.rxmtblFieldsDATA_FROMSetText(Sender: TField;
  const Text: String);
begin
  if (Text = '文件导入') or (Text = '0') then
    Sender.AsString := '0'
  else if (Text = '系统默认') or (Text = '1') then
    Sender.AsString := '1'
  else if (Text = '自增字段') or (Text = '2') then
    Sender.AsString := '2'
  else
    Sender.AsString := '1';
end;

procedure TfrmDBTxtImp.cmbedtFileNameButtonClick(Sender: TObject);
begin
  if OpenDialog1.Execute then
    cmbedtFileName.Text := OpenDialog1.FileName;
end;

procedure TfrmDBTxtImp.cmbedtTableNameButtonClick(Sender: TObject);
begin
  rxmtblFields.Close;
  rxmtblFields.Open;
  if (Sender as TComboEdit).Text = '' then
    Exit;
  OraDsFields.Close;
  OraDsFields.SQL.Text := 'select table_name     '
                        + '      ,column_name    '
                        + '      ,column_id      '
                        + '      ,data_length    '
                        + '      ,nullable       '
                        + '  from cols t         '
                        + ' where table_name = ''' + UpperCase((Sender as TComboEdit).Text) + ''''
                        + ' order by column_id   ';
  OraDsFields.Open;
  while not OraDsFields.Eof do
  begin
    rxmtblFields.Append;
    rxmtblFields.FieldByName('table_name').AsString := OraDsFields.FieldByName('table_name').AsString;
    rxmtblFields.FieldByName('column_name').AsString := OraDsFields.FieldByName('column_name').AsString;
    rxmtblFields.FieldByName('column_id').AsFloat := OraDsFields.FieldByName('column_id').AsFloat;
    rxmtblFields.FieldByName('data_length').AsFloat := OraDsFields.FieldByName('data_length').AsFloat;
    rxmtblFields.FieldByName('nullable').AsString := OraDsFields.FieldByName('nullable').AsString;
    rxmtblFields.FieldByName('ignore_coute').AsString := '0';
    rxmtblFields.FieldByName('date_format').AsString := '';

    if OraDsFields.FieldByName('nullable').AsString = 'Y' then
    begin
      rxmtblFields.FieldByName('ADJUST_TYPE').AsString := '0';
      rxmtblFields.FieldByName('UPDATE_MODE').AsString := '1';
    end
    else
    begin
      rxmtblFields.FieldByName('ADJUST_TYPE').AsString := '1';
      rxmtblFields.FieldByName('UPDATE_MODE').AsString := '0';
    end;
    rxmtblFields.FieldByName('DATA_FROM').AsString := '0';
    rxmtblFields.FieldByName('DEFAULT_VALUE').AsString := '';
    rxmtblFields.FieldByName('FILE_INDEX').AsInteger := OraDsFields.FieldByName('column_id').AsInteger - 1;
    rxmtblFields.Post;
    OraDsFields.Next;
  end;
  OraDsFields.Close;
end;

procedure TfrmDBTxtImp.FormCreate(Sender: TObject);
begin
  cmbedtTableName.EnterAsTab := False;
  cmbedtFileName.EnterAsTab := False;
  TDrawGrid(dgrdFields).Options := TDrawGrid(dgrdFields).Options - [goFixedVertLine];
  TDrawGrid(dgrdFields).Options := TDrawGrid(dgrdFields).Options - [goFixedHorzLine];
end;

procedure TfrmDBTxtImp.cmbedtTableNameKeyPress(Sender: TObject;
  var Key: Char);
begin
  if Key = #13 then
    (Sender as TComboEdit).Button.Click;
end;

procedure TfrmDBTxtImp.Button1Click(Sender: TObject);
begin
  if cmbedtTableName.Text = '' then
  begin
    ShowMessage('请输入数据库表名称！');
    Exit;
  end;
  if (not rxmtblFields.Active) or rxmtblFields.IsEmpty then
  begin
    ShowMessage('数据库表字段信息加载失败！');
    Exit;
  end;
  if cmbedtFileName.Text = '' then
  begin
    ShowMessage('请输入文本文件名称！');
    Exit;
  end;
  if not FileExists(cmbedtFileName.Text) then
  begin
    ShowMessage('找不到文本文件！');
    Exit;
  end;
  DoImpData(rxmtblFields.FieldByName('table_name').AsString, cmbedtFileName.Text);
end;

procedure TfrmDBTxtImp.DoImpData(ATableName, AFileName: string);
var
  pFileFieldTemp: PFileField;
  ifImpFile: TImpFile;
begin
  if (ATableName = '') or (AFileName = '') then
    Exit;
  if (not rxmtblFields.Active) or rxmtblFields.IsEmpty then
    Exit;
  ifImpFile := TImpFile.Create;
  try
    OraDsFields.Close;
    OraDsFields.SQL.Text := 'select * from ' + ATableName + ' where 1<0 ';
    try
      OraDsFields.Open;
    except
      ShowMessage('目标表打开失败！');
      Exit;
    end;
    ifImpFile.fFirstLine := rbFirstData.Checked;
    ifImpFile.fShowProgress := True;
    ifImpFile.FileName := AFileName;
    ifImpFile.DataNo := ATableName;
    ifImpFile.DataName := ATableName;
    ifImpFile.TableName := ATableName;
    ifImpFile.CommitCount := Round(spinCmtCnt.Value);
    ifImpFile.AdjustFlag := cbAdjustFlag.Checked;
    ifImpFile.DateFormat := edtDateFormat.Text;
    ifImpFile.IgnoreCoute := cbIgnoreCoute.Checked;
    ifImpFile.fForceDefineFields := cbForceDefFields.Checked;
    
    if rbTab.Checked then
      ifImpFile.Separator := #9
    else if rbSep.Checked then
      ifImpFile.Separator := ';'
    else if rbComma.Checked then
      ifImpFile.Separator := ','
    else if rbSpace.Checked then
      ifImpFile.Separator := ' '
    else
      ifImpFile.Separator := edtSeparator.Text;
    ifImpFile.fFileFields.Clear;
    if rxmtblFields.State in [dsEdit, dsInsert] then
      rxmtblFields.Post;
    rxmtblFields.First;
    while not rxmtblFields.Eof do
    begin
      //创建字段定义对象
      pFileFieldTemp := New(PFileField);
      pFileFieldTemp^ := TFileField.Create;
      //设定字段定义对象的定义参数
      pFileFieldTemp^.fTableName := ATableName;
      pFileFieldTemp^.fFieldName := rxmtblFields.FieldByName('column_name').AsString;
      pFileFieldTemp^.fFieldType := OraDsFields.FieldByName(rxmtblFields.FieldByName('column_name').AsString).DataType;
      pFileFieldTemp^.fAdjustFlag := rxmtblFields.FieldByName('adjust_type').AsString = '1';
      pFileFieldTemp^.fDataLength := rxmtblFields.FieldByName('data_length').AsInteger;
      if rxmtblFields.FieldByName('update_mode').AsString = '1' then
        pFileFieldTemp^.fUpdateMode := fumUpdate
      else if rxmtblFields.FieldByName('update_mode').AsString = '2' then
        pFileFieldTemp^.fUpdateMode := fumAdd
      else if rxmtblFields.FieldByName('update_mode').AsString = '3' then
        pFileFieldTemp^.fUpdateMode := fumUnion
      else
        pFileFieldTemp^.fUpdateMode := fumNone;
      if rxmtblFields.FieldByName('data_from').AsString = '1' then
        pFileFieldTemp^.fDataType := fdtUseDefault
      else if rxmtblFields.FieldByName('data_from').AsString = '2' then
        pFileFieldTemp^.fDataType := fdtAutoID
      else
        pFileFieldTemp^.fDataType := fdtFromFile;
      if rxmtblFields.FieldByName('file_index').IsNull then
        pFileFieldTemp^.fFileIndex := -1
      else
        pFileFieldTemp^.fFileIndex := rxmtblFields.FieldByName('file_index').AsInteger;
      pFileFieldTemp^.fDefaultValue := rxmtblFields.FieldByName('default_value').AsString;
      pFileFieldTemp^.fDateFormat := rxmtblFields.FieldByName('date_format').AsString;
      pFileFieldTemp^.fIgnoreCoute := rxmtblFields.FieldByName('ignore_coute').AsString = '1';
      //添加字段定义对象（到当前文件定义对象）
      ifImpFile.fFileFields.Add(pFileFieldTemp);
      rxmtblFields.Next;
    end;
    ifImpFile.DoImport(nil);
    if FileExists(ifImpFile.LogFileName) then
    begin
      if not Assigned(frmMsg) then
        frmMsg := TfrmMsg.Create(Application);
      frmMsg.reMsg.Lines.LoadFromFile(ifImpFile.LogFileName);
      frmMsg.Show;
    end;
  finally
    OraDsFields.Close;
    ifImpFile.Free;
  end;
end;

procedure TfrmDBTxtImp.rxmtblFieldsIGNORE_COUTEGetText(Sender: TField;
  var Text: String; DisplayText: Boolean);
begin
  if Sender.AsString = '0' then
    Text := '否'
  else if Sender.AsString = '1' then
    Text := '是'
  else
    Text := Sender.AsString;
end;

procedure TfrmDBTxtImp.rxmtblFieldsIGNORE_COUTESetText(Sender: TField;
  const Text: String);
begin
  if (Text = '1') or (Text = 't') or (Text = 'T') or (Text = '是') then
    Sender.AsString := '1'
  else
    Sender.AsString := '0'
end;

end.