Returning a TList from a TADOQuery

Question

I wish to create a function which makes use of a TADOQuery and to which I can pass a select statement, it will in turn fetch the results from the database and return the results as a TList

function GetList(SelectStatement : string) : TList;
var
  ResultList : TList;
Begin
  with ADOQuery do
    Begin
      close;
      SQL.Clear;
      SQL.Add(SelectStatement);
      open;
      //This is where am not sure
      //Get the results and populate the ResultList
    End;

  Result := ResultList;
End;

Note that the function is a generic, so one cannot "hard code" the ResultList.

Is the above achievable?

Answer 1

You might use a array of variant to store the fields of one row and generic list of these "row arrays" to keep these.

uses Generics.Collections;
{$R *.dfm}

Type
  TVarArray = Array of Variant;
  TVarList = TList<TVarArray>;

Function GetAds2List(Ads:TAdoDataset):TVarList;

  Function GetRow:TVarArray;
    var
    i: Integer;
    begin
      SetLength(Result, ADS.FieldCount);
      for i := 0 to ADS.FieldCount - 1 do
      begin
        Result[i] := ADS.Fields[i].Value;
      end;    
    end;
begin
    Result := TVarList.Create;
    Ads.First;
    While not ADS.Eof do
    begin
      Result.Add(GetRow);
      Ads.Next;
    end;
end;

Example usage:

procedure TForm3.Button1Click(Sender: TObject);
var
  l:TVarList;
  I,J: Integer;
  s:String;
  Function sep(idx:Integer):String;
   begin
     if idx=0 then
        Result := ''
     else
        Result := ' , ';    
   end;
begin
    ReportMemoryLeaksOnShutDown := true;
    l := GetAds2List(Ads1);
    for I := 0 to l.Count - 1 do
      begin
        s := '';
        for j := 0 to High(l[i])  do
           begin
             s := s + sep(j) + VarToStrDef(l[i][j],'*NULL*');
           end;
        memo1.lines.add(s);
      end;    
    l.Free;
end;