JQuery

jQuery JSON- Showing Records in GridView and Perform SQL Paging

Figure 1 shows the DataTable in design mode from which I am reading records.

table design
Figure 1.

The following is the script of My Employee table:

  1. CREATE TABLE [dbo].[Employee](  
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [varchar](50) NULL,  
  4.     [Email] [varchar](500) NULL,  
  5.     [Country] [varchar](50) NULL,  
  6.     [ProjectID] [int] NULL,  
  7.     [ManagerName] [varchar](50) NULL,  
  8.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
  9. (  
  10.     [ID] ASC  
  11. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  12. ) ON [PRIMARY]  
  13.   
  14. GO  
To do this I created a Stored Procedure as in the following:
  1. CREATE PROCEDURE [dbo].[GetEmployee_SQLPaging]  
  2.       @PageIndex INT = 1  
  3.       ,@PageSize INT = 10  
  4.       ,@RecordCount INT OUTPUT  
  5. AS  
  6. BEGIN  
  7.       SET NOCOUNT ON;  
  8.       SELECT ROW_NUMBER() OVER  
  9.       (  
  10.             ORDER BY [ID] ASC  
  11.       )AS RowNumber  
  12.       ,[ID],[Name],[Email],[Country] INTO #Results FROM [Employee]             
  13.        
  14.       SELECT @RecordCount = COUNT(*) FROM #Results  
  15.              
  16.       SELECT * FROM #Results  
  17.       WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1  
  18.        
  19.       DROP TABLE #Results  
  20. END  
SQL Query
Figure 2.

Now the following is my aspx:
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="JSON_GridView_SQLPaging.Default" %>  
  2.   
  3. <!DOCTYPE html>  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <style type="text/css">  
  8.         body {  
  9.             font-family: Arial;  
  10.             font-size: 10pt;  
  11.         }  
  12.   
  13.         .Pager span {  
  14.             text-align: center;  
  15.             color: navy;  
  16.             display: inline-block;  
  17.             width: 20px;  
  18.             background-color: red;  
  19.             margin-right: 3px;  
  20.             line-height: 150%;  
  21.             border: 2px solid navy;  
  22.         }  
  23.   
  24.         .Pager a {  
  25.             text-align: center;  
  26.             display: inline-block;  
  27.             width: 20px;  
  28.             background-color: green;  
  29.             color: #fff;  
  30.             border: 1px solid #3AC0F2;  
  31.             margin-right: 3px;  
  32.             line-height: 150%;  
  33.             text-decoration: none;  
  34.         }  
  35.     </style>  
  36.     <title></title>  
  37.     <script src="Scripts/jquery.min.js"></script>  
  38.     <script src="jQueryPager.min.js" type="text/javascript"></script>  
  39.     <script type="text/javascript">  
  40.         $(function () {  
  41.             BindEmployee(1);  
  42.         });  
  43.         $(".Pager .page").live("click", function () {  
  44.             BindEmployee(parseInt($(this).attr('page')));  
  45.         });  
  46.         function BindEmployee(pageIndex) {  
  47.             $.ajax({  
  48.                 type: "POST",  
  49.                 url: "Default.aspx/BindEmployee",  
  50.                 data: '{pageIndex: ' + pageIndex + '}',  
  51.                 contentType: "application/json; charset=utf-8",  
  52.                 dataType: "json",  
  53.                 success: OnSuccess,  
  54.                 failure: function (response) {  
  55.                     alert(response.d);  
  56.                 },  
  57.                 error: function (response) {  
  58.                     alert(response.d);  
  59.                 }  
  60.             });  
  61.         }  
  62.   
  63.         function OnSuccess(response) {  
  64.             var xmlDoc = $.parseXML(response.d);  
  65.             var xml = $(xmlDoc);  
  66.             var emp = xml.find("Employee");  
  67.             var row = $("[id*=gvEmployee] tr:last-child").clone(true);  
  68.             $("[id*=gvEmployee] tr").not($("[id*=gvEmployee] tr:first-child")).remove();  
  69.             $.each(emp, function () {  
  70.                 var employee = $(this);  
  71.                 $("td", row).eq(0).html($(this).find("ID").text());  
  72.                 $("td", row).eq(1).html($(this).find("Name").text());  
  73.                 $("td", row).eq(2).html($(this).find("Email").text());  
  74.                 $("td", row).eq(3).html($(this).find("Country").text());  
  75.                 $("[id*=gvEmployee]").append(row);  
  76.                 row = $("[id*=gvEmployee] tr:last-child").clone(true);  
  77.             });  
  78.             var pager = xml.find("Pager");  
  79.             $(".Pager").jQueryPagerFunc({  
  80.                 ActiveCssClass: "current",  
  81.                 PagerCssClass: "pager",  
  82.                 PageIndex: parseInt(pager.find("PageIndex").text()),  
  83.                 PageSize: parseInt(pager.find("PageSize").text()),  
  84.                 RecordCount: parseInt(pager.find("RecordCount").text())  
  85.             });  
  86.         };  
  87.     </script>  
  88. </head>  
  89. <body>  
  90.     <form id="form1" runat="server">  
  91.         <div>  
  92.             <table style="width: 100%; text-align: center; border: solid 5px red; background-color: blue; vertical-align: top;">  
  93.                 <tr>  
  94.                     <td>  
  95.                         <div>  
  96.                             <fieldset style="width: 99%;">  
  97.                                 <legend style="font-size: 20pt; color: white; font-family: Verdana">jQuery JSON - Showing Records in Grid View With SQL Paging</legend>  
  98.                                 <table style="width: 100%;">  
  99.                                     <tr>  
  100.                                         <td style="vertical-align: top; background-color: #9DD1F1; text-align: center;">  
  101.                                             <asp:GridView ID="gvEmployee" runat="server" AutoGenerateColumns="false"  
  102.                                                 HeaderStyle-ForeColor="White" Width="100%" BackColor="Yellow">  
  103.                                                 <Columns>  
  104.                                                     <asp:BoundField ItemStyle-Width="30px" DataField="ID" HeaderText="ID" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" ItemStyle-ForeColor="Red" />  
  105.                                                     <asp:BoundField ItemStyle-Width="80px" DataField="Name" HeaderText="Name" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" ItemStyle-ForeColor="Red" />  
  106.                                                     <asp:BoundField ItemStyle-Width="100px" DataField="Email" HeaderText="Email" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" ItemStyle-ForeColor="Red" />  
  107.                                                     <asp:BoundField ItemStyle-Width="80px" DataField="Country" HeaderText="City" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" ItemStyle-ForeColor="Red" />  
  108.                                                 </Columns>  
  109.                                                 <HeaderStyle BackColor="Red" HorizontalAlign="Left" />  
  110.                                             </asp:GridView>  
  111.                                             <br />  
  112.                                             <div class="Pager" style="background-color: green; padding-top: 10px; padding-bottom: 10px;"></div>  
  113.                                         </td>  
  114.                                     </tr>  
  115.                                 </table>  
  116.                             </fieldset>  
  117.                         </div>  
  118.                     </td>  
  119.                 </tr>  
  120.             </table>  
  121.         </div>  
  122.     </form>  
  123. </body>  
  124. </html>  
Now the following is the aspx.cs code:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Web.Services;  
  9. using System.Configuration;  
  10. using System.Data.SqlClient;  
  11.   
  12. namespace JSON_GridView_SQLPaging  
  13. {  
  14.     public partial class Default : System.Web.UI.Page  
  15.     {  
  16.         private static int PageSize = 10;  
  17.         protected void Page_Load(object sender, EventArgs e)  
  18.         {  
  19.             if (!IsPostBack)  
  20.             {  
  21.                 BindDummyRowToGridView();  
  22.             }  
  23.         }  
  24.   
  25.         private void BindDummyRowToGridView()  
  26.         {  
  27.             DataTable dummy = new DataTable();  
  28.             dummy.Columns.Add("ID");  
  29.             dummy.Columns.Add("Name");  
  30.             dummy.Columns.Add("Email");  
  31.             dummy.Columns.Add("Country");  
  32.             dummy.Rows.Add();  
  33.             gvEmployee.DataSource = dummy;  
  34.             gvEmployee.DataBind();  
  35.         }  
  36.   
  37.         [WebMethod]  
  38.         public static string BindEmployee(int pageIndex)  
  39.         {  
  40.             string query = "[GetEmployee_SQLPaging]";  
  41.             SqlCommand cmd = new SqlCommand(query);  
  42.             cmd.CommandType = CommandType.StoredProcedure;  
  43.             cmd.Parameters.AddWithValue("@PageIndex", pageIndex);  
  44.             cmd.Parameters.AddWithValue("@PageSize", PageSize);  
  45.             cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;  
  46.             return ReadData(cmd, pageIndex).GetXml();  
  47.         }  
  48.   
  49.         private static DataSet ReadData(SqlCommand cmd, int pageIndex)  
  50.         {  
  51.             string connectionString = @"Data Source=INDIA\MSSQLServer2k8; Initial Catalog= TestDB; Integrated Security=true;";  
  52.             using (SqlConnection con = new SqlConnection(connectionString))  
  53.             {  
  54.                 using (SqlDataAdapter sda = new SqlDataAdapter())  
  55.                 {  
  56.                     cmd.Connection = con;  
  57.                     sda.SelectCommand = cmd;  
  58.                     using (DataSet ds = new DataSet())  
  59.                     {  
  60.                         sda.Fill(ds, "Employee");  
  61.                         DataTable dt = new DataTable("Pager");  
  62.                         dt.Columns.Add("PageIndex");  
  63.                         dt.Columns.Add("PageSize");  
  64.                         dt.Columns.Add("RecordCount");  
  65.                         dt.Rows.Add();  
  66.                         dt.Rows[0]["PageIndex"] = pageIndex;  
  67.                         dt.Rows[0]["PageSize"] = PageSize;  
  68.                         dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;  
  69.                         ds.Tables.Add(dt);  
  70.                         return ds;  
  71.                     }  
  72.                 }  
  73.             }  
  74.         }  
  75.     }  
  76. }  
For this I created JavaScript file jQueryPager.min.js as in the following:
  1. function jQueryPagerFunc(a, b)  
  2. {  
  3.     var c = '<a style = "cursor:pointer" class="page" page = "{1}">{0}</a>';  
  4.     var d = "<span>{0}</span>";  
  5.     var e, f, g;  
  6.     var g = 5;  
  7.     var h = Math.ceil(b.RecordCount / b.PageSize);  
  8.     if (b.PageIndex > h)  
  9.     { b.PageIndex = h }  
  10.     var i = "";  
  11.     if (h > 1)  
  12.     {  
  13.         f = h > g ? g : h;  
  14.         e = b.PageIndex > 1 && b.PageIndex + g - 1 < g ? b.PageIndex : 1;  
  15.         if (b.PageIndex > g % 2)  
  16.         {  
  17.             if (b.PageIndex == 2) f = 5;  
  18.             else f = b.PageIndex + 2  
  19.         }  
  20.         else  
  21.         {  
  22.             f = g - b.PageIndex + 1  
  23.         }  
  24.         if (f - (g - 1) > e)  
  25.         {  
  26.             e = f - (g - 1)  
  27.         }  
  28.         if (f > h)  
  29.         {  
  30.             f = h;  
  31.             e = f - g + 1 > 0 ? f - g + 1 : 1  
  32.         }  
  33.         var j = (b.PageIndex - 1) * b.PageSize + 1;  
  34.         var k = j + b.PageSize - 1;  
  35.         if (k > b.RecordCount)  
  36.         {  
  37.             k = b.RecordCount  
  38.         }  
  39.         i = "<b>Records " + (j == 0 ? 1 : j) + " - " + k + " of " + b.RecordCount + "</b> ";  
  40.         if (b.PageIndex > 1)  
  41.         {  
  42.             i += c.replace("{0}", "<<").replace("{1}", "1");  
  43.             i += c.replace("{0}", "<").replace("{1}", b.PageIndex - 1)  
  44.         }  
  45.         for (var l = e; l <= f; l++)  
  46.         {  
  47.             if (l == b.PageIndex)  
  48.             {  
  49.                 i += d.replace("{0}", l)  
  50.             }  
  51.             else  
  52.             {  
  53.                 i += c.replace("{0}", l).replace("{1}", l)  
  54.             }  
  55.         }  
  56.         if (b.PageIndex < h)  
  57.         {  
  58.             i += c.replace("{0}", ">").replace("{1}", b.PageIndex + 1);  
  59.             i += c.replace("{0}", ">>").replace("{1}", h)  
  60.         }  
  61.     }  
  62.     a.html(i);  
  63.     try  
  64.     {  
  65.         a[0].disabled = false  
  66.     }  
  67.     catch (m)  
  68.     { }  
  69. }  
  70.   
  71. (function (a)  
  72. {  
  73.     a.fn.jQueryPagerFunc = function (b) {  
  74.         var c = {};  
  75.         var b = a.extend(c, b);  
  76.         return this.each(function () { jQueryPagerFunc(a(this), b) })  
  77.     }  
  78. })  
  79. (jQuery);  
Now run your application.

first record
Figure 3.

second record
Figure 4.

third record
Figure 5.

forth record
Figure 6.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs