可排序的DBGrid

Question

我想实现一个可排序的DBgrid (当单击列标题时对其行进行排序)。我设法使它可以按升序排序，但我不能按降序排序。以下是我的设计设置：

Query1.DatabaseName:='Test';
DataSetProvider1.DataSet:=Query1;
ClientDataSet1.ProviderName:=DataSetProvider1;
DataSource1.DataSet:=ClientDataSet1;
DBGrid1.DatSource:=DataSource1;

下面是我的代码片段：

procedure TForm2.FormShow(Sender: TObject);
begin
  Query1.Open;
  ClientDataSet1.Data:=DataSetProvider1.Data;
  ClientDataSet1.AddIndex('objnameDESC','objname',[ixDescending]);
  ClientDataSet1.AddIndex('SUM(cd.worktime)DESC','SUM(cd.worktime)',[ixDescending]);
end;

procedure TForm2.DBGrid1TitleClick(Column: TColumn);
begin
  case Column.Index of
  0: if ClientDataSet1.IndexFieldNames='objname' then
       ClientDataSet1.IndexFieldNames:='objnameDESC'
     else
       ClientDataSet1.IndexFieldNames:='objname';
  1: if ClientDataSet1.IndexFieldNames='SUM(cd.worktime)' then
       ClientDataSet1.IndexFieldNames:='SUM(cd.worktime)DESC'
     else
       ClientDataSet1.IndexFieldNames:='SUM(cd.worktime)';
  end;
end;

当我第一次点击一个列的标题时，排序是按升序进行的-所以到目前为止一切都是正常的。当我第二次单击时，我希望排序是按降序进行的，但我得到的却是消息：

Project ... raised Exception class EDatabaseError with message
'ClientDataSet1: Field 'objnameDESC' not found'.

你知道我做错了什么吗？

Answer 1

错误的赋值

除了错误的赋值之外，切换回“升序”是不可能的。

2列的需要4个索引。

假设'objname‘和'SUM(cd.worktime)’是字段。

procedure TForm2.FormShow(Sender: TObject);
....
ClientDataSet1.AddIndex('col0_asc','objname',[]);
ClientDataSet1.AddIndex('col0_desc','objname',[ixDescending]);
ClientDataSet1.AddIndex('col1_asc','SUM(cd.worktime)',[]);
ClientDataSet1.AddIndex('col1_desc','SUM(cd.worktime)',[ixDescending]);
....

使用ClientDataSet1.IndexName

procedure TForm2.DBGrid1TitleClick(Column: TColumn);
begin
  case Column.Index of
  0: if ClientDataSet1.IndexName='col0_asc' then
       ClientDataSet1.IndexName:='col0_desc'
     else
       ClientDataSet1.IndexName:='col0_asc';
  1: if ClientDataSet1.IndexName='col1_asc' then
       ClientDataSet1.IndexName:='col1_desc'
     else
       ClientDataSet1.IndexName:='col1_asc';
  end;
....

或更短的

procedure TForm2.DBGrid1TitleClick(Column: TColumn);
begin
     if ClientDataSet1.IndexName='col'+IntToStr(Column.Index)+'_asc' then
       ClientDataSet1.IndexName:='col'+IntToStr(Column.Index)+'_desc'
     else
       ClientDataSet1.IndexName:='col'+IntToStr(Column.Index)+'_asc';
....

，但最好测试处于活动状态(AddIndex =)的列数。

procedure TForm2.DBGrid1TitleClick(Column: TColumn);
begin
   if Column.Index < 2 then begin 
     if ClientDataSet1.IndexName='col'+IntToStr(Column.Index)+'_asc' then
       ClientDataSet1.IndexName:='col'+IntToStr(Column.Index)+'_desc'
     else
       ClientDataSet1.IndexName:='col'+IntToStr(Column.Index)+'_asc';
   end;
....

Answer 2

由于您已经在使用TClientDataSet，因此您可以使用我为此目的而开发的一个组件。创建一个实例，设置它的网格属性，它将自动连接到OnTitleClick事件。

