updatesqlediteh.pas

最新的 DBGRIDEH4.0

procedure TUpdateSQLEditFormEh.InitGenerateOptions;
var
  UpdTabName: string;

  procedure InitFromDataSet;
  begin
    // If this is a Query with more than 1 table in the "from" clause then
    //  initialize the list of fields from the table rather than the dataset.
    if (UpdateTableName.Items.Count > 1) then
      GetTableFieldNames
    else
    begin
      GetDataSetFieldNames;
      FDatasetDefaults := True;
    end;
    SetDefaultSelections;
  end;

  procedure InitFromUpdateSQL;
  var
    UpdFields,
    WhFields: TStrings;
  begin
    UpdFields := TStringList.Create;
    try
      WhFields := TStringList.Create;
      try
        ParseUpdateSQL(MemoModify.Text, UpdTabName, UpdFields, WhFields);
        GetDataSetFieldNames;
        if SetSelectedItems(UpdateFieldList, UpdFields) < 1 then
          SelectAll(UpdateFieldList);
        if SetSelectedItems(KeyFieldList, WhFields) < 1 then
          SelectAll(KeyFieldList);
      finally
        WhFields.Free;
      end;
    finally
      UpdFields.Free;
    end;
  end;

begin
  // If there are existing update SQL statements, try to initialize the
  // dialog with the fields that correspond to them.
  if MemoModify.Lines.Count > 0 then
  begin
    ParseUpdateSQL(MemoModify.Text, UpdTabName, nil, nil);
    // If the table name from the update statement is not part of the
    // dataset, then initialize from the dataset instead.
    if (UpdateTableName.Items.Count > 0) and
       (UpdateTableName.Items.IndexOf(UpdTabName) > -1) then
    begin
      UpdateTableName.Text := UpdTabName;
      InitFromUpdateSQL;
    end else
    begin
      InitFromDataSet;
      UpdateTableName.Items.Add(UpdTabName);
    end;
  end else
    InitFromDataSet;
  SetButtonStates;
end;

procedure TUpdateSQLEditFormEh.InitUpdateTableNames;
begin
  UpdateTableName.Items.Clear;
  GetSQLTableNames(DataDriver.SelectSQL.Text, UpdateTableName.Items);
  if UpdateTableName.Items.Count > 0 then
     UpdateTableName.ItemIndex := 0;
end;

procedure TUpdateSQLEditFormEh.SetButtonStates;
begin
  GetTableFieldsButton.Enabled := UpdateTableName.Text <> '';
  PrimaryKeyButton.Enabled := GetTableFieldsButton.Enabled and
    (KeyFieldList.Items.Count > 0);
  GenerateButton.Enabled := GetTableFieldsButton.Enabled and
    (UpdateFieldList.Items.Count > 0) and (KeyFieldList.Items.Count > 0);
//  DefaultButton.Enabled := Assigned(DataSet) and not FDatasetDefaults;
end;

// Table FieldNames

procedure TUpdateSQLEditFormEh.GetTableFieldNames;
var
  FieldsList: TMemTableEh;
begin
  FieldsList := TMemTableEh.Create(nil);
  FieldsList.FieldDefs.Add('FieldName', ftString, 100);
  FieldsList.FieldDefs.Add('InKey', ftBoolean);
  FieldsList.FieldDefs.Add('FieldSize', ftInteger);
  FieldsList.CreateDataSet;
  if DataDriver.DesignDataBase <> nil then
//    (DataDriver.DesignDataBase as IDesignDataBaseEh).GetFieldList(UpdateTableName.Text, FieldsList);
    TDesignDataBaseEh(DataDriver.DesignDataBase).GetFieldList(UpdateTableName.Text, FieldsList);

  KeyFieldList.Clear;
  FieldsList.First;
  while not FieldsList.Eof do
  begin
    KeyFieldList.Items.Add(VarToStr(FieldsList['FieldName']));
    FieldsList.Next;
  end;
  FieldsList.Free;
  UpdateFieldList.Items.Assign(KeyFieldList.Items);
  cbIncrementField.Items := UpdateFieldList.Items;
  if TDesignDataBaseEh(DataDriver.DesignDataBase).GetIncrementObjectsList <> nil then
    cbIncrementObject.Items := TDesignDataBaseEh(DataDriver.DesignDataBase).GetIncrementObjectsList;
end;

// PrimaryKeyFields

procedure TUpdateSQLEditFormEh.SelectPrimaryKeyFields;
var
  FieldsList: TMemTableEh;
  Index: Integer;
