Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Load data on page scroll using jquery

Suggested Videos
Part 67 - Calling aspx page method using jquery
Part 68 - How to call wcf service using jquery
Part 69 - Difference between window height and document height



In this video we will discuss, how to load more data on page scroll using jQuery AJAX



This is similar to Facebook. As you scroll down on the page more data will be loaded.

When the page is initially loaded we want to retrieve and display the first 50 rows from the following database table tblEmployee. As we scroll down and when we hit the bottom of the page we want to load the next set of 50 rows.



Step 1 : Create SQL Server table and insert employee data

Create table tblEmployee
(
    Id int primary key identity,
    Name nvarchar(50),
    Gender nvarchar(10),
    Salary int
)
GO

Insert into tblEmployee values('Name 1','Male 1',100)
Insert into tblEmployee values('Name 2','Male 2',200)
Insert into tblEmployee values('Name 3','Male 3',300)
Insert into tblEmployee values('Name 4','Male 4',400)
Insert into tblEmployee values('Name 5','Male 5',500)

Step 2 : Create a stored procedure to retrieve employee data. This procedure retrieves the correct set of 50 records depending on the page number.

Create procedure spGetEmployees
@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 Id, Name, Gender, Salary,
             ROW_NUMBER() OVER (ORDER BY ID ASC) AS ROWNUMBER
     From    tblEmployee
    )
    SELECT *
    FROM RESULT
    WHERE ROWNUMBER BETWEEN @StartRow AND @EndRow
End

Step 3 : Create new asp.net web application project. Name it Demo. 

Step 4 : Include a connection string in the web.config file to your database.
<add name="DBCS"
      connectionString="server=.;database=SampleDB;integrated security=SSPI"/>

Step 5 : Add a class file to the project. Name it Employee.cs. Copy and paste the following code.

namespace Demo
{
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public int Salary { get; set; }
    }
}

Step 6 : Add a new WebService (ASMX). Name it EmployeeService.asmx. Copy and paste the following code.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Script.Services;
using System.Web.Services;

namespace Demo
{
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    [System.Web.Script.Services.ScriptService]
    public class EmployeeService : System.Web.Services.WebService
    {
        [WebMethod]
        public void GetEmployees(int pageNumber, int pageSize)
        {
            List<Employee> listEmployees = new List<Employee>();

            string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand("spGetEmployees", con);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter()
                {
                    ParameterName = "@PageNumber",
                    Value = pageNumber
                });

                cmd.Parameters.Add(new SqlParameter()
                {
                    ParameterName = "@PageSize",
                    Value = pageSize
                });

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.ID = Convert.ToInt32(rdr["Id"]);
                    employee.Name = rdr["Name"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.Salary = Convert.ToInt32(rdr["Salary"]);
                    listEmployees.Add(employee);
                }
            }

            JavaScriptSerializer js = new JavaScriptSerializer();
            Context.Response.Write(js.Serialize(listEmployees));
        }
    }
}

Step 7 : Add an HTML page to the ASP.NET project. Copy and paste the following HTML and jQuery code

<!DOCTYPE html>
<html>
<head>
    <script src="jquery-1.11.2.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            var currentPage = 1;
            loadPageData(currentPage);
            $(window).scroll(function () {
                if ($(window).scrollTop() == $(document).height() - $(window).height()) {
                    currentPage += 1;
                    loadPageData(currentPage);
                }
            });

            function loadPageData(currentPageNumber) {
                $.ajax({
                    url: 'EmployeeService.asmx/GetEmployees',
                    method: 'post',
                    dataType: "json",
                    data: { pageNumber: currentPageNumber, pageSize: 50 },
                    success: function (data) {
                        var employeeTable = $('#tblEmployee tbody');

                        $(data).each(function (index, emp) {
                            employeeTable.append('<tr><td>' + emp.ID + '</td><td>'
                                + emp.Name + '</td><td>' + emp.Gender
                                + '</td><td>' + emp.Salary + '</td></tr>');
                        });
                    },
                    error: function (err) {
                        alert(err);
                    }
                });
            }
        });
    </script>
</head>
<body style="font-family:Arial">
    <h1>The data will be loaded on demand as you scroll down the page</h1>
    <table id="tblEmployee" border="1"
           style="border-collapse:collapse; font-size:xx-large">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Gender</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody></tbody>
    </table>
</body>
</html>

jQuery tutorial for beginners

4 comments:

  1. excellent video.its really helpful

    ReplyDelete
  2. but could you tell me , what if i have images in database... without images this code is not usable for me ,so pls mention what if i have images

    ReplyDelete
  3. but could you tell me , what if i have images in database... without images this code is not usable for me ,so pls mention what if i have images
    also i am using handler

    ReplyDelete
  4. hello pls help me how to write code with out webservice using aspx pages pls solve my issure sir

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.