DataSnap using AutoInc key and refresh current record only after insert

Question

I've not been able to find an answer on this anywhere. Using Delphi XE7 with TClientDataSet, DataSnap & SQL Server. I need to insert a record, apply updates and then refresh that record so I can get the Id and assign it to my object. Seems pretty basic requirement, but on the contrary it is proving to be a royal pain.

I've found the obvious stuff on EDN, SO and Dr Bob:

http://edn.embarcadero.com/article/20847

DataSnap and the autoinc field

http://www.drbob42.com/examines/examinC0.htm

However these seem to focus on a "Refresh" of the TClientDataSet to re-fetches the entire table/query. Whilst this does actually resolve the Id field itself (good!), it also moves the cursor off the current record which was just inserted and so I'm not able to get the Id and assign it to my object. Also, for performance over HTTP I don't really want to refetch the entire table every time a record is inserted, if there's 10,000 records this will consume too much bandwidth and be ridiculously slow!

Consider the following code:

function TRepository<I>.Insert(const AEntity: I): I;
begin
  FDataSet.DisableControls;
  try
    FDataSet.Insert;
    AssignEntityToDataSet(AEntity);  // SET'S ALL THE RELEVANT FIELDS
    FDataSet.Post;
    FDataSet.ApplyUpdates(-1);
    FDataSet.Refresh;  // <--- I tried RefreshRecord here but it cannot resolve the record
    AEntity.Id := FDataSet.FieldByName('Id').AsInteger; // <----- THIS NOW POINTS TO WRONG ROW
  finally
    FDataSet.EnableControls;
  end;
end;

Does anyone know how to achieve this? I need to be able to refresh and stay on the current record otherwise I do not know the Id of the record just created and the GUI cannot stay focused on the current record.

Hopefully something obvious I'm missing.

Cheers. Rick.

Answer 1

Assuming you can get hands on the new ID inside the AfterUpdateRecord event of your DataProvider, your event handler then may look like this (the current record of DeltaDS is the one just inserted into SourceDS):

  if (UpdateKind = ukInsert) then begin
    DeltaDS.FindField('Id').NewValue := <TheNewID>;
  end;

Make sure to have the poPropogateChanges option set in the provider. This will transfer the changed Id field back to the ClientDataSet.

Now you can get rid of the FDataSet.Refresh call.

Answer 2

SQL Server does allow you to get the last identity it generated in several ways - there's no need to "refresh" the record/query which means re-issuing a SELECT and can generate undesiderable side-effects. You can use SELECT SCOPE_IDENTITY() or use an OUTPUT clause. If the Delphi database driver supports it, TField.AutogenerateValue should accomplish that task automatically (see http://docwiki.embarcadero.com/Libraries/XE7/en/Data.DB.TField.AutoGenerateValue)

Otherwise you have to put that new data into your delta (see Raabe answer - this has to be done on the datasnap server which actually talks to the database) after reading it, so it's sent back to the client. You also need to set properly and TField.ProviderFlags to ensure data are applied correctly (see http://docwiki.embarcadero.com/RADStudio/XE7/en/Influencing_How_Updates_Are_Applied), usually you don't want those field appear in an UPDATE.