Saturday, 2 January 2010

Bound DataGridView Sorting

Update: Sample Application




This is an old post I've been meaning to post since May, so here it is!

This one is a little strange, yet completely understandable.

I have an existing DataGridView with various bound items in it, a few of these items are lookup values - the grid uses a DataGridViewComboBoxColumn to cross-bind an ID with a list of ID/Lookup values.

The fun starts (or ends depending on your outlook in life) when the client asks why can't I sort on this column? Or, why doesn't this column sort correctly?

This is because in a bound DataGridView, the sorting action is delegated to the ApplySort of the underlying IBindingList. You can confirm this in Reflector if you disassemble the DataGridView and look through the SortInternal method. Simply, the grid code says "if you are data-bound, do this, else do that": 'this' is ApplySort, 'that' is Rows.Sort(IComparer, ListSortDirection).

One obvious solution is to create a dummy column and marshal the values between that and the underlying data column, so that when the users sort the unbound column - it sorts as they would expect; alphabetically.

My approach was slightly more contorted. In the derived DataGridView class we had (DataGridViewPlus), I created a new Sort method, taking an IComparer:

    public virtual void Sort(IComparer<DataGridViewRow> comparer)
    {
      // We sort the grid rows using the provided comparer.
      List<DataGridViewRow> sortedRows = new List<DataGridViewRow>();
 
      foreach (DataGridViewRow row in this.Rows)
        sortedRows.Add(row);
 
      sortedRows.Sort(comparer);
 
      // We need to work with the DataView object.
      BindingSource source = this.DataSource as BindingSource;
      DataView view = (source.Current as DataRowView).DataView;
 
      // We get the table structure - not any rows.
      DataTable tempTable = view.Table.Clone();
 
      // Importantly, we import the rows in the same order as our sorted list.
      foreach (DataGridViewRow row in sortedRows)
        tempTable.ImportRow((row.DataBoundItem as DataRowView).Row);
 
      // We can't simply copy the table across, so we clear and re-import.
      view.Table.Rows.Clear();
 
      foreach (DataRow row in tempTable.Rows)
        view.Table.ImportRow(row);
    }

I then created a nested class for a comparer specific to sorting rows based on the FormattedValue of a cell:

  public partial class DataGridViewPlus
  {
    public class RowDisplayTextComparer : IComparer, IComparer<DataGridViewRow>
    {
      #region Attributes & Properties
 
      private static int SortModifier = 1;
 
      private int _columnIndex = -1;
 
      #endregion
 
      #region Constructors
 
      public RowDisplayTextComparer(ListSortDirection sortDirection, int columnIndex)
      {
        if (sortDirection == ListSortDirection.Descending)
          SortModifier = -1;
        else
          SortModifier = 1;
 
        _columnIndex = columnIndex;
      }
 
      #endregion
 
      #region IComparer<DataGridViewRow> Members
 
      public int Compare(DataGridViewRow x, DataGridViewRow y)
      {
        int res = string.Compare(
                x.Cells[_columnIndex].FormattedValue.ToString(),
                y.Cells[_columnIndex].FormattedValue.ToString());
 
        return res * SortModifier;
      }
 
      #endregion
 
      #region IComparer Members
 
      int IComparer.Compare(object x, object y)
      {
        return Compare(x as DataGridViewRow, y as DataGridViewRow);
      }
 
      #endregion
    }
  }

In the real world, you use it by handling the column header clicking yourself for the column that requires a little help:

    private void gridGroups_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
    {
      if (e.ColumnIndex != colGroupID.Index)
      {
        colGroupID.HeaderCell.SortGlyphDirection = SortOrder.None;
        return;
      }
 
      ListSortDirection sort = ListSortDirection.Ascending;
 
      if (colGroupID.HeaderCell.SortGlyphDirection != SortOrder.None)
      {
        sort = colGroupID.HeaderCell.SortGlyphDirection == SortOrder.Ascending ? 
          ListSortDirection.Descending : ListSortDirection.Ascending;
      }
 
      gridGroups.Sort(new DataGridViewPlus.RowDisplayTextComparer(sort, colGroupID.Index));
 
      colGroupID.HeaderCell.SortGlyphDirection = sort == ListSortDirection.Ascending ?
        SortOrder.Ascending : SortOrder.Descending;
    }

