Master-detail using ClientDataSet.AppyUpdates with separate DataSetProvider

Question

I use two ClientDataSets for a master-detail relationship with a DataSetProvider for each CDS. I don’t use a nested CDS for the detail, since I do an in-memory filtering for the master-detail relationship.

The problem I have is when I need to apply my changes to the underlying database (firebird). For detail INSERTs I have to apply the master first and for detail DELETEs I have to apply the detail first (without violating the master-detail relationship in the db). So far so good. But what shall I do when my detail CDS has a mixture of INSERTs and DELETEs? Then I cannot apply it before or after the master CDS.

How can I handle such situation, without using nested CDS?

Answer 1

Usually, you insert/update one master record and then you work with details (insert, update, delete). One way to solve your problem is to place the entire operation in a transaction (start transaction before making any changes), insert/update master record (single record), do MasterCDS.ApplyUpdates, work with detail records, do DetailCDS.ApplyUpdates and finally commit or rollback the entire transaction. Since your CDS are in a master/detail relationship, DetailCDS will "see" records in MasterCDS as soon as they are Posted and you will get primary key value as soon as you ApplyUpdates on MasterCDS. That way you maintain referential integrity (foreign key constraints) and you can do whatever you have on the DetailCDS.

Additionally, there are events on both TClientDataSet and TDataSetProvider that give you (almost) complete control over the entire process, so take a closer look at all available events.

NOTE: I may be wrong about some details as I'm explaining this from my memory, but the idea is important. Experiment a bit and you'll find the solution.

Answer 2

I would apply the master and then the detail.

In the case where you delete a master record I would cascade delete the detail records by overriding the BeforeApplyUpdates event.

When you delete the detail you will again need to override the BeforeApplyUpdates. If the master record is missing do not bother to perform a delete.

You can 'skip' the deletion of detail records by using a custom update command. The only reason to do this is to stop datasnap from generating a SQL command itself and then failing because rows affected = 0. I would probably use something like

IF EXISTS (SELECT * FROM dbo.ParentTable WHERE ParentKey = @ParentKey)
BEGIN
  DECLARE @rowcount INT
  DELETE
    FROM dbo.ChildTable
    WHERE ChildKey = @ChildKey
  SET @rowcount = @@ROWCOUNT
  IF @rowcount <> 1
  BEGIN
    RAISERROR('Record not found.(%d)', 15, 1, @rowcount) WITH SETERROR
  END
END

Then in the BeforeUpdateRecord event you call this command

case UpdateStatus of
  ukDelete:
    begin
      sqlDeleteChild.Parameters.ParamByName('@ChildKey').Value := DeltaDS.FieldByName('ChildKey').OldValue;
      sqlDeleteChild.Parameters.ParamByName('@ParentKey').Value := DeltaDS.FieldByName('ParentKey').OldValue;
      sqlDeleteChild.Execute;
    end;
  ...
end;
Applied := true;

Answer 3

When using separate Data Set Providers, I found that the updates in each DSP were in a separate transaction (at least with Interbase). I was not concerned with this, but your application might require all datasets to be kept consistent.