C# .NET Programming Blog

Every one of us has used paging in our projects to display tabular data in DataGrids or GridViews or in the more recent and more flexible ListViews.

The problem we often face is when the data is less the paging works fine, but as the data starts to increase the page load times start increasing. The main reason for this is because we tend to bind the grids with entire data table and then use the built in functionality for paging.

This article describes how to effectively do server side paging by retrieving only a finite amount of data using the traditional stored procedure technique, but optimized for not being affected by volume of data, and an alternative method using the very famous linq framework.

Stored Procedures

If you Google for server side paging using stored procedures, you will find a majority of articles showing you a very typical method of using temporary tables for storing data. But temporary tables are made in memory of the server which is not a good practice. Increased page size will just increase the memory consumption.

The following piece of code shows a stored procedure using rowcount to limit the number of rows queried instead of using temporary tables. This method is much more efficient and gets marginally affected when the data increases.

Show Code

CREATE PROCEDURE spProductList
	@PageNumber int,
	@PageSize int
AS
BEGIN

    	DECLARE @index int
	SET @index = @PageNumber * @PageSize

	SET ROWCOUNT @index

	DECLARE @First int

	IF @PageNumber = 0 SET @First = 0
	ELSE SELECT @First = ProductID FROM Products

	SET ROWCOUNT @PageSize

	SELECT * FROM Products WHERE ProductID > @First

	SET ROWCOUNT 0

END

ROWCOUNT restricts the number of rows returned in the result set. As you can see in the stored procedure we set the rowcount as PageSize * PageNumber, this gives us the sequence number of the first identity we want to start the result set from. Next we set the rowcount as PageSize which will restrict the result to the PageSize.

There is a limitation to this method. It will work only in tables that have identities or integer type columns which are primary keys. Another limitation is that the sequence should be continuous, you cannot skip a number in between or the count would be improper.

All these limitations will require more code written to handle these conditions. However I chose not to do this since we have a much simpler method using linq shown below.

Linq to SQL

The second method is implemented using linq. As you can see the amount of code written is much less as compared to the stored procedure. And all the limitations of having a continuous sequence are no longer prevalent.

Most people would argue in terms of the performance tradeoff. I personally feel that the linq based solution maybe a bit slower as compared to a precompiled stored procedure, but it is much simpler in terms of maintainability and readability and implementation.

But anyways you do have an option of calling a stored procedure using linq. This should definitely bring you best of both worlds. In this sample we will however concentrate on a simple linq query for paging.

Show Code

var result = (from product in db.Products select product).Skip(index).Take(size);

In the above linq query ‘index’ is the index of the row to start the count from, and ‘size’ is the page size. This single line of linq query ensures proper server side paging. The result is returned as a object of type IQueryable which can be used as a datasource for GridViews or ListViews.

Sample

The attached sample contains paging examples using both methods described above in a ListView using DataPager. Refer the zip file for further details.

I have used the products table from northwind database for the sample. I have not done much of a performance comparison between the two but both seem fairly fast. If anyone else has the chance to do a thorough performance test please do let me know the outcome.

Download Sample
PagingSample.zip