Click here to Skip to main content
Click here to Skip to main content
Go to top

Unlimited Scroll in ASP.NET DataList Control

, 10 Feb 2014
Rate this:
Please Sign up or sign in to vote.
Unlimited scroll like Facebook news feed or Twitter tweets
A Technical Blog article. View entire blog here.

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 10th to 20th 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 
    • 2nd 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

raju melveetilpurayil

United Kingdom United Kingdom
Microsoft Certified Professional Developer.

Comments and Discussions

 
SuggestionUse StringBuilder to concatenate result instead string [modified] PinmemberManish Ray14-Feb-14 2:49 
QuestionSome Problem Pinmemberjitendra1_4-Feb-14 0:34 
AnswerRe: Some Problem Pinmemberraju melveetilpurayil8-Feb-14 15:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 10 Feb 2014
Article Copyright 2011 by raju melveetilpurayil
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid