Searching and Sorting and Paging in Grid view Control with Simple JQuery

Introduction

In a Web Application, we mainly use Gridview controls to display data fetched from a database. Dynamically data also needs to be searched and sorted. Sorting users usually requires a lot of coding for gridviews on sorting, searching and paging events. In this article, you will learn how to perform Searching, Sorting and Paging in a Gridview Control with simple JQuery.

Step 1: Create a Database

Add 1 table in it.

1. Table: tbSorting (say)



Step 2: Open Visual Studio 2005/2008->File->New Website

Step 3: Open Solution Explorer ->Add new item -> Add SearchNSortGrid.aspx


Step 4: Design the page and Place required grid view control inside update Panel and a Textbox Control to search data in grid view. Below is the source code.

Search: <asp:TextBox ID="txtSearch" runat="server" OnTextChanged="txtSearch_TextChanged" Height="20px" Width="208px"  />  

<asp:UpdatePanel ID="UpdatePanel1" runat="server" >
 <ContentTemplate>   
<asp:GridView ID="Gridview2" runat="server" AutoGenerateColumns="False" AllowPaging="True" AllowSorting="True" DataSourceID="dsGridview" Width="540px" CssClass="yui" PageSize="5">
<Columns> 
 <asp:BoundField DataField="Id" HeaderText="Id" SortExpression="Id" ItemStyle-Width="40px"
ItemStyle-HorizontalAlign="Center" InsertVisible="False" ReadOnly="True" >
<ItemStyle HorizontalAlign="Center" Width="40px" /></asp:BoundField>
<asp:BoundField DataField="First" HeaderText="First"
SortExpression="First" />
<asp:BoundField DataField="Last" HeaderText="Last"SortExpression="Last"/> 
<asp:BoundField DataField="Department" HeaderText="Department"
ItemStyle-Width="130px" SortExpression="Department" >
<ItemStyle Width="130px" /></asp:BoundField>
<asp:BoundField DataField="Location" HeaderText="Location"
ItemStyle-Width="130px" SortExpression="Location" >
<ItemStyle Width="130px" /></asp:BoundField>
</Columns>
</asp:GridView>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="txtSearch" EventName="TextChanged" />
</Triggers>
</asp:UpdatePanel>

Step 5: Bind the GridView data with the SqlDataSource.

<asp:SqlDataSource ID="dsGridview" runat="server"
ConnectionString="<%$ ConnectionStrings:testDBConnectionString2 %>"       
SelectCommand="SELECT [Id], [First], [Last], [Department], [Location] FROM [tbSorting]"
FilterExpression="First like '%{0}%' or Last like '%{1}%'"><FilterParameters>
<asp:ControlParameter Name="First"ControlID="txtSearch" PropertyName="Text"/>
<asp:ControlParameter Name="Last" ControlID="txtSearch" PropertyName="Text"/>
</FilterParameters> </asp:SqlDataSource>

Step 6: Add Java Scripts and CSS inside head portion.

1. 
   <script type="text/javascript">
     jQuery(document).ready(function () {
      $("#Gridview2").tablesorter({ debug: false, widgets: ['zebra'], sortList: [[0, 0]] });
     });
   </script>
    

2. Also add a reference of the two js files and 1 CSS.
   
   <script type="text/javascript" src=http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js > </script>
   <script type="text/javascript" src="Scripts/jquery.tablesorter-2.0.3.js"></script>
   <link type="text/css" rel="stylesheet" href="Scripts/style1.css" />

Step 7: Now at the code behind write the code as given Below.

using System;

using System.Web;

using System.Collections.Generic;

using System.Linq;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.Adapters;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using System.Text;

using System.Text.RegularExpressions;

using System.IO;

 

public partial class SearchNSortGrid : System.Web.UI.Page

{

    string SearchString = "";

    public string connection;

   

    protected void Page_Load(object sender, EventArgs e)

    {

     connection = System.Configuration.ConfigurationManager.ConnectionStrings["Connectionstring"].ToString();

  txtSearch.Attributes.Add("onkeyup", "setTimeout('__doPostBack(\'" + txtSearch.ClientID.Replace("_", "$") + "\',\'\')', 0);");

   if (!IsPostBack)

        {

            Gridview2.DataBind();

        }

    }

    protected void txtSearch_TextChanged(object sender, EventArgs e)

    {

        SearchString = txtSearch.Text;

    }

    public string HighlightText(string InputTxt)

    {

      string Search_Str = txtSearch.Text.ToString();         // Setup the regular expression and add the Or operator.  

        Regex RegExp = new Regex(Search_Str.Replace(" ", "|").Trim(), RegexOptions.IgnoreCase);         // Highlight keywords by calling the         //delegate each time a keyword is found.   

        return RegExp.Replace(InputTxt, new MatchEvaluator(ReplaceKeyWords));         // Set the RegExp to null. 

        RegExp = null;

    }

    public string ReplaceKeyWords(Match m)

    {

        return "<span class=highlight>" + m.Value + "</span>";

    }

}

Step 8: Run application (Press F5).

Summary

The Final Layouts Will be as given Below.

Figure: 1.1: On Load At first time.



Figure 1.2: On Searching with initials 'As'



Figure 1.3: On Sorting with Department Column Field in ASC Order



Figure 1.4: On Paging in Grid view.