"WhileNotEof" or Anonymous Code in Real World

One observation struck me while looking though some existing database access code. There is code that opens a select SQL query, iterates through it while "EOF" flag is not true and then closes the query.

FDQuery1.Open;
try
  while not FDQuery1.Eof do
  begin
     // access fields in the current record here
     FDQuery1.Next;
  end;
finally
  FDQuery1.Close;
end;

How such code could be parameterised to avoid repetitions and reduce the number of lines? The thing is that the code that accesses the fields in the current record is every time different, because there are different fields in different tables.

What if we pass this code as anonymous code to a helper method defined on the FireDAC "TFDQuery" class?

Let's very quickly build a simple demo and verify if this approach would work. I'm using the latest Embarcadero Delphi 10.1 Berlin Update 2. File -> New multi-device application. Save All. Make sure you have a database connection defined in Data Explorer. I'm using sample MASTSQL InterBase database (C:\Users\Public\Documents\Embarcadero\Studio\18.0\Samples\Data\MASTSQL.GDB) and FireDAC as my preferred database library.

Add a new data module to the project and just drag-and-drop "VENDORS" table from Data Explorer onto the new data module. This will result in adding "MastsqlConnection: TFDConnection" component and one FireDAC query component "VendorsTable: TFDQuery" with "SELECT * FROM VENDORS" sql code.

Now let's define a simple class that will represent information about a vendor. Add a new unit to the project and name it "uTypes". Here is the code of a super simple "TVendor" class and corresponding vendor class generic list.

unit uTypes;

interface

uses
  System.Generics.Collections;

type
  TVendor = class
    VendorNo: integer;
    VendorName: string;
  end;

  TVendors = class(TObjectList)
  end;

implementation

end.

Now to the actual code. In the data module class we can define "GetVendors1" method that will take vendors list as parameter and fill it in code with vendors objects based on data from the query. Something list this:

procedure TDMMain.GetVendors1(AVendors: TVendors);
var v: TVendor;
begin
  if AVendors <> nil then
  begin
    AVendors.Clear;

    VendorsTable.Open;
    try
      while not VendorsTable.Eof do
      begin
        v := TVendor.Create;
        v.VendorNo := VendorsTable.FieldByName('VENDORNO').AsInteger;
        v.VendorName := VendorsTable.FieldByName('VENDORNAME').AsString;
        AVendors.Add(v);
        VendorsTable.Next;
      end;
    finally
      VendorsTable.Close;
    end;
  end;
end;

We could easily imagine a very similar code to read data from other tables. How to avoid the repetition?

Let's define a class helper for "TFDQuery" class.

unit uFDQueryHelper;

interface

uses
  FireDAC.Comp.Client;

type
  TProc = reference to procedure;

  TFDQueryHelper = class helper for TFDQuery
    procedure WhileNotEof(p: TProc);
  end;

implementation

{ TFDQueryHelper }

procedure TFDQueryHelper.WhileNotEof(p: TProc);
begin
  self.Open;
  try
    while not self.Eof do
    begin
      p;
      self.Next;
    end;
  finally
    self.Close;
  end;
end;

end.

If we add such a unit to the uses of our data module, methods defined in the helper would appear as just belonging to the "TFDQuery". The varying part of the helper is a reference to parameterless procedure "TProc". In this way the code passed as anonymous code as the parameter to "WhileNotEof" method will be executed in the right place for every record in the underlying table.

Now our database code can be much simpler. Here is the "GetVendors2" method that is using the "WhileNotEof" method from the helper.

procedure TDMMain.GetVendors2(AVendors: TVendors);
var v: TVendor;
begin
  if AVendors <> nil then
  begin
    AVendors.Clear;

    VendorsTable.WhileNotEof(procedure
    begin
      v := TVendor.Create;
      v.VendorNo := VendorsTable.FieldByName('VENDORNO').AsInteger;
      v.VendorName := VendorsTable.FieldByName('VENDORNAME').AsString;
      AVendors.Add(v);
    end
    );
  end;
end;

That's it. Anonymous methods in real world:-)

The demo source code can be downloaded from here.