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

Interaction with low-level FireDac objects in TDataSet.

Interaction with low-level FireDac objects in TDataSet.

Publish date: 05.05.2022

    DISCOVER MORE OF WHAT MATTERS TO YOU

    Interaction with low-level FireDac objects in TDataSet

    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

    Tags

    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