FDQuery (SQL) results to array of records in Delphi

Question

Is there a way to copy FDQuery results to array of records directly? So this is declared types:

type
  TPerson = record
    id: integer;
    name: string;
    surname: string;
  end;

type
  TPersonList = array of TPerson;

and I have SQLite DB with columns id, nameandsurname. Normally I should add values to that array like this:

var Persons: TPersonList;
begin
Persons[0].id := FDQuery1.FieldByName('id').AsInteger;
....
....
....
end;

but is there any better/compact way to do this? Some function like:

while not FDQuery.eof do begin
    ...
    Persons[i] := FDQuery1[i];
    ...
end;

Maybe directly or in the loop? Or should i create some function to do that? As I have many columns, and many different record types with different structure but with exact structure like in db.

Answer 1

There is no simple way to do this directly, but there are techniques you can use to improve the efficiency of both the source and the runtime performance. One way might be to create a simple helper to initialise new values of the appropriate type from a given dataset.

You could use a record method for this but this will make using cached field references a bit less elegant, so I suggest a separate, dedicated initialiser class. This can use cached field references for efficiency:

type
  TPersonFDInitialiser = class
  private
    fldID: TIntegerField;
    fldName: TStringField;
    fldSurname: TStringField;
    function get_NewValue: TPerson;
  public
    constructor Create(const aSource: TDataset);
    property NewValue: TPerson read get_NewValue;
  end;

Caching the field references in the constructor avoids having to look them up by name every time you retrieve values for each record. Using the appropriate class for the field data type allows direct access to each field value without any conversions:

constructor TPersonFDInitialiser.Create(const aSource: TDataset);
begin
  inherited;

  fldID      := aSource.FieldByName('id') as TIntegerField;
  fldName    := aSource.FieldByName('name') as TStringField;
  fldSurname := aSource.FieldByName('surname') as TStringField;
end;


function TPersonFDInitialiser.get_NewValue: TPerson;
begin
  result.ID      := fldID.Value;
  result.Name    := fldName.Value;
  result.Surname := fldSurname.Value;
end;

As you can see, this isn't a huge amount of work (a little more than would be needed to initialise 1 record value explicitly) but makes iterative use both more elegant and quicker to write, looking a little something like this:

recno := 0;
init := TPersonFDInitialiser.Create(qryPersons);
try
  while NOT qryPersons.EOF do
  begin
    persons[recno] := init.NewValue;

    qryPersons.Next;
    Inc(recno);
  end;

finally
  init.Free;
end;

Where qryPersons is some TFDQuery that returns person rows and persons is your array of TPerson records (sized/dminesioned appropriately, of course)

By using the TDataset base class (from which TFDQuery ultimately derives) you can use this initialiser class anywhere that you need to initialise a TPerson from a TDataset descendant, whether that is a TFDQuery or a TClientDataset or whatever (as long as the fields in that dataset are named consistently, as written, but the initialiser could be made more flexible in this regard if needed. This is left as an exercise).

Going Further

There are numerous enhancements that could be made to improve the utility of such an initialiser class according to your needs, for example:

// To initialise a one-off, new TPerson value from a data set use a 
//  class function which will internally create an initialiser, obtain 
//   a new TPerson then destroy the initialiser for you:
//
// Note that this will need to be overloaded if it has the same name as
//  the instance method (which must also then be overloaded):

class function TPersonFDInitialiser.NewValue(const aSource: TDataset): TPerson; overload;


// Implement a procedure which will initialise an existing TPerson value
//  (by reference) with values from the current record.
// 
// Again, a class procedure overload could be provided for one-off use
//  taking care of creating and freeing the required initialiser:

class procedure TPersonFDInitialiser.SetPerson(const aSource: TDataset; var aPerson: TPerson); overload;
procedure TPersonFDInitialiser.SetPerson(var aPerson: TPerson); overload;

NOTE: These SetPerson methods could be implemented in such a way that get_NewValue actually calls that method so that you have only one method in the entire implementation which actually does any setting of values. This would eliminate duplication of initialisation code, improving reliability and maintainability of the initialiser class:

function TPersonFDInitialiser.get_NewValue: TPerson;
begin
  SetPerson(result);
end;


procedure TPersonFDInitialiser.SetPerson(var aPerson: TPerson);
begin
  aPerson.ID      := fldID.Value;
  aPerson.Name    := fldName.Value;
  aPerson.Surname := fldSurname.Value;
end;