Post
Ask Question

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.


Similar Articles