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 th开发者_StackOverflow中文版e 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.

---

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

---

Yes, i got the solution.

function GetStringsADO(Q:TADOQuery;strSQL,strConn:String):TStringList;
var
  GetStr:TStringList;
begin
    Q:=nil;
    Q:=TADOQuery.Create(nil);
    Q.ConnectionString:=strConn;
    Q.SQL.Text:= strSQL
    try 
        Q.Open;
    except on e:Exception do 
      begin
          ShowMessage('Error Detected: ' + e.Message)
          Exit;
      end;
    end;

    while Q.Eof = False do
    begin
        GetStr.Add(Q.Recordset.GetString(2,1,';',sLineBreak,'NULL'));
        Q.Next;
    end;
    Result:=GetStr;
end;

---

You can use TClientDataset for this task. It can do the work for you.
TClientDataset already has a method to dump the entire content of the Dataset to a File or a Stream (including a TStrinStream -for strings-).

Connect your TADOQuery to a TClientDataset (using a TDatasetProvider) and when you open the TADOQuery you can get the content with a code like this:

var
  stream:TStringStream;
begin
  ClientDataSet1.Active := not ClientDataSet1.Active;
  stream := TStringStream.Create;
  try
  if ClientDataSet1.Active then
    ClientDataSet1.SaveToStream(stream, dfXML);
    Memo1.Lines.Text := stream.DataString;
  finally
    stream.Free;
  end;

In this case is appropriae select the format dfXML or dfXMLUTF8.