.jpg)
How to load Data on page scroll
using JQuery
Note: In this article we will
work on this concept when the page is initially loaded we
want to retrieve and display the first
30 rows following database table Customers .As we scroll down and we hit the bottom of the page we want to load next set
of 30 records from table.
-- **********************
Sql Query ****************************------
create database Northwind
use
Northwind
CREATE TABLE [dbo].[Customers](
[CustomerID]
[nchar](5) NOT NULL,
[CompanyName]
[nvarchar](40) NOT NULL,
[ContactName]
[nvarchar](30) NULL,
[ContactTitle]
[nvarchar](30) NULL,
[Address]
[nvarchar](60) NULL,
[City]
[nvarchar](15) NULL,
[Region]
[nvarchar](15) NULL,
[PostalCode]
[nvarchar](10) NULL,
[Country]
[nvarchar](15) NULL,
[Phone]
[nvarchar](24) NULL,
[Fax]
[nvarchar](24) NULL,
CONSTRAINT [PK_Customers] PRIMARY
KEY CLUSTERED
(
[CustomerID]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- select data
only 4 column from customers table
select CustomerId,ContactName,City,PostalCode,Country from Customers
-- Create
stored procedures...................
create proc spShow_Customer
@PageNumber int,
@PageSize int
as
begin
declare @StartRow int
declare @EndRow int
set
@StartRow=((@PageNumber-1)*@PageSize)+1;
set
@EndRow=@PageNumber*@PageSize;
with Result as
(
--select * ,ROW_NUMBER() over(order by Country
asc) RowNumber from Customers
select CustomerId,ContactName,City,PostalCode,Country,ROW_NUMBER() over(order by Country asc) RowNumber from Customers
)
select * from
Result where RowNumber between
@StartRow and @EndRow
end
-- execute
stored procedures
exec spShow_Customer 3,20
-- **********************
end query ****************************------
Now we are working at front-end part using
HTML page, Linq ,web services , jquery in this website
First we will take a Website that’s name
is PageScroll and take Jquery file that’s name is jquery-3.1.0.js and
you can download it from here Jquery Download and DataClasses.dbml
for connectivity like given image….
Then we will add WebServices.asmx and call stored procedures that’s name
is spShow_Customer
Code for WebServices.asmx………………….
using System;
using
System.Collections.Generic;
using System.Data.Linq;
using System.Linq;
using System.Web;
using
System.Web.Script.Serialization;
using
System.Web.Services;
/// <summary>
/// Summary description
for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service
to be called from script, using ASP.NET AJAX, uncomment the following line.
//
[System.Web.Script.Services.ScriptService]
public class WebService :
System.Web.Services.WebService
{
public WebService () {
//Uncomment the following
line if using designed components
//InitializeComponent();
}
[WebMethod]
public string HelloWorld()
{
return "Hello
World";
}
DataClassesDataContext database = new DataClassesDataContext();
[WebMethod]
public void ShowAllCustomer(int pageno, int pagesize)
{
var v =
database.spShow_Customer(pageNumber:pageno,pageSize:pagesize);
JavaScriptSerializer jss = new JavaScriptSerializer();
Context.Response.Write(jss.Serialize(v.ToList()));
}
}
Then
after we will check above services whether it‘s working or not???
Now we
have to add an html page
named as ShowCustomer.html and the jquery code is described here
Code for ShowCustomer.html……………………..
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<script src="jquery-3.1.0.min.js"></script>
<script type="text/javascript">
$(document).ready(function ()
{
//showdata();
var currentPageNo = 1;
showdata(currentPageNo);
$(window).scroll(function ()
{
//alert('hi');
if
($(window).scrollTop() == $(document).height() - $(window).height())
{
currentPageNo += 1;
showdata(currentPageNo);
}
});
function
showdata(currentPageNo)
{
$.ajax({
url: 'WebService.asmx/ShowAllCustomer',
method:'post',
data: { pageno:
currentPageNo,pagesize: 30 },
dataType: 'json',
success: function (data)
{
var list = null;
$.each(data, function (i, d)
{
list += "<tr><td>" + d.RowNumber + "</td><td>" + d.CustomerId + "</td><td>" + d.ContactName + "</td><td>" + d.City + "</td><td>" + d.PostalCode + "</td><td>" + d.Country + "</td></tr>";
});
$('#table1').append(list);
},
error: function (response)
{
alert('eror');
}
});
}
});
</script>
</head>
<body>
<form id="f1" >
<table id="table1" border="1" width="700px">
<tr><th>RowNumber</th><th>CustomerId</th><th>ContactName</th><th>City</th><th>PostalCode</th><th>Country</th></tr>
</table>
</form>
</body>
</html>
Result: Here we find that it will first
show 30 records of table until it touch the bottom(scroll), As it will
touch the bottom it will show next 30
records of tab
