Paging through ResultSets in an ASP.NET application (Part 1)

By: CodeGear

Abstract: This article shows how to display pageable data in an ASP.NET application developed with C#Builder using BDP.NET managed provider for Interbase. Covers Default Paging mechanism.

Paging through ResultSets in an ASP

Paging through ResultSets in an ASP.NET application

By Sam Kapoor
Borland Software Corporation.

Data paging is an extremely important concept to adopt in real-world web-applications that return the result of a long query. By data-paging we mean displaying only a fixed number of rows to each user requesting the page. A good example of this is a search engine. The .NET framework allows for 2 mechanisms for data-paging namely built-in-paging and custom-paging and in this article I will cover the first of these approaches including showing how to implement it and pointing out the pros and cons of the first approach.

Built-in-paging : The DataGrid control allows for built-in-paging by setting the AllowPaging property to True and by setting a PageSize. Once these properties are set the DataGrid automatically keeps track of the current page index, extracts the fixed number of rows that have been setup in the PageSize and refreshes the page. 

Creating a Simple project using the Data-Grids Built-in-paging mechanism:

Lets go ahead and create an ASP.NET application in C#Builder by clicking on File/New ASP.NET Application and leaving the default web-server of IIS.

                   

      

Once we are on the Design surface of the web-form we can select the tab for Data Explorer, expand the Interbase node, browse down to the list of tables, select the EMPLOYEE table and drag and drop it on the web-forms design-surface. (For this example I am using the Employee.gdb sample database that ships with Interbase and I am assuming that you have your Interbase connection set up correctly. For more information on how to do that please refer to John Kasters article on BDN). After dragging and dropping the EMPLOYEE table from the Data Explorer we see that we have an instance of a BdpConnection and a BdpDataAdapter.

                   

We can now right-click the BdpDataAdapter and select the Configure Data Adapter and on the DataSet tab we can select New DataSet and give it a name of EmpDS.

This step creates a .NET DataSet for us to bind to our DataGrid. Now we can select a DataGrid Web Control and drag that onto our web-forms design surface. To begin setting the DataGrids properties we can start on the Data property. Set the DataSource to EmpDS, the DataMember to Table1 and the DataKeyField to EMP_NO. This step binds the datasource to the DataGrid. Now under the Paging property type set the AllowPaging option to True and set the PageSize to 10 (default). Now lets do a bit of formatting on our DataGrid to make it look a bit more appealing. First lets set the AutoFormat to Professional 1 (this property is on the bottom panel of the Object Inspector), then set the Pager Styles Mode property to NextPrev (instead of Numeric pages), the NextPageText to Next and the PrevPageText to Prev. For the built-in-paging mechanism to work all the records that need to be paged have to reside in the web-servers memory.

Caching the Data:

Since the DataGrids built-in-paging mechanism requires all the data to be resident in memory we can use the Cache object to store our data the first time the page is requested (the cache object is global to all sessions). Remember that in order for the built-in-paging mechanism to work the DataGrid component requires that the entire data be loaded and bound to the DataGrid on each page request. If we dont employ this caching mechansim we would end up having to query the entire resultset on each page request (in other words if our query returns a 1000 rows and we want to display only 10 on a page we would end up querying for the 1000 rows on each page request).

void LoadData() {

//store the DataSet in Cache

   Cache["Data"] = bdpDataAdapter1.DataSet;

}

void BindData() {

  //retreive DataSet from Cache and bind to DataGrid

   DataSet ds = (DataSet) Cache["Data"];

   dataGrid1.DataSource = ds;

   dataGrid1.DataMember = ds.Tables[0].TableName;

   dataGrid1.DataKeyField = ds.Tables[0].Columns[0].ColumnName;

   dataGrid1.DataBind();

}

private void Page_Load(object sender, System.EventArgs e) {

// If page requested first time then Load Data into Cache

  if (!IsPostBack) {

    LoadData();

  }

 BindData();

}

The code above shows that the Data is loaded into the Cache only the first time the page is requested after which each subsequent request retreives the data from the Cache and binds it to the DataGrid.

Getting the Pager to work:

Once we set the DataGrid components AllowPaging property to True the DataGrid acquires a Data Pager which is an element that allows you to link to other pages when Paging is enabled. In order to get the pager to work we must write an EventHandler for the PageIndexChanged event of the DataGrid.

private void dataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e) {

 dataGrid1.CurrentPageIndex = e.NewPageIndex;

 BindData();

}

The NewPageIndex property contains the updated index of the page and when the DataBind occurs on the DataGrid it extracts the next or previous subset of rows (number set in the PageSize) based on whether the next or previous button of the pager was clicked and renders them on the DataGrid. The DataGrid automatically enables and disables the next and previous links based on which page is selected (for example when the first page is selected the previous link is disabled).

Thats it, and now we have a solution that uses the built-in-paging mechanism of the DataGrid component.

Benefits of Built-in-paging:

The obvious benefit to the built-in-paging mechanism of the DataGrid component is that it is easy to implement. If the data to be retreived is from a lookup table (relatively small in size) and is to be shared by all the sessions then using the built-in-paging mechanism is a fair appoarch.

Drawbacks of Built-in-paging:

Although the built-in-paging mechanism is easy to implement it suffers from some severe limitations. In order for the built-in-paging to work all the data has to be downloaded and stored in the Web-Servers memory. Even though we can think of the speed of the data-retreival to be improved with caching yet by using this approach the overall scalability of the application will be hindered. Especially if you have a lot of data that is being retreived in your query and you are distinctly storing a copy of the data in the Session object (unique to each session) you will be taxing the web-server quite a bit and will eventually bog down or even crash your application. 

Complete Code-Behind C# class can be found here

In the next article I will show how to build a customized-paging solution.


Server Response from: ETNASC04