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>