[RESOLVED] I'm having a sorting problem with dataSets. Please help me out..

[tgf-47]

Hi,

I want to sort my datatable column(0) alphabetically, but cant seem to find a way to do it.

Just to give you an idea:

dim dsDATA as new dataset
dsDATA.tables.add("TBL1")
dsDATA.tables("TBL1").columns.add("NME")
dsDATA.tables("TBL1").columns.add("SURNME")
dsDATA.tables("TBL1").columns.add("NBR")
 
dsDATA.tables("TBL1").Rows.add("JACK","ABC","3242343")
dsDATA.tables("TBL1").Rows.add("ABDUL","ASD","5324")
dsDATA.tables("TBL1").Rows.add("ROD","BLACK","322468")

Ok so my 1st column is the one with the heading "NME" (column 0)

How do I sort this???

[cicatrix]

How to sort a datatable

[jmcilhinney]

To elaborate, it's not really possible to sort an actual DataTable. When you bind a DataTable to controls, the data you see is actually the contents of the table's DefaultView, which is a DataView. You can sort and filter the data exposed by a DataView, but that doesn't actually change the DataTable at all.

If all you're doing is binding then that's fine. If you need to access the data in code though, you must loop through the DataRowView's in the DataView rather than the DataRows in the DataTable. That's fine, because you can use those DataRowViews almost interchangeably with the DataRows. If you ever need a DataRow, you can get it from the Row property of the corresponding DataRowView.

If you really do need a sorted DataTable then you can always set the Sort of the original table's DeafultView and then call its ToTable method. That will create a new DataTable that is in the same order as the DataView. You can also filter and project that way.

The code provided in cicatrix's link will do the job, but it is a bit dodgy. First of all, the method is named AlphabeticSort when there's nothing in the code that limits it to sorting alphabetic text. Secondly, it creates a new DataSet that it never uses. Next, it uses a meaningless Integer for the sort direction when the .NET Framework has multiple enumerations for sort direction or you could declare your own. Finally, it returns dtTable.DefaultView.Table, which is exactly the same as just dtTable. Furthermore, there's no point returning the very same DataTable that was passed in because the caller already has a reference to it. I would change that code to this:

Private Sub SortDataTable(ByVal table As DataTable, _
                          ByVal column As String, _
                          ByVal direction As ListSortDirection)
    table.DefaultView.Sort = String.Format("{0} {1}", _
                                           column, _
                                           direction)
End Sub

As you can see, the code is far simpler. It's also more generic as it lets you specify any column. You could even overload it to allow you to sort by multiple columns:

Private Sub SortDataTable(ByVal table As DataTable, _
                          ByVal columns As String(), _
                          ByVal directions As ListSortDirection())
    If columns.Length <> directions.Length Then
        Throw New ArgumentException("Column count must match direction count.")
    End If
 
    Dim upperBound As Integer = columns.GetUpperBound(0)
    Dim phrases(upperBound) As String
 
    For index = 0 To upperBound
        phrases(index) = String.Format("{0} {1}", _
                                       columns(index), _
                                       directions(index))
    Next
 
    table.DefaultView.Sort = String.Join(", ", phrases)
End Sub

Note that ListSortDirection is a member of the System.ComponentModel namespace. You might also use the System.Windows.Forms.SortOrder enumeration, which includes a None value. The code would need some slight adjustment to handle that though, e.g.

Private Sub SortDataTable(ByVal table As DataTable, _
                          ByVal column As String, _
                          Optional ByVal direction As SortOrder = SortOrder.None)
    table.DefaultView.Sort = String.Format("{0} {1}", _
                                           column, _
                                           If(direction = SortOrder.None, _
                                              String.Empty, _
                                              direction.ToString))
End Sub