• Blog
  • Interaction with low-level FireDac objects in TDataSet

Interaction with low-level FireDac objects in TDataSet

Reading from inner structures

Publish date:
Discover more of what matters to you

FireDac data access components have a rather high-speed performance within a standard TDataSet program interface. But when you are working with large data volumes, the TDataSet interface can be inconvenient and not fast enough. Moreover, as TDataSet is closely tied with the display of data by the DB-Aware components, it makes too many extra actions and sends too many notifications. It also offers data processing and reading line-by-line which means that the current record is being moved. In order to avoid all this and to have the possibility to deal with TDataSet data as a flat data array, we can go one level lower and work with objects that represent the inner realization of TFDDataSet. Unlike the BDE or TClientDataSet, this realization is written in Delphi and a full source code is available during the debugging process.

This approach has some obvious disadvantages. For example, it is impossible to use the same techniques with another data access set and also there are chances that Embarcadero will change the object data realization. But this realization is documented and in general, Delphi has always been known for its backward compatibility and it means that we can suppose that our “low-level” approach will have a long life ad is not just a “hack”.

We will consider data that are stored locally and won’t touch the principle of working directly with databases like in-memory TFDDataSet which is an ancestor of all  TDataSet in FireDac.

TFDDataSet inner objects

The object of the TFDDatsTable type that is available via the public property TFDDataSet.Table is storage inside TFDDataSet. This object physically stores data, ensures recording the log of changes, and storing record versions in the case of CachedUpdates. Moreover, this object can be common for several sets of the TFDataSet type in the case of calling CloneCursor. Access to records is provided by the Rows property: TFDDatSTableRowList is a list of object rows TFDDatsRow. The row class provides access to data via the group of Value properties Value – ValueI by column index, ValueS by column name, ValueO by column object TFDDatSColumn. The TFDDatsTable.Columns property provides a list of column objects. This information is enough to provide access to all data of TFDataSet that are physically stored in it.

For our convenience, let’s create a class helper 

12345678
TFDDataSetHelper = class helper for TFDDataSet
private
function GetFullRawData(AColumnIndex: Integer; ARowIndex: Integer): Variant;
function GetFullRawDataSize: TSize;
public
property FullRawData[AColumnIndex: Integer; ARowIndex: Integer]: Variant read GetFullRawData;
property FullRawDataSize: TSize read GetFullRawDataSize;
end;

 Two properties:

  • FullRawDataSize- data size (number of columns X and rows Y)
  • FullRawData – access to data by the number of a column and row

Realization

123456789
function TFDDataSetHelper.GetFullRawDataSize: TSize;
begin
Result := TSize.Create(Table.Columns.Count, Table.Rows.Count);
end;
function TFDDataSetHelper.GetFullRawData(AColumnIndex: Integer; ARowIndex: Integer): Variant;
begin
Result := Table.Rows[ARowIndex].ValueI[AColumnIndex];
end;

Now we can deal with TFDDataSet as a flat data array.

123
for var i := 0 to MyDataSet.FullRawDataSize.cx – 1 do // columns
for var j := 0 to MyDataSet.FullRawDataSize.cy – 1 do // rows
DoSomething(MyDataSet.FullRawData[i, j]);

This method is effective, for instance, for quick data export to MS Excel. Instead of a cycle within records “while not MyDataSet.Eof do MyDataSet.Next; end;” that requires storing bookmark, calls to  DisableControls EnableControls, etc. everything can be done with a simple cycle.

But using this method, we ignore filters, range and sorting of TFDataSet. To get the current data we need to consider the following inner object – TFDDatSView. This object represents a list of rows that are included in the Table that can be sorted and/or filtered in accordance with such properties as IndexName, IndexFieldNames, Filter, SetRange, etc. Each active object TFDIndex inside has a correspondence in a form of the relevant TFDDatSView object. The ongoing dataset TFDDatSView is represented by the TFDDataSet.SourceView property.

Let’s upgrade our helper.

12345678
TFDDataSetHelper = class helper for TFDDataSet
private
function GetCurrentRawData(AColumnIndex: Integer; ARowIndex: Integer): Variant;
function GetCurrentRawDataSize: TSize;
public
property CurrentRawData[AColumnIndex: Integer; ARowIndex: Integer]: Variant read GetCurrentRawData;
property CurrentRawDataSize: TSize read GetCurrentRawDataSize;
end;

And, accordimgly, its realization: 

123456789
function TFDDataSetHelper.GetCurrentRawDataSize: TSize;
begin
Result := TSize.Create(Table.Columns.Count, Source.Rows.Count);
end;
function TFDDataSetHelper.GetCurrentRawData(AColumnIndex: Integer; ARowIndex: Integer): Variant;
begin
Result := Source.Rows[ARowIndex].ValueI[AColumnIndex];
end;

Access via this property will provide us with data in such order and set that the data will display the DB-Aware components – with filters, sorting, etc.

We should mention that it is simple to create your own TFDDatSView by indicating sorting and filtering:

