Sorting Datagridview Column with Mixed Values

[timh]

Hi,

I suspect the answer to my question may lie with "IComparer" interface, but I can't get my head round how that works. Hopefully someone can help with a simple solution to my problem, or explain how to use IComparer for my scenario.

Within my bound datagridview, I have a column ("racenumber") which will only usually contain integers (1, 2, 3 etc) , but could potentially contain the odd non-numeric character (e.g., the numbers could be 1, 2, 3, 3A, 4 etc). Consequently, the values in this column are stored within the XML datasource as strings.

The problem comes when I try to sort the column, as because the values are strings, the numbers order incorrectly (100 comes before 50 etc). I have seen a lot of posts about this type of issue, but none seem to replicate my scenario and generally the suggestion is to change the datatype to integer, which I can't do, because of the potential for non-numeric characters.

I have considered adding a hidden, unbound column to the grid, which I would populate with a modified string representation of the number, including leading zeros, as I think that should then work. That column would then contain the values to sort when I click the header of the "racenumber" column. I really don't want to be displaying the leading zeros in the "racenumber" column if I can avoid it. But if I add this unbound column to the datagridview, how do I go about copying (and modifying) the entries in the "racenumber" column? I can create a function to add the leading zeros to the "racenumber" string, but how to get these figures into the pseudo column?

As always, any advice would be gratefully received.

Thanks,

Tim

 

[jmcilhinney]

The first thing that comes to mind for me is to use the IComparable interface rather than IComparer. You can define a type that represents a value and build in the ability to compare the current instance to another instance. That's exactly how types like String and Integer work, so you would then be able to inherently sort lists of your type anywhere you could do so with fundamental types. Here's the sort of thing you would need:

Public Class RaceNumber
    Implements IComparable, IComparable(Of RaceNumber)

    ''' <summary>
    ''' The numeric part.
    ''' </summary>
    Public Property Number() As Integer?
    ''' <summary>
    ''' The text part.
    ''' </summary>
    Public Property Suffix() As String


    ''' <summary>
    ''' Creates a new instance from a String.
    ''' </summary>
    Public Sub New(text As String)
        'Get all the leading digits.
        Dim numberPart = New String(text.TakeWhile(Function(ch) Char.IsDigit(ch)).ToArray())
        Dim numberLength = numberPart.Length

        'Create a number from the leading digits if there are any.
        If numberLength > 0 Then
            Number = CInt(numberPart)
        End If

        'Get the remaining text.
        If numberLength < text.Length Then
            Suffix = text.Substring(numberLength)
        End If
    End Sub


    ''' <summary>
    ''' Compares this instance to another object.
    ''' </summary>
    Public Function CompareTo(obj As Object) As Integer Implements IComparable.CompareTo
        Return CompareTo(DirectCast(obj, RaceNumber))
    End Function

    ''' <summary>
    ''' Compares this instance to another instance.
    ''' </summary>
    Public Function CompareTo(other As RaceNumber) As Integer Implements IComparable(Of RaceNumber).CompareTo
        Dim result As Integer

        'Compare numbers first.
        If Me.Number.HasValue AndAlso other.Number.HasValue Then
            result = Me.Number.Value.CompareTo(other.Number.Value)
        ElseIf Me.Number.HasValue Then
            'Values without a number are considered less than those with.
            result = 1
        ElseIf other.Number.HasValue Then
            'Values without a number are considered less than those with.
            result = -1
        End If

        If result = 0 Then
            'Numbers are equivalent to compare suffixes.
            result = String.Compare(Me.Suffix, other.Suffix)
        End If

        Return result
    End Function

    ''' <summary>
    ''' Returns a string representation.
    ''' </summary>
    Public Overrides Function ToString() As String
        Return Number & Suffix
    End Function


    ''' <summary>
    ''' Converts a String to a RaceNumber.
    ''' </summary>
    Public Shared Operator CType(input As String) As RaceNumber
        Return New RaceNumber(input)
    End Operator

    ''' <summary>
    ''' Converts a RaceNumber to a String.
    ''' </summary>
    Public Shared Operator CType(input As RaceNumber) As String
        Return input.ToString()
    End Operator

End Class

That may seem complicated but it's not really. First there are two properties to store the numeric and non-numeric parts. Next there's a constructor to create an instance from a string. Next are the methods that provide the implement the comparison for the standard and generic forms of the IComparable interface. Next there is a method that will create a string representation for display. Finally there are operators for conversion from and to type String. You should be able to add a new column to your table and populate it with instances of that type generated from the Strings in your XML.

 

[timh]

Thanks for that very detailed reply; I really appreciate the time you have taken to explain this to me. I'm going to show my ignorance now though...please remember I am not an IT professional and have no programming training! I just try to pick things up as I go along...

I understand the basics of what you are saying about creating the custom variable type "RaceNumber" and from creating a simple test application, I can see how to write a String value and the corresponding RaceNumber value into respective columns of an unbound DGV. What I am still struggling with though is how to write the corresponding RaceNumber value into the unbound column of my existing bound DGV and further, then to implement the CompareTo function to actually sort this new column.

I think maybe I'm trying to be too ambitious with this part of my application...

 

[jmcilhinney]

I would tend not to use an unbound column. Add the RaceNumber values to your data source and bind the data to your grid. You can hide the original column and just show the column of RaceNumber values. When you're done and want to save changes, you just loop through the records and call ToString on each RaceNumber value and assign the result to the original String column. So, let's say that you have a DataTable that has a RaceNumber column containing the original String values. You could then do this:

myDataTable.Columns.Add("RaceNumberValue", GetType(RaceNumber))

For Each row As DataRow In myDataTable.Rows
    row("RaceNumberValue") = New RaceNumber(CStr(row("RaceNumber")))
Next

That will create and populate the new column so it will be bound along with the other columns. The CType operators should take care of conversions between a String for display and the actual Values contain in the cells. When you're done, you can push any additions and edits from the RaceNumberValue column back to the original RaceNumber column:

For Each row As DataRow In myDataTable.Rows
    row("RaceNumber") = row("RaceNumberValue").ToString()
Next