CODEWITHSUNDEEP
vb.netdatatable
BClaydon
BClaydon

Reputation: 1970

How do I sort a datatable

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?

Upvotes: 6

Views: 61248

Answers (3)

ajit
ajit

Reputation: 132

This may help you sortExp can be field on which based the sort should be performed filterExp that should evaluate to true or false. assuming the following fields

Dim filterExp As String = "Patient<> ''"
Dim sortExp As String = "Date "
dt_item.Select(filterExp, sortExp, DataViewRowState.CurrentRows)

The above code shows how to filter and sort the data table dt_item. The filter expression selects Date whose Patient is not NULL. The sort expression causes the results to be sorted by the Date column

Upvotes: 1

Steve
Steve

Reputation: 5545

You can use something like this:

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

Upvotes: 15

ps2goat
ps2goat

Reputation: 8485

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

Upvotes: 6

Related Questions