Is it possible to use the adoquery result as a string?

Question

Im using the ado components to connect to an ms sql database. I know how to show the result of a query in db grid. But i want to store the results as string in an array for example. Is this possible or is there another way to use the query results?

Answer 1

I think you will have to write the code yourself to parse the recordset and put the values into an array.

Answer 2

There's no direct way to do that, however I use AdoQuery.Recordset.GetString to get the record as string, here's an example I use to export the Data to CSV file.

procedure TForm2.btnExportClick(Sender: TObject);
var
  Sl :TStringList;
  S  :string;
begin
  if SaveDialog1.Execute then
  begin
    try
      Sl := TStringList.Create;
      qryExport.First;
      if chkFields.Checked then
        Sl.Add(GetFields(qryExport));
      Sl.Add(qryExport.Recordset.GetString(2,-1,';'

             ,#13#10,'(Null)'));
      Sl.SaveToFile(SaveDialog1.FileName);
    finally
      Sl.Free;
    end;
  end;
end;

function TForm2.GetFields(qry: TADOQuery): string;
var
  I : Integer;
  S : string;
begin
  S := '';
  for I := 0 to qry.Fields.Count - 1 do
    S := S + qry.Fields[I].FieldName + ';';
  Result := S;
end

Answer 3

You can access any of the resulting fields by code. The dataset contains all of the records, but you will have to navigate through each one in code:

// navigate to the first record in the set
ADODataset1.first;
// while the dataset is NOT empty ...
while not ADODataset1.eof do
  begin
    // process a field
    sValue := ADODataset1.FieldByName('fieldname').AsString;
    DoSomething(sValue);
    // move the cursor to the next record
    ADODataset1.Next;
  end;

Answer 4

You can use ADORecordSet.GetRows to get your data into an array. This is a common practice in ASP to speed up the page load - rather than looping thru a recordset, you get the data into an array, close the recordset, and loop the array to display the contents. I think can be applied in Delphi too, with success.

var
  ...
  TableContents : OleVariant;
  ...
begin

  ...

  ADORecordSet.Open('select * FROM MyTable', ADOConnection, adOpenForwardOnly,
    adLockReadOnly, adCmdText);

  TableContents := ADORecordSet.GetRows(adGetRowsRest,EmptyParam,EmptyParam);

  someText := TableContents[1,1];

  ...

end;

Hope it helps.