introduction most of the times while creating database driven web pages like product listing, employee directory, etc. we display the data in grid format. asp.net has come up with web controls like datagrid, datalist and repeater that allow you to display your data in tabular format easily.
amongst the above three web controls the datagrid control is the most advanced and supports paging, templates etc. the other two controls are used in places where you need more control over the rendering of your data. the datalist and repeater controls have very flexible templates that give you total control over the formatting of your data, but one of the major drawbacks of these controls is that they do not support paging!!
paging simply means splitting data display (ui) over number of pages in order to facilitate easy to browse interface and minimize the data sent out to the client. for example, you have a web page displaying your products listing. it would not be a good idea to show all the 1000+ products you are offering on the one single page, since it will make the page difficult to browse as well as it will take a lot of time to display on the clients browser due to the size of data (plus a heavy load on your server). the second reason this is not a good idea is that the client would not want to know about all the products you are selling since the last 10 years, he might visit to page to look out for the new products that you are offering. in such scenarios, you divide the display of data into different pages and then display say 10 - 20 items per page and provide the client with links to view additional pages, this is called paging. remember we are using server-side scripting so you don't have to physically create all the pages, you just have to code one page in such a way that it keeps paging all the records.
hence some of the merits of paging are: 1) easy to browse pages. 2) faster to load pages on client side since the amount to display per page is less. 3) less load on the database server, since for every user you only pull out a limited amount of data, rather than pulling out all the records for each client.
as i mentioned before, the datalist and repeater controls are very flexible and there would be a large number of places you might want to use these controls. even though these controls do not support paging internally, in this article i will display a method using which, you can easily enable simple paging (previous , next ) in your datalist and repeater controls.
25 october 2002, update: the small error in the buildpagers method has been corrected. thanks to all readers who pointed out the bug!
requirements 1) asp.net v1 2) sql server 7.0/2000/msde (optional, i am going to use the northwind database that installs with the .net sdk)
simple paging in repeater control
1) listing 1, shows the code for a normal page that selects all records from the products table and displays it using the repeater control.
<%@ page language="c#" debug="true" %> <%@ import namespace="system.data" %> <%@ import namespace="system.data.sqlclient" %> <html> <script language="c#" runat="server"> void page_load( object sender , eventargs e) { //build the grid only if the page has been accessed for the first time if( !ispostback ) buildgrid(); }
public void buildgrid() { sqlconnection myconnection = new sqlconnection( "server=(local)//netsdk;database=northwind;trusted_connection=yes" ); sqldataadapter myadapter = new sqldataadapter( "select productid, productname, quantityperunit, unitprice from products", myconnection); //fill the dataset dataset ds = new dataset(); myadapter.fill(ds,"products"); //databind the repeater myrepeater.datasource = ds.tables["products"].defaultview; myrepeater.databind(); } </script> <body> <h1>products listing</h1> <form runat="server"> <asp:repeater id="myrepeater" runat="server"> <headertemplate> <table width="100%" border="1" cellpadding="1" cellspacing="2"> <tr> <th> product id </th> <th> product </th> <th> quantity per unit </th> <th> unit price </th> </tr>
listing 1 - simple repeater control 2) the first step to enable paging in this page is to add three hidden html controls that will maintain the values necessary for paging. you can add these controls below after your repeater control.
the first control with the id pagesize is used to define the number of records you want to display per page. current i have set it to display 10 records, you can set it to a value you wish. the second control with the id currentpage is used to track the current page that's been displayed. usually you want to start displaying the records from the first page so we set the value of this control to 1. lastly, the control with the id totalsize is used to store the count of the total number of records available.
3) next, we add two linkbutton controls that will enable navigation to the previous and next pages. add these controls below the repeater control definition. note: you might be tempted to include these controls within your repeater control's footertemplate, but i advise you not to follow this approach since it will complicate matters much more while writing code to handle these buttons it will be very difficult to get a reference to these controls.
listing 3, defines the two linkbuttons with the id prev and next respectively. feel free to change the text property of these controls to something appropriate to your implementation. also note that i have set the onclick event of both these controls to one single event handling method called page_repeater. listing 4 displays the page_repeater method code.
public void page_repeater( object sender, eventargs e ) { //check for button clicked if( ((linkbutton)sender).id == "prev" ) { //check if we are on any page greater than 0 if( ( int.parse( currentpage.value ) - 1 ) >=0 ) { //decrease the currentpage value currentpage.value = ( int.parse( currentpage.value ) - 1 ).tostring() ; } } else if( ((linkbutton)sender).id == "next" ) { //check if we can display the next page. if( ( int.parse( currentpage.value ) * int.parse( pagesize.value ) ) < int.parse( totalsize.value ) ) { //increment the currentpage value currentpage.value = ( int.parse( currentpage.value ) + 1 ).tostring() ; } } //rebuild the grid buildgrid(); }
listing 4: page_repeater method
in the page_repeater method i first check which of the button was clicked. if the prev linkbutton was clicked, i check the value of the control with id currentpage. if the current value minus 1 is greater than equal to 0, it indicates i have previous pages to display. so i decrement the value of the control with id currentpage by 1. if the next linkbutton was clicked, i check out if the product of the controls with id's currentpage and the pagesize is less than the value of the control with id totalsize, indicating that i have more pages to display. hence i increment the value of the control with id currentpage. lastly, the buildgrid method is given a call to re-build the repeater.
4) the current buildgrid method is configured to fetch all the records from the database. as i had stated earlier this is a bad practice and only limited records should be fetched each time to increase scalability of your web site. in listing 5, below i modify the buildgrid method so that it only pulls out the required records from the database. in case you are data binding your control to a datareader rather then a dataset, then you will have to modify your sql statement appropriately so that only the required records are fetched.
public void buildgrid() { sqlconnection myconnection = new sqlconnection( "server=(local)//netsdk;database=northwind;trusted_connection=yes" ); sqldataadapter myadapter = new sqldataadapter( "select productid, productname, quantityperunit, unitprice from products" , myconnection); //fill the dataset dataset ds = new dataset();
//get the start record count //remember database count is zero based so first decrease the value of //the current page int startrecord = ( int.parse( currentpage.value ) - 1 ) * int.parse( pagesize.value ); //fetch only the necessary records. myadapter.fill( ds , startrecord , int.parse( pagesize.value ) , "products");
//databind the repeater myrepeater.datasource = ds.tables["products"].defaultview; myrepeater.databind();
//second part //create a new command to select the total number of records sqlcommand mycmd = new sqlcommand( "select count(*) from products", myconnection ); myconnection.open(); //retrieve the value totalsize.value = mycmd.executescalar().tostring() ; myconnection.close(); }
listing 5 - modified buildgrid method
as its clear from listing 5, the only change i have made is that now i use a different overload of the fill method that takes the start index and the number of records to fetch along with the standard dataset and table name. using this overload reduces the amount of data fetched from the database increasing the performance of your application. next, i construct and execute another query that returns the total number of records the database contains. this value is then stored to the control with id totalsize. remember that we cannot get the total records count from the dataset since our updated fill method only returns the necessary number of records. in your own implementation you will have to change this sql query appropriately to return the number of records present in the database. you can leave this second part out from your code if you just want to display a fixed number of pages and directly set the value of the control with id totalsize manually. if the number of records does not change frequently you can encapsulate the second part in an if( !ispostback ) structure, so that the total number of records is only retrieved the first time the repeater is built.
