Custom comparer datagridview sort

Question

I have a datagridview with a bindingsource as datasource, and the bindingsource has a datatable as a datasource. Some columns are strings but I want them to be sorted in a specific way.

The grid sorts them as 1, 10, 10,0 44a, 6c.

But I want them to sorted: 1, 6c, 10, 44a, 100 as if i would take the numbers only from the values and sort them accordingly.

Is there a way I cand add a custom comparer when certain columns are being sorted? Any other soulutions would be ok if the grid, bindingsource, datatable schema is not changed.

Answer 1

Is there a way I can add a custom comparer Yes!

When the DGV is bound to a DataSource, you have to act on (sort) the source not the DGV itself. This rules out some options like using the SortCompare event. The method below uses a DataView.

First, I started with the Natural String Sorter from this answer and made a few changes:

Imports System.Runtime.InteropServices

Partial Class NativeMethods
    <DllImport("shlwapi.dll", CharSet:=CharSet.Unicode)>
    Private Shared Function StrCmpLogicalW(s1 As String, s2 As String) As Int32
    End Function

    Friend Shared Function NaturalStringCompare(str1 As String, str2 As String) As Int32
        Return StrCmpLogicalW(str1, str2)
    End Function
End Class

Public Class NaturalStringComparer
    Implements IComparer(Of String)
    Private mySortFlipper As Int32 = 1

    Public Sub New()

    End Sub

    Public Sub New(sort As SortOrder)
        mySortFlipper = If(sort = SortOrder.Ascending, 1, -1)
    End Sub

   Public Function Compare(x As String, y As String) As Integer _
            Implements IComparer(Of String).Compare

        ' convert DBNull to empty string
        Dim x1 = If(String.IsNullOrEmpty(x), String.Empty, x)
        Dim y1 = If(String.IsNullOrEmpty(y), String.Empty, y)

        Return (mySortFlipper * NativeMethods.NaturalStringCompare(x1, y1))
    End Function
End Class

That Comparer can be used in a variety ways as evidenced from the linked question. It is typically used for things like a List of file names. Since the sort target here is DB data, a couple of lines were added to Compare for when it encounters null data. (The OP, mvaculisteanu, discovered it was slow when null values were passed).

This would also work, handled as a separate step other edge cases can be easily added:

Return (mySortFlipper * NativeMethods.NaturalStringCompare(If(x, ""), If(y,""))

I don't know how you are using the BindingSource, so I had to make some guesses on the configuration. My test DataTable has 3 columns, #1 is set to programmatic to implement the comparer. Form level object variables used (so you understand my configuration - hopefully it is similar):

Private dgvDV As DataView
Private dgvBS As BindingSource

' config:
dgvDV = New DataView(dgvDT)

dgvBS = New BindingSource()
dgvBS.DataMember = "myDT"
dgvBS.DataSource = dgvDT

dgv2.Columns(0).SortMode = DataGridViewColumnSortMode.Automatic
dgv2.Columns(1).SortMode = DataGridViewColumnSortMode.Programmatic
dgv2.Columns(2).SortMode = DataGridViewColumnSortMode.Automatic

The magic, such as it is, happens in the ColumnHeaderMouseClick event:

Private SortO As SortOrder = SortOrder.Ascending
Private Sub dgv2_ColumnHeaderMouseClick(sender As Object...etc

    ' the special column we want to sort:
    If e.ColumnIndex = 1 Then
        ' create new DV
        dgvDV = DGVNaturalColumnSort("Text", SortO)

        ' reset the BindingSource:
        dgvBS.DataSource = dgvDV
        ' update glyph
        dgv2.Columns(1).HeaderCell.SortGlyphDirection = SortO

        ' flip order for next time:
        SortO = If(SortO = SortOrder.Ascending, SortOrder.Descending, SortOrder.Ascending)
    End If
End Sub

Then, a helper function which implements the sort and create a new DataView:

Private Function DGVNaturalColumnSort(colName As String, sortt As SortOrder) As DataView
    Dim NComparer As New NaturalStringComparer(sortt)
    Dim tempDT = dgvDV.Table.AsEnumerable().
        OrderBy(Function(s) s.Field(Of String)(colName), NComparer).
        CopyToDataTable

    Return New DataView(tempDT)
End Function

Because you pass the name of the column, it should be easy to use when there are multiple such columns. Results:

Sort None on top, then Sort Asc and Desc below

User changes to the column(s) such as the order and width are preserved. This also works just fine without a BindingSource. Just use your DataView as the DataSource:

  dgvBS.DataSource = dgvDV

Using a DataTable as the DataSource could be problematic and "heavier" since you would have to copy the table. A DataView makes this quite simple.

---

I also found this AlphaNumeric sorter for java. Being curious, I converted it to .NET to compare them. It works well but not quite the same. Given the same starting point, 25-35 of 1000 sequences will typically come out differently:

 PInvoke:  03, 03, 03s, 3A
Alphanum:  03, 3A...3RB, 03s, 3X 

Its not totally wrong, the 03s is in the right area and the results synch back up for awhile after that. It also treats leading dashes differently and is a bit slower than PInvoke. It does handle Nothing values fine though.