Unlimited Scroll in ASP.NET DataList Control

Unlimited scroll is not a big deal, for example Facebook news feed or twitter tweets, the news or tweets updates when user reaches bottom of the page. Actually client side script checks the scroll position of the container, if the position is at the bottom, the scripts request content form server and update the container. In here, the container is a DIV tag, named holder, and puts some style tag height, width and overflow.holder held in the DataList.

CSS for the Holder

<style type="text/css">
    #holder {width: 1900px; height:200px;overflow:auto;  }
</style>

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 on the client side, I set the current item count to 10 and next item count to 0.

var current=10;
var next=0;

and call the function for load next form JavaScript, it’s nothing but calling server via AJAX, i.e., requesting Handler page with a query string of start and next. The below  image shows the request URL form client, I used Firebug to show requests.

Let's look at 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 setting 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 10^th to 20^th row from 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's take a look at how it works. Everything works depending on holders scroll function. Script checks the scroll position of the container is bottom or not, if it is at the bottom, function call loadNext().

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

Handler page is nothing, like an aspx page. It calls a class file DataClass, DataClass is simple class file to reduce bulky code in Handler page. It call the DataLayer and returns the data from database, after doing some formatting to fill on the DataList and writes 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 the class on that file.

1. DataClass
• Contain two functions on handler page we are calling first function GetAjaxContent(start,end) it returns the records from database 
• 2^nd function loads data on Page_Load even
2. Provide
• Provides SqlConnection from web.config
3. DBHelper
• Data layer takes 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>
/// Summary description for DataClass
/// </summary>
/// 
public class DataClass
{
    public DataClass()
    {
    }
    /// <summary>    
    ///  return rows depend on position
    ///  if you need 10th to 20th you need to pass start=10 and end=20
    /// </summary>
    /// <returns>
    /// database start position of one row
    /// database end position of one row
    /// </returns>
    public string GetAjaxContent(int start, int end)
    {
        string result = string.Empty;
        //adding sp params with values in Dictionary entry.
        Dictionary<string,> keyValPair = new Dictionary<string,object>();
        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(@"<table>
<tbody>
	<tr>
		<td style="width: 50px; ">{0}</td>
		<td style="width: 400px; ">{1}</td>
		<td style="width: 150px; ">{2}</td>
	</tr>
</tbody>
</table>", 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;
    }
    /// <summary>
    /// function to bind data on page load
    /// </summary>
    /// <returns />
    public DataTable FirstTenRecords()
    {
        Dictionary<string,object> keyValPair = new Dictionary<string,object>();
        keyValPair.Add("@start", 0);
        keyValPair.Add("@next", 10);

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

/// <summary>
/// return sqlconnection string from web.config file
/// </summary>
public class Provider
{
    public static SqlConnection GetConnection()
    {
        return new SqlConnection(ConfigurationManager.AppSettings["SqlConnectionString"]);
    }
}
/// <summary>
/// Data layer
/// </summary>
public class DBHelper
{
    public DBHelper()
    { }

    SqlConnection con;
    SqlCommand cmd;
    SqlDataAdapter adapter;
    public DataTable GetTable(string SPName, Dictionary<string,object> 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<string,object> 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;
    }
} 

Download source

CodeProject