DataGrid is one of the coolest controls in Asp.net (I know I said it before :) ). Paging in datagrid is also very simple and easy to implement. In this article I will show you that what you must do in order to get maximum performance when performing paging in datagrid.

 Introduction:

DataGrid is one of the coolest controls in Asp.net (I know I said it before :) ). Paging in datagrid is also very simple and easy to implement. In this article I will show you that what you must do in order to get maximum performance when performing paging in datagrid.

Paging in Datagrid (The easy way):

You can easily configure your datagrid to enable paging. This can be done by setting the "Allow Paging" property to true. Below is the code that most of the developers use while doing paging.

privatevoid myDataGrid_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e) { myDataGrid.CurrentPageIndex = e.NewPageIndex; BindData(); }

And here is the BindData method:

privatevoid BindData() { Database db = DatabaseFactory.CreateDatabase(); DBCommandWrapper selectCommandWrapper = db.GetStoredProcCommandWrapper("sp_GetLatestArticles"); DataSet ds = db.ExecuteDataSet(selectCommandWrapper); myDataGrid.DataSource = ds; myDataGrid.DataBind(); }

This code will execute without any errors and will do the paging. The problem using this approach is when we view the next page in the datagrid by clicking a link button or a next button the whole data is fetched again from the datagrid. This means if you have 1000 rows in the database those rows will be fetched on every click of the pager. This results in low performance since we are accessing the database too often.

Let's see how we can improve performance by introducing 'Caching'.

DataGrid Paging using Caching:

You can use caching to save the result in the cache object and retrieve and bind it to datagrid without going to the database. Let's see a small example:

 

privatevoid BindData() { Database db = DatabaseFactory.CreateDatabase(); DBCommandWrapper selectCommandWrapper = db.GetStoredProcCommandWrapper("sp_GetLatestArticles"); if(Cache["MyData"] == null) { DataSet ds = db.ExecuteDataSet(selectCommandWrapper); Cache["MyData"] = ds; } myDataGrid.DataSource = (DataSet) Cache["MyData"]; myDataGrid.DataBind(); }

All we are doing is putting the data into the Cache object and loading only when the Cache object is null.

The downside of using this approach is that it after first loading from the database it will always fetch the data from the Cache object. This means that if you add some new data it will not appear on the page unless you build your application. Let's see how you can solve this problem.

 

privatevoid BindData() { Database db = DatabaseFactory.CreateDatabase(); DBCommandWrapper selectCommandWrapper = db.GetStoredProcCommandWrapper("sp_GetLatestArticles"); if(Cache["MyData"] == null) { DataSet ds = db.ExecuteDataSet(selectCommandWrapper); Cache.Insert("MyData",ds,null,DateTime.Now.AddMinutes(2),TimeSpan.Zero); } myDataGrid.DataSource = (DataSet) Cache["MyData"]; myDataGrid.DataBind(); }

All I did is added the DataSet into the Cache object and set the time to expire in 2 minutes. This means that if you enter data in the database it will appear in the datagrid after 2 minutes. You can change the time to what ever you like.

 

In Asp.net 2.0 you will have SqlCacheDependency which will refresh the cache each time a new item is added to the database.

You can also use custom paging which allows you to load set amount of pages to from the database.

I hope you like the article happy coding.