How to Sorting GridView Manually in Asp.net C# VB.Net

Asp.net SqlDataSource control ease our lives because if you are using SqlDataSource control to bind a GridView control directly then no need to sorting GridView or Paging GridView control, since you will achieve it automatically. But if you are using different datasource like Datatable, DataSet then you need to GridView Sorting manually. Here in this article I will show you how you can develop GridView Sorting easily. One thing keep in mind that When you need to sort a GridView then each time you have to bind the GridView with data.

So you have two way to hold data:
1. You can read data from database each time
2. You can store data within viewstate or cache

Objective/Outcome:
How to Sorting GridView control Manually in Asp.net Csharp VB.Net

Here I am using asp.net cache since you knew that Viewstate will increase the page response time. OK lets start. Add a page in your project then add a GridView on it. Now from GridView properties set the AllowSorting=true. Now in your each bind column set the sortexpression like below:

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowSorting="true" OnSorting="GridView1_Sorting" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5">
         <Columns>
             <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name"/>
             <asp:BoundField DataField="Size_gm" HeaderText="Size" SortExpression="Size_gm" />
             <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price"/>
             <asp:BoundField DataField="ManufactureDate" HeaderText="Manufacture Date" SortExpression="ManufactureDate"/>
             <asp:BoundField DataField="ExpireDate" HeaderText="Expire Date" SortExpression="ExpireDate"/>
         </Columns>
        </asp:GridView>

To Complete the Example We need to Create a DataBase Table:

CREATE TABLE [dbo].[Product](
	[ID] [bigint] NOT NULL,
	[Name] [varchar](max) NOT NULL,
	[Size_gm] [int] NOT NULL,
	[Price] [numeric](18, 2) NOT NULL,
	[ManufactureDate] [smalldatetime] NOT NULL,
	[ExpireDate] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert some Data into the Database Table:

INSERT INTO Product Values(1,'Fair & Lovely',250,75,'Aug 04, 2014','Aug 04, 2015')
INSERT INTO Product Values(2,'Dove Soap',250,75,'Aug 02, 2014','Aug 04, 2015')
INSERT INTO Product Values(3,'Sunsilk',250,75,'Aug 07, 2014','Aug 04, 2015')
INSERT INTO Product Values(4,'Matador',250,75,'Jul 04, 2014','Aug 04, 2015')
INSERT INTO Product Values(5,'Colgate',250,75,'Jan 04, 2014','Aug 04, 2015')
INSERT INTO Product Values(6,'Lux Soap',250,75,'Feb 04, 2014','Aug 04, 2015')
INSERT INTO Product Values(7,'Meril Fresh Gel',250,75,'Aug 14, 2014','Aug 04, 2015')
INSERT INTO Product Values(8,'Tibbet Pomed',250,75,'Aug 24, 2014','Aug 04, 2015')

Now need to configure Web.Config File:

<configuration>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->
  </connectionStrings>
...................
...................

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now Bind the GridView:
C# Code:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dt;
            String SQL = "SELECT * FROM Product ORDER BY Name";


            string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(sConstr))
            {
                using (SqlCommand comm = new SqlCommand(SQL, conn))
                {
                    conn.Open();
                    using (SqlDataAdapter da = new SqlDataAdapter(comm))
                    {
                        dt = new DataTable("tbl");
                        da.Fill(dt);
                    }
                }
            }

            GridView1.DataSource = dt;
            GridView1.DataBind();
            Cache["dt"] = dt;
            // The first sorting combination saved here
            ViewState["Column_Name"] = "Name";
            ViewState["Sort_Order"] = "ASC";
        }
    }

Note: Here we capture the first sorted column & sorting type in the page ViewState.

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim dt As DataTable
            Dim SQL As String = "SELECT * FROM Product ORDER BY Name"


            Dim sConstr As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
            Using conn As SqlConnection = New SqlConnection(sConstr)
                Using comm As SqlCommand = New SqlCommand(SQL, conn)
                    conn.Open()
                    Using da As SqlDataAdapter = New SqlDataAdapter(comm)
                        dt = New DataTable("tbl")
                        da.Fill(dt)
                    End Using
                End Using
            End Using
            GridView1.DataSource = dt
            GridView1.DataBind()
            Cache("dt") = dt
            'The first sorting combination saved here
            ViewState("Column_Name") = "Name"
            ViewState("Sort_Order") = "ASC"

        End If
    End Sub

Note: Here we capture the first sorted column & sorting type in the page ViewState.

Now time to Implement GridView Sorting Event:
C# Code:

    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
        if (e.SortExpression == ViewState["Column_Name"].ToString())
        {
            if (ViewState["Sort_Order"].ToString() == "ASC")
                RebindData(e.SortExpression, "DESC");
            else
                RebindData(e.SortExpression, "ASC");
        }
        else
            RebindData(e.SortExpression, "ASC");
    }

VB.Net Code:

    Protected Sub GridView1_Sorting(sender As Object, e As GridViewSortEventArgs)
        If e.SortExpression = ViewState("Column_Name").ToString() Then
            If ViewState("Sort_Order").ToString() = "ASC" Then
                RebindData(e.SortExpression, "DESC")
            Else
                RebindData(e.SortExpression, "ASC")
            End If

        Else
            RebindData(e.SortExpression, "ASC")
        End If
    End Sub

Now need to write a Reusable Method to Rebind the GridView:
C# Code:

    private void RebindData(string sColimnName, string sSortOrder)
    {
        DataTable dt = (DataTable)Cache["dt"];
        dt.DefaultView.Sort = sColimnName + " " + sSortOrder;
        GridView1.DataSource = dt;
        GridView1.DataBind();
        ViewState["Column_Name"] = sColimnName;
        ViewState["Sort_Order"] = sSortOrder;
    }

VB.Net Code:

    Private Sub RebindData(sColimnName As String, sSortOrder As String)
        Dim dt As DataTable = CType(Cache("dt"), DataTable)
        dt.DefaultView.Sort = sColimnName + " " + sSortOrder
        GridView1.DataSource = dt
        GridView1.DataBind()
        ViewState("Column_Name") = sColimnName
        ViewState("Sort_Order") = sSortOrder
    End Sub

Code Explanation:
Here if you look at my bind query in page_load event then you found that Name column already sorted in ascending order that’s why I have stored the Name column name & the sorting order, so that if user click again on Name column then i need to sort the column in descending order but if user click on other column then i need to sort that column in ascending order. Hope now you can understand the logic why I use two viewstate variables ViewState[“Column_Name”] and ViewState[“Sort_Order”]. Basically those two variables is used to remeber user last sorting action.

Now its your turn to make a generic sorting class for your project.

Download Code Example C#        Download Code Example VB.Net

Posted in .Net, Asp.net, C#, Gridview, VB.Net
One comment on “How to Sorting GridView Manually in Asp.net C# VB.Net
  1. G S says:

    Great article having issues on this very problem

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code
     
 

*