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.
Connect with Us