In this article I will explain how to get (retrieve) data from database and display it on page using jQuery AJAX Web Method in ASP.Net using C# and VB.Net.
In order to display data I am making use of GridView control.
Database
For this article I have used Microsoft’s Northwind database. You can download it using the link provided below.
HTML Markup
Below is the HTML Markup. It has an ASP.Net GridView gvCustomers and an HTML DIV control where the Pager will be populated for pagination
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" RowStyle-BackColor="#A1DCF2"
HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White">
<Columns>
<asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="CustomerID" />
<asp:BoundField ItemStyle-Width="150px" DataField="ContactName" HeaderText="Contact Name" />
<asp:BoundField ItemStyle-Width="150px" 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.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Web.Services
Imports System.Configuration
Imports System.Data.SqlClient
Populating GridView Dummy with Dummy Data
In Page Load event of the page I am populating the GridView with dummy data so that we can use its HTML Table structure to populate data using jQuery.
I have created a variable PageSize which will decide the number of records to be 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 (!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 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
Pagination SQL Server Stored Procedure
Following is the stored procedure which will perform pagination in SQL Server database and return per page data
CREATE PROCEDURE [dbo].[GetCustomers_Pager]
@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
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
WebMethod to handle jQuery AJAX calls
Below is the Web Method which is being called by the jQuery AJAX function which populates the GridView with data and also does Pagination.
The PageIndex is passed as parameter from Client side based on what is Page is clicked by the user, while the PageSize we get from the static variable we have declared. Based on these parameters we can fetch the records Page wise.
The records fetched along with the Total Record Count are sent to Client Side as XML string.
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
Client Side Implementation
Below is the complete Client Side Implementation, here on load event of jQuery the GridView is populated with PageIndex 1 using the method GetCustomers which populates the ASP.Net GridView which was initially populated with dummy data.
Using the Record Count, the pager is populated inside the HTML DIV using the ASPSnippets_Pager jQuery Plugin.
<script type="text/javascript" src="http://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.d);
},
error: function (response) {
alert(response.d);
}
});
}
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
Below is the necessary CSS styles which you need to put on your 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
Below is the screenshot of ASP.Net GridView with Client Side Paging using jQuery AJAX
Demo
Downloads
