Post
Ask Question

Show Search Data and Paging in ASP.Net Grid View Using jQuery

The following is my Data Table structure from which I am fetching data:

table design
Image 1.

Data in my table:

table
Image 2.

To do this I created the following Stored Procedure:

store procedure
Image 3.

My Stored Procedure is:

  1. CREATEPROCEDURE [dbo].[GetStudentDataWithSearch]  
  2. (       @SearchTerm VARCHAR(100)='',  
  3.       @PageIndex INT= 1,  
  4.       @PageSize INT= 10,  
  5.       @RecordCount INTOUTPUT  
  6. )  
  7. AS  
  8. BEGIN  
  9. SETNOCOUNTON;  
  10. SELECTROW_NUMBER()OVER  
  11. (  
  12. ORDERBY StudentID ASC  
  13.         )AS RowNumber  
  14.         ,StudentID  
  15.         ,Name  
  16.         ,Email  
  17.         ,Class,EnrollYear,City  INTO #Results FROM Student  
  18.         WHERE [Name] LIKE'%'+ @SearchTerm +'%'OR @SearchTerm =''  
  19.   
  20. SELECT*FROM #Results  
  21. WHERE RowNumber BETWEEN(@PageIndex -1)* @PageSize + 1 AND(((@PageIndex -1)* @PageSize + 1)+ @PageSize)- 1  
  22.   
  23. SELECT @RecordCount =COUNT(*)FROM #Results  
  24.   
  25. DROPTABLE #Results  
  26. END  
