AutoIncrement fields and DataSnap 2010
In this article, I'll demonstrate how we can define and use AutoIncrement fields (in SQL Server or Blackfish SQL) with Delphi 2010's DataSnap.

SQL Server
First of all, we need a sample table. Using SQL Server 2008, I’ve used the following SQL to create a new table with an identity key field:

  CREATE TABLE dbo.Demo
  (
    DemoID int NOT NULL IDENTITY (1, 1),
    DemoName nvarchar(50) NULL
  )  ON [PRIMARY]
  GO
  ALTER TABLE dbo.Demo ADD CONSTRAINT
    PK_Demo PRIMARY KEY CLUSTERED
    (
      DemoID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  GO
  ALTER TABLE dbo.Demo SET (LOCK_ESCALATION = TABLE)
  GO

Note that we need a tool like Microsoft SQL Server Management Studio to create this table, since the Data Explorer does not support creating “identity” fields, which is the equivalent of an auto-increment field in SQL Server.

BlackfishSQL
For Blackfish SQL, the SQL DDL to create a similar table is as follows:

  CREATE TABLE DEMO (
    DEMOID INTEGER AUTOINCREMENT NOT NULL,
    DEMONAME VARCHAR(50)
  )
  ALTER TABLE DEMO ADD PRIMARY KEY (DEMOID)

We can use the Data Explorer for this table, since the type “INTEGER AUTOINCREMENT” is selectable from there.

DataSnap Server
Once we have the sample table(s) with the AutoIncrement field, it's time to build the DataSnap Server. Do File | New - Other, and create a DataSnap Server application using the Wizard from the Object Repositort.
Place a TSQLConnection component on the ServerMethods Unit, and connect it to the database you want to test. Then, place a TSQLDataSet component, connect it to the TSQLConnection component and specify the following SQL statement for its CommandText property:

  SELECT DEMOID, DEMONAME FROM DEMO

If you then use the Fields Editor on the TSQLDataSet to add all fields, you’ll notice that the DEMOID field is of type TIntegerField, and not a TAutoIncrementField. This is no problem, but we have to change one setting for the DEMOID field in order to make this example work: the TIntegerField.ProviderFlags.pfInUpdate sub-property needs to be set to False. That’s it.
Now, place a TDataSetProvider on the ServerMethods Unit, and give it a sensible name (since it will be exported), like dspDemo. Then, connect it to the TSQLDataSet component. Normally, I would recommend to set the UpdateMode property of the TDataSetProvider to upWhereChanged, but since we didn’t specify DEMOID as primary key, this will not work, and we should stick to the default UpWhereAll value of the UpdateMode property (in other words: you don’t have to change this, it’s already working right).

We’ve just finished the DataSnap 2010 server, allow the DEMOID field to act as an autoincrement field – it’s not required, so when sending the INSERT it will not be part of the query, but the DEMOID field’s value will be retrieved when we do a Refresh right after the ApplyUpdates. And the UPDATE and DELETE commands will now use all fields in the WHERE clause, which will include the DEMOID field (the primary key – although the Delphi application doesn’t recognize it as such).

DataSnap Client
Run the DataSnap Server application, so we can connect to it from a DataSnap Client application.
We can use a regular VCL Forms Application for our DataSnap client. Place a TSQLConnection component on the form, and connect it to the DataSnap Server by setting the Driver to DataSnap and the appropriate subproperties of the Driver property (see my white paper or videos for details).
Place a TDSProviderConnection component and a TClientDataSet component and connect these to the dspDemo (TDataSetProvider) from the DataSnap Server side.
Use the Fields Editor to create and examine the fields DEMOID and DEMONAME. You may notice – to your surprise – that DEMOID is again required. We must set the Required property to False here as well.
Remove phInUpdate flag on DEMOID on TSQLDataSet and TClientDataSet

In order to apply updates, we need to call ApplyUpdates:

  procedure TForm12.Button1Click(Sender: TObject);
  begin
    if ClientDataSet1.ApplyUpdates(0) = 0 then
      ClientDataSet1.Refresh;
  end;

Note that we also must need the Refresh method in order to retrieve the value of the DEMOID value that the DBMS assigns to this AutoIncrement field. The call to Refresh may no longer be needed when the poPropogateChanges flag of the TDataSetProvider Options property is (finally) implemented, one day. But until that day, we cannot refresh a single record and must call Refresh on the entire ClientDataSet.

Master-Detail
In order to use this technique in combination with master-detail relationships, we cannot leave the primary key field empty. In that case, we need to assign a unique temporary value, such as a negative value.
For this, we need to implement the OnNewRecord event of the local TClientDataSet, as follows:

  procedure TForm12.ClientDataSet1NewRecord(DataSet: TDataSet);
  const {$J+}
    ID: Integer = -1;
  begin
    DataSet.FieldByName('DEMOID').AsInteger := ID;
    Dec(ID);
  end;

Note that thie typed constant will start at -1 and get decreased by 1 for every new record we add (during the "run" of the application).

Summary
In this article, I've demonstrated how we can define and use AutoIncrement fields (in SQL Server or Blackfish SQL) with Delphi 2010's DataSnap.

References
Dan Miser - How to use Autoinc fields with DataSnap