How To : Create Custom Paging Grid Using JSON + JQuery + ASP.NET

In this article, I am going to create a light weight grid that display data from DB in a custom paging way just like Facebook & twitter without postback using JSON & JQuery.

Let's take this case, you have a comments table that contains large number of records and want to display them in a web page. before start typing the code, we need to take the below points into considerations:

• Since we have large number of records and cannot be retrieved all in the page load, we are going to create custom paging stored procedure that retrieved small chunks of data page per page.
• The page should be light and fast, so we need to kill the ASP.NET Evil(the viewstate) by using Jquery to draw the html table & JSON to Ajaxify the page and make it faster and light than using the regular Ajax Code (JSON is an Object Notation String based where the regualre Ajax is an XML based which makes it faster, lighter and easy to deal with).

The DataBase Part

Using SQL Server 2008, create a new table name it [CommentsTable] as you can see in the below figure:

Create New Stored Procedure and name it [GetComments_Paging] and write the below code :

 CREATE PROCEDURE [dbo].[GetComments_Paging]
  
  @PageNum INT,
  @RowCount INT
 
AS
BEGIN

SELECT [t1].*
FROM (SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CommentId]) AS [ROW_NUMBER],[t0].CommentId,
    [t0].CommentTitle,[t0].CreatedOn,[t0].UserName,[t0].CommentBody 
     FROM CommentsTable AS [t0]) AS [t1] WHERE [t1].[ROW_NUMBER]
     BETWEEN @PageNum AND @PageNum + @RowCount ORDER BY [t1].[ROW_NUMBER]

END

As you noticed, the stored procedure takes two input parametrs (@PageNum to indicate which page index you want to retrieve) and (@RowCount to specify how many records you want to retrieve) .

The WebSerivce Part

We need to create a class (clsComments) to be the container of the comments data and to use it with the JSON code. as below

Then we will create a webservice called (CommentService.asmx) and as below:

It is important to know that we need to add [System.Web.Script.Services.ScriptService] attribute typically above the class name to allow this Web Service to be called from Javascript.

The ASP.NET Part

add the below HTML code

Now we will let JSON call our CommentService.asmx and Jquery will bind the results inside ResultsTable Div.

and test whether the page is doing a post back or not, add the below code behind

public partial class _Default : System.Web.UI.Page
{
    protected const int rowsCount = 2;

    protected void Page_Load(object sender, EventArgs e)
    {
        lblDate.Text = DateTime.Now.ToLongTimeString();
       
    }
}

Now run the page and click on More Comments button and notice how the comments get displayed page per page without postback as below

hope someone will found it useful.

~ Abdulla AbdelHaq