DataSetProvider - DataSet to ClientDataSet

Question

EDIT: It seems as if the DataSetProvider doesn't have the functionality I need for this project, so I'll be implementing a custom class for loading the data into the ClientDataSet.

I am trying to take data from a TMSQuery which is connected to my DB and populate a ClientDataSet with some of that data using a DataSetProvider.

My problem is that I will need to modify some of this data before it can go into my ClientDataSet. The ClientDataSet has persistent fields that will not match up with the raw DB data. I can't even get a string from the DB into a memo field in ClientDataSet.

The ClientDataSet is a part of my data tier so I will need to conform the data from the DB to the ClientDataSet field by field (well most will be able to go right through, but many will require routing and/or conversion).

Does anyone have experience with this?

Answer 1

If I need a ClientDataSet to have data that doesn't exactly match the database schema, I write a query for the TQuery component that returns the data in the format that I want. I then write my own, separate, Delete, Insert, Refresh, and Update queries for the TQuery component.

Alternatively, you could create a view on the database and use the view in your TQuery component.

If you want a custom ClientDataSet that is independent of the database, what you need is an in-memory dataset. If you don't have an in-memory dataset component, Google for "TClientDataSet as in-memory dataset". What you end up with though, is basically a glorified list view component. Of course, you can hook into the OnUpdateRecord of the in-memory dataset to know when to update your real dataset.

Answer 2

You're looking for the TDataSetProvider.BeforeUpdateRecord event. Write an event handler for this event and you can take manual control of how the data is applied back to the data base.

Something like this

procedure TDataModule1.DataSetProvider1BeforeUpdateRecord(Sender: TObject; SourceDS: TDataSet; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind; var Applied: Boolean);
begin
  { Set applied to tell DataSnap that you have applied this record yourself }
  Applied := True;

  case UpdateKind of
    ukModify:
      begin
        Table1.Edit;
        { set the values of the fields something like this }
        if not VarIsEmpty(DeltaDS.FieldByName('NewValue')) then
          Table1['SomeField'] := DeltaDS.FieldByName('SomeField').NewValue;
        Table1.Post;
    end;

    ukInsert:
      begin
        Table1.Insert;
        { set the values of the fields }
        Table1['SomeField'] := DeltaDS['SomeField']
        Table1.Post;
      end;

    ukDelete:
      if Table1.Locate('PrimaryKeyField', DeltaDS['PrimaryKeyField'], []) then
        Table1.Delete;
  end; // case
end;

Answer 3

You can modify the data going to the ClientDataSet by implementing the TDataSetProvider.OnGetData event.

procedure TDataModule1.DataSetProvider1GetData(Sender: TObject; DataSet: TCustomClientDataSet);
begin
  DataSet.First;
  while not DataSet.Eof do begin
    DataSet.Edit;
    DataSet['Surname'] := UpperCase(DataSet['Surname']);
    DataSet.Post;
    DataSet.Next;
  end; // while
end;

When applying updates from the ClientDataSet you can use the TDataSetProvider.OnUpdateData event. Like the OnGetData event you are operating on the whole dataset rather than a single record.

procedure TDataModule1.DataSetProvider1UpdateData(Sender: TObject; DataSet: TCustomClientDataSet);
begin
  DataSet.First;
  while not DataSet.Eof do begin
    DataSet.Edit;
    DataSet['Surname'] := LowerCase(DataSet['Surname']);
    DataSet.Post;
    DataSet.Next;
  end; // while
end;

This OnUpdateData event is called before the OnBeforeUpdateRecord event. Also the OnGetData and OnUpdateData events operate on the whole dataset while OnBeforeUpdateRecord is called once for each modified record.