Ofline
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;