begin
  FieldsList := TMemTableEh.Create(nil);
  FieldsList.FieldDefs.Add('FieldName', ftString, 100);
  FieldsList.FieldDefs.Add('InKey', ftBoolean);
  FieldsList.FieldDefs.Add('FieldSize', ftInteger);
  FieldsList.CreateDataSet;
  if DataDriver.DesignDataBase <> nil then
//    (DataDriver.DesignDataBase as IDesignDataBaseEh).GetFieldList(UpdateTableName.Text, FieldsList);
    TDesignDataBaseEh(DataDriver.DesignDataBase).GetFieldList(UpdateTableName.Text, FieldsList);

  FieldsList.First;
  while not FieldsList.Eof do
  begin
    Index := -1;
    if FieldsList['InKey'] = True then
      Index := KeyFieldList.Items.IndexOf(FieldsList['FieldName']);
    if Index > -1 then KeyFieldList.Selected[Index] := True;
    FieldsList.Next;
  end;
  FieldsList.Free;
end;

procedure TUpdateSQLEditFormEh.SetDefaultSelections;
//var
//  DSFields: TStringList;
begin
  if FDatasetDefaults {r not Assigned(DataSet)}then
  begin
    SelectAll(UpdateFieldList);
    SelectAll(KeyFieldList);
  end
{ else if (DataSet.FieldDefs.Count > 0) then
  begin
    DSFields := TStringList.Create;
    try
      GetDataFieldNames(DataSet, '', DSFields);
      SetSelectedItems(KeyFieldList, DSFields);
      SetSelectedItems(UpdateFieldList, DSFields);
    finally
      DSFields.Free;
    end;
  end;}
end;

procedure TUpdateSQLEditFormEh.ShowWait(WaitMethod: TWaitMethod);
begin
  Screen.Cursor := crHourGlass;
  try
    WaitMethod;
  finally
    Screen.Cursor := crDefault;
  end;
end;

{ Event Handlers }

procedure TUpdateSQLEditFormEh.FormCreate(Sender: TObject);
begin
//  HelpContext := hcDUpdateSQL;
end;

procedure TUpdateSQLEditFormEh.HelpButtonClick(Sender: TObject);
begin
  Application.HelpContext(HelpContext);
end;

procedure TUpdateSQLEditFormEh.DefaultButtonClick(Sender: TObject);
begin
  with UpdateTableName do
    if Items.Count > 0 then ItemIndex := 0;
  ShowWait(GetDataSetFieldNames);
  FDatasetDefaults := True;
  SetDefaultSelections;
  KeyfieldList.SetFocus;
  SetButtonStates;
end;

procedure TUpdateSQLEditFormEh.GenerateButtonClick(Sender: TObject);
begin
  GenerateSQL;
  FSettingsChanged := False;
end;

procedure TUpdateSQLEditFormEh.PrimaryKeyButtonClick(Sender: TObject);
begin
  ShowWait(SelectPrimaryKeyFields);
  SettingsChanged(Sender);
end;

procedure TUpdateSQLEditFormEh.PageControlChanging(Sender: TObject;
  var AllowChange: Boolean);
begin
  if (PageControl.ActivePage = PageControl.Pages[0]) and
    not SQLPage.Enabled then
    AllowChange := False;
end;

procedure TUpdateSQLEditFormEh.GetTableFieldsButtonClick(Sender: TObject);
begin
  ShowWait(GetTableFieldNames);
  SetDefaultSelections;
  SettingsChanged(Sender);
end;

procedure TUpdateSQLEditFormEh.SettingsChanged(Sender: TObject);
begin
  FSettingsChanged := True;
  FDatasetDefaults := False;
  SetButtonStates;
end;

procedure TUpdateSQLEditFormEh.FormCloseQuery(Sender: TObject;
  var CanClose: Boolean);
begin
  if (ModalResult = mrOK) and FSettingsChanged then
    CanClose := MessageDlg('SSQLNotGenerated', mtConfirmation,
      mbYesNoCancel, 0) = mrYes;
end;

procedure TUpdateSQLEditFormEh.UpdateTableNameChange(Sender: TObject);
begin
  SettingsChanged(Sender);
end;

procedure TUpdateSQLEditFormEh.UpdateTableNameClick(Sender: TObject);
begin
  if not Visible then Exit;
  GetTableFieldsButtonClick(Sender);
end;

procedure TUpdateSQLEditFormEh.SelectAllClick(Sender: TObject);
begin
  SelectAll(FieldListPopup.PopupComponent as TListBox);
