GridView Paging and Sorting

Introduction

Paging and Sorting are most commonly used features of ASP.Net GridView. And it is very easy to implement in GridView with small lines of code. Here I am going to demonstrate how to use Paging and Sorting in GridView for better use of data display.
Steps for Paging and Sorting

We will perform following steps to enable paging and sorting into our GridView

• Set AllowPaging="True" and AllowSorting="True" properties of GridView to True to enable Paging and Sorting.
• Set the SortExpression property of each column. By default each Data Bound columns has the bounded column name as default value for the SortExpression property.  
• Handle PageIndexChanging and Sorting Events of GridView to respond to paging and sorting actions 

Implementation of GridView Paging and Sorting
Markup of .aspx page

In .aspx page we used Ajax for better use of ASP.Net 2.0 features. Using Ajax we can easily update specific part of a page without any page reload or flickering. Here we used various ASP.Net Ajax control like ScriptManager, UpdatePanel. Here we used Boundfield for bidning data with gridview, in boundfield we have set various property like DataField and SortExpression.

• Boundfield: Displays the value of a field in a data source. This is the default column type of the GridView control.
• DataField property to specify the name of the data field to bind to the BoundField object.
• The SortExpression property to determine the name of the column being sorted.

Importing Namespace

Importing new namespace of sqlClient for using various methods and properties of this namespace.

Imports System.Data.SqlClient

Global Variable Declaration

Declare global variable as a string but private and constant so its value will be same everywhere in the page during all events which will be performed on gridview control.

Private Const ASCENDING As String = " ASC"
Private Const DESCENDING As String = " DESC"

Data Retrieval Function

GetData() is a function that will return value as a dataview. Here we create connection with SQLServer database and fill SQLAdapter with dataset. ViewState("sortExp") will check previuous expression of sorting if it is nothing then it will return defaultview of dataset table else it will create new dataview for specific dataset.

Private Function GetData() As Data.DataView

        '**** Declaration of Local-Variable. ****
        Dim strConnection, strQuery As String
        Dim ds As Data.DataSet = New Data.DataSet
        Dim dv As Data.DataView = New Data.DataView
        Dim dp As SqlDataAdapter = New SqlDataAdapter

        Try
            strConnection = "Data Source=xx.xx.xx.xx;Initial Catalog= database;User ID=user;Password=password;"
            strQuery = "Your Query"

            dp = New SqlDataAdapter(strQuery, strConnection)
            dp.Fill(ds)

            If (ViewState("sortExp") <> Nothing) Then
                dv = New Data.DataView(ds.Tables(0))

                If (GridViewSortDirection = SortDirection.Ascending) Then
                    GridViewSortDirection = SortDirection.Descending
                    dv.Sort = CType(ViewState("sortExp") & DESCENDING, String)
                Else
                    GridViewSortDirection = SortDirection.Ascending
                    dv.Sort = CType(ViewState("sortExp") & ASCENDING, String)
                End If
            Else
                dv = ds.Tables(0).DefaultView
            End If

            Return dv

        Catch ex As Exception

        Finally
            ds.Dispose()
            dp.Dispose()
        End Try

End Function

GridView Sorting Direction Property

GridViewSortDirection() is a property that is declared as a SortDirection. Here ViewState("sortDir") is used for storing direction of gridview sorting like Ascending or Descending order.

Public Property GridViewSortDirection() As SortDirection
        Get
            If ViewState("sortDir") = Nothing Then
                ViewState("sortDir") = SortDirection.Ascending
            End If

            Return CType(ViewState("sortDir"), SortDirection)
        End Get

        Set(ByVal value As SortDirection)
            ViewState("sortDir") = value
        End Set

End Property

Page Load Event

Here we will bind gridview with data during pageload event, but here we used PostBack property so data will be bind if page is not postback. GetData() function will return value as dataview and it will be act as a datasource for gridview.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then
            GridView1.DataSource = GetData()
            GridView1.DataBind()
        End If

End Sub

GridView PageIndexChanging Event

The PageIndexChanging event is raised when one of the pager buttons is clicked, but before the GridView control handles the paging operation. This enables you to provide an event-handling method that performs a custom routine, such as canceling the paging operation, whenever this event occurs.

Here we are setting GridView PageIndex property with new PageIndex which is chose by user and again binding gridview with data.

Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging

        GridView1.PageIndex = e.NewPageIndex
        GridView1.DataSource = GetData()
        GridView1.DataBind()

End Sub

GridView Sorting Event

The Sorting event is raised when the hyperlink to sort a column is clicked, but before the GridView control handles the sort operation. This enables you to provide an event-handling method that performs a custom routine, such as canceling the sorting operation, whenever this event occurs.
Here we are setting SortExpression of GridView to ViewState("sortExp") and again binding gridview with data.

Protected Sub GridView1_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles GridView1.Sorting

        ViewState("sortExp") = e.SortExpression
        GridView1.DataSource = GetData()
        GridView1.DataBind()

End Sub

Code-Behind in VB.NET

Private Const ASCENDING As String = " ASC"
    Private Const DESCENDING As String = " DESC"

    Private Function GetData() As Data.DataView

        Dim strConnection, strQuery As String
        Dim ds As Data.DataSet = New Data.DataSet
        Dim dv As Data.DataView = New Data.DataView
        Dim dp As SqlDataAdapter = New SqlDataAdapter

        Try
            strConnection = "Data Source=Database Server;Initial Catalog= DatabaseName;User ID=UserName;Password=Password;"

            strQuery = "SELECT * From TableName"

            dp = New SqlDataAdapter(strQuery, strConnection)
            dp.Fill(ds)

            If (ViewState("sortExp") <> Nothing) Then
                dv = New Data.DataView(ds.Tables(0))

                If (GridViewSortDirection = SortDirection.Ascending) Then
                    GridViewSortDirection = SortDirection.Descending
                    dv.Sort = CType(ViewState("sortExp") & DESCENDING, String)
                Else
                    GridViewSortDirection = SortDirection.Ascending
                    dv.Sort = CType(ViewState("sortExp") & ASCENDING, String)
                End If
            Else
                dv = ds.Tables(0).DefaultView
            End If

            Return dv

        Catch ex As Exception

        Finally
            ds.Dispose()
            dp.Dispose()
        End Try

    End Function

    '** Property for Getting Employee Gridview Display Data Order. **
    Public Property GridViewSortDirection() As SortDirection
        Get
            If ViewState("sortDir") = Nothing Then
                ViewState("sortDir") = SortDirection.Ascending
            End If

            Return CType(ViewState("sortDir"), SortDirection)
        End Get

        Set(ByVal value As SortDirection)
            ViewState("sortDir") = value
        End Set

    End Property

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then
            GridView1.DataSource = GetData()
            GridView1.DataBind()
        End If

    End Sub

    Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging

        GridView1.PageIndex = e.NewPageIndex
        GridView1.DataSource = GetData()
        GridView1.DataBind()

    End Sub


    Protected Sub GridView1_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles GridView1.Sorting

        ViewState("sortExp") = e.SortExpression
        GridView1.DataSource = GetData()
        GridView1.DataBind()

    End Sub

End Class