The following is my aspx code:
  1. <%@PageLanguage="C#"AutoEventWireup="true"CodeBehind="ShowSearchPaging.aspx.cs"Inherits="ShowSearchAndPagingUsing_jQuery.ShowSearchPaging"%>  
  2.   
  3. <!DOCTYPEhtml>  
  4.   
  5. <htmlxmlns="http://www.w3.org/1999/xhtml">  
  6. <headrunat="server">  
  7. <title>jQuery: Show Data and Paging in ASP.NET Grid View using jQuery</title>  
  8. <scripttype="text/javascript"src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>  
  9. <scriptsrc="jquery.pagination.min.js"type="text/javascript"></script>  
  10. <scripttype="text/javascript">  
  11.         $(function () {  
  12.   
  13.             GetStudents(1);  
  14.         });  
  15.         $("[id*=txtSearch]").live("keyup", function () {  
  16.             GetStudents(parseInt(1));  
  17.         });  
  18.         $(".Pagination .page").live("click", function () {  
  19.             GetStudents(parseInt($(this).attr('page')));  
  20.         });  
  21. function SearchTerm() {  
  22. return jQuery.trim($("[id*=txtSearch]").val());  
  23.         };  
  24. function GetStudents(pageIndex) {  
  25.   
  26.             $.ajax({  
  27.                 type: "POST",  
  28.                 url: "ShowSearchPaging.aspx/GetStudents",  
  29.                 data: '{searchTerm: "' + SearchTerm() + '", pageIndex: ' + pageIndex + '}',  
  30.                 contentType: "application/json; charset=utf-8",  
  31.                 dataType: "json",  
  32.                 success: OnSuccess,  
  33.                 failure: function (response) {  
  34.                     alert(response.d);  
  35.                 },  
  36.                 error: function (response) {  
  37.                     alert(response.d);  
  38.                 }  
  39.             });  
  40.         }  
  41. var row;  
  42. function OnSuccess(response) {  
  43. var xmlDoc = $.parseXML(response.d);  
  44. var xml = $(xmlDoc);  
  45. var customers = xml.find("Student");  
  46. if (row == null) {  
  47.                 row = $("[id*=GridViewStudent] tr:last-child").clone(true);  
  48.             }  
  49.             $("[id*=GridViewStudent] tr").not($("[id*=GridViewStudent] tr:first-child")).remove();  
  50. if (customers.length > 0) {  
  51.                 $.each(customers, function () {  
  52. var customer = $(this);  
  53.                     $("td", row).eq(0).html($(this).find("Name").text());  
  54.                     $("td", row).eq(1).html($(this).find("Email").text());  
  55.                     $("td", row).eq(2).html($(this).find("Class").text());  
  56.                     $("td", row).eq(3).html($(this).find("EnrollYear").text());  
  57.                     $("td", row).eq(4).html($(this).find("City").text());  
  58.                     $("td", row).eq(5).html($(this).find("Country").text());  
  59.                     $("[id*=GridViewStudent]").append(row);  
  60.                     row = $("[id*=GridViewStudent] tr:last-child").clone(true);  
  61.                 });  
  62. var pager = xml.find("dtForPaging");  
  63.                 $(".Pagination").jQ_Pager({  
  64.                     ActiveCssClass: "current",  
  65.                     PagerCssClass: "pager",  
  66.                     PageIndex: parseInt(pager.find("PageIndex").text()),  
  67.                     PageSize: parseInt(pager.find("PageSize").text()),  
  68.                     RecordCount: parseInt(pager.find("RecordCount").text())  
  69.                 });  
  70.   
  71.                 $(".Name").each(function () {  
  72. var searchPattern = new RegExp('(' + SearchTerm() + ')', 'ig');  
  73.                     $(this).html($(this).text().replace(searchPattern, "<span style='background-color:red;color:white;'>" + SearchTerm() + "</span>"));  
  74.                 });  
  75.             } else {  
  76. var empty_row = row.clone(true);  
  77.                 $("td:first-child", empty_row).attr("colspan", $("td", row).length);  
  78.                 $("td:first-child", empty_row).attr("align", "center");  
  79.                 $("td:first-child", empty_row).html("No records found for the search criteria.");  
  80.                 $("td", empty_row).not($("td:first-child", empty_row)).remove();  
  81.                 $("[id*=GridViewStudent]").append(empty_row);  
  82.             }  
  83.         };  
  84. </script>  
  85. </head>  
  86. <body>  
  87.    <formid="form1"runat="server">  
  88.       <div>  
  89.          <tablestyle="border: solid15pxblue; width: 100%; vertical-align: central;">  
  90.             <tr>  
  91.                <tdstyle="padding-left: 20px; padding-top: 20px; padding-bottom: 20px; background-color: skyblue; text-align: center; font-family: Verdana; font-size: 20pt; color: red;">jQuery: Show Data and Paging in ASP.NET Grid View using jQuery</td>  
  92.             </tr>  
  93.             <tr>  
  94.                <tdstyle="padding-left: 100px; padding-top: 20px; padding-bottom: 20px; background-color: #EE9A4D; font-family: Arial; font-size: 15pt; color: #E41B17">Enter Name To Search #:  
  95.                   <asp:TextBoxID="txtSearch"runat="server"/>  
  96.                </td>  
  97.             </tr>  
  98.             <tr>  
  99.                <td>  
  100.                   <tablestyle="width: 80%; text-align: center; vertical-align: central;">  
  101.                      <tr>  
  102.                         <tdstyle="text-align: left;">  
  103.                               <asp:GridViewID="GridViewStudent"runat="server"AutoGenerateColumns="False"Width="100%"  
  104.                                  BackColor="White"BorderColor="#3366CC"BorderStyle="None"BorderWidth="1px"CellPadding="4"GridLines="Both">  
  105.                                     <Columns>  
  106.                                        <asp:BoundFieldDataField="Name"HeaderText="Student Name"HeaderStyle-HorizontalAlign="Left"ItemStyle-CssClass="Name"></asp:BoundField>  
  107.                                        <asp:BoundFieldDataField="Email"HeaderText="Email"HeaderStyle-HorizontalAlign="Left"/>  
  108.                                        <asp:BoundFieldDataField="Class"HeaderText="Class"HeaderStyle-HorizontalAlign="Left"/>  
  109.                                        <asp:BoundFieldDataField="EnrollYear"HeaderText="Enroll Year"HeaderStyle-HorizontalAlign="Left"/>  
  110.                                        <asp:BoundFieldDataField="City"HeaderText="City"HeaderStyle-HorizontalAlign="Left"/>  
  111.                                        <asp:BoundFieldDataField="Country"HeaderText="Country"HeaderStyle-HorizontalAlign="Left"/>  
  112.                                     </Columns>  
  113.                                     <FooterStyleBackColor="#99CCCC"ForeColor="#003399"/>  
  114.                                        <HeaderStyleBackColor="#003399"Font-Bold="True"ForeColor="#CCCCFF"/>  
  115.                                        <PagerStyleBackColor="#99CCCC"ForeColor="#003399"HorizontalAlign="Left"/>  
  116.                                        <RowStyleBackColor="White"ForeColor="#003399"/>  
  117.                                        <SelectedRowStyleBackColor="#009999"Font-Bold="True"ForeColor="#CCFF99"/>  
  118.                                        <SortedAscendingCellStyleBackColor="#EDF6F6"/>  
  119.                                        <SortedAscendingHeaderStyleBackColor="#0D4AC4"/>  
  120.                                        <SortedDescendingCellStyleBackColor="#D6DFDF"/>  
  121.                                        <SortedDescendingHeaderStyleBackColor="#002876"/>  
  122.                                     </asp:GridView>  
  123.                                  </td>  
  124.                               </tr>  
  125.                               <tr>  
  126.                               <td>  
  127.                                  <divclass="Pagination"style="background-color: orange; font-family: Verdana; font-size: 10pt; height: 30px; text-align: center; vertical-align: central; padding-top: 20px; padding-bottom: 10px;">  
  128.                               </div>  
  129.                               </td>  
  130.                            </tr>  
  131.                         </table>  
  132.                      </td>  
  133.                   </tr>  
  134.                </table>  
  135.             </div>  
  136.          </form>  
  137.       </body>  
  138. </html>  
