Is it possible to use the ADOQuery result as a string?
I'm using the ADO components to connect to an MS SQL database. I know how to show the result of a query in a DBGrid. But I want to store the results as a string in an array, for example.
Is this possible, or is there another way to use the query results?
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
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;
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.