Paging with the DataGrid

only for RuBoard

Letting the user page through data from a data store has become an important part of Web site development. An online retailer with 500 products doesn't want to provide one big list of all those products. It's much better to display a short list of products and provide links so the user can scroll through pages of the remaining products. The DataGrid lets you add paging functionality with either the built-in paging functions or a custom function.

The paging functionality works with the DataGrid 's CurrentPageIndex property, which defines the page of data that's to be displayed, based on the number of records in the data source and the PageSize property of the DataGrid . The PageSize default value is 10 records per page. This property can be overridden with any integer value. The CurrentPageIndex value is set each time a user navigates to another page of data, and the DataGrid is bound to the data source again.

Built-In Paging

The built-in paging functionality is designed to work best with smaller data sources. Each time CurrentPageIndex is set, the data source is retrieved. For instance, a DataTable in a DataSet is retrieved from the database, and the DataGrid is bound to the data source. Before the DataBind() method is called, the CurrentPageIndex property must be set. When the DataBind() method is called, the appropriate range of records from the data source is bound to the DataGrid .

The built-in paging functionality requires an event handler to manage the paging. For the paging to work, the data binding of the DataGrid has to be moved out of the Page_Load() event handler. This is because you need to ensure that the CurrentPageIndex property is set prior to calling the DataBind() method. Every time a page-navigation link is clicked, such as a " Next Page" link or a page number link, a post-back to the page occurs. You want the DataGrid to be bound to the data source on the first page request. On a post-back from a page-navigation link, you want to make sure the CurrentPageIndex property is set prior to any data binding. In Listing 6.5, you create the same Web Form from the previous examples, but you set the DataGrid to use the built-in paging functionality.

Listing 6.5 Using Built-In Paging with the DataGrid
 [Code Behind VB - 06.05.vb] 01: Imports System 02: Imports System.Web 03: Imports System.Web.UI 04: Imports System.Web.UI.WebControls 05: Imports System.Data 06: Imports System.Data.SqlClient 07: 08: Public Class Listing0605 : Inherits Page 09: 10:   Protected myDataGrid As DataGrid 11: 12:   Protected Sub Page_Load(Source As Object, E As EventArgs) 13:    If Not Page.IsPostBack Then 14:      BindData() 15:    End If 16:   End Sub 17: 18:   Protected Sub BindData() 19:    Dim myDataSet As New DataSet 20:    Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT CompanyName, graphics/ccc.gif ContactName, ContactTitle, Phone, Fax FROM Customers", graphics/ccc.gif "server=localhost;database=Northwind;uid=sa;pwd=;") 21:    myDataAdapter.Fill(myDataSet, "Customers") 22: 23:    myDataGrid.DataSource = myDataSet.Tables("Customers") 24:    myDataGrid.DataBind() 25:   End Sub 26: 27:   Protected Sub PageIndexChanged_OnClick(Sender As Object, E As graphics/ccc.gif DataGridPageChangedEventArgs) 28:    myDataGrid.CurrentPageIndex = e.NewPageIndex 29:    BindData() 30:   End Sub 31: 32: End Class [Code Behind C# - 06.05.cs] 01: using System; 02: using System.Web; 03: using System.Web.UI; 04: using System.Web.UI.WebControls; 05: using System.Data; 06: using System.Data.SqlClient; 07: 08: public class Listing0605 : Page{ 09: 10:   protected DataGrid myDataGrid; 11: 12:   protected void Page_Load(Object sender, EventArgs e){ 13:    if(!Page.IsPostBack){ 14:      BindData(); 15:    } 16:   } 17: 18:   protected void BindData(){ 19:    DataSet myDataSet = new DataSet(); 20:    SqlDataAdapter myDataAdapter = new SqlDataAdapter("SELECT CompanyName, ContactName, graphics/ccc.gif ContactTitle, Phone, Fax FROM Customers", graphics/ccc.gif "server=localhost;database=Northwind;uid=sa;pwd=;"); 21:    myDataAdapter.Fill(myDataSet, "Customers"); 22: 23:    myDataGrid.DataSource = myDataSet.Tables["Customers"]; 24:    myDataGrid.DataBind(); 25:   } 26: 27:   protected void PageIndexChanged_OnClick(Object sender, DataGridPageChangedEventArgs graphics/ccc.gif e){ 28:    myDataGrid.CurrentPageIndex = e.NewPageIndex; 29:    BindData(); 30:   } 31: 32: } [Web Form VB] 01: <%@ Page Inherits="Listing0605" src="06.05.vb" %> [Web Form C#] 01: <%@ Page Inherits="Listing0605" src="06.05.cs" %> [Web Form VB & C#] 02: <html> 03: <head> 04:  <style ref="stylesheet" type="text/css"> 05:    .tableItem { font: x-small Verdana, Arial, sans-serif;} 06:    .tableHeader { font: bold small Arial; color:#663300; background-color:#CCCC66;} 07:    .alternatingItem { font: x-small Verdana, Arial, sans-serif; background-color: graphics/ccc.gif #FFFFCC;} 08:    A { color:#663300} 09:    A:hover { color:red} 10:  </style> 11: </head> 12: <body> 13: <form runat="server" method="post"> 14:  <asp:DataGrid runat="server" id="myDataGrid" 15:   Width="740" 16:   Cellpadding="4" 17:   Cellspacing="0" 18:   Gridlines="Horizontal" 19:   HorizontalAlign="Center" 20:   HeaderStyle-CssClass="tableHeader" 21:   ItemStyle-CssClass="tableItem" 22:   AlternatingItemStyle-CssClass="alternatingItem" 23:   AllowPaging="True" 24:   OnPageIndexChanged="PageIndexChanged_OnClick" 25:   PageSize="10" 26:  /> 27: </form> 28: </body> 29: </html> 

