How do I sort a datatable

Question

How do I sort a datatable? I need to return a datatable from a function. I have been struggling with this for hours, and the internet has a few different answers, none of which seem to work for me.

Edit: I want to punch myself. Do a DataView.Sort on your table, then a DataView.ToTable() to put the sorted data into a new dataset... Example:

Dim view As New DataView(OriginalDataSet) 'Put your original dataset into a dataview
view.Sort = "ColumnName" ' Sort your data view
Dim NewDataSet As DataTable = view.ToTable() ' Put your dataview into a new datatable

End of example

I have a relatively simple example table below, taken from a teaching website. The one twist is that there are duplicate values in the row I am trying to sort on.

Module Module1

    Sub Main()
    ' Get a DataTable instance from helper function.
    Dim table As DataTable = GetTable()
    End Sub

    ''' <summary>
    ''' Helper function that creates new DataTable.
    ''' </summary>
    Function GetTable() As DataTable
    ' Create new DataTable instance.
    Dim table As New DataTable
    ' Create four typed columns in the DataTable.
    table.Columns.Add("Dosage", GetType(Integer))
    table.Columns.Add("Drug", GetType(String))
    table.Columns.Add("Patient", GetType(String))
    table.Columns.Add("Date", GetType(DateTime))
    ' Add five rows with those columns filled in the DataTable.
    table.Rows.Add(25, "Indocin", "David", DateTime.Now)
    table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now)
    table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now)
    table.Rows.Add(21, "Combivent", "Janet", DateTime.Now)
    table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now)
table.Rows.Add(21, "Aspirin", "Janet", DateTime.Now)
    Return table
    End Function

End Module

I have tried selecting into an array, then looping through the array and putting each row into a new datatable, but the select isn't grabbing rows. Example:

drarray = ds.Select("I want to select all here", "MySortColumn")

I have tried various looping strategies, with inner loops, etc and can't seem to figure that out.

I have tried dataTable.DefaultView.Sort = "sortExp" but I can't get that to work.

So what am I missing? I figure with the DefaultView and Select methods I'm just missing something syntactly.

So what's the best way to go, and what am I missing?

Answer 1

You can use something like this:

Return table.Select("","Columns to sort on").CopyToDataTable

Answer 2

Use a DataView to create a view of your data in the DataTable. This allows you to sort, filter, etc. Here's a C# example: Datatable VS dataview