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]
  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]
  14. SELECT @RecordCount = COUNT(*) FROM #Results
  16. SELECT * FROM #Results
  17. WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
  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" %>
  3. <!DOCTYPE html>
  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. }
  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. }
  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. }
  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;
  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. }
  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. }
  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. }
  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. }
  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.