In this article, I am going to explain your how you can bind data to GridView using jQuery Ajax in ASP.NET step by step by fetching data from WebMethod and SQL server stored procedure with paging.
Before we proceed I am going to implement Paging in SQL Server stored procedure using AdventureWorks2012 demo database for this article, you can download AdventureWorks2012.bak file of database from https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks and restore .bak file in SQL server.
Step 1: Create a New project in your Visual Studio, by navigating to File -> New Project -> Web(Left Hand menu)/Asp.NET Web application (Right hand menu)
Provide a name to your project and Click "OK"
Now, select "Web Forms" template for this project & click "OK"
Step 2: Let's create the stored procedure in the database which fetched BusinessEntityID, Full Name and PersonType from table Person.Person of AdventureWorks2012 database
USE AdventureWorks2012
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[GridViewjQueryAjax]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GridViewjQueryAjax]
GO
CREATE PROCEDURE GridViewjQueryAjax
(
@PageNo INT,
@RowCountPerPage INT
)
AS
SELECT
BusinessEntityID
,PersonType
,FirstName + ' ' + MiddleName + ' ' + LastName as FullName
FROM Person.Person
ORDER BY BusinessEntityID
OFFSET (@PageNo - 1) * @RowCountPerPage ROWS
FETCH NEXT @RowCountPerPage ROWS ONLY
SELECT Count(*) As 'Total' From Person.Person
GOAbove scripts create a stored procedure with name "GridViewjQueryAjax" and take two argument while executing it "PageNo & RowCountPerPage", it returns table data with Total number of row count from Person.Person Table as "Total".
If you try execute the above procedure in your SQL server management studio with PageNo=1 & RowCountPerPage=10, output will be as below
Step 3: Now, in your Default.aspx, let's write GridView code, with a button to bind data on click and image loader.
<asp:GridView ID="GridDemo" runat="server" AutoGenerateColumns="False" Font-Names="Arial"
Font-Size="10pt" HeaderStyle-BackColor="GrayText" HeaderStyle-ForeColor="White" Width="500px">
<Columns>
<asp:BoundField DataField="BusinessEntityID" HeaderText="Business Entity ID" />
<asp:BoundField DataField="PersonType" HeaderText="Person Type"/>
<asp:BoundField DataField="FullName" HeaderText="Full Name"/>
</Columns>
</asp:GridView>
<div id="pagingDiv"></div>
<img src="Content/loader.gif" id="Loader" />
<input type="button" value="Bind " id="submitButton" />Note: Do not forget to comment line
//settings.AutoRedirectMode = RedirectMode.Permanent;place in your RouteConfig.cs otherwise you may get authentication error while loading data in gridview the example.
In the above GridView code, I am binding the GridView to only 3 columns created using Stored Procedure, the pagingDiv is the HTML div control where the paging links for this GridView will be created.
The Content/loader.gif is a loading gif image that will show up whenever the AJAX call is under process & we have also added button there which is for binding the GridView with jQuery AJAX. on clicking this button with "submitButton" id.
Step 4: Create C# code in Default.aspx.cs, to load dummy data on page load.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindDummyRow();
}
}
private void BindDummyRow()
{
DataTable dummy = new DataTable();
dummy.Columns.Add("BusinessEntityID");
dummy.Columns.Add("PersonType");
dummy.Columns.Add("FullName");
dummy.Rows.Add();
GridDemo.DataSource = dummy;
GridDemo.DataBind();
}now, we have dummy data for page load, let's create the WebMethod for fetching data from stored procedure and return data as XML
[WebMethod]
public static string GetData(string pageNo)
{
SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
DataSet dataSet = new DataSet();
conn.ConnectionString = @"Data Source=LocalServer;Initial Catalog=AdventureWorks2012;User ID=userID;Password=password";
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ExampleUsageOfSQLServerPagingFeature";
cmd.Parameters.AddWithValue("@PageNo", pageNo);
cmd.Parameters.AddWithValue("@RowCountPerPage", 10);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = cmd;
sqlDataAdapter.Fill(dataSet);
return dataSet.GetXml();
}The [WebMethod] GetData() function will fetch the records from the Persons table using the stored procedure created above and fill the records into the DataSet then return these records in XML format. As we are calling GetData() function using jQuery Ajax, so there will be no post back.
Step 5: This is one of the important step of this article, as all important work will be done via jQuery Ajax, let's add image loader, show/hide method's
$("#Loader").hide();
$(document).ajaxStart(function () {
$("#Loader").show();
});
$(document).ajaxStop(function () {
$("#Loader").hide();
}); Now, create the function to call WebMethod on Submit button click
$("#submitButton").click(function (e) {
CallServerSideFunction(1);
return false;
});
function CallServerSideFunction(pageNo) {
$.ajax({
type: "POST",
url: "Default.aspx/GetData",
data: "{pageNo:" + pageNo + "}",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (result, status, xhr) {
console.log(result);
OnSuccess(result.d, pageNo);
},
error: function (req, status, error) {
alert(req + " " + status + " " + error);
}
});
}
function OnSuccess(message, pageNo) {
var xmlDoc = $.parseXML(message);
var xml = $(xmlDoc);
var product = xml.find("Table");
var row = $("[id*=GridDemo] tr:last-child").clone(true);
$("[id*=GridDemo] tr").not($("[id*=GridDemo] tr:first-child")).remove();
$.each(product, function () {
var customer = $(this);
$("td", row).eq(0).html($(this).find("BusinessEntityID").text());
$("td", row).eq(1).html($(this).find("PersonType").text());
$("td", row).eq(2).html($(this).find("FullName").text());
$("[id*=GridDemo]").append(row);
row = $("[id*=GridDemo] tr:last-child").clone(true);
});
var result = Paging(pageNo, 10, xml.find("Table1 > Total").text(), "myClass", "myDisableClass");
$("#pagingDiv").html(result)
} CallServerSideFunction() does the jQuery AJAX work & this AJAX call will call the C# function GetData(). It passes the page number as a parameter.
For paging, we will again call CallServerSideFunction() with page number
$("#pagingDiv").on("click", "a", function () {
CallServerSideFunction($(this).attr("pn"));
});We also need to create custom paging function to handle paging, so here is the jQuery code for it.
function Paging(PageNumber, PageSize, TotalRecords, ClassName, DisableClassName) {
var ReturnValue = "";
var TotalPages = Math.ceil(TotalRecords / PageSize);
if (+PageNumber > 1) {
if (+PageNumber == 2)
ReturnValue = ReturnValue + "<a pn='" + (+PageNumber - 1) + "' class='" + ClassName + "'>Previous</a> ";
else {
ReturnValue = ReturnValue + "<a pn='";
ReturnValue = ReturnValue + (+PageNumber - 1) + "' class='" + ClassName + "'>Previous</a> ";
}
}
else
ReturnValue = ReturnValue + "<span class='" + DisableClassName + "'>Previous</span> ";
if ((+PageNumber - 3) > 1)
ReturnValue = ReturnValue + "<a pn='1' class='" + ClassName + "'>1</a> ..... | ";
for (var i = +PageNumber - 3; i <= +PageNumber; i++)
if (i >= 1) {
if (+PageNumber != i) {
ReturnValue = ReturnValue + "<a pn='";
ReturnValue = ReturnValue + i + "' class='" + ClassName + "'>" + i + "</a> | ";
}
else {
ReturnValue = ReturnValue + "<span style='font-weight:bold;'>" + i + "</span> | ";
}
}
for (var i = +PageNumber + 1; i <= +PageNumber + 3; i++)
if (i <= TotalPages) {
if (+PageNumber != i) {
ReturnValue = ReturnValue + "<a pn='";
ReturnValue = ReturnValue + i + "' class='" + ClassName + "'>" + i + "</a> | ";
}
else {
ReturnValue = ReturnValue + "<span style='font-weight:bold;'>" + i + "</span> | ";
}
}
if ((+PageNumber + 3) < TotalPages) {
ReturnValue = ReturnValue + "..... <a pn='";
ReturnValue = ReturnValue + TotalPages + "' class='" + ClassName + "'>" + TotalPages + "</a>";
}
if (+PageNumber < TotalPages) {
ReturnValue = ReturnValue + " <a pn='";
ReturnValue = ReturnValue + (+PageNumber + 1) + "' class='" + ClassName + "'>Next</a>";
}
else
ReturnValue = ReturnValue + " <span class='" + DisableClassName + "'>Next</span>";
return (ReturnValue);
}Now, we just need to add the CSS, to style the paging buttons and disable inactive buttons etc
<style>
#content {
position: relative;
border: dashed 2px #CCC;
}
#content #loadingImg {
display: none;
position: absolute;
margin: auto;
top: 0;
left: 0;
right: 0;
bottom: 0;
}
#content #gridView {
background-color: purple;
}
#content #gridView tr th {
background-color: #CCC;
}
#content #submitButton {
margin: 10px;
}
#pagingDiv {
padding: 15px 0;
}
#pagingDiv .myDisableClass {
background-color: #4CAF50;
}
#pagingDiv a, #pagingDiv span {
display: inline-block;
padding: 0px 9px;
margin-right: 4px;
border-radius: 3px;
border: solid 1px #c0c0c0;
background: #e9e9e9;
box-shadow: inset 0px 1px 0px rgba(255,255,255, .8), 0px 1px 3px rgba(0,0,0, .1);
font-size: .875em;
font-weight: bold;
text-decoration: none;
color: #717171;
text-shadow: 0px 1px 0px rgba(255,255,255, 1);
}
#pagingDiv a:hover {
cursor: pointer;
background: #fefefe;
background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#FEFEFE), to(#f0f0f0));
background: -moz-linear-gradient(0% 0% 270deg,#FEFEFE, #f0f0f0);
}
#pagingDiv a.active {
border: none;
background: #616161;
box-shadow: inset 0px 0px 8px rgba(0,0,0, .5), 0px 1px 0px rgba(255,255,255, .8);
color: #f0f0f0;
text-shadow: 0px 0px 3px rgba(0,0,0, .5);
}
#pagingDiv span {
color: #f0f0f0;
background: #616161;
}
</style>So my Complete Defaut.aspx page code will be as below
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="GridViewUsingjQuery._Default" %>
<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">
<style>
#content {
position: relative;
border: dashed 2px #CCC;
}
#content #loadingImg {
display: none;
position: absolute;
margin: auto;
top: 0;
left: 0;
right: 0;
bottom: 0;
}
#content #gridView {
background-color: purple;
}
#content #gridView tr th {
background-color: #CCC;
}
#content #submitButton {
margin: 10px;
}
#pagingDiv {
padding: 15px 0;
}
#pagingDiv .myDisableClass {
background-color: #4CAF50;
}
#pagingDiv a, #pagingDiv span {
display: inline-block;
padding: 0px 9px;
margin-right: 4px;
border-radius: 3px;
border: solid 1px #c0c0c0;
background: #e9e9e9;
box-shadow: inset 0px 1px 0px rgba(255,255,255, .8), 0px 1px 3px rgba(0,0,0, .1);
font-size: .875em;
font-weight: bold;
text-decoration: none;
color: #717171;
text-shadow: 0px 1px 0px rgba(255,255,255, 1);
}
#pagingDiv a:hover {
cursor: pointer;
background: #fefefe;
background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#FEFEFE), to(#f0f0f0));
background: -moz-linear-gradient(0% 0% 270deg,#FEFEFE, #f0f0f0);
}
#pagingDiv a.active {
border: none;
background: #616161;
box-shadow: inset 0px 0px 8px rgba(0,0,0, .5), 0px 1px 0px rgba(255,255,255, .8);
color: #f0f0f0;
text-shadow: 0px 0px 3px rgba(0,0,0, .5);
}
#pagingDiv span {
color: #f0f0f0;
background: #616161;
}
</style>
<br/>
<asp:GridView ID="GridDemo" runat="server" AutoGenerateColumns="False" Font-Names="Arial"
Font-Size="10pt" HeaderStyle-BackColor="GrayText" HeaderStyle-ForeColor="White" Width="500px">
<Columns>
<asp:BoundField DataField="BusinessEntityID" HeaderText="Business Entity ID" />
<asp:BoundField DataField="PersonType" HeaderText="Person Type"/>
<asp:BoundField DataField="FullName" HeaderText="Full Name"/>
</Columns>
</asp:GridView>
<div id="pagingDiv"></div>
<img src="Content/loader.gif" id="Loader" />
<input type="button" value="Bind " id="submitButton" />
<script>
$(document).ready(function () {
$("#Loader").hide();
$(document).ajaxStart(function () {
$("#Loader").show();
});
$(document).ajaxStop(function () {
$("#Loader").hide();
});
$("#submitButton").click(function (e) {
CallServerSideFunction(1);
return false;
});
function CallServerSideFunction(pageNo) {
$.ajax({
type: "POST",
url: "Default.aspx/GetData",
data: "{pageNo:" + pageNo + "}",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (result, status, xhr) {
console.log(result);
OnSuccess(result.d, pageNo);
},
error: function (req, status, error) {
alert(req + " " + status + " " + error);
}
});
}
$("#pagingDiv").on("click", "a", function () {
CallServerSideFunction($(this).attr("pn"));
});
function OnSuccess(message, pageNo) {
var xmlDoc = $.parseXML(message);
var xml = $(xmlDoc);
var product = xml.find("Table");
var row = $("[id*=GridDemo] tr:last-child").clone(true);
$("[id*=GridDemo] tr").not($("[id*=GridDemo] tr:first-child")).remove();
$.each(product, function () {
var customer = $(this);
$("td", row).eq(0).html($(this).find("BusinessEntityID").text());
$("td", row).eq(1).html($(this).find("PersonType").text());
$("td", row).eq(2).html($(this).find("FullName").text());
$("[id*=GridDemo]").append(row);
row = $("[id*=GridDemo] tr:last-child").clone(true);
});
var result = Paging(pageNo, 10, xml.find("Table1 > Total").text(), "myClass", "myDisableClass");
$("#pagingDiv").html(result)
}
});
function Paging(PageNumber, PageSize, TotalRecords, ClassName, DisableClassName) {
var ReturnValue = "";
var TotalPages = Math.ceil(TotalRecords / PageSize);
if (+PageNumber > 1) {
if (+PageNumber == 2)
ReturnValue = ReturnValue + "<a pn='" + (+PageNumber - 1) + "' class='" + ClassName + "'>Previous</a> ";
else {
ReturnValue = ReturnValue + "<a pn='";
ReturnValue = ReturnValue + (+PageNumber - 1) + "' class='" + ClassName + "'>Previous</a> ";
}
}
else
ReturnValue = ReturnValue + "<span class='" + DisableClassName + "'>Previous</span> ";
if ((+PageNumber - 3) > 1)
ReturnValue = ReturnValue + "<a pn='1' class='" + ClassName + "'>1</a> ..... | ";
for (var i = +PageNumber - 3; i <= +PageNumber; i++)
if (i >= 1) {
if (+PageNumber != i) {
ReturnValue = ReturnValue + "<a pn='";
ReturnValue = ReturnValue + i + "' class='" + ClassName + "'>" + i + "</a> | ";
}
else {
ReturnValue = ReturnValue + "<span style='font-weight:bold;'>" + i + "</span> | ";
}
}
for (var i = +PageNumber + 1; i <= +PageNumber + 3; i++)
if (i <= TotalPages) {
if (+PageNumber != i) {
ReturnValue = ReturnValue + "<a pn='";
ReturnValue = ReturnValue + i + "' class='" + ClassName + "'>" + i + "</a> | ";
}
else {
ReturnValue = ReturnValue + "<span style='font-weight:bold;'>" + i + "</span> | ";
}
}
if ((+PageNumber + 3) < TotalPages) {
ReturnValue = ReturnValue + "..... <a pn='";
ReturnValue = ReturnValue + TotalPages + "' class='" + ClassName + "'>" + TotalPages + "</a>";
}
if (+PageNumber < TotalPages) {
ReturnValue = ReturnValue + " <a pn='";
ReturnValue = ReturnValue + (+PageNumber + 1) + "' class='" + ClassName + "'>Next</a>";
}
else
ReturnValue = ReturnValue + " <span class='" + DisableClassName + "'>Next</span>";
return (ReturnValue);
}
</script>
</asp:Content>
That's it, we are done now, build your project and execute it in browser, you will see output as below
After clicking Bind button your output will be as below
Here is the Gif image of the demo.
That's it, we are done, you can download source code, but it doesn't include SQL database you can download it from the link provided at the beginning of the article, and stored procedure script is given in step 2, feel free to ask any questions related to this in questions section or comment below.
Buy us a coffee
Become a Patron