In Listing 6.5, you use the data access code from Listing 6.1. However, this time it's moved into a method named BindData() . This allows you to call BindData() anytime you need to bind the DataGrid to the data source. On lines 12 “16 in the code behind class, you change the Page_Load() event handler to call BindData() , only when the request for the page is not a post-back event. This is done using the Page object's Boolean property, IsPostBack . If IsPostBack is true, the request is a result of a post-back from one of the page-navigation links. Rather than simply binding the data source to the DataGrid , you need to ensure that the CurrentPageIndex property is set.

On lines 27 “30 of the code behind class, you create an event handler for the DataGrid 's PageIndexChanged event. In this event handler you set the myDataGrid.CurrentPageIndex to the NewPageIndex value passed in the DataGridPageChangedEventArgs . Next you make a call to the BindData() method, and the DataGrid is bound. The NewPageIndex is the page index for the page to be rendered, depending on the link that was clicked and the PageSize property of the DataGrid . When BindData() is called, the data is retrieved from the database again, and the DataGrid is bound to the appropriate records based on the values of the aforementioned properties.

The properties for handling the DataGrid 's paging values are set on lines 23 “25 of the Web Form. When the AllowPaging property is set to true, it turns on the built-in paging functionality. The default value is false. The OnPageIndexChanged property specifies the event handler to refer to when a page-navigation link is clicked. Lastly, the PageSize property can be set to any integer, and the default value is 10. This defines how many records to display per page. Figure 6.5 shows the rendered page from Listing 6.5.

Figure 6.5. The built-in paging functionality of the DataGrid renders pages of data in blocks according to the PageSize property.
graphics/06fig05.gif

In Figure 6.5, you can see the page-navigation links in the lower-left corner of the table. Less-than and greater-than symbols are used to navigate to the previous and next pages in the data source. The DataGrid exposes a number of properties for altering the display of the page- navigation links.