type
  TDBGridSorter = class(TComponent)
  strict private
    FSortColumn: TColumn;
    FGrid: TDBGrid;
    procedure CreateIndex(const FieldName: string; Descending: Boolean);
    function GetDataSet: TClientDataSet;
      function MakeIndexName(const FieldName: string; Descending: Boolean): string;
    procedure SetSortColumn(const Value: TColumn);
    procedure SortByField(const FieldName: string; out Descending: Boolean);
  private
    procedure SetGrid(const Value: TDBGrid);
  strict protected
    procedure GridTitleClick(Column: TColumn);
    property DataSet: TClientDataSet read GetDataSet;
  public
    property Grid: TDBGrid read FGrid write SetGrid;
    property SortColumn: TColumn read FSortColumn write SetSortColumn;
  end;

procedure TDBGridSorter.CreateIndex(const FieldName: string; Descending: Boolean);
var
  cds: TClientDataSet;
  indexDef: TIndexDef;
  indexName: string;
begin
  cds := DataSet;
  if cds <> nil then begin
    indexName := MakeIndexName(FieldName, Descending);
    if cds.IndexDefs.IndexOf(indexName) < 0 then begin
      indexDef := cds.IndexDefs.AddIndexDef;
      indexDef.Name := indexName;
      indexDef.Fields := FieldName;
      indexDef.CaseInsFields := FieldName;
      if Descending then
        indexDef.DescFields := FieldName;
    end;
  end;
end;

function TDBGridSorter.GetDataSet: TClientDataSet;
begin
  if (Grid <> nil) and (Grid.DataSource <> nil) and (Grid.DataSource.DataSet is TClientDataSet) then
    Result := TClientDataSet(Grid.DataSource.DataSet)
  else
    Result := nil;
end;

procedure TDBGridSorter.GridTitleClick(Column: TColumn);
begin
  SortColumn := Column;
end;

function TDBGridSorter.MakeIndexName(const FieldName: string; Descending: Boolean): string;
const
  cAscDesc: array[Boolean] of string = ('_ASC', '_DESC');
begin
  Result := FieldName +  cAscDesc[Descending];
end;

procedure TDBGridSorter.SetGrid(const Value: TDBGrid);
begin
  if FGrid <> Value then begin
    if FGrid <> nil then begin
      FGrid.OnTitleClick := nil;
      FGrid.RemoveFreeNotification(Self);
    end;
    FGrid := Value;
    if FGrid <> nil then begin
      FGrid.FreeNotification(Self);
      FGrid.OnTitleClick := GridTitleClick;
    end;
  end;
end;

procedure TDBGridSorter.SetSortColumn(const Value: TColumn);
const
  cOrder: array[Boolean] of string = ('˄', '˅');
var
  descending: Boolean;
  S: string;
begin
  if FSortColumn <> nil then begin
    S := FSortColumn.Title.Caption;
    if StartsStr(cOrder[false], S) or StartsStr(cOrder[true], S) then begin
      Delete(S, 1, 2);
      FSortColumn.Title.Caption := S;
    end;
  end;
  FSortColumn := Value;
  if FSortColumn <> nil then begin
    SortByField(FSortColumn.FieldName, descending);
    FSortColumn.Title.Caption := Format('%s %s', [cOrder[descending], FSortColumn.Title.Caption]);
  end;
end;

procedure TDBGridSorter.SortByField(const FieldName: string; out Descending:
    Boolean);
var
  cds: TClientDataSet;
  curIndex: TIndexDef;
  N: Integer;
begin
  cds := DataSet;
  if cds <> nil then begin
    descending := false;
    N := cds.IndexDefs.IndexOf(cds.IndexName);
    if N >= 0 then begin
      curIndex := cds.IndexDefs[N];
      if SameText(FieldName, curIndex.Fields) then
        descending := not (ixDescending in curIndex.Options)
    end;
    { make sure the index exists }
    CreateIndex(FieldName, descending);
    cds.IndexName := MakeIndexName(FieldName, descending);
  end;
end;

Answer 3

您应该设置IndexName，而不是IndexFieldNames。IndexFieldNames接受字段名并动态创建索引。

procedure TForm2.DBGrid1TitleClick(Column: TColumn);
begin
  case Column.Index of
  0: if ClientDataSet1.IndexFieldNames='objname' then
       ClientDataSet1.IndexName:='objnameDESC'
     else
       ClientDataSet1.IndexFieldNames:='objname';
  1: if ClientDataSet1.IndexFieldNames='SUM(cd.worktime)' then
       ClientDataSet1.IndexFieldNames:='SUM(cd.worktime) DESC'
     else
       ClientDataSet1.IndexFieldNames:='SUM(cd.worktime)';
  end;
end;