Now my aspx.cs code is:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Linq;  
  7. using System.Web;  
  8. using System.Web.Services;  
  9. using System.Web.UI;  
  10. using System.Web.UI.WebControls;  
  11.   
  12. namespace ShowSearchAndPagingUsing_jQuery  
  13. {  
  14. publicpartialclassShowSearchPaging : System.Web.UI.Page  
  15.     {  
  16. privatestaticint PageSize = 5;  
  17.   
  18. protectedvoid Page_Load(object sender, EventArgs e)  
  19.         {  
  20. if (!IsPostBack)  
  21.             {  
  22.                 BindGridViewHeader();  
  23.             }  
  24.         }  
  25.   
  26. privatevoid BindGridViewHeader()  
  27.         {  
  28. DataTable dtHeader = newDataTable();  
  29.             dtHeader.Columns.Add("Name");  
  30.             dtHeader.Columns.Add("Email");  
  31.             dtHeader.Columns.Add("Class");  
  32.             dtHeader.Columns.Add("EnrollYear");  
  33.             dtHeader.Columns.Add("City");  
  34.             dtHeader.Columns.Add("Country");  
  35.             dtHeader.Rows.Add();  
  36.             GridViewStudent.DataSource = dtHeader;  
  37.             GridViewStudent.DataBind();  
  38.         }  
  39.   
  40.         [WebMethod]  
  41. publicstaticstring GetStudents(string searchTerm, int pageIndex)  
  42.         {  
  43. string query = "[GetStudentDataWithSearch]";  
  44. SqlCommand cmd = newSqlCommand(query);  
  45.             cmd.CommandType = CommandType.StoredProcedure;  
  46.             cmd.Parameters.AddWithValue("@SearchTerm", searchTerm);  
  47.             cmd.Parameters.AddWithValue("@PageIndex", pageIndex);  
  48.             cmd.Parameters.AddWithValue("@PageSize", PageSize);  
  49.             cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;  
  50. return GetData(cmd, pageIndex).GetXml();  
  51.         }  
  52.   
  53.   
  54.   
  55. privatestaticDataSet GetData(SqlCommand cmd, int pageIndex)  
  56.         {  
  57. SqlDataAdapter da;  
  58. DataSet ds = newDataSet();  
  59.   
  60. SqlConnection con = newSqlConnection();  
  61.             ds = newDataSet();  
  62.             con.ConnectionString = @"Data Source=MYPC\SqlServer2k8; Initial Catalog=SchoolManagement; Integrated Security=true;";  
  63.             cmd.Connection = con;  
  64.             da = newSqlDataAdapter(cmd);  
  65.             da.Fill(ds, "Student");  
  66.             con.Open();  
  67.             cmd.ExecuteNonQuery();  
  68.             con.Close();  
  69.   
  70. DataTable dt = newDataTable("dtForPaging");  
  71.             dt.Columns.Add("PageIndex");  
  72.             dt.Columns.Add("PageSize");  
  73.             dt.Columns.Add("RecordCount");  
  74.             dt.Rows.Add();  
  75.             dt.Rows[0]["PageIndex"] = pageIndex;  
  76.             dt.Rows[0]["PageSize"] = PageSize;  
  77.             dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;  
  78.             ds.Tables.Add(dt);  
  79. return ds;  
  80.   
  81.         }  
  82.     }  
  83. }  
Now run the application:

application
Image 4.

run the application
Image 5.

output
Image 6.

next
Image 7.

 


Similar Articles