VB DataTable Sort


This article is written by Pon Saravanan  on 10-Feb-10 Last modified on :05-Apr-10





Sorting Data in a DataTable Tutorial

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.

Sorting 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.

Sorting using a select method in DataTable

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.

Source Code (Windows Forms)

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











Comments
  • GUEST
    I like to do this:

    Dim dt As datatable

    'add rows to table here

    dt.defaultview.sort = "col_1 ASC, col_2 DESC"
    dt = dt.defaultview.totable
    2/23/2011 9:49:26 AM

  • GUEST
    Guest, you ROCK!!! That was a great suggestion. Thank you. 4/7/2011 11:29:32 AM

  • GUEST
    GUEST,
    Thank you very much :) works great..
    5/6/2011 1:47:57 AM

  • GUEST
    GUEST,
    Yes,That is way simple. Just what I was looking for.
    9/1/2011 9:41:28 AM

  • GUEST
    Excellent! Just what I needed! Thank you!! 9/4/2011 6:21:50 AM

  • GUEST
    Dear (1st) guest,

    thats a great catch! Thanks!
    3/13/2012 5:11:09 AM

  • GUEST
    Many thanks to guest 2/23/2011 ! His/ her proposal worked very well. 5/4/2012 11:16:34 AM

  • GUEST
    Dear 1st GUEST,

    Thats really great!!
    It worked !!
    This is what I was looking for...

    Ranjani.
    1/24/2014 6:27:59 AM


Comments
   
Captcha Image
For you specially:  
Captcha Text Enter the text in the image.(Not Case sensitive)    



Spam Bot Trap



   



Select Theme
White
Gray
Blue
Brown
GraySimple