1234
var oMyView := MyDataSet.Table.Select('field2 = 1', 'field1:A');
for var i := MyDataSet.Table.Columns.Count – 1 do
for var j := MyView.Rows.Count – 1 do
DoSomething(MyView.Rows[j].ValueI[i]);

This method is rather convenient but the speed of filtration remains the same as here we use the expression of a filter that requires analysis, etc. When we have a big data set or a high complexity of filter logic, it will take a lot of time. That’s why let’s choose a more difficult option and use a function of row filtering. To do that we need to introduce an additional object – a filter mechanism TFDDatSMechFilter.

For simplifying the task, let’s create a temporary object-functor with filter functionality. Let’s find records, where the field inc = 4.

12345678910111213141516171819202122232425262728293031323334353637383940414243
type
TFilterFunctor = class
public
function FilterRow(AMech: TFDDatSMechFilter; ARow: TFDDatSRow; AVersion: TFDDatSRowVersion): Boolean;
end;
function TFilterFunctor.FilterRow(AMech: TFDDatSMechFilter; ARow: TFDDatSRow; AVersion: TFDDatSRowVersion): Boolean;
begin
Result := VarSameValue(ARow.ValueI[0], 4);
end;
procedure Test;
begin
var oQuery := TFDQuery.Create(nil); // Let’s create TFDQuery and select Try data Try
oQuery.SQL.Add('select * from port');
oQuery.ConnectionName := 'SAMONET';
oQuery.Open;
var oTemp := TFilterFunctor.Create; // Let’s create our temporary functor Try
var oMech := TFDDatSMechFilter.Create('', [], oTemp.FilterRow); // Filtration mechanism
var oView := TFDDatsView.Create(oQuery.Table, 'port', vcUser); //View
try
oView.Name := 'IncEqual4FilteredView'; // any name
oView.Mechanisms.Add(oMech); // let’s add a mechanism to our View
oView.Active := True; // let’s make it active
for var I := 0 to oView.Table.Columns.Count – 1 do // and display data
for var j := 0 to oView.Rows.Count – 1 do
begin
Trace(oView.Table.Columns[i].Name + '=' + VarToStr(oView.Rows[j].ValueI[i]));
end;
finally
FreeAndNil(oView); //Please, bear in mind that this mechanism itself will be destroyed together with View
end;
finally
FreeAndNil(oTemp);
end;
finally
FreeAndNil(oQuery);
end;
end;

This example is more complicated than a variant with a text filter but it can work faster. In FireDac there is also a set of mechanisms that can be added to TFDDatsView – sorting, filtering by record status (in the CachedUpdates regime), Range, Distinct. When it is required to rebuild View, it is necessary to call the Rebuild method and then the list of rows will be built once again.

TFDDatsView has functions of data search – Find, IndexOf, Search group. They return a row index to View.

1234567891011
var oQuery := TFDQuery.Create(nil);
try
oQuery.SQL.Add('select * from port');
oQuery.ConnectionName := 'SAMONET';
oQuery.Open;
var iRowIndex := oQuery.SourceView.Find([5], 'inc');
if iRowIndex >= 0 then
ShowMessage(oQuery.SourceView.Rows[iRowIndex].AsString['name']);
finally
FreeAndNil(oQuery);
end;

All the above-mentioned methods are described (though not in a full volume) in documents and examples provided by Delphi. The next method is an obvious “hack”. Using this method, you need to understand, that you really need to have weighty reasons for doing it this way as we need to rely on the inner TFDDataSet realization that can be easily changed by Embarcadero.

We got a row from Bookmark  without following it.

Bookmark itself is TArray<byte> – a byte dynamic array that has a structure inside.

12345
TFDBookmarkData = record
[unsafe] FRow: TFDDatSRow;
FRowIndex: Integer;
FResultSetNum: Integer;
end;

Let’s pay attention to the FRow field.

1234567
function GetValueFromBookmark(ADataSet: TFDDataSet; const ABookmark: TBookmark; const AFieldName: String): variant;
var
pBmk: PFDBookmarkData;
begin
pBmk := PFDBookmarkData(TRecordBuffer(ABookmark));
Result := pBmk.ValueS[AFieldName];
end;

Please, bear in mind that it is crucial to be confident that Bookmark points to valid data. It is necessary to check it in advance using the BookmarkValid method, otherwise, we will get  Access Violation when we turn to FRow.

We’ve considered the ways to deal with TFDDataSet data without using the standard TDataSet program interface. In usual cases there is no need to apply these methods, it is enough just to use TDelphi-way. But when it is necessary to ensure a high-speed performance and to avoid any extra events in the UI that are created due to the TDataSet functioning, it is possible to omit any additional actions and turn to data “directly”.

by Michael Longneck, senior content contributor at Softacom

Subscribe to our newsletter and get amazing content right in your inbox.

This field is required
This field is required Invalid email address

Thank you for subscribing!
See you soon... in your inbox!

confirm your subscription, make sure to check your promotions/spam folder

Subscribe to our newsletter and get amazing content right in your inbox.

You can unsubscribe from the newsletter at any time

This field is required
This field is required Invalid email address

You're almost there...

A confirmation was sent to your email

confirm your subscription, make sure to check
your promotions/spam folder