TFDMemTable a better alternative to TClientDataset in Delphi

TFDMemTable

FDMemTable is FireDAC dataset component that supports in memory table functionality. By using FDMemTable we can store data in a table format in local memory. We don’t need any database connection for this. We just need to add fields with definitions. Then we can create the table and add, edit, delete records. If we are only using FireDAC, then FDMemTable should be used only when we have memory-only data. And also in some special cases; for example, you can load CSV file into FDMemTable, then use LocalSQL to query this CSV data.

Lets create the in-memory dataset.

Var

   FDMemTable1 : TFDMemTable;

………..

with FDMemTable1.FieldDefs do

begin

   Add(‘EmpID’, ftInteger, 0, True);

   Add(‘EmpName’, ftString, 50, False);

   CreateDataset;

   Open;

end;

Or

with FDMemTable1.FieldDefs.AddFieldDef do

begin

  Name := 'EmpID';

  DataType := ftInteger;

  Required := True;

end;

with FDMemTable1.FieldDefs.AddFieldDef do

begin

  Name := 'EmpName';

  DataType := ftString;

  Size := 50;

end;

FDMemTable1.CreateDataset;

FDMemTable1.Open;

Adding data to FDMemtable1

with FDMemTable1 do

begin

   Append;

   Fields[0].AsInteger := 1;

   Fields[1].AsString := ‘jack’;

   Post;

end;

Editing data to FDMemtable1

with FDMemTable1 do

begin

   Edit;

   Fields[0].AsInteger := 1;

   Fields[1].AsString := ‘jack rollins’;

   Post;

end;

Deleting data to FDMemtable1

FDMemTable1.Delete;

Delete all records

FDMemTable1.EmptyDataset;

Sorting Data

We have to use Index for sorting. So we can use property Indexes, IndexDefs or IndexFieldNames.

Lets check one by one..

FDMemTable1.IndexFieldNames := ‘EmpID ASC’;

Or

FDMemTable1.IndexDefs.Add('Index1', 'EmpID', [ixPrimary]);

FDMemTable1.IndexName := 'Index1';

Or

with FDMemTable1.Indexes.Add do

begin

  Name := 'Index2';

  Fields := 'EmpName';

  Options := [soNoCase];

end;

FDMemTable1.IndexName := 'Index2';

Finding Data

Just like other dataset we can use Locate, Lookup and Find methods to find a specific record.

Locate

  FDMemTable1.Locate('EmpID', 5, []);

  Or

  FDMemTable1.Locate('EmpName', ’Raj’, [loCaseInsensitive]);

Lookup

var

  sName: string;

…..

  sName := FDMemTable1.Lookup('EmpID', 5, 'EmpName');

FindKey 

Findkey will work if only any Index is applied

  FDMemTable1.FindKey([1]);

  FDMemTable1.FindFirst;

  FDMemTable1.FindLast;

  FDMemTable1.FindNext;

  FDMemTable1.FindPrev;

Filtering Data

FDMemTable1.Filtered := False;

FDMemTable1.Filter := ‘EmpID=15’;

FDMemTable1.Filtered := True;

Copy data from one FDMemTable to another

CopyDataSet method

The most simple way to copy the structure and data from a TdataSet or a TFDMemTable to another TFDMemTable is to use the CopyDataSet method:

FDMemTable1.CopyDataSet(SourceFDMemTable2, [coStructure, coRestart, coAppend]);

FDMemTable1.CopyDataSet(SourceDataset2, [coStructure, coRestart, coAppend]);

Data property

// UniDirectional must be false 

FDQuery1.FetchOptions.Undirectional := False;

FDQuery1.Sql.Text := ‘select * from Employee’;

FDQuery1.Open;

FDMemTable1.Data := FDQuery1.Data;

FDMemTable1.First;

while not FDMemTable1.Eof do

begin

  FDMemTable1.Edit;

  .......

  FDMemTable1.Post;

  FDMemTable1.Next;

end;

Difference between CopyDataset and Data property

1. CopyDataSet can copy from a non-FireDAC dataset but Data property allows you to copy data only from a FireDAC dataset.

2. CopyDataSet works through TDataSet firing appropriate events but Data property works through no events are fired.

3. CopyDataSet copies only current field values but Data property copies all record field versions and preserves the row state (inserted, deleted, updated, or unchanged).

Assigning values to Data is much faster than CopyDataSet.

Optimize the performance of TFDMemTable

Set the following properties LogChanges, FetchOptions, ResourceOptions, and UpdateOptions, DisableControls.

  FDMemTable1.LogChanges := False;

  FDMemTable1.ResourceOptions.SilentMode := True;

  FDMemTable1.UpdateOptions.LockMode := lmNone;

  FDMemTable1.UpdateOptions.LockPoint := lpDeferred;

  FDMemTable1.UpdateOptions.FetchGeneratorsPoint := gpImmediate;

  FDMemTable1.DisableControls;

  FDMemTable1.BeginBatch;

  try

    for i := 1 to 1000 do begin

      FDMemTable1.Append;

      // ...

      FDMemTable1.Post;

    end;

  finally

    FDMemTable1.EndBatch;

    FDMemTable1.EnableControls;

  end;