Delphi TClientDataset SQL Server RowVersion

Question

Does anyone have any experience using SQL Server 's RowVersion column with TClientDataset? Specifically, I need to get the RowVersion value back on insert. It seems to get the RowVersion value back on updates but a newly inserted row it does not.

Answer 1

This seems to involve a similar problem to when you need to use a CDS with a SqlServer table which has an Identity field, where the value in the field is set by the server, so you don't know it during the CDS-side insert operation. One way to do that is explained in this article http://edn.embarcadero.com/article/20847. It involves assigning a temporary, negative, value to the Identity field in the CDS in the OnNewRecord event, and then using the Refresh method of the CDS to retrieve the server-side identity field value once the new record has been posted to the server by calling ApplyUpdates.

In D7, which I'm using for this answer as a sort of lowest common denominator, this technique seems to work fine to a RowVersion field as well. An extract from a sample project is below. To get it working, I had to define the RowVersion field as an ftBytes field with a size of 8, and to change the ProviderFlags to exclude pfInWhere and pfInUpdate from the AdoQuery's identity field, ID.

The RowVerStr field in the CDS is just to display the value legibly in a TDBGrid.

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSetProvider1: TDataSetProvider;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    ADOQuery1ID: TAutoIncField;  //  The ID field is an Identity field on the server
    ADOQuery1IntValue: TIntegerField;  //  a user field
    CDS1: TClientDataSet;
    CDS1ID: TAutoIncField;
    CDS1IntValue: TIntegerField;
    CDS1RowVersion: TBytesField;
    CDS1RowVerStr: TStringField;  //  just for display, fkInternalCalc, size = 20
    ADOQuery1RowVersion: TBytesField;
    procedure FormCreate(Sender: TObject);
    procedure CDS1AfterPost(DataSet: TDataSet);
    procedure CDS1AfterDelete(DataSet: TDataSet);
    procedure CDS1NewRecord(DataSet: TDataSet);
    procedure CDS1AfterInsert(DataSet: TDataSet);
    procedure CDS1CalcFields(DataSet: TDataSet);
  public
    FID : Integer;  //  To generate temporary value for CDS identity field
    function NextID : Integer;
  end;

[...]

function GetRowVerString(V : Variant) : String;
var
  i,
  Dim,
  Min,
  Max : Integer;
  i64 : Int64;
begin
  Result := '';
  if not VarIsArray(V) then Exit;
  Dim := VarArrayDimCount(V);
  Max := VarArrayHighBound(V, Dim);
  Min := VarArrayLowBound(V, Dim);

  for i := 0 to 7 do
    Int64Rec(i64).Bytes[i] := V[i];
  Result := IntToStr(i64);
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  //  CDS1ID is an ftAutoInc field, so we need to remove its read-only flag so
  //  that we can assign it a temporary negative value in the OnNewRecord event
  CDS1ID.ReadOnly := False;

  AdoQuery1RowVersion.ProviderFlags := AdoQuery1RowVersion.ProviderFlags - [pfInWhere, pfInUpdate];

  CDS1.Open;
  Caption := IntToStr(CDS1.RecordCount);
end;

procedure TForm1.CDS1AfterPost(DataSet: TDataSet);
begin
  if CDS1.ApplyUpdates(0) = 0 then
    CDS1.Refresh;
end;

procedure TForm1.CDS1AfterDelete(DataSet: TDataSet);
begin
  CDS1.ApplyUpdates(-1);
end;

function TForm1.NextID: Integer;
begin
  Dec(FID);
  Result := FID;
end;

procedure TForm1.CDS1NewRecord(DataSet: TDataSet);
begin
  CDS1.FieldByName('ID').AsInteger := NextID;
  CDS1.FieldByName('IntValue').AsInteger := Random(100);
end;

procedure TForm1.CDS1AfterInsert(DataSet: TDataSet);
begin
  CDS1.Post;
end;

procedure TForm1.CDS1CalcFields(DataSet: TDataSet);
begin
  CDS1RowVerStr.AsString := GetRowVerString(CDS1RowVersion.Value);
end;