Fetching multiple nested datasets in Delphi DataSnap

Question

I have Delphi client-server application that uses DataSnap. On client side I have a chain of nested client datasets (cdsMaster -> cds1 -> cds2 -> cds3).

TDM = class(TDataModule)
  cdsMaster: TClientDataSet;
  cdsMaster_cds1: TDataSetField;
  cds1: TClientDataSet;
  cds1_cds2: TDataSetField;
  cds2: TClientDataSet;
  cds2_cds3: TDataSetField;
  cds3: TClientDataSet;
end;

On server side, I have a similar set of datasets with master-detail relations.

TCoDataModule = class(TRemoteDataModule, ICoDataModule)
  prvMaster: TDataSetProvider;
  dsMaster: TIBDataSet;
  ds1: TIBTable;
  ds2: TIBTable;
  ds3: TIBTable;
end;

First, I need to fetch content of cdsMaster once (without details), and then fetch full details on demand in single packet (all nested content of cds1, cds2, cds3 for selected master record). What's the best way to implement this?

If I disable option poFetchDetailsOnDemand for prvMaster, it loads the entire database on connect. If I enable it, it fetches details records one-by-one, making huge traffic overhead and performance slowdown.

Marc Guillot · Answer 1 · 2016-12-01T08:51:18.263

I would personally add parameters to indicate the record or range of records to retrieve on the master Clientdataset, and disable poFetchDetailsOnDemand so it will return the full details for those few selected records on the master table.

This way you don't fetch the details one-by-one, and don't load the entire database either, you only load the needed master record/s and all its details.

Example: Change your master clientdataset sql from something like SELECT * FROM COSTUMER to something like SELECT * FROM COSTUMER WHERE ID = :ID

PS: If you also need the full list of master records, without details, you can load them into a separate clientdataset. This way you can show on a grid all the possible master records, and when the users selects to view the details, you open the second clientataset which loads the full master record (and only that record) with all its details.

Fetching multiple nested datasets in Delphi DataSnap

1 Answers1