Refresh Nested DataSet with poFetchDetailsOnDemand

Question

Is there a way to refresh only the Detail DataSet without reloading all master dataset?

this is what I've tried so far:

DM.ClientDataSet2.Refresh;      
DM.ClientDataSet2.RefreshRecord;

I have also tried:

DM.ClientDataSet1.Refresh;

But the method above refreshes the entire Master dataset, not just the current record.

Now, the following code seems to do anything:

DM.ClientDataSet1.RefreshRecord;

Is there a workaround or a proper way to do what I want? (maybe an interposer...)

Additional Info:

ClientDataSet1 = Master Dataset

ClientDataSet2 = Detail DataSet , is the following: *

object ClientDataSet2: TClientDataSet
    Aggregates = <>
    DataSetField = ClientDataSet1ADOQuery2
    FetchOnDemand = False
    .....
end

Provider properties:

object DataSetProvider1: TDataSetProvider
    DataSet = ADOQuery1
    Options = [poFetchDetailsOnDemand]
    UpdateMode = upWhereKeyOnly
    Left = 24
    Top = 104
  end

Answer 1

Googling finds numerous articles that say that it isn't possible at all with nested ClientDataSets without closing and re-opening the master CDS, which the OP doesn't want to do in this case. However ...

The short answer to the q is yes, in the reasonably simple case I've tested, and it's quite straightforward, if a bit long-winded; getting the necessary steps right took a while to figure out.

The code is below and includes comments explaining how it works and a few potential problems and how it avoids or works around them. I have only tested it with TAdoQueries feeding the CDSs' Provider.

When I started looking into all this, it soon became apparent that with the usual master + detail set-up, although Providers + CDSs are happy to refresh the master data from the server, they simply will not refresh the detail records once they've been read from the server for the first time since the cdsMaster was opened. This may be by design of course.

I don't think I need to post a DFM to go with the code. I simply have AdoQueries set up in the usual master-detail way (with the detail query having the master's PK as a parameter), a DataSetProvider pointed at the master AdoQuery, a master CDS pointed at the provider, and a detail cDS pointed at the DataSetField of the cdsMaster. To experiment and see what's going on, there are DBGrids and DBNavigators for each of these datasets.

In brief, the way the code below works is to temporarily filter the AdoQuery master and the CDS masterdown to the current row and then force a refresh of their data and the dtail data for the current master row. Doing it this way, unlike any other I tried, results in the detail rows nested in the cdsMaster's DataSet field getting refreshed.

Btw, the other blind alleys I tried included with and without poFetchDetailsOnDemand set to true, ditto cdsMaster.FetchDetailsOnDemand. Evidently "FetchDetailsOnDemand" doesn't mean ReFetchDetailsOnDemand!

I ran into a problem or two getting my "solution" working, the stickiest one being described in this SO question: Refreshing a ClientDataSet nested in a DataSetField

I've verified that this works correctly with a Sql Server 2000(!) back-end, including picking up row data changes fired at the server from ISqlW. I've also verified, using Sql Server's Profiler, that the network traffic in a refresh only involves the single master row and its details.

Delphi 7 + Win7 64-bit, btw.

procedure TForm1.cdsMasterRowRefresh(MasterPK : Integer);
begin
  //  The following operations will cause the cursor on the cdsMaster to scroll
  //  so we need to check and set a flag to avoid re-entrancy
  if DoingRefresh then Exit;
  DoingRefresh := True;

  try
    //  Filter the cdsMaster down to the single row which is to be refreshed.
    cdsMaster.Filter := MasterPKName + ' = ' + IntToStr(MasterPK);
    cdsMaster.Filtered := True;
    cdsMaster.Refresh;
    Inc(cdsMasterRefreshes);  //  just a counter to assist debugging

    //  release the filter
    cdsMaster.Filtered := False;

    // clearing the filter may cause the cdsMaster cursor to move, so ...
    cdsMaster.Locate(MasterPKName, MasterPK, []);
  finally
    DoingRefresh := False;
  end;
end;

procedure TForm1.qMasterRowRefresh(MasterPK : Integer);
begin
  try
    //  First, filter the AdoQuery master down to the cdsMaster current row
    qMaster.Filter := MasterPKName + ' = ' + IntToStr(MasterPK);
    qMaster.Filtered := True;

    //  At this point Ado is happy to refresh only the current master row from the server
    qMaster.Refresh;

    // NOTE:
    //  The reason for the following operations on the qDetail AdoQuery is that I noticed
    //  during testing situations where this dataset would not be up-to-date at this point
    //  in the refreshing operations, so we update it manually.  The reason I do it manually
    //  is that simply calling qDetail's Refresh provoked the Ado "Insufficient key column
    //  information for updating or refreshing" despite its query not involving a join
    //  and the underlying table having a PK

    qDetail.Parameters.ParamByName(MasterPKName).Value := MasterPK;
    qDetail.Close;
    qDetail.Open;

    //  With the master and detail rows now re-read from the server, we can update
    //  the cdsMaster
    cdsMasterRowRefresh(MasterPK);
  finally
    //  Now, we can clear the filter
    qMaster.Filtered := False;
    qMaster.Locate(MasterPKName, MasterPK, []);
    // Obviously, if qMaster were filtered in the first place, we'd need to reinstate that later on
  end;
end;

procedure TForm1.RefreshcdsMasterAndDetails;
var
  MasterPK : Integer;
begin
  if cdsMaster.ChangeCount > 0 then
    raise Exception.Create(Format('cdsMaster has %d change(s) pending.', [cdsMaster.ChangeCount]));
  MasterPK := cdsMaster.FieldByName(MasterPKName).AsInteger;

  cdsDetail.DisableControls;
  cdsMaster.DisableControls;
  qDetail.DisableControls;
  qMaster.DisableControls;

  try
    try
      qMasterRowRefresh(MasterPK);
    except
      //  Add exception handling here according to taste
      //  I haven't encountered any during debugging/testing so:
      raise;
    end;
  finally
    qMaster.EnableControls;
    qDetail.EnableControls;
    cdsMaster.EnableControls;
    cdsDetail.EnableControls;
  end;
end;

procedure TForm1.cdsMasterAfterScroll(DataSet: TDataSet);
begin
  RefreshcdsMasterAndDetails;
end;

procedure TForm1.cdsMasterAfterPost(DataSet: TDataSet);
//  NOTE:  The reason that this, in addition to cdsMasterAfterScroll, calls RefreshcdsMasterAndDetails is
//         because RefreshcdsMasterAndDetails only refreshes the master + detail AdoQueries for the current
//         cdsMaster row.  Therefore in the case where the current cdsMaster row or its detail(s)
//         have been updated, this row needs the refresh treatment before we leave it.
begin
  cdsMaster.ApplyUpdates(-1);
  RefreshcdsMasterAndDetails;
end;

procedure TForm1.btnRefreshClick(Sender: TObject);
begin
  RefreshcdsMasterAndDetails;
end;

procedure TForm1.cdsDetailAfterPost(DataSet: TDataSet);
begin
  cdsMaster.ApplyUpdates(-1);
end;