Post
Ask Question

Show and Paging in ASP.Net Gridview 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].[GetStudentData]  
  2. (  
  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, Country  INTO #Results FROM Student  
  18.   
  19. SELECT*FROM #Results  
  20. WHERE RowNumber BETWEEN(@PageIndex -1)* @PageSize + 1 AND(((@PageIndex -1)* @PageSize + 1)+ @PageSize)- 1  
  21.   
  22. SELECT @RecordCount =COUNT(*)FROM #Results  
  23.   
  24. DROPTABLE #Results  
  25. END  
The following is my aspx code:
  1. <%@PageLanguage="C#"AutoEventWireup="true"CodeBehind="Default.aspx.cs"Inherits="jQueryPagination.Default"%>  
  2. <!DOCTYPEhtml>  
  3. <htmlxmlns="http://www.w3.org/1999/xhtml">  
  4. <headrunat="server">  
  5. <title></title>  
  6. <scripttype="text/javascript"src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>  
  7. <scriptsrc="jquery.pagination.min.js"type="text/javascript"></script>  
  8. <scripttype="text/javascript">  
  9.         $(document).ready(function () {  
  10.             GetStudents(1);  
  11.         });  
  12.         $("[id*=txtSearch]").live("keyup", function () {  
  13.             GetStudents(parseInt(1));  
  14.         });  
  15.         $(".Pagination .page").live("click", function () {  
  16.             GetStudents(parseInt($(this).attr('page')));  
  17.         });  
  18.   
  19.         functionGetStudents(pageIndex) {  
  20.   
  21.             $.ajax({  
  22.                 type: "POST",  
  23.                 url: "Default.aspx/GetStudents",  
  24.                 data: '{pageIndex: ' + pageIndex + '}',  
  25.                 contentType: "application/json; charset=utf-8",  
  26.              dataType: "json",  
  27.                 success: OnSuccess,  
  28.                 failure: function (response) {  
  29.                     alert(response.d);  
  30.                 },  
  31.                 error: function (response) {  
  32.                     alert(response.d);  
  33.                 }  
  34.             });  
  35.         }  
  36.         var row;  
  37.         functionOnSuccess(response) {  
  38.             varxmlDoc = $.parseXML(response.d);  
  39.             var xml = $(xmlDoc);  
  40.             var students = xml.find("Student");  
  41.             if (row == null) {  
  42.                 row = $("[id*=GridViewStudent] tr:last-child").clone(true);  
  43.             }  
  44.             $("[id*=GridViewStudent] tr").not($("[id*=GridViewStudent] tr:first-child")).remove();  
  45.             if (students.length> 0) {  
  46.                 $.each(students, function () {  
  47.                     var student = $(this);  
  48.                     $("td", row).eq(0).html($(this).find("Name").text());  
  49.                     $("td", row).eq(1).html($(this).find("Email").text());  
  50.                     $("td", row).eq(2).html($(this).find("Class").text());  
  51.                     $("td", row).eq(3).html($(this).find("EnrollYear").text());  
  52.                     $("td", row).eq(4).html($(this).find("City").text());  
  53.                     $("td", row).eq(5).html($(this).find("Country").text());  
  54.                     $("[id*=GridViewStudent]").append(row);  
  55.                     row = $("[id*=GridViewStudent] tr:last-child").clone(true);  
  56.                 });  
  57.                 var pager = xml.find("dtForPaging");  
  58.                 $(".Pagination").jQ_Pager({  
  59.                     ActiveCssClass: "current",  
  60.                     PagerCssClass: "pager",  
  61.                     PageIndex: parseInt(pager.find("PageIndex").text()),  
  62.                     PageSize: parseInt(pager.find("PageSize").text()),  
  63.                     RecordCount: parseInt(pager.find("RecordCount").text())  
  64.                 });  
  65.   
  66.                 $(".Name").each(function () {  
  67.                     varsearchPattern = newRegExp('(' + SearchTerm() + ')', 'ig');  
  68.                     $(this).html($(this).text().replace(searchPattern, "<span class = 'highlight'>" + SearchTerm() + "</span>"));  
  69.                 });  
  70.             }   
  71.             else {  
  72.                 varempty_row = row.clone(true);  
  73.                 $("td:first-child", empty_row).attr("colspan", $("td", row).length);  
  74.                 $("td:first-child", empty_row).attr("align", "center");  
  75.                 $("td:first-child", empty_row).html("No records found for the search criteria.");  
  76.                 $("td", empty_row).not($("td:first-child", empty_row)).remove();  
  77.                 $("[id*=GridViewStudent]").append(empty_row);  
  78.             }  
  79.         };  
  80. </script>  
  81.   
  82. </head>  
  83. <body>  
  84.     <formid="form1"runat="server">  
  85.         <div>  
  86.             <tablestyle="border: solid15pxblue; width: 100%; vertical-align: central;">  
  87.                 <tr>  
  88.                     <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: Display Records With Paging in ASP.NET Grid View using jQuery</td>  
  89.                 </tr>  
  90.                 <tr>  
  91.                 <td>  
  92.                     <tablestyle="width: 80%; text-align: center; vertical-align: central;">  
  93.                         <tr>  
  94.                             <tdstyle="text-align: left;">  
  95.                                 <asp:GridViewID="GridViewStudent"runat="server"AutoGenerateColumns="False"Width="100%"  
  96.                                      BackColor="#DEBA84"BorderColor="#DEBA84"BorderStyle="None"BorderWidth="1px"CellPadding="3"CellSpacing="2">  
  97.                                          <Columns>  
  98.                                             <asp:BoundFieldDataField="Name"HeaderText="Student Name"HeaderStyle-HorizontalAlign="Left"></asp:BoundField>  
  99.                                             <asp:BoundFieldDataField="Email"HeaderText="Email"HeaderStyle-HorizontalAlign="Left"/>  
  100.                                             <asp:BoundFieldDataField="Class"HeaderText="Class"HeaderStyle-HorizontalAlign="Left"/>  
  101.                                             <asp:BoundFieldDataField="EnrollYear"HeaderText="Enroll Year"HeaderStyle-HorizontalAlign="Left"/>  
  102.                                             <asp:BoundFieldDataField="City"HeaderText="City"HeaderStyle-HorizontalAlign="Left"/>  
  103.                                             <asp:BoundFieldDataField="Country"HeaderText="Country"HeaderStyle-HorizontalAlign="Left"/>  
  104.                                          </Columns>  
  105.                                             <FooterStyleBackColor="#F7DFB5"ForeColor="#8C4510"/>  
  106.                                             <HeaderStyleBackColor="#A55129"Font-Bold="True"ForeColor="White"/>  
  107.                                             <PagerStyleForeColor="#8C4510"HorizontalAlign="Center"/>  
  108.                                             <RowStyleBackColor="#FFF7E7"ForeColor="#8C4510"/>  
  109.                                             <SelectedRowStyleBackColor="#738A9C"Font-Bold="True"ForeColor="White"/>  
  110.                                             <SortedAscendingCellStyleBackColor="#FFF1D4"/>  
  111.                                             <SortedAscendingHeaderStyleBackColor="#B95C30"/>  
  112.                                             <SortedDescendingCellStyleBackColor="#F1E5CE"/>  
  113.                                             <SortedDescendingHeaderStyleBackColor="#93451F"/>  
  114.                                     </asp:GridView>  
  115.                                  </td>  
  116.                                 </tr>  
  117.                                  <tr>  
  118.                                     <td>  
  119.                                      <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;">  
  120.                                  </div>  
  121.                              </td>  
  122.                           </tr>  
  123.                       </table>  
  124.                    </td>  
  125.                </tr>  
  126.             </table>  
  127.          </div>  
  128.         </form>  
  129.     </body>  
  130. </html>  
Now My aspx.cs code is:
  1. using System;  
  2. usingSystem.Collections.Generic;  
  3. usingSystem.Data;  
  4. usingSystem.Data.SqlClient;  
  5. usingSystem.Linq;  
  6. usingSystem.Web;  
  7. usingSystem.Web.Services;  
  8. usingSystem.Web.UI;  
  9. usingSystem.Web.UI.WebControls;  
  10.   
  11. namespacejQueryPagination  
  12. {  
  13.     publicpartialclassDefault : System.Web.UI.Page  
  14.     {  
  15.         privatestaticintPageSize = 5;  
  16.   
  17.         protectedvoidPage_Load(object sender, EventArgs e)  
  18.         {  
  19.             if (!IsPostBack)  
  20.             {  
  21.                 BindGridViewHeader();  
  22.             }  
  23.         }  
  24.   
  25.         privatevoidBindGridViewHeader()  
  26.         {  
  27.             DataTabledtHeader = newDataTable();  
  28.             dtHeader.Columns.Add("Name");  
  29.             dtHeader.Columns.Add("Email");  
  30.             dtHeader.Columns.Add("Class");  
  31.             dtHeader.Columns.Add("EnrollYear");  
  32.             dtHeader.Columns.Add("City");  
  33.             dtHeader.Columns.Add("Country");  
  34.             dtHeader.Rows.Add();  
  35.             GridViewStudent.DataSource = dtHeader;  
  36.             GridViewStudent.DataBind();  
  37.         }  
  38.   
  39.         [WebMethod]  
  40.         publicstaticstringGetStudents(intpageIndex)  
  41.         {  
  42.             stringSP_Name = "[GetStudentData]";  
  43.             SqlCommandcmd = newSqlCommand(SP_Name);  
  44.             cmd.CommandType = CommandType.StoredProcedure;  
  45.             cmd.Parameters.AddWithValue("@PageIndex", pageIndex);  
  46.             cmd.Parameters.AddWithValue("@PageSize", PageSize);  
  47.             cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;  
  48.             returnGetStudentData(cmd, pageIndex).GetXml();  
  49.         }  
  50.   
  51.   
  52.         privatestaticDataSetGetStudentData(SqlCommandcmd, intpageIndex)  
  53.         {  
  54.             SqlDataAdapter da;  
  55.             DataSet ds = newDataSet();  
  56.   
  57.             SqlConnection con = newSqlConnection();  
  58.             ds = newDataSet();  
  59.             con.ConnectionString = @"Data Source=MyPC\SqlServer2k8; Initial Catalog=SchoolManagement; Integrated Security=true;";  
  60.             cmd.Connection = con;  
  61.             da = newSqlDataAdapter(cmd);  
  62.             da.Fill(ds, "Student");  
  63.             con.Open();  
  64.             cmd.ExecuteNonQuery();  
  65.             con.Close();  
  66.   
  67.             //Addning Table For Paging Data  
  68.             DataTabledt = newDataTable("dtForPaging");  
  69.             dt.Columns.Add("PageIndex");  
  70.             dt.Columns.Add("PageSize");  
  71.             dt.Columns.Add("RecordCount");  
  72.             dt.Rows.Add();  
  73.             dt.Rows[0]["PageIndex"] = pageIndex;  
  74.             dt.Rows[0]["PageSize"] = PageSize;  
  75.             dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;  
  76.             ds.Tables.Add(dt);  
  77.             return ds;  
  78.         }  
  79.     }  
  80. }  
Now run the application:

display record
Image 4.

paging
Image 5.

run the application
Image 6.


Similar Articles