Introduction:-
In this article , I
have described how to laod data on demand in Gridview in asp.net using Jquery
with Ajax. For this I have used Jquery with Ajax and Stored procedure.It better
to go through by practicle approach.
First Make a table in
DataBase and stored procedure for this table.
Table
Structure:-
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[Customers](
[CustomerID]
[int] NULL,
[CompanyName]
[varchar](max) NULL,
[ContactName]
[varchar](max) NULL,
[City]
[varchar](max) NULL,
[Country]
[varchar](max) NULL,
[PostalCode]
[int] NULL,
[Phone]
[int] NULL,
[Fax]
[int] NULL
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
GO
Stored Procedure:-
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
PROCEDURE [dbo].[GetCustomersPageWise]
@PageIndex
INT = 1
,@PageSize INT = 10
,@PageCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER
BY [CustomerID]
ASC
)AS RowNumber
,[CustomerID]
,[CompanyName]
,[ContactName]
,[City]
,[Country]
,[PostalCode]
,[Phone]
,[Fax]
INTO #Results
FROM [Customers]
DECLARE @RecordCount INT
SELECT @RecordCount = COUNT(*) FROM #Results
SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
PRINT @PageCount
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
Now
open your visual studio and create one project -> Open Design Page ->
drag and drop one gridview control to design page.
Design the page like this:-
<!DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1"
runat="server">
<title></title>
<style type="text/css">
.Grid
td
{
background-color:
#A1DCF2;
color:
black;
font-size:
10pt;
font-family:
Arial;
line-height:
200%;
cursor:
pointer;
width:
100px;
}
.Grid
th
{
background-color:
#3AC0F2;
color:
White;
font-family:
Arial;
font-size:
10pt;
line-height:
200%;
width:
100px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<table class="Grid" cellspacing="0" rules="all" border="1" id="Table1" style="width:500px;border-collapse:collapse;">
<tr>
<th scope="col" style="width:200px;">Customer Name</th><th scope="col" style="width:100px;">City</th><th scope="col" style="width:100px;">Country</th><th scope="col" style="width:100px;">Postal
Code</th>
</tr>
</table>
<div id="dvGrid"
style="height: 250px; overflow: auto; width: 517px">
<asp:GridView ID="gvCustomers"
runat="server"
AutoGenerateColumns="false"
CssClass="Grid"
Width = "500">
<Columns>
<asp:BoundField DataField="ContactName" HeaderText
= "Customer
Name" ItemStyle-CssClass="name" ItemStyle-Width="200" HeaderStyle-Width="200" />
<asp:BoundField DataField="City" HeaderText
= "City"
ItemStyle-CssClass="city"
ItemStyle-Width="100"
HeaderStyle-Width="100"
/>
<asp:BoundField DataField="Country" HeaderText
= "Country"
ItemStyle-CssClass="country"
ItemStyle-Width="100"
HeaderStyle-Width="100"
/>
<asp:BoundField DataField="PostalCode" HeaderText
= "Postal
Code" ItemStyle-CssClass="postal" ItemStyle-Width="100" HeaderStyle-Width="100" />
</Columns>
</asp:GridView>
</div>
<script type="text/javascript"
src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
var
pageIndex = 1;
var
pageCount;
$(function
() {
//Remove
the original GridView header
$("[id$=gvCustomers]
tr").eq(0).remove();
});
//Load
GridView Rows when DIV is scrolled
$("#dvGrid").on("scroll", function
(e) {
var
$o = $(e.currentTarget);
if
($o[0].scrollHeight - $o.scrollTop() <= $o.outerHeight()) {
GetRecords();
}
});
//Function
to make AJAX call to the Web Method
function
GetRecords() {
pageIndex++;
if
(pageIndex == 2 || pageIndex <= pageCount) {
//Show
Loader
if
($("[id$=gvCustomers] .loader").length
== 0) {
var row = $("[id$=gvCustomers]
tr").eq(0).clone(true);
row.addClass("loader");
row.children().remove();
row.append('<td colspan = "999" style =
"background-color:white"><img id="loader"
alt="" src="103.gif"
/></td>');
$("[id$=gvCustomers]").append(row);
}
$.ajax({
type: "POST",
url: "ScrollGridView.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
to recieve XML response append rows to GridView
function
OnSuccess(response) {
var xmlDoc
= $.parseXML(response.d);
var
xml = $(xmlDoc);
pageCount =
parseInt(xml.find("PageCount").eq(0).find("PageCount").text());
var
customers = xml.find("Customers");
$("[id$=gvCustomers]
.loader").remove();
customers.each(function
() {
var
customer = $(this);
var
row = $("[id$=gvCustomers] tr").eq(0).clone(true);
$(".name", row).html(customer.find("ContactName").text());
$(".city", row).html(customer.find("City").text());
$(".postal", row).html(customer.find("PostalCode").text());
$(".country", row).html(customer.find("Country").text());
$("[id$=gvCustomers]").append(row);
});
//Hide
Loader
$("#loader").hide();
}
</script>
</form>
</body>
</html>
Code behind Page:-
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using
System.Web.UI.WebControls;
using System.Data;
using System.Web.Services;
using System.Configuration;
using
System.Data.SqlClient;
using System.Collections;
using System.Web.Security;
using
System.Web.UI.HtmlControls;
using
System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class ScrollGridView : System.Web.UI.Page
{
protected
void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
gvCustomers.DataSource =
GetCustomersPageWise(1, 10);
gvCustomers.DataBind();
}
}
public
static DataSet
GetCustomersPageWise(int pageIndex, int pageSize)
{
string
constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using
(SqlConnection con = new SqlConnection(constring))
{
using
(SqlCommand cmd = new
SqlCommand("GetCustomersPageWise"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PageIndex",
pageIndex);
cmd.Parameters.AddWithValue("@PageSize",
pageSize);
cmd.Parameters.Add("@PageCount", SqlDbType.Int,
4).Direction = ParameterDirection.Output;
using (SqlDataAdapter
sda = new SqlDataAdapter())
{
cmd.Connection =
con;
sda.SelectCommand = cmd;
using (DataSet ds
= new DataSet())
{
sda.Fill(ds,
"Customers");
DataTable dt = new
DataTable("PageCount");
dt.Columns.Add("PageCount");
dt.Rows.Add();
dt.Rows[0][0] = cmd.Parameters["@PageCount"].Value;
ds.Tables.Add(dt);
return ds;
}
}
}
}
}
[WebMethod]
public
static string
GetCustomers(int pageIndex)
{
//Added
to similate delay so that we see the loader working
//Must
be removed when moving to production
System.Threading.Thread.Sleep(2000);
return
GetCustomersPageWise(pageIndex, 10).GetXml();
}
}
Run
the code and Check the Output.
No comments:
Post a Comment
Note: only a member of this blog may post a comment.