Infinite Scrolling In ASP.NET With jQuery

28. April 2012 12:53

ASP.NET Jquery T-SQL  5 Comments

I didn't create the whole solution by myself. The initial idea is from the All-In-One Code Framework sample. I was just customizing my BlogEngine and for one of the module I thought it would be nice not to use paging and I should go for infinite scrolling stuff. This idea seems cool to me and without wasting any time I just do a quick web search and I came across a solution which is a part of a All-In-One Code Framework. This was not the complete solution I was looking for but I can re-use the jQuery part in the sample.

Assembling jQuery Part

I just re-use the all jQuery script as it is without any major modification. The only change I made is the name of the web method in the url parameter of the Ajax method I have in page code behind and change the method name to InfiniteScroll:

function InfiniteScroll() {
    $('#divPostsLoader').html('<img src="images/loader.gif">');

    //send a query to server side to present new content
    $.ajax({
        type: "POST",
        url: "Default.aspx/GetData",
        data: "{}",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function (data) {
            if (data != "") {
                $('.divLoadData:last').after(data.d);
            }
            $('#divPostsLoader').empty();
        }
    })
};

I already have 2 divs in the page where the data is being loaded when the user scrolls down at the bottom of the page. This is the simplest Ajax call written in jQuery and I assume you are aware of the syntax and methods and require no explanation. What I require here is to call this function when the user scrolls at the bottom of the page. So how to know when the user scrolls and reach at the bottom of the page? Here is the method which fires the InfiniteScroll() function:

$(window).scroll(function () {
    if ($(window).scrollTop() == $(document).height() - $(window).height()) {
        InfiniteScroll();
    }
});

Make sure you put the scroll function inside the document.ready function. Now when the user scrolls at the bottom of the page then it will call the InfiniteScroll() function which in turn loads new data in the divLoadData div.

The Code-Behind

In the code-behind create a WebMethod function that returns a HTML string. For this example I am loading all my blog posts title, post date and slug from my blog engine database which is a SQL Server CE 4.0. Here is the method:

[WebMethod]
public static string GetData()
{
    RecordCount = RecordCount + 10;
    string Sql = "SELECT Title, DateCreated, Slug FROM be_Posts ORDER BY Title OFFSET " + FirstCount + " ROWS FETCH NEXT 10 ROWS ONLY";
    FirstCount = RecordCount;
    StringBuilder sb = new StringBuilder();
    dt = new DataTable();
    da = new SqlCeDataAdapter(Sql, con);
    con.Open();
    da.Fill(dt);

    DataView dv = dt.DefaultView;

    foreach (DataRowView row in dv)
    {
        sb.AppendFormat("<p>Post Title" + " <strong>" + row["Title"] + "</strong>");
        sb.AppendFormat("<p>Post Date" + " <strong>" + row["DateCreated"] + "</strong>");
        sb.AppendFormat("<p>Slug" + " <strong>" + row["Slug"] + "</strong>");
        sb.AppendFormat("<hr/>");
    }

    sb.AppendFormat("<divstyle='height:15px;'></div>");
    con.Close();
    return sb.ToString();
}

Infinite scrolling is nothing but is a sort of automatic paging. Every time a user scrolls down at the bottom of the page, the query gets fired and gets the new set of data. As I am using SQL CE 4.0 the paging query is different than that of the SQL Server 2008.

Query for SQL CE 4.0:

SELECT Title, DateCreated, Slug FROM be_Posts ORDER BY Title OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Change the OFFSET and FETCH NEXT to get the next result set.

Query For SQL Server 2008:

SELECT Title, DateCreated, Slug FROM ( 
     SELECT 
          ROW_NUMBER() OVER (ORDER BY title) AS row, Title, DateCreated, Slug
     FROM be_Posts
) AS a WHERE row BETWEEN 1 AND 10

I have changed the query in the above code to get me the set of next results every time a user scrolls at the bottom of the page. When the user scrolls down I increment the FirstCount with 10 which is a value of RecordCount variable. I have attached the complete solution so you can try. I don't have the database included in the solution as it is my blog database. You have to create one to test it out.

Download: InfiniteScroll.zip (2.28 mb)

Currently rated 2.0 by 1 person

Comments (5)

&#211;scar Óscar
5/7/2012 12:13:39 AM #

Hello! Nice solution! I have one question: If the data tha you get from the data base is a personal data(sensible), is it secure to do this with a webservice? Thank you!

Prashant Prashant
5/8/2012 12:12:56 PM #

Thanks!! Many people do that with a web service. We can use web service because when you deploy the web service by default only your application will be able to access the web methods and not anyone from outside the application context. If you want it to public then you have to make changes to the web.config to make the web service available to public.

emma emma
12/10/2012 1:30:00 PM #

thank u , u can update link file InfiniteScroll.zip (2.28 mb) . I cant download it

Prashant Prashant
12/12/2012 4:42:00 AM #

Link is working fine...please check again!!

Deal Maity Deal Maity
8/31/2016 4:37:24 PM #

Hi. Nice solution but i face à little probleme. When i scroll down the new set off data is incremented on the top of the list, not on it's bottom. Did someone face the same case and how can i resolve it. thank you for all

Add Comment

Visit blogadda.com to discover Indian blogs Computers Blogs