Bidirectional Sorting on Any Column in DataGrid
 
Published: 23 Nov 2003
Unedited - Community Contributed
Abstract
The purpose of this article is to sort data displayed in datagrid in ascending or descending direction and reverses the direction of the sort automatically each time user clicks the column in datagrid. This article is divided into two parts. The first part uses sortexpression property of the column for sorting and the latter part uses dynamic attributes property of datagrid and dataview class for sorting.
by Pani Baruri & Abhijit Mandrekar
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 23383/ 81

Introduction

The purpose of this article is to sort data displayed in datagrid in ascending or descending direction and reverses the direction of the sort automatically each time user clicks the column in datagrid. This article is divided into two parts. The first part uses sortexpression property of the column for sorting and the latter part uses dynamic attributes property of datagrid and dataview class for sorting.

Advantages

  • This kind of sorting can be applied to any number of desired columns you want data to be sorted. 
  • It reverses the sort direction each time automatically for each column.

 

Part 1: Using SortExpression Property

Design Time:

 

  1. Drag and drop a DataGrid on the form.
  2. Set AllowSorting property of the grid to True.
  3. Set AutogenerateColumns property of the grid to False.
  4. Add bound columns to datagrid and set their properties as follows. The sortexpression property refers to the name of column in database table.

 

Note: Employees table from Northwind database is being used to illustrate the purpose.

 

DataTextField

Header Text

Sort Expression

EmployeeID

Employee ID

EmployeeID ASC

LastName

Last Name

LastName ASC

FirstName

First Name

FirstName ASC

Title

Title

Title ASC

City

City

City ASC

Country

Country

Country ASC

 

 

Run-time:

 

Pseudo logic:

 

  1. Trap sortcommand event of the datagrid control.

 

  1. Read sortexpression property of the event argument.

 

  1. Sort data in grid using current sort expression.

 

  1. Use split function to separate column name (which represents database field name) and sort direction. The first element of an array contains database field name.

 

  1. If sort direction is ASC then new sort direction is DESC and vice versa. Save the new direction into 2nd element of the array.

 

  1. Loop through all columns

            If column’s sort expression matches sort expression returned from event argument then

                        Concatenate first element of an array, space and second element of an array to

build a new sort expression.

                        Assign new sort expression to current column’s sortexpression property.

                        Exit loop

            End If

End Loop

 

Note: In order for this example to work properly write binding SQL query with order by clause with no mention of column names. The column names are appended at run-time to the binding SQL query.

 

VB.Net Code:

 

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Put user code to initialize the page here

        If Not Page.IsPostBack Then

            BindDataGrid("EmployeeID ASC")

        End If

    End Sub

 

    Private Sub BindDataGrid(ByVal strSortField As String)

        Dim cn As SqlConnection

        Dim cmdSelect As SqlCommand

 

        cn = New SqlConnection("Server=localhost;UID=sa;PWD=;Database=Northwind")

        cmdSelect = New SqlCommand("Select * From employees Order By " & strSortField, cn)

        cn.Open()

        DataGrid1.DataSource = cmdSelect.ExecuteReader()

        DataGrid1.DataBind()

        cn.Close()

 

    End Sub

 

 

 

    Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles DataGrid1.SortCommand

        Dim arrSortExpr() As String

        Dim i As Integer

 

        If e.SortExpression = "" Then Return

 

        BindDataGrid(e.SortExpression)

 

        arrSortExpr = Split(e.SortExpression, " ")

        For i = 0 To DataGrid1.Columns().Count - 1

            If (DataGrid1.Columns(i).SortExpression = e.SortExpression) Then

                If UCase(arrSortExpr(1)) = "ASC" Then

                    arrSortExpr(1) = "DESC"

                ElseIf UCase(arrSortExpr(1)) = "DESC" Then

                    arrSortExpr(1) = "ASC"

                End If

 

                DataGrid1.Columns(i).SortExpression = arrSortExpr(0) & " " & arrSortExpr(1)

                Exit For

            End If

        Next

    End Sub

Part 2: Using Attributes Property and Data View

The Part 1 example has slight limitation on the programmer side who wants to use the code above as is. The limitation is he/she has to remember to set the sortexpression property of column in datagrid as database field name followed by sort direction at design time e.g. lastname ASC.

 

The following example illustrates the use of adding sortexpression and sortdirection dynamic attributes to datagrid, maintain their state through view state and sort data in datagrid using dataview. The sortexpression property of datagrid set in this example is to generate sortcommand event only. The sortexpression attribute of datagrid stores last column name used for sorting and sortdirection attribute stores last direction used for sorting.

 

Design Time:

 

  1. Drag and drop a DataGrid on the form.
  2. Set AllowSorting property of the grid to True.
  3. Set AutogenerateColumns property of the grid to False.
  4. Add bound columns to datagrid and set their properties as follows. The sortexpression property refers to the name of column in database table.

 

Note: Employees table from Northwind database is being used to illustrate the purpose.

 

DataTextField

Header Text

Sort Expression

EmployeeID

Employee ID

EmployeeID

LastName

Last Name

LastName

FirstName

First Name

FirstName

Title

Title

Title

City

City

City

Country

Country

Country

 

 

Run-time:

 

Pseudo logic:

 

  1. Add dynamic attributes sortexpression and sortdirection to datagrid first time the page is loaded.

 

  1. Assign appropriate column name and sort order to sortexpression and sortdirection attribute respectively to sort data in order to get initial look and feel of data when the page is loaded first time.

 

  1. Get dataset and dataview objects. Set sort property of dataview object to concatenated string built using sortexpression and sortdirection dynamic attributes.

 

  1. Bind datagrid using dataview object.

 

  1. On every consecutive page loads and column clicks, trap sortcommand event of datagrid.

 

  1. Compare sortexpression property of the event argument obtained from sortcommand event with dynamic attribute sortexpression.

 

  1. If Match is found then
    1. Check sortdirection attribute.
    2. If sortdirection equals “ASC” then

                                                               i.      Assign DESC to sortdirection attribute.

    1. Else

                                                               i.      Assign ASC to sortdirection attribute.

    1. End If

 

  1. Repeat steps from Step 3 onwards.

 

Note: When you run the following code and click the same column two or more times then sort direction is reversed automatically for that column. However if column 3 is clicked followed by column 1 and 2 click then column 3 is sorted only in ascending direction no matter what the sort direction was before for the same column. This is because previous sort order is not preserved in the sortexpression property of the column. The overall result is same depending how you perceive it. This example teaches you how to make use of dynamic attributes and dataview object.

 

VB.Net Code:

 

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Put user code to initialize the page here

        If Not Page.IsPostBack Then

            If DataGrid1.Attributes("SortExpression") Is Nothing Then

                DataGrid1.Attributes("SortExpression") = "LastName"

                DataGrid1.Attributes("SortDirection") = "ASC"

            End If

            BindDataGrid()

 

        End If

    End Sub

 

    Private Sub BindDataGrid()

        Dim cn As SqlConnection

        Dim cmdSelect As SqlCommand

 

        cn = New SqlConnection("Server=amandrek;UID=sa;PWD=;Database=Northwind")

        Dim strSQL As String = "Select * From employees"

 

        Dim da As New SqlDataAdapter(strSQL, cn)

        Dim ds As New DataSet()

 

        da.Fill(ds, "Table1")

 

        Dim dv As DataView = ds.Tables(0).DefaultView

 

        Dim SortExpression As String = DataGrid1.Attributes("SortExpression")

        Dim SortDirection As String = DataGrid1.Attributes("SortDirection")

 

        dv.Sort = SortExpression + " " + SortDirection

 

        DataGrid1.DataSource = dv

        DataGrid1.DataBind()

        cn.Close()

 

    End Sub

 

    Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles DataGrid1.SortCommand

        Dim SortExpression As String = e.SortExpression

        Dim SortDirection As String = "ASC"

 

        If SortExpression.Equals(DataGrid1.Attributes("SortExpression").ToString()) Then

            If DataGrid1.Attributes("SortDirection").ToString().StartsWith("ASC") Then

                SortDirection = "DESC"

            Else

                SortDirection = "ASC"

            End If

        End If

        DataGrid1.Attributes("SortExpression") = SortExpression

        DataGrid1.Attributes("SortDirection") = SortDirection

 

        BindDataGrid()

 

    End Sub



User Comments

Title: Not bad but, not what I want.   
Name: AK
Date: 2007-08-02 1:34:42 AM
Comment:
I want sorting and paging in a grid view in asp.net 2.0 which is dynamically binded i.e. some times it has 3 columns or some time 4, 5, 6 or more columns.
Please suggest possible solution.

Thanks in advance,
AK
Title: Mr   
Name: John
Date: 2007-01-24 9:27:50 AM
Comment:
Thanx a lot if its not i never be saved
Title: great   
Name: mohsin
Date: 2006-10-18 5:52:20 AM
Comment:
Thats a great solution,very comprehensive and easy to understand and ...
Title: mr   
Name: rutts
Date: 2006-09-29 6:53:38 AM
Comment:
code is much helpful to solve my doubt regarding dataview
Title: Ms   
Name: Renee
Date: 2006-04-20 7:49:38 PM
Comment:
Thank you, this is awesome. Easy to follow and nice solution.
Title: Mr   
Name: Arif
Date: 2006-01-12 4:18:59 AM
Comment:
Thank you very much for your help. This ia a great article which works straight out of the box.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-07-16 12:33:31 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search