end;

procedure TUpdateSQLEditFormEh.ClearAllClick(Sender: TObject);
var
  I: Integer;
begin
  with FieldListPopup.PopupComponent as TListBox do
  begin
    Items.BeginUpdate;
    try
      for I := 0 to Items.Count - 1 do
        Selected[I] := False;
    finally
      Items.EndUpdate;
    end;
  end;
end;

procedure TUpdateSQLEditFormEh.MemoModifyKeyDown(Sender: TObject;
  var Key: Word; Shift: TShiftState);
begin
  if (Key = VK_RETURN) and (ssCtrl in Shift) then
    Close;
end;

procedure TUpdateSQLEditFormEh.cbInsertClick(Sender: TObject);
begin
  tsInsert.TabVisible := cbInsert.Checked;
  tsModify.TabVisible := cbUpdate.Checked;
  tsDelete.TabVisible := cbDelete.Checked;
  tsGetrec.TabVisible := cbGetrec.Checked;
  tsSpecParams.TabVisible := cbSpecParams.Checked or cbUpdateFields.Checked or
    cbKeyFields.Checked or cbTableName.Checked;
end;

procedure TUpdateSQLEditFormEh.GenGetRecSQL(SelectSQL, KeyFields, SQL: TStrings);
var
  BindText, FieldName, TabAlias: String;
  I: Integer;
begin
  BindText := '';
  SQL.Assign(SelectSQL);
  TabAlias := '';
  for I := 0 to KeyFields.Count - 1 do
  begin
    FieldName := KeyFields[I];
    BindText := Format('  %s%s%s%1:s = :%1:sOLD_%2:s%1:s', { Do not localize }
      [TabAlias, '', FieldName]);
    if I < KeyFields.Count - 1 then
      BindText := Format('%s and',[BindText]); { Do not localize }
    SQL.Add(BindText);
  end;
end;

procedure TUpdateSQLEditFormEh.GenerateSQLViaDBService;
var
//  DBService: TCustomDBService;
  DesignUpdateParams: TDesignUpdateParamsEh;
  DesignUpdateInfo: TDesignUpdateInfoEh;
begin
  if TDesignDataBaseEh(DataDriver.DesignDataBase).GetCustomDBService <> nil then
  begin
    DesignUpdateParams := TDesignUpdateParamsEh.Create;
    DesignUpdateInfo := TDesignUpdateInfoEh.Create;
    DesignUpdateParams.TableName := UpdateTableName.Text;
    DesignUpdateParams.IncremenField := cbIncrementField.Text;
    DesignUpdateParams.IncremenObject := cbIncrementObject.Text;
    GetSelectedItems(KeyFieldList, DesignUpdateParams.KeyFields);
    GetSelectedItems(UpdateFieldList, DesignUpdateParams.UpdateFields);
    DesignUpdateParams.SelectSQL := DataDriver.SelectSQL;

    if TDesignDataBaseEh(DataDriver.DesignDataBase).
      GetCustomDBService.GetUpdateSQLCommand(DesignUpdateParams, DesignUpdateInfo) then
    begin
      if cbInsert.Checked then
        MemoInsert.Lines := DesignUpdateInfo.InsertSQL;
      if cbUpdate.Checked then
        MemoModify.Lines := DesignUpdateInfo.UpdateSQL;
      if cbDelete.Checked then
        MemoDelete.Lines := DesignUpdateInfo.DeleteSQL;
      if cbGetRec.Checked then
        MemoGetRec.Lines := DesignUpdateInfo.GetrecSQL;
      if cbSpecParams.Checked then
        mSpecParams.Lines := DesignUpdateInfo.SpecParams;
      if cbUpdateFields.Checked then
        MemoUpdateFields.Lines := DesignUpdateInfo.UpdateFields;
      if cbKeyFields.Checked then
        MemoKeyFields.Lines := DesignUpdateInfo.KeyFields;
      if cbTableName.Checked then
        dbeTableName.Text := DesignUpdateInfo.TableName;
    end;
    DesignUpdateParams.Free;
    DesignUpdateInfo.Free;
  end;
end;

procedure TUpdateSQLEditFormEh.FillMemoFromList(Memo: TMemo; List: TListBox);
var
  i: Integer;
begin
  Memo.Clear;
  for i := 0 to List.Items.Count-1 do
  begin
    if List.Selected[i] then
      Memo.Lines.Add(List.Items[i]);
  end;
end;

end.