I'm always open to constructive criticism about better ways to code the solution - so if you are reading this and think its naff, let me know :)

Its also a little rough around the edges - no null value checking in the comparer for instance. But:

12 comments:

  1. Hi Adam,

    one remark and one question:

    When I started working with this, I had a crash when importing the sorted rows, because DataBoundItem was null for the last row in the DataGridView. I modified your code as follows;

    foreach (DataGridViewRow row in sortedRows)
    {
    if (row.DataBoundItem != null)
    tempTable.ImportRow((row.DataBoundItem as DataRowView).Row);
    }

    And, then, more importantly, I have a number of foreign key constraints on the table, so merely clearing the table before re-importing it is no option. Any solution for this?

    Regards & thanks,
    Danny

    ReplyDelete
  2. Unfortunately, off the top of my head, no I don't have a solution to this currently. Without actually having tried this, I would suggest that you need to look at also importing any child rows into their own tables. However, this does highlight the limitation of my solution - in my scenario there was only one table.

    Perhaps look into creating dummy string columns to show and sort the display values whilst holding the underlying data-type in another hidden column.

    Sorry I can't be more help.

    ReplyDelete
  3. Hi Adem - I managed to fix it and get the code up and running by temporarily disabling the dataSet's constraints:

    dataSet.EnforceConstraints = false;
    table.Rows.Clear();

    foreach (DataRow row in tempTable.Rows)
    table.ImportRow(row);

    dataSet.EnforceConstraints = true;

    Everything works fine now - based on your original idea. I guess you're the first on the internet really finding a solid workaround for what should have been solved by Microsoft in the first place!

    Thanks - Danny

    ReplyDelete
  4. Very cool - I'll amend the code soon to reflect your fixes. Thanks for updating me!

    ReplyDelete
  5. Hi gents,

    Nice solution you have here. However it didn't work correctly when datagridview has been sorted already. I just added a small bit after
    view.Table.Rows.Clear();


    view.Sort = "";

    This makes it to sort column and display whole datagridview correctly even if it has been sorted by another column before.

    Thanks a lot,
    Alex

    ReplyDelete
  6. Nice one Alex. Danny above sent me the full code he modified. I will add your change and re-post it, definitely made it to my errands list!

    Glad people are getting use of it.

    ReplyDelete
  7. Hi Adam,
    this post is very interesting for me!
    But where can I get the re-posted and modified code of yours?

    Regards-
    Dietrich

    ReplyDelete
  8. Hi Dietrich,

    The code for the bound sorting is also part of the sample application I made for the drag-dropping.

    Regards,
    Adam

    ReplyDelete
  9. Having trouble converting some of it to VB.net. Any possibility of a conversion? (and yes I've run it through a number of converters)

    The sorting is driving me wild. I've spent 2 days trying various looping methods and cannot get anything to work with a data bound table.

    Unfortunately my data needs to be edited/added to, so I cannot simply add another data column, I must derrive so they can sort correctly, but still use the grid to add new data.

    ReplyDelete
    Replies
    1. Sorry I replied without even spotting the "Reply" button on your comment! See below...

      Delete
  10. I can convert my posted code to VB.NET without too much issue if you like, but to support adding/editing I'd need to revisit it and see what needs doing. I'm not sure if the grid I was working on at the time was directly editable or not, it was a long time ago :-)

    I'll look into it for you.

    ReplyDelete
  11. Thanks a bunch - this code you posted might look ridiculous, but it's exactly what I needed. My DataView was taking 2+ minutes to sort 8000 rows, which is incomprehensible. By using this method with my own comparer and sorting an rray, it takes milliseconds now.

    ReplyDelete