As you learned earlier, there's a PagerStyle style object for controlling the appearance of the page-navigation links. All of the same properties you used with the other style objects are available with the PagerStyle object. Additionally, there are a few extra properties that only apply to the PagerStyle object:

  • Mode ” Determines the page-navigation link type. Possible values are NextPrev and NumericPages .

  • NextPageText ” The text to display for the link to navigate to the next page. Only available when the Mode property is NextPrev .

  • PageButtonCount ” The number of page links to display when the Mode property is NumericPages . If more pages exist beyond the number displayed, an ellipsis will be rendered after the last link. The default value is 10.

  • Position ” Determines the position of the page-navigation links. Possible values are Top , Bottom , TopAndBottom .

  • PrevPageText ” The text to display for the link to navigate to the previous page. Only available when the Mode property is NextPrev .

  • Visible Boolean property to show or hide the page-navigation links. The default is true.

In Listing 6.6, you use the same Web Form from the preceding example. By adding a couple of property values, you easily can alter the output of the page-navigation links.

Listing 6.6 Changing the Appearance of the Page-Navigation Links
 [Web Form VB] 01: <%@ Page Inherits="Listing0605" src="06.05.vb" %> [Web Form C#] 01: <%@ Page Inherits="Listing0605" src="06.05.cs" %> [Web Form VB & C#] 02: <html> 03: <head> 04:  <style ref="stylesheet" type="text/css"> 05:    .tableItem { font: x-small Verdana, Arial, sans-serif;} 06:    .tableHeader { font: bold small Arial; color:#663300; background-color:#CCCC66;} 07:    .alternatingItem { font: x-small Verdana, Arial, sans-serif; background-color: graphics/ccc.gif #FFFFCC;} 08:    A { color:#663300} 09:    A:hover { color:red} 10:    .pageLinks { font: bold x-small Verdana, Arial, sans-serif;} 11:  </style> 12: </head> 13: <body> 14: <form runat="server" method="post"> 15:  <asp:DataGrid runat="server" id="myDataGrid" 16:   Width="740" 17:   Cellpadding="4" 18:   Cellspacing="0" 19:   Gridlines="Horizontal" 20:   HorizontalAlign="Center" 21:   HeaderStyle-CssClass="tableHeader" 22:   ItemStyle-CssClass="tableItem" 23:   AlternatingItemStyle-CssClass="alternatingItem" 24:   AllowPaging="True" 25:   OnPageIndexChanged="PageIndexChanged_OnClick" 26:   PageSize="10" 27:   PagerStyle-CssClass="pageLinks" 28:   PagerStyle-Mode="NextPrev" 29:   PagerStyle-NextPageText="Next" 30:   PagerStyle-PrevPageText="Previous" 31:   PagerStyle-HorizontalAlign="Center" 32:   PagerStyle-Position="TopAndBottom" 33:  /> 34: </form> 35: </body> 36: </html> 

In Listing 6.6, you add six PagerStyle properties to the data grid. On line 27, you specify the cascading style sheet class to use for the page-navigation links. (The style sheet class was added on line 10.) On line 28, you specify that the page-navigation links should be the next and previous links (this is also the default value). On lines 29 and 30, you specify the text to be displayed for the next and previous links. On line 31, you specify that the page-navigation links should be centered. Lastly, on line 32, you specify that the page-navigation links should be rendered at both the top and bottom of the DataGrid output. Figure 6.6 shows the rendered output from Listing 6.6.

Figure 6.6. Next and previous links can be created by specifying the NextPageText and PrevPageText properties of the DataGrid.
graphics/06fig06.gif

In Listing 6.7, you use the same Web Form, but set the DataGrid to use the NumericPages mode of page navigation.

Listing 6.7 Using the NumericPages Mode with the DataGrid
 [VB & C#] 15:  <asp:DataGrid runat="server" id="myDataGrid" 16:   Width="740" 17:   Cellpadding="4" 18:   Cellspacing="0" 19:   Gridlines="Horizontal" 20:   HorizontalAlign="Center" 21:   HeaderStyle-CssClass="tableHeader" 22:   ItemStyle-CssClass="tableItem" 23:   AlternatingItemStyle-CssClass="alternatingItem" 24:   AllowPaging="True" 25:   OnPageIndexChanged="PageIndexChanged_OnClick" 26:   PageSize="10" 27:   PagerStyle-CssClass="pageLinks" 28:   PagerStyle-Mode="NumericPages" 29:   PagerStyle-HorizontalAlign="Right" 30:   PagerStyle-Position="TopAndBottom" 31:  /> 

In Listing 6.7 you do not have to set as many PagerStyle properties, since there are no next and previous links. On line 28 you simply specify that the DataGrid will use the NumericPages style of page-navigation links. Additionally, you could specify the PageButtonCount but the default value of ten (10) is reasonable. Figure 6.7 shows the rendered output of Listing 6.7.

Figure 6.7. You easily can use page numbers as the page-navigation links by specifying the PagerStyle-Mode="NumericPages" .
graphics/06fig07.gif

Custom Paging

The best part of the built-in paging is that it's easy to use and there's very little code you have to write to implement it. The downside is that on each page request, you retrieve all the data from the data store, even if it is not being rendered on the current DataGrid page. Imagine you're querying a table with thousands of rows of data, and thousands of users are requesting the page at the same time. This type of load on a Web server and a database server could cause sub-optimal performance, to say the least. In this kind of scenario, the best alternative is to query only the records of data needed for the page being viewed , and to handle the paging yourself.

The DataGrid provides a custom-paging alternative if the built-in paging doesn't suit your needs. With custom paging, the CurrentPageIndex isn't used. Rather, the DataGrid binds to rows 0- n, n being the number of the PageSize property of the DataGrid . This allows you to query only the records required for the current page request and handle the paging manually.

To make the custom paging work, you must set the AllowCustomPaging property of the DataGrid to true. This tells the DataGrid to enable the PageSize property, but not to handle the paging itself. Basically the DataGrid is displaying records as if paging were not enabled, except that a few properties, such as PageSize , are enabled. You then must return data from the data source according to your paging scheme.

Listing 6.8 shows a SQL Server stored procedure that takes two input parameters (the page number to return and the number of records per page) and one output parameter (the total number of records in the table). Like the previous examples, this stored procedure works against the Customers table in the Northwind database.

Warning

The following code sample is restricted to a SQL Server 7.0+ database. In order for the code in the following listings to run, you will need to add the stored procedure to the Northwind database in SQL Server. To add the stored procedure:

  1. Open the SQL Server Enterprise Manager.

  2. Navigate to the Northwind Stored Procedures node.

  3. Right click on the Northwind Stored Procedures node, and choose New Stored Procedure.

  4. Enter the code in Listing 6.8 and click OK.


Listing 6.8 SQL Server Stored Procedure to Retrieve One Page of Data
 01: CREATE PROCEDURE [spGet_Customers_By_Page] 02:  @CurrentPage int, 03:  @PageSize int, 04:  @TotalRecs int output 05: AS 06:  --Create a temp table 07:  --Add and ID column to count the records 08:  CREATE TABLE #TempTable 09:  (10:   ID int IDENTITY PRIMARY KEY, 11:   CompanyName nvarchar(40), 12:   ContactName nvarchar (30), 13:   ContactTitle nvarchar (30), 14:   Phone nvarchar (24), 15:   Fax nvarchar (24) 16:) 17:  --Fill the temp table with the Customers data 18:  INSERT INTO #TempTable 19:   (CompanyName, ContactName, ContactTitle, Phone, Fax) 20:  SELECT CompanyName, ContactName, ContactTitle, Phone, Fax 21:  FROM Customers 22:  --Create variable to identify the first and 23:  --last record that should be selected 24:  DECLARE @FirstRec int, @LastRec int 25:  SELECT @FirstRec = (@CurrentPage - 1) * @PageSize 26:  SELECT @LastRec = (@CurrentPage * @PageSize + 1) 27:  --Select one page of data based on the record numbers above 28:  SELECT CompanyName, ContactName, ContactTitle, Phone, Fax 29:  FROM #TempTable 30:  WHERE ID > @FirstRec AND ID < @LastRec 31:  --Return the total number of records available as an output parameter 32:  SELECT @TotalRecs = COUNT(*) FROM Customers 

In Listing 6.8, you create a SQL stored procedure that returns records for one page of data based on the current page requested and the number of records in the page. On lines 2 and 3, you specify the input parameters, @CurrentPage and @PageSize . @CurrentPage specifies the page number being requested, and @PageSize is the PageSize property of the DataGrid . On line 4, you create an output parameter to return the number of records in the table.

On lines 8 “16, you create a temporary table to hold the Customers table data that you're querying. Since the Customers table doesn't have sequential numbering in an Identity column, you add an Identity column in the temporary table. On lines 18 “21, you select all of the rows from the Customers table (only the columns you need) and insert them into the temporary table.

Using the @CurrentPage and @PageSize variables , you identify the first and last record to select. On lines 28 “30, you select one page of data to return to the Web Form. Finally, on line 32, you get the total number of records in the Customers table and return that value as the @TotalRecs parameter.

In Listing 6.9, you build a code behind class and a Web Form that use the custom paging functionality of the DataGrid . You make a call to the stored procedure in Listing 6.8, passing in the page number being requested and the PageSize property of the DataGrid .

Listing 6.9 Using Custom Paging with the DataGrid
 [Code Behind VB  06.09.vb] 01: Imports System 02: Imports System.Web 03: Imports System.Web.UI 04: Imports System.Web.UI.WebControls 05: Imports System.Data 06: Imports System.Data.SqlClient 07: 08: Public Class Listing0609 : Inherits Page 09: 10:   Protected myDataGrid As DataGrid 11:   Protected CurrentPage As Label 12:   Protected TotalPages As Label 13:   Protected NextPage As LinkButton 14:   Protected PreviousPage As LinkButton 15:   Protected FirstPage As LinkButton 16:   Protected LastPage As LinkButton 17:   Protected CurrentPageNumber As Integer 18: 19:   Sub Page_Load(Sender As Object, E As EventArgs) 20:    If Not Page.IsPostBack Then 21:      CurrentPageNumber = 1 22:      BindData() 23:    End If 24:   End Sub 25: 26:   Sub BindData() 27:    Dim myConnection As SqlConnection = new SqlConnection( graphics/ccc.gif "server=localhost;database=Northwind;uid=sa;pwd=;") 28:    Dim myCommand As SqlCommand = new SqlCommand("spGet_Customers_By_Page", graphics/ccc.gif myConnection) 29:    Dim myReader As SqlDataReader = Nothing 30: 31:    With myCommand 32:      .CommandType = CommandType.StoredProcedure 33:      .Parameters.Add(new SqlParameter("@CurrentPage", SqlDbType.Int)) 34:      .Parameters("@CurrentPage").Value = CurrentPageNumber 35:      .Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int)) 36:      .Parameters("@PageSize").Value = myDataGrid.PageSize 37:      .Parameters.Add(new SqlParameter("@TotalRecs", SqlDbType.Int)) 38:      .Parameters("@TotalRecs").Direction = ParameterDirection.Output 39:    End With 40: 41:    Try 42:     myConnection.Open() 43:     myReader = myCommand.ExecuteReader() 44:     myDataGrid.DataSource = myReader 45:     myDataGrid.DataBind() 46: 47:    Finally 48:     myConnection.Close() 49:    End Try 50: 51:    CurrentPage.Text = CurrentPageNumber.ToString() 52: 53:    If Not Page.IsPostBack Then 54:     Dim Total_Records As Integer = CInt(myCommand.Parameters("@TotalRecs").Value) 55:     Dim Total_Pages As Decimal = Decimal.Parse(Total_Records.ToString())/ graphics/ccc.gif myDataGrid.PageSize 56:     TotalPages.Text = (System.Math.Ceiling( graphics/ccc.gif Double.Parse(Total_Pages.ToString()))).ToString() 57:    End If 58: 59:    Select Case CurrentPageNumber 60:     Case 1 61:      PreviousPage.Enabled = False 62:      NextPage.Enabled = True 63:     Case Int32.Parse(TotalPages.Text) 64:      NextPage.Enabled = False 65:      PreviousPage.Enabled = True 66:     Case Else 67:      PreviousPage.Enabled = True 68:      NextPage.Enabled = True 69:    End Select 70: 71:   End Sub 72: 73:   Sub NavigationLink_OnClick(Sender As Object, E As CommandEventArgs) 74:    Select Case e.CommandName 75:     Case "First" 76:      CurrentPageNumber = 1 77: 78:     Case "Last" 79:      CurrentPageNumber = Int32.Parse(TotalPages.Text) 80: 81:     Case "Next" 82:      CurrentPageNumber = Int32.Parse(CurrentPage.Text) +1 83: 84:     Case "Prev" 85:      CurrentPageNumber = Int32.Parse(CurrentPage.Text) -1 86: 87:     End Select 88: 89:     BindData() 90: 91:   End Sub 92: 93: End Class [Code Behind C# - 06.09.cs] 01: using System; 02: using System.Web; 03: using System.Web.UI; 04: using System.Web.UI.WebControls; 05: using System.Data; 06: using System.Data.SqlClient; 07: 08: public class Listing0609 : Page{ 09: 10:   protected DataGrid myDataGrid; 11:   protected Label CurrentPage; 12:   protected Label TotalPages; 13:   protected LinkButton NextPage; 14:   protected LinkButton PreviousPage; 15:   protected LinkButton FirstPage; 16:   protected LinkButton LastPage; 17:   protected int CurrentPageNumber; 18: 19:   protected void Page_Load(Object sender, EventArgs e){ 20:    if (!Page.IsPostBack){ 21:      CurrentPageNumber = 1; x22:      BindData(); 23:    } 24:   } 25: 26:   protected void BindData(){ 27:    SqlConnection myConnection = new SqlConnection( graphics/ccc.gif "server=localhost;database=Northwind;uid=sa;pwd=;"); 28:    SqlCommand myCommand = new SqlCommand("spGet_Customers_By_Page", myConnection); 29:    SqlDataReader myReader = null; 30: 31: 32:    myCommand.CommandType = CommandType.StoredProcedure; 33:    myCommand.Parameters.Add(new SqlParameter("@CurrentPage", SqlDbType.Int)); 34:    myCommand.Parameters["@CurrentPage"].Value = CurrentPageNumber; 35:    myCommand.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int)); 36:    myCommand.Parameters["@PageSize"].Value = myDataGrid.PageSize; 37:    myCommand.Parameters.Add(new SqlParameter("@TotalRecs", SqlDbType.Int)); 38:    myCommand.Parameters["@TotalRecs"].Direction = ParameterDirection.Output; 39: 40: 41:    try{ 42:     myConnection.Open(); 43:     myReader = myCommand.ExecuteReader(); 44:     myDataGrid.DataSource = myReader; 45:     myDataGrid.DataBind(); 46:    } 47:    finally{ 48:     myConnection.Close(); 49:    } 50: 51:    CurrentPage.Text = CurrentPageNumber.ToString(); 52: 53:    if (!Page.IsPostBack){ 54:     int Total_Records = ((int) myCommand.Parameters["@TotalRecs"].Value); 55:     decimal Total_Pages = Decimal.Parse(Total_Records.ToString())/ graphics/ccc.gif myDataGrid.PageSize; 56:     TotalPages.Text = (System.Math.Ceiling(Double.Parse( graphics/ccc.gif Total_Pages.ToString()))).ToString(); 57:    } 58: 59:    if(CurrentPageNumber == 1){ 60:     PreviousPage.Enabled = false; 61:     NextPage.Enabled=true; 62:    } else{ 63:     if(CurrentPageNumber == Int32.Parse(TotalPages.Text)){ 64:      NextPage.Enabled=false; 65:      PreviousPage.Enabled=true; 66:     } else{ 67:      PreviousPage.Enabled=true; 68:      NextPage.Enabled=true; 69:     } 70:    } 71:   } 72: 73:   protected void NavigationLink_OnClick(Object sender, CommandEventArgs e){ 74:    switch (e.CommandName){ 75:     case "First": 76:      CurrentPageNumber = 1; 77:      break; 78:     case "Last": 79:      CurrentPageNumber = Int32.Parse(TotalPages.Text); 80:      break; 81:     case "Next": 82:      CurrentPageNumber = Int32.Parse(CurrentPage.Text) +1; 83:      break; 84:     case "Prev": 85:      CurrentPageNumber = Int32.Parse(CurrentPage.Text) -1; 86:      break; 87:     } 88: 89:     BindData(); 90: 91:   } 92: 93: } [Web Form VB] <%@ Page Inherits="Listing0609" src="06.09.vb" %> [Web Form VB] <%@ Page Inherits="Listing0609" src="06.09.cs" %> [Web Form VB & C#] 01: <html> 02: <head> 03:  <style ref="stylesheet" type="text/css"> 04:    .tableItem { font: x-small Verdana, Arial, sans-serif;} 05:    .tableHeader { font: bold small Arial; color:#663300; background-color:#CCCC66;} 06:    .alternatingItem { font: x-small Verdana, Arial, sans-serif; background-color: graphics/ccc.gif #FFFFCC;} 07:    A { color:#663300} 08:    A:hover { color:red} 09:    .pageLinks { font: bold x-small Verdana, Arial, sans-serif;} 10:  </style> 11: </head> 12: <body> 13: <form runat="server" method="post"> 14:  <asp:DataGrid runat="server" id="myDataGrid" 15:   Width="740" 16:   Cellpadding="4" 17:   Cellspacing="0" 18:   Gridlines="Horizontal" 19:   HorizontalAlign="Center" 20:   HeaderStyle-CssClass="tableHeader" 21:   ItemStyle-CssClass="tableItem" 22:   AlternatingItemStyle-CssClass="alternatingItem" 23:   AllowPaging="True" 24:   AllowCustomPaging="True" 25:   PageSize="10" 27:  /> 28:  <center> 29:  <p class="pageLinks"> 30:  <b>Page 31:  <asp:Label id="CurrentPage" CssClass="pageLinks" runat="server" /> 32:  of 33:  <asp:Label id="TotalPages" CssClass="pageLinks" runat="server" /> 34:  </p> 35:  <asp:LinkButton runat="server" CssClass="pageLinks" 36:    id="FirstPage" Text="[First Page]" 37:    OnCommand="NavigationLink_OnClick" CommandName="First" /> 38:  <asp:LinkButton runat="server" CssClass="pageLinks" 39:    id="PreviousPage" Text="[Previous Page]" 40:    OnCommand="NavigationLink_OnClick" CommandName="Prev" /> 41:  <asp:LinkButton runat="server" CssClass="pageLinks" 42:    id="NextPage" Text="[Next Page]" 43:    OnCommand="NavigationLink_OnClick" CommandName="Next" /> 44:  <asp:LinkButton runat="server" CssClass="pageLinks" 45:    id="LastPage" Text="[Last Page]" 46:    OnCommand="NavigationLink_OnClick" CommandName="Last" /> 47:  </center> 48: </form> 49: </body> 50: </html> 

In Listing 6.9, you create a Web Form that calls to the stored procedure in Listing 6.8. The stored procedure returns one page of records based on the PageSize property of the DataGrid and the page number being requested.

On line 11 of the code behind class, you create a page-level variable named CurrentPage . This variable holds the page number of the page of data being requested. In the Page_Load() event handler, you specify the CurrentPage value as 1 only when the request is not a post-back; that is, when the request is the first visit to the page. Any clicks on the page-navigation links will trigger a post-back, and the CurrentPage value will be set in the BindData() method.

Lines 26 “71 are the BindData() method. Here you create a SqlCommand and a SqlDataReader to retreive the data from the database. The SqlCommand executes the stored procedure, which requires setting the SqlCommand.CommandType property to CommandType.StoredProcedure . You create and set the input parameters on lines 32 “36. On lines 41 “46, in a Try / Finally block, you open the SqlConnection and use the SqlCommand.ExecuteReader() method to execute the stored procedure and create a SqlDataReader stream of data. You set the DataGrid.DataSource property to the SqlDataReader , and call the DataGrid.DataBind() method to bind the data in the SqlDataReader to the DataGrid . You now have one page of data displayed in the DataGrid .

Next, you add values for page navigation. Just below the DataGrid but above the page-navigation links, you'll display a page counter indicating the page number the user is viewing and the total number of pages. On line 51, you set the Text property of an ASP.NET Label control to the value of CurrentPage . Next, you set the TotalPages.Text property (also a Label control), but only on the first page request. For this example, you're safe in assuming that the total number of records won't change as you navigate through the pages, so it only needs to be set on the first request. ViewState will maintain the TotalPages.Text value on subsequent page requests .

In the stored procedure, you return the total number of records ( @TotalRecs ), not the total number of pages. Although you could add the math into the stored procedure and return the total number of pages, you might want the versatility of having the total number of records available. The code in Listing 6.9 doesn't take advantage of the @TotalRecs value, in terms of displaying it to the user, but it does use the value to calculate the total number of pages, which is displayed to the user.

Using the System.Math class, you make a call to the Ceiling() method. This method takes a Double data type as its input parameter and returns the same value rounded up to the nearest whole number equal to or greater than the value passed in. For example, calling Ceiling(1.2) would return the value 2 . The value returned is also a Double data type. Since it's likely that the total number of records won't be divisible evenly by the DataGrid.PageSize property, you can use the Ceiling() method to round it up to the nearest whole number. For example, say there are 91 records in the Customers table and the PageSize property is 10. The result of dividing the number of records by the PageSize value is 9.1. There are nine full pages and one extra record. Really, what you want is 10 pages with only one record on the last page. Using Ceiling() , 9.1 gets rounded to 10, which is the result you want.

Before exiting the BindData() method, you check the CurrentPage number and enable or disable the navigation links as appropriate. You're using ASP.NET LinkButton s for the navigation control, which allows you to set the Enabled property. A value of True (the default) will make the link perform as expected and trigger a post-back event. A value of False will render the text of the link, but it won't provide the HTML to trigger a post-back.

Before you add all of the server controls to the Web Form, you need to create an event handler for the LinkButton click event. On line 73, you create the NavigationLink_OnClick() event handler, which will work for all four LinkButton s. With each LinkButton , you'll add a CommandName property ( First , Last , Next , Prev ). The event handler evaluates the CommandName ( e.CommandName ) and sets the CurrentPage value accordingly before calling the BindData() method.

Once the event handler for the navigation controls is in place, you finish the Web Form by adding the server controls. With the DataGrid , you set AllowPaging to True to enable the PageSize property, and you set AllowCustomPaging to True to disable the built-in paging functionality. On line 26 of the Web Form, you specify the PageSize property (10 in this example), and on line 27, you set the PagerStyle-Visible property to False to hide the standard DataGrid navigation.

On lines 32 and 34, you add two Label controls for the CurrentPage value and the TotalPages value set previously. Next you add four LinkButtons : one for the first page, one for the previous page, one for the next page, and one for the last page. Each LinkButton control specifies the NavigationLink_OnClick() event handler in the OnCommand property, specifying that the NavigationLink_OnClick() event handler should be called when the page posts back. The CommandName properties are set according to the Select Case evaluator ( switch in C#) in the event handler.

The Web Form built in Listing 6.9 will render a page with a table of 10 records and four navigation links. Figure 6.8 shows the rendered page from Listing 6.9.

Figure 6.8. The custom paging options available with the DataGrid allow you to build any type of paging that's appropriate for your application.
graphics/06fig08.gif
only for RuBoard


Programming Data-Driven Web Applications with ASP. NET
Programming Data-Driven Web Applications with ASP.NET
ISBN: 0672321068
EAN: 2147483647
Year: 2000
Pages: 170

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net