Unlimited Scroll in ASP.Net DataList Control

Unlimited scroll is not a big deal, for example facebook news feed its scroll when user reach bottom of the page. Actually client side script check the scroll position the container, if the position is in bottom, the scripts request content form server and update the container. In here the container is a DIV tag, named holder, and put some style tag height, width and overflow.holder is hold the DataList

CSS for the holder.

Here I used Northwind database Products Table to demonstrate the unlimited scroll in this  article and used a Handler page, First time page loads with 10 records in DataList, take a look below.

        if (!IsPostBack)
        {
            DataClass data = new DataClass();
            DataList1.DataSource = data.FirstTenRecords();
            DataList1.DataBind();
        }

And in client side I set current item count to 10 and next item count to 0.

var current=10;
var next=0;

and call function for load next form javascript, it’s nothing but calling server via AJAX, ie requesting Handler page with a query string of start and next. below  image shows how the request url form client, I used firebug to show requests

lets look on loadNext

 var loadNext = function () {
            next = current + 10;
            $.ajax({
                url: "Handler.ashx?start=" + current + "&next=" + next,
                success: function (data) {
                    $("#DataList1").append(data);
                }
            });
            current = current + 10;
        };

Before calling the Handler page set next, after set current to current+10

  next = current + 10;
  current = current + 10;

To get the data from a specific row number I used a stored procedure, it will return my data, I want to send number of position, if I send start=10&next=20, It will return 10th to 20th row form the database.

USE [Northwind]
GO
/****** Object:  StoredProcedure [dbo].[ProductPages]    Script Date: 11/28/2011 12:03:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ProductPages]
(
 @start int,
 @next int
) 
AS
BEGIN
SELECT ProductID,ProductName,UnitPrice FROM
(
   SELECT ROW_NUMBER() OVER(ORDER BY ProductID,ProductName,UnitPrice) NUM,
   * FROM Products
) A 
WHERE NUM >@start AND NUM <=@next
END

Now let take a look on how its work. everything works depends on holders scroll function. script check the scroll position of the container is bottom or not, if it on bottom, function call loadNext().

 $(document).ready(function () {
            $("#holder").scroll(function () {
                if ($(this)[0].scrollHeight - $(this).scrollTop() == $(this).outerHeight()) {
                    loadNext();
                }
            });
        });

Handler page is nothing, like a aspx page.Its call a class file DataClass, DataClass is simple class file to reduce bulky code in Handler page. It call the DataLayer and return the data from database, after do some format to fill on the DataList and write it on response.

public void ProcessRequest(HttpContext context)
    {
        string startQstring = context.Request.QueryString["start"];
        string nextQstring = context.Request.QueryString["next"];
        //null check
        if ((!string.IsNullOrWhiteSpace(startQstring)) && (!string.IsNullOrWhiteSpace(nextQstring)))
        {
            //convert string to int
            int start = Convert.ToInt32(startQstring);
            int next = Convert.ToInt32(nextQstring);
            
            //setting content type
            context.Response.ContentType = "text/plain";
            DataClass data = new DataClass();
            //writing response
            context.Response.Write(data.GetAjaxContent(start, next));
        }
    }

There is only one class file. But I put there class on that file.
1:DataClass :

• Contain two function On handler page we are calling first function GetAjaxContent(start,end) it retrun the records from database. 
• 2nd function loaad data on Page_Load even

2: Provide : Provide SqlConnection from web.config
3:DBHelper : Data layer Take a look

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// 

/// Summary description for DataClass
/// 
/// 


public class DataClass
{
    public DataClass()
    {
    }
    /// 

    ///  return rows depend on position
    ///  if you need 10th to 20th you need to pass start=10 and end=20
    /// 
    /// 
database start position of one row
    /// 
database end position of one row
    /// 
    public string GetAjaxContent(int start, int end)
    {
        string result = string.Empty;
        //adding sp params with values in Dictionary entry.
        Dictionary keyValPair = new Dictionary();
        keyValPair.Add("@start", start);
        keyValPair.Add("@next", end);

        DBHelper DBHelper = new DBHelper();
        //passing the Stored Procedure name and keyvalue pair
        DataTable dataTable = DBHelper.GetTable("ProductPages", keyValPair);
        if (dataTable.Rows.Count > 0)
        {
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                result += string.Format(@"
                                                        
                                                            

                                                                


                                                                    
{0}
{1}
{2}
                                                                

", dataTable.Rows[i][0].ToString(), dataTable.Rows[i][1].ToString(), dataTable.Rows[i][2].ToString());
            }

        }
        //this string is going to append on Datalist on client.
        return result;
    }
    /// 

    /// function to bind data on page load
    /// 
    /// 
    public DataTable FirstTenRecords()
    {
        Dictionary keyValPair = new Dictionary();
        keyValPair.Add("@start", 0);
        keyValPair.Add("@next", 10);

        DBHelper DBHelper = new DBHelper();
        DataTable dataTable = DBHelper.GetTable("ProductPages", keyValPair);
        return dataTable;
    }
}

/// 

/// return sqlconnection string formweb.config file
/// 
public class Provider
{
    public static SqlConnection GetConnection()
    {
        return new SqlConnection(ConfigurationManager.AppSettings["SqlConnectionString"]);
    }
}
/// 

/// Data layer
/// 
public class DBHelper
{
    public DBHelper()
    { }

    SqlConnection con;
    SqlCommand cmd;
    SqlDataAdapter adapter;
    public DataTable GetTable(string SPName, Dictionary SPParamWithValues)
    {
        DataTable dataTable = new DataTable();
        try
        {
            con = Provider.GetConnection();
            //open DB connection
            con.Open();
            cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            cmd.CommandText = SPName;
            foreach (KeyValuePair paramValue in SPParamWithValues)
            {
                cmd.Parameters.AddWithValue(paramValue.Key, paramValue.Value);
            }
            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(dataTable);
        }
        finally
        {
            //close connection string
            con.Close();
        }
        return dataTable;
    }
}

If it not display properly please use the images

Download Source

Download full source code CodeProject