How to sort a dataset without using ORDER BY

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

How to sort a dataset without using ORDER BY

Post by brace » Tue 08 Jan 2013 10:16

Hello, I would like to know if there is a method/suggseted techinque for sorting a dataset.

I cannot use ORDER BY because what I do is "merging the data from 2 datasets", of course merging the data I lose the ordering.

Let me write the code I use:

Code: Select all

// first helper function
procedure CopyRecordValue(daDataSet, aDataSet: TDataSet; exclFields: string = '');
var
  i: integer;
  tmpString: string;
  aFieldList: TStringList;
begin
  aFieldList := TStringList.Create;
  try
    aDataSet.GetFieldNames(aFieldList);
    i := 0;
    repeat
      inc(i);
      tmpString := GetSubString(exclFields, ';', i);
      if tmpString <> '' then
        if aFieldList.IndexOf(tmpString) >= 0 then
          aFieldList.Delete(aFieldList.IndexOf(tmpString));
    until tmpString = '';

    for i := 0 to daDataSet.FieldCount - 1 do
      if aFieldList.IndexOf(daDataSet.Fields[i].FieldName) >= 0 then
        if not aDataSet.FieldByName(daDataSet.Fields[i].FieldName).Calculated then
          if daDataSet.Fields[i].IsNull then
            aDataSet.FieldByName(daDataSet.Fields[i].FieldName).Clear
          else
          begin
            case aDataSet.FieldByName(daDataSet.Fields[i].FieldName).DataType of
              ftBoolean:
                aDataSet.FieldByName(daDataSet.Fields[i].FieldName).AsBoolean :=
                  daDataSet.Fields[i].AsBoolean;
              ftCurrency:
                aDataSet.FieldByName(daDataSet.Fields[i].FieldName).AsCurrency :=
                  daDataSet.Fields[i].AsCurrency;
              ftDate, ftTime, ftDateTime:
                aDataSet.FieldByName(daDataSet.Fields[i].FieldName).AsDateTime :=
                  daDataSet.Fields[i].AsDateTime;
              ftString, ftMemo, ftFixedChar, ftWideString,ftWideMemo:
                aDataSet.FieldByName(daDataSet.Fields[i].FieldName).AsString :=
                  daDataSet.Fields[i].AsString;
              ftInteger, ftSmallint, ftWord:
                aDataSet.FieldByName(daDataSet.Fields[i].FieldName).AsInteger :=
                  daDataSet.Fields[i].AsInteger;
              ftFloat, ftBCD:
                aDataSet.FieldByName(daDataSet.Fields[i].FieldName).AsFloat :=
                  daDataSet.Fields[i].AsFloat;
              ftBlob:
                aDataSet.FieldByName(daDataSet.Fields[i].FieldName).Value :=
                  daDataSet.Fields[i].Value;
            end;
          end;
  finally
    aFieldList.Free;
  end;
end;

// second helper function (this uses the previous one)
procedure MergeDataSets(daDataSet, aDataSet: TDataSet; exclFields: string = '');
begin
  daDataSet.First;
  while not daDataSet.EOF do
  begin
    aDataSet.Append;
    CopyRecordValue(daDataSet, aDataSet, exclFields);
    aDataSet.CheckBrowseMode;
    daDataSet.Next;
  end;
  aDataSet.First;
end;


// This is how I call the function (sqlMainQUery is my dataset, sqlInsertedData is another dataset (a TMSQuery) whose content I merge in sqlMainQuery:
MergeDataSets(sqlMainQuery, sqlInsertedData);

//after this I would like to do something like:
sqlMainQuery.SortDataset('EXPECTED_DATE')

// where EXPECTED_DATE is the field I would like to sort by
Please let me know if there is something like "SortDataset" or if you can provide a solution.

Final note: of course I could rewrite all the code and do a single query insetead of merging two, but I am working on old code and this will be a huge amount of work for me so I would prefer to find a way to sort the data.

brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Re: How to sort a dataset without using ORDER BY

Post by brace » Tue 08 Jan 2013 13:31

Found IndexFieldNames is the solution, I never used that. So my issue is solved.

AndreyZ

Re: How to sort a dataset without using ORDER BY

Post by AndreyZ » Tue 08 Jan 2013 16:15

You found the correct solution.
Please note, that using the IndexFieldNames property represents local ordering (performed on the local computer), when using the ORDER BY statement represents server ordering (performed on the server).

Post Reply