In this article I will explain with an example, how to implement paging (pagination) in HTML table using
jQuery AJAX and JSON in ASP.Net with C# and VB.Net.
A dummy GridView will be rendered as HTML table and later this table will be used for populating records using
jQuery AJAX and JSON in ASP.Net.
The Paging in HTML table will be implemented using ASPSnippets_Pager
jQuery Plugin.
Download ASPSnippets_Pager Plugin
You can download ASPSnippets_Pager
jQuery Plugin using the link provided below.
Database
For this article I have used Microsoft’s Northwind database. You can download it using the link provided below.
HTML Markup
The following HTML Markup consists of an ASP.Net GridView control with three BoundField columns.
There's also an HTML DIV which will be used to populate the Pages for Pagination.
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="CustomerID" />
<asp:BoundField DataField="ContactName" HeaderText="Contact Name" />
<asp:BoundField DataField="City" HeaderText="City" />
</Columns>
</asp:GridView>
<br />
<div class="Pager"></div>
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Services
Populating GridView with Dummy data
Inside the Page Load event, the GridView is populated with dummy records.
The variable PageSize will be used to determine the number of records displayed per Page in GridView.
Note: The dummy DataTable being populated has the same columns which will be returned from the SQL Query.
C#
private static int PageSize = 10;
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
BindDummyRow();
}
}
private void BindDummyRow()
{
DataTable dummy = new DataTable();
dummy.Columns.Add("CustomerID");
dummy.Columns.Add("ContactName");
dummy.Columns.Add("City");
dummy.Rows.Add();
gvCustomers.DataSource = dummy;
gvCustomers.DataBind();
}
VB.Net
Private Shared PageSize As Integer = 10
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
BindDummyRow()
End If
End Sub
Private Sub BindDummyRow()
Dim dummy As New DataTable()
dummy.Columns.Add("CustomerID")
dummy.Columns.Add("ContactName")
dummy.Columns.Add("City")
dummy.Rows.Add()
gvCustomers.DataSource = dummy
gvCustomers.DataBind()
End Sub
Stored Procedure for Paging
The following Stored Procedure makes use of Row_Number function to fetch records Page Wise from SQL Server Database Table.
The PageIndex and PageSize values are passed as parameter and the Stored Procedure returns the Total records of the Table using the RecordCount Output parameter.
CREATE PROCEDURE [dbo].[GetCustomers_Pager]
@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
AS
BEGIN
SET NO COUNTON;
SELECT ROW_NUMBER()OVER
(
ORDER BY [CustomerID] ASC
) AS RowNumber
,[CustomerID]
,[CompanyName]
,[ContactName]
,[City]
INTO #Results
FROM [Customers]
SELECT @RecordCount = COUNT(*)
FROM #Results
SELECT * FROM #Results
WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 AND (((@PageIndex - 1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
Fetching data from Database for populating HTML Table
The following WebMethod handles call made from the
jQuery AJAX function.
Inside the WebMethod, the Paging Stored Procedure is called with the input parameters PageIndex and PageSize and results are populated into a DataSet along with the PageIndex, PageSize and RecordCount values.
The DataSet is converted into an XML string and sent to the
jQuery AJAX function.
Note: Total Record Count is necessary to populate pager.
C#
[WebMethod]
public static string GetCustomers(int pageIndex)
{
string query = "[GetCustomers_Pager]";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
return GetData(cmd, pageIndex).GetXml();
}
private static DataSet GetData(SqlCommand cmd, int pageIndex)
{
string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds, "Customers");
DataTable dt = new DataTable("Pager");
dt.Columns.Add("PageIndex");
dt.Columns.Add("PageSize");
dt.Columns.Add("RecordCount");
dt.Rows.Add();
dt.Rows[0]["PageIndex"] = pageIndex;
dt.Rows[0]["PageSize"] = PageSize;
dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;
ds.Tables.Add(dt);
return ds;
}
}
}
}
VB.Net
<WebMethod>
Public Shared Function GetCustomers(pageIndex As Integer) As String
Dim query As String = "[GetCustomers_Pager]"
Dim cmd As New SqlCommand(query)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
cmd.Parameters.AddWithValue("@PageSize", PageSize)
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
Return GetData(cmd, pageIndex).GetXml()
End Function
Private Shared Function GetData(cmd As SqlCommand, pageIndex As Integer) As DataSet
Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Using con As New SqlConnection(strConnString)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using ds As New DataSet()
sda.Fill(ds, "Customers")
Dim dt As New DataTable("Pager")
dt.Columns.Add("PageIndex")
dt.Columns.Add("PageSize")
dt.Columns.Add("RecordCount")
dt.Rows.Add()
dt.Rows(0)("PageIndex") = pageIndex
dt.Rows(0)("PageSize") = PageSize
dt.Rows(0)("RecordCount") = cmd.Parameters("@RecordCount").Value
ds.Tables.Add(dt)
Return ds
End Using
End Using
End Using
End Function
Populating HTML Table and adding Pagination using jQuery
Inside the
jQuery document ready event handler, first an
AJAX call is made to the WebMethod and the records for the First page is fetched.
The value of the
PageIndex,
PageSize and
RecordCount are used to populate the Pager inside the HTML DIV using the
ASPSnippets_Pager jQuery Plugin.
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(function () {
GetCustomers(1);
});
$(".Pager .page").live("click", function () {
GetCustomers(parseInt($(this).attr('page')));
});
function GetCustomers(pageIndex) {
$.ajax({
type: "POST",
url: "Default.aspx/GetCustomers",
data: '{pageIndex: ' + pageIndex + '}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.responseText);
},
error: function (response) {
alert(response.responseText);
}
});
}
function OnSuccess(response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
var customers = xml.find("Customers");
var row = $("[id*=gvCustomers] tr:last-child").clone(true);
$("[id*=gvCustomers] tr").not($("[id*=gvCustomers] tr:first-child")).remove();
$.each(customers, function () {
var customer = $(this);
$("td", row).eq(0).html($(this).find("CustomerID").text());
$("td", row).eq(1).html($(this).find("ContactName").text());
$("td", row).eq(2).html($(this).find("City").text());
$("[id*=gvCustomers]").append(row);
row = $("[id*=gvCustomers] tr:last-child").clone(true);
});
var pager = xml.find("Pager");
$(".Pager").ASPSnippets_Pager({
ActiveCssClass: "current",
PagerCssClass: "pager",
PageIndex: parseInt(pager.find("PageIndex").text()),
PageSize: parseInt(pager.find("PageSize").text()),
RecordCount: parseInt(pager.find("RecordCount").text())
});
};
</script>
CSS
The following CSS classes will needed to be inherited on the web page.
<style type="text/css">
body
{
font-family: Arial;
font-size: 10pt;
}
.Pager span
{
text-align: center;
color: #999;
display: inline-block;
width: 20px;
background-color: #A1DCF2;
margin-right: 3px;
line-height: 150%;
border: 1px solid #3AC0F2;
}
.Pager a
{
text-align: center;
display: inline-block;
width: 20px;
background-color: #3AC0F2;
color: #fff;
border: 1px solid #3AC0F2;
margin-right: 3px;
line-height: 150%;
text-decoration: none;
}
</style>
Screenshot
Demo
Downloads