• Client-Side Sort GridView using JQUERY TableSorter

    Posted on July 30, 2012 by in ASP.NET, C#, Dotnet

    Although GridView supports inbuilt sorting, we do have to write custom code to do Client-Side sorting. However we could just use TableSorter plugin and perform sorting and many other features that comes out of the box. In this article, I am going to explain how to sort GridView using JQUERY TableSorter plugin.

    TableSorter Overview:
    tablesorter is a jQuery plugin for turning a standard HTML table with THEAD and TBODY tags into a sortable table without page refreshes. tablesorter can successfully parse and sort many types of data including linked data in a cell. It has many useful features including:

    • Multi-column sorting
    • Parsers for sorting text, URIs, integers, currency, floats, IP addresses, dates (ISO, long and short formats), time.
    • Support secondary “hidden” sorting (e.g., maintain alphabetical sort when sorting on other criteria)

    Where can I download TableSorter?
    http://tablesorter.com/docs/#Download

    When you unzip __jquery.tablesorter.zip and you would see following files.
    sort gridview using jquery tablesorter 1

    Example Overview:
    I have created a web page with gridview and some other ASP.NET controls. GridView control on the page is initially loaded with products from Production.Product table.  With the help of TableSorter plugin, we could achieve sorting functionality.

    Database Connection
    I am going to use Adventure Works as datasource. Open web.config and add following entry to connection strings element

    <add name="Sql" connectionString="Data Source=(local);Initial Catalog= AdventureWorks;User=testuser;Password=testuser;"    providerName="System.Data.SqlClient"/> 

    DataSource:
    The SqlDataSource data source control is simply a control that represents data in an SQL relational database to data-bound controls. The ConnectionString property of the SqlDataSource control specifies how to connect to the database. This property can be a hard-coded connection string or can point to a connection string in a Web.config file as shown in the code given above. The SelectCommand property specifies the SQL statement to execute to retrieve the data.

    <asp:SqlDataSource ID="sqlDSProducts" runat="server" 
    ConnectionString="<%$ ConnectionStrings:Sql %>"
    SelectCommand="SELECT TOP 10 P.ProductID, P.Name, P.ProductNumber, P.ListPrice  FROM Production.Product P">       
    </asp:SqlDataSource>
    

    Code:
    I have added reference to following script files downloaded from plugin website.

    <script type="text/javascript" src="jquery-latest.js"></script>
    <script type="text/javascript" src="jquery.tablesorter.js"></script>
    

    tablesorter only works on standard HTML tables. So we must make GridView to create thead and tbody tags instead of th and tr tags.

    protected void Page_Load(object sender, EventArgs e)
    {
        if (this.gvProducts.Rows.Count > 0)
        {
            //Gets or sets a value indicating whether a System.Web.UI.WebControls.GridView
            //control renders its header in an accessible format. This property is provided
            //to make the control more accessible to users of assistive technology devices.
            gvProducts.UseAccessibleHeader = true;
            gvProducts.HeaderRow.TableSection = TableRowSection.TableHeader;
            gvProducts.FooterRow.TableSection = TableRowSection.TableFooter;
        }
    
    }
    

    The first thing we go to do is sort the table by notifying the tableSorter to the sort the table when the document is loaded.

    $(document).ready(function() {
        $("#gvProducts").tablesorter({            
            });
    });
    

    We can also set the default columns to sort on by setting the configuration options during initialization itself. In below snippet, I am notifying tablesorter to sort on column # 1 in descending order. Also I am changing the multi sort key from default shift to al button

    $(document).ready(function() {
        $("#gvProducts").tablesorter({
                // sort on the first column order desc
            sortList: [[0, 1]]
            // change the multi sort key from the default shift to alt button         
            sortMultiSortKey: 'altKey' 
         });
    });
    

    If you want to disable sorting any particular column you could do so as shown below:

    $(document).ready(function() {        
            $("#gvProducts").tablesorter({            
                 headers: 
                 {             
                    // assign the secound column (we start counting zero)             
                    1: 
                    {                 
                        // disable it by setting the property sorter to false                 
                        sorter: false            
                    }
                } ,
                 sortList: [[0, 1]]
            });    
    });
    

    I have added a GridView control to the page.  I am using the theme that comes with the plugin itself to format the grid.  I have also added 4 field columns and which we would be binding with products information.  In order to bind the datasource to gridview, set the DataSourceID property to sqldatasource control ID (sqlDSProducts).

    <asp:GridView ID="gvProducts"           
        AutoGenerateColumns="False"  CssClass="tablesorter"
        runat="server" DataKeyNames="ProductID"  
        DataSourceID="sqlDSProducts">      
        <Columns>
            <asp:BoundField DataField="ProductID" HeaderText="Product ID"/>  
            <asp:BoundField DataField="ProductNumber" HeaderText="Product Number"/>  
            <asp:TemplateField HeaderText="Product Name">                
                <ItemTemplate>               
                    <asp:Label runat="server" Text='<%# Eval("Name")%>' ID="lblName"></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="ListPrice" HeaderText="Price" DataFormatString="{0:c}"/>
          
        </Columns>  
    </asp:GridView>
    

    When you run the application, you would see following page sorted on ProductID in descending order and sorting disabled on “Product Name” column.
    sort gridview using jquery tablesorter 2

    Complete Source Code:
    GridViewSort.aspx

    <html>
    <head runat="server">
        <title></title>    
        <link rel="stylesheet" type="text/css" href="themes/blue/style.css" />
        <script type="text/javascript" src="jquery-latest.js"></script>
    <script type="text/javascript" src="jquery.tablesorter.js"></script>
        <script language="javascript" type="text/javascript">
            $(document).ready(function() {        
            $("#gvProducts").tablesorter({
                // sort on the first column, order desc
                sortList: [[0, 1]],
                // change the multi sort key from the default shift to alt button         
                //sortMultiSortKey: 'altKey'        
                 headers: 
                 {             
                    // assign the secound column (we start counting zero)             
                    2: 
                    {                 
                        // disable it by setting the property sorter to false                 
                        sorter: false            
                    }
                }              
                });    
            }); 
           
        </script>
    </head>
    <body>
    <form id="form1" runat="server">
      <asp:SqlDataSource ID="sqlDSProducts" runat="server" 
    ConnectionString="<%$ ConnectionStrings:Sql %>"
    SelectCommand="SELECT top 10 P.ProductID, P.Name, P.ProductNumber, P.ListPrice
    		FROM Production.Product P">      
    </asp:SqlDataSource> 
    <div>
    <asp:GridView ID="gvProducts"  Width="80%"          
        AutoGenerateColumns="False"  CssClass="tablesorter"
        runat="server" DataKeyNames="ProductID"  
        DataSourceID="sqlDSProducts">      
        <Columns>
            <asp:BoundField DataField="ProductID" HeaderText="Product ID"/>  
            <asp:BoundField DataField="ProductNumber" HeaderText="Product Number"/>  
            <asp:TemplateField HeaderText="Product Name">                
                <ItemTemplate>               
                    <asp:Label runat="server" Text='<%# Eval("Name")%>' ID="lblName"></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>        
            <asp:BoundField DataField="ListPrice" HeaderText="Price" DataFormatString="{0:c}"/>      
        </Columns>  
    </asp:GridView> 
        </div>        
    </form>
    </body>
    </html>
    

    GridViewSort.aspx.cs

    public partial class GridViewSort : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (this.gvProducts.Rows.Count > 0)
            {
                //Gets or sets a value indicating whether a System.Web.UI.WebControls.GridView
                //control renders its header in an accessible format. This property is provided
                //to make the control more accessible to users of assistive technology devices.
                gvProducts.UseAccessibleHeader = true;
                gvProducts.HeaderRow.TableSection = TableRowSection.TableHeader;
                gvProducts.FooterRow.TableSection = TableRowSection.TableFooter;
            }
        }    
    }
    
    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    One Responseso far.

    1. Umasankar says:

      Last row i dont want to sort. last row always to be last position. how to do on editable grid.

    Leave a Reply