How to load Data on page scroll
using JQuery,EntityFramwork and Json in Asp.NetMVC
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 tiwari
use
tiwari
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 ****************************------
First we will take a Project that’s name is MvcApplication4 and take Jquery
file that’s name is jquery-3.1.0.js and
you can download it from here Jquery Download and take ADO.NET Entity
Data Model that’s name is Model1.edmx
for connectivity like given image….
Then we will add Controller that’s name
is HomeController and call stored
procedures that’s
name is spShow_Customer
Code for HomeController.cs………………….
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using
MvcApplication4.Models;
namespace
MvcApplication4.Controllers
{
public class HomeController : Controller
{
//
// GET: /Home/
tiwariEntities database = new tiwariEntities();
public ActionResult Index()
{
return View();
}
[HttpGet]
public JsonResult
ShowAllCustomer(int pageno, int pagesize)
{
var v = database.spShow_Customer(pageNumber:pageno,
pageSize: 30);
return Json(v, JsonRequestBehavior.AllowGet);
}
}
}
Now we
have to add an View which named as Index.cshtml
and the jquery code is described here
Code for
Index.cshtml……………………..
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<title></title>
<script src="~/Scripts/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)
{
$.get("Home/ShowAllCustomer", { pageno:
currentPageNo, pagesize: 30 }, 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);
});
}
});
</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
0 comments:
Post a Comment