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 =''
  20. SELECT*FROM #Results
  21. WHERE RowNumber BETWEEN(@PageIndex -1)* @PageSize + 1 AND(((@PageIndex -1)* @PageSize + 1)+ @PageSize)- 1
  23. SELECT @RecordCount =COUNT(*)FROM #Results
  25. DROPTABLE #Results
  26. END
The following is my aspx code:
  1. <%@PageLanguage="C#"AutoEventWireup="true"CodeBehind="ShowSearchPaging.aspx.cs"Inherits="ShowSearchAndPagingUsing_jQuery.ShowSearchPaging"%>
  3. <!DOCTYPEhtml>
  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 () {
  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) {
  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. });
  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;
  12. namespace ShowSearchAndPagingUsing_jQuery
  13. {
  14. publicpartialclassShowSearchPaging : System.Web.UI.Page
  15. {
  16. privatestaticint PageSize = 5;
  18. protectedvoid Page_Load(object sender, EventArgs e)
  19. {
  20. if (!IsPostBack)
  21. {
  22. BindGridViewHeader();
  23. }
  24. }
  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. }
  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. }
  55. privatestaticDataSet GetData(SqlCommand cmd, int pageIndex)
  56. {
  57. SqlDataAdapter da;
  58. DataSet ds = newDataSet();
  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();
  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;
  81. }
  82. }
  83. }
Now run the application:

application
Image 4.

run the application
Image 5.

output
Image 6.

next
Image 7.