Interaction with low-level FireDac objects in TDataSet.

Reading from inner structures

Posted by: Alex A. Publish date: 05.05.2022

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 

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

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.

  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.

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: 

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:

  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.

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.

  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.

TFDBookmarkData = record

    [unsafe] FRow: TFDDatSRow;

    FRowIndex: Integer;

    FResultSetNum: Integer;

  end;

Let’s pay attention to the FRow field.

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”.

In one of our future articles, we will have a look at the “direct” data editing in inner TFDDataSet objects.

by Michael Longneck, senior content contributor at Softacom

Talk to us and get your project moving
Book a free consultation with a solution expert.
Name
This field is required
E-mail
Company web site
This field is required
Phone Number
This field is required