Custom Sorting in Data Grid

Custom Sorting in Data Grid

• Ion101

  Do you sort on data table DataGridview has a sort method, you can implement your custom sort method on it, refer to following link

  http://msdn2.microsoft.com/en-us/library/wstxtkxs.aspx

  
  
  

• vagrant

  Any one with an answer
  
  

• Kent Liu

  I am using VC 2003 and i guess DataGridView is available only in VS 2005.
  

• U_T_A

  For all those, who have had a tough time performing custom sorting on a particular (or group of) column(s) in a DataGridView(DGV) bound to a DataSource, I have finally found one way, that I believe could fit most custom sorting situations in Bound mode for a DGV.
  Although this solution uses a DataTable as the DGV's source, it should not be too difficult to adapt it to other sources.
  
  The basic problem when using a table as a DGV's source is the closely guarded nature of System.Data assembly. e.g. You cannot explicity create a DataRow, you remove a row from a Table & it loses all its contents etc.
  All this makes interchanging two rows in a table during the Sort operation extremely difficult. Moreover, if you do it somehow, managing the DataBindings & parent-child relationships is another issue.
  
  After considerable head-scratching to implement a Sort operation within these constraints & failing to do so (although I would love to see such a solution), I tried a completely different approach which worked.
  
  The idea is simple. Add all the rows from the DGV's source DataTable to a List, perform the sort operation on this list, construct a new table from this sorted list & set it as the DGV's DataSource.
  I have used this approach in a Grid containing nearly 30K rows with considerably good performance. So, I believe performance also should not be an issue.
  
  Here goes the code:
  

  Code Block

  Friend Sub DataGridView_ColumnHeaderMouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dgv.ColumnHeaderMouseClick
  Try
  Me.dgv.Cursor = Cursors.WaitCursor
  
  'Get the column whose header was clicked.
  Dim col As DataGridViewColumn = Me.dgv.Columns(e.ColumnIndex)
  
  'Check if this column is CustomSortingColumn.
  If (col.Name = "YourCustomSortColName") Then
  'Set the Sort mode to Programmatic.
  col.SortMode = DataGridViewColumnSortMode.Programmatic
  
  'Decide the Sort order, based on whether this column has a sorting glyph set, indicating
  'that it was the last sorted column & also the sort direction.
  Dim sortOrder As Windows.Forms.SortOrder
  If (col.HeaderCell.SortGlyphDirection = Windows.Forms.SortOrder.Ascending) Then
  sortOrder = Windows.Forms.SortOrder.Descending
  Else
  sortOrder = Windows.Forms.SortOrder.Ascending
  End If
  
  'Get the DataSource of the Grid, which is a DataTable in this case.
  Dim table As DataTable = CType(dgv.DataSource, DataTable)
  'Instantize a IComparer object that will compare two DataRows in the DataTable
  'based on the desired field, & the sortOrder.
  Dim comparer As New CustomSortColComparer(col.Name, sortOrder)
  
  'Add all rows in the table to a list, WITHOUT removing them from the table.
  Dim rowList As New List(Of DataRow)
  For Each row As DataRow In table.Rows
  rowList.Add(row)
  Next
  
  'Sort the rows, using the desired comparer.
  rowList.Sort(comparer)
  
  'Copy sorted rows to a new DataTable.
  Dim sortedTable As DataTable
  'Following two instructions amount to copying of the Schema of the original table to the
  'new table.
  sortedTable = table.Clone()
  sortedTable.Clear()
  
  sortedTable.BeginLoadData()
  For Each row As DataRow In rowList
  'Note that importing rows preserves property settings of the row.
  'However, I have not checked whether this would also preserve the rows parent or child
  'relationships to rows in other tables.
  sortedTable.ImportRow(row)
  Next
  sortedTable.EndLoadData()
  
  'Set the new sorted table as the Grid's datasource.
  dgv.DataSource = sortedTable
  
  'Set the glyph for the column, setting its sort mode to Progrmmatic is necessary,
  'or it raises an exception.
  'Use col.Name to index into the column collection, as the user might have reordered
  'the columns in the DGV, so a column's index in the DGV & the source DataTable might not be the same.
  dgv.Columns(col.Name).SortMode = DataGridViewColumnSortMode.Programmatic
  dgv.Columns(col.Name).HeaderCell.SortGlyphDirection = sortOrder
  End If
  
  Finally
  dgv.Cursor = Cursors.Arrow
  End Try
  End Sub
  
  
  

  Class CustomSortColComparer
  Implements IComparer(Of DataRow)
  
  Private accNoColName As String
  Private sortOrder As Windows.Forms.SortOrder
  
  Friend Sub New(ByVal accNoColName As String, ByVal sortOrder As Windows.Forms.SortOrder)
  'Store the name of the column on which custom sorting is to be performed & the sortOrder.
  Me.accNoColName = accNoColName
  Me.sortOrder = sortOrder
  End Sub
  
  Public Function Compare(ByVal row1 As DataRow, ByVal row2 As DataRow) As Integer Implements System.Collections.Generic.IComparer(Of DataRow).Compare
  'Extract the column values on which custom sorting is to be performed from the two rows.
  Dim value1 As String = row1(Me.accNoColName)
  Dim value2 As String = row2(Me.accNoColName)
  

  
  

  'Provide your custom sort logic for comparing value1 & value2 here.
  

  
  

  'Comprison code goes here
  

  
  

  'Return the comparison result.
  'If sortOrder is descending, invert the compareResult sign.

  'Remember, the sortOrder was stored by the constructor.

  If (Me.sortOrder = Windows.Forms.SortOrder.Descending) Then
  compareResult = -compareResult
  End If
  Return (compareResult)
  End Function
  End Class
  
  

  
  Finally, I would like to mention that although I greatly admire .NET's clean library design, Sorting in DGV & DataTable is one thing I personally am not too happy about.
  According to the documentation, when the DGV is databound, the Sorting logic has to come from the DataSource.
  Now, when you click a column header of a DGV bound to a DataTable, the data is sorted based on the default ordering of the Column's value type. However, as the documentation says, this operation is implemented by the underlying DataSource, i.e. the DataTable in this case. This means that the DataTable class provides some sort of sorting functionality, that is hidden inside the System.Data assembly probably using Friend modifiers. Wouldn't it had been better if the DataTable class provided a Sort method (like Lists & other collections) that accepts an IComparer object to define the sort order
  

  
  

• faraaz_malak_c92eb4

  But the Sort method can be used to implement custom Sorting only when the DataSource property is not set.
  
  I have both, a Grid & a GridView in my app. The GridView has its DataSource property set to a DataTable, while the Grid is bound to a DataSet with the SetDataBinding() method.
  A particular column belonging to both, the Grid & GridView has string contents of the form BB1, BB2 etc, which are sorted as BB1, BB10, BB2, which is not appropriate in my case, as I want them to be sorted like BB1, BB2, BB10.
  
  I have prepared the logic needed for my custom sorting. My question is how can I make both the Grid & GridView to use my sorting method, or better still a comparison method (by implementing IComparable etc.) for a particular column only, when they have been bound to DataSources.
  Also, I would like to show the Sorting glyph after custom sorting has been performed.
  
  
• Custom Sorting in Data Grid