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;
