How to sort a dataset without using ORDER BY
Posted: 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:
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.
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 byFinal 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.