VB DataTable Sort

When there is a need of sorting rows in a DataTable, there is no direct support available in DataTable. There is no direct way to get the sorted records into a DataTable out of the box.  There are few ways to sort the data in the DataTable but that needs a few lines of code that I would like to discuss here. We have already discused how to sort a records in Gridview using DataView.

Basically I like the DataView to sort the records as there is a support natively available as a property DataView.Sort. And the sorted records can be forced to a DataTable using a ToTable () method in a DataView. Not only sorting available but also there are other useful functions like filter, and distinct etc... So we can filter and sort altogether.

Like I said earlier there are few ways to achieve sorting in the DataTable. The select method of the DataTable also offers the row filtering and sorting altogether. But the biggest drawback (at least to me) is the out put is a array of data rows.  This is not easier to be used as good as a DataView or DataTable. If that output as a DataTable with same structure, there will be a lot of places we don’t need to go to DataView.

However this is a very subjective view of usage, as I have seen developers working comfortably with arrays. So if select method fits you well, then sorting and filtering is a breeze.

Imports System.Data.SqlClient
Public Class Form1
Private ReadOnly Property ConnectionString() As String
Get
Return "Server=.\SQLEXPRESS;Database=NorthWind;Trusted_Connection=True"
End Get
End Property
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
ListBox1.Items.Clear()
ListBox1.DisplayMember = "ProductName"
ListBox1.ValueMember = "ProductID"
ListBox1.DataSource = GetSortedDataView()
End Sub
Public Function GetSortedRows() As DataRow()
Dim SelectQry = "select * from Products"
Dim SampleSource As New DataSet
Try
Dim SampleDataAdapter As New SqlDataAdapter(SelectQry, ConnectionString)
SampleDataAdapter.Fill(SampleSource)
Return SampleSource.Tables(0).Select("", "ProductName asc")
Catch ex As Exception
Throw ex
End Try
End Function
Public Function GetSortedDataView() As DataView
Dim SelectQry = "select * from Products"
Dim SampleSource As New DataSet
Dim SortableView As DataView
Try
Dim SampleDataAdapter As New SqlDataAdapter(SelectQry, ConnectionString)
SampleDataAdapter.Fill(SampleSource)
SortableView = SampleSource.Tables(0).DefaultView
SortableView.Sort = "ProductName asc"
Catch ex As Exception
Throw ex
End Try
Return SortableView
End Function 
End Class

View the original article here