CODEWITHSUNDEEP
sqldelphi
Pieter B
Pieter B

Reputation: 1967

Variable structure for database results

A lot of times when we query the database, we just need one column with varchar. So I've made a nice function for querying the database and putting the results in a stringlist:

function Getdatatostringlist(sqlcomponent, sqlquery: string): TStringlist;

What I'm looking for now is basically the same function but for results with multiple columns where you don't know in advance what type the data is, be it varchar, int, datetime.

What kind of datastructure would be good to use here.

The reason I want this is that I try not to work on open datasets. I like much more to fetch all results into a temporary structure, close the dataset and work on the results.

After Kobiks reply about using in Memory datasets I came up with the following, it's fast put together to test the concept:

procedure TForm1.Button2Click(Sender: TObject);
var
  MyDataSet : TAdoDataSet;
begin
 MyDataSet := GetDataToDataSet('SELECT naam FROM user WHERE userid = 1', ADOConnection1);
 try
   Form1.Caption := MyDataSet.FieldByName('naam').AsString;
 finally
   MyDataSet.free;
 end;
end;

function TForm1.GetDataToDataSet(sSql: string; AdoConnection: TADOConnection): TAdoDataSet;
begin
  Result := TAdoDataSet.Create(nil);
  Result.LockType := ltBatchOptimistic;
  Result.Connection := AdoConnection;
  Result.CommandText :=  sSql;
  Result.Open;
  Result.Connection := nil;
end;

I think this is something to build on.

Upvotes: 1

Views: 164

Answers (3)

alzaimar
alzaimar

Reputation: 4622

Why don't you like working with open datasets? They usually do not block the server. Copying the data from the dataset to whatever you want is extra overhead which is most likely not necessary.

A dataset provides exactly the functionality you want: A matrix with variable columns and rows.

EDIT: However, if you have iterate through the dataset often, you should consider creating a class holding the relevant information and then copy the data into a generic list, dictionary, tree or whatever you need as fast lookup structure.

Of course you could think of building something smart which can be as flexible as a dataset but: The more general things get, the poorer the performance (usually).

Upvotes: 1

kobik
kobik

Reputation: 21252

You should use any disconnected in-memory TDataSet descendant, such as TClientDataSet.

Do not attempt to re-invent the wheel by storing a record-set in some new "Variant" structure. A TClientDataSet already contains all features you need to manipulate a "temporary" data structure.

Here is how you create a TClientDataSet structure:

cds.FieldDefs.Add('id', ftInteger);
cds.FieldDefs.Add('name', ftString, 100);
// ...
// create it
cds.CreateDataSet; 
// add some data records
cds.AppendRecord([1, 'Foo']);
cds.AppendRecord([2, 'Bar']);

Many TDataSets has an ability to be used as an in-memory (client) datasets depending on the provider and LockType, for example a TADODataSet with LockType=ltBatchOptimistic could fetch results-set from the server, and then remain disconnected.

Upvotes: 7

bummi
bummi

Reputation: 27377

For exchanging Data with Excel this structure is usefull, might be useful for other purposes.

Function GetDatasetasDynArray(Ads: TDataset; WithHeader: Boolean = true): Variant;
// 20130118 by Thomas Wassermann
var
  i, x, y: Integer;
  Fields: Array of Integer;
begin
  x := 0;
  y := Ads.RecordCount;
  if WithHeader then
    inc(y);
  SetLength(Fields, Ads.FieldCount);
  for i := 0 to Ads.FieldCount - 1 do
    if Ads.Fields[i].Visible then
    begin
      Fields[x] := i;
      inc(x);
    end;
  SetLength(Fields, x);
  Result := VarArrayCreate([0, y - 1 , 0, length(Fields) - 1], VarVariant);
  y := 0;
  if WithHeader then
  begin
    for i := Low(Fields) to High(Fields) do
    begin
      Result[y, i] := Ads.Fields[Fields[i]].DisplayLabel;
    end;
    inc(y);
  end;
  try
    Ads.DisableControls;
    Ads.First;
    while not Ads.EOF do
    begin
      for i := Low(Fields) to High(Fields) do
      begin
        Result[y, i] := Ads.Fields[Fields[i]].Value;
      end;
      Ads.Next;
      inc(y);
    end;
  finally
    Ads.EnableControls;
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
 DynArray:Variant;
begin

   DynArray := GetDatasetasDynArray(Adodataset1,true);
   //DynArray[0,x] Header or First row
   //DynArray[1,x] First row or SecondRow
   Excel.Range.Value := DynArray;
end;

Upvotes: 3

Related Questions