Paging In ASP.NET, Part 2

By Andrew Merlino

In Part 1 we examined the first event handler in the code-behind class, the Page_Load event handler. In this part we continue our examination of the code-behind class.

- continued -

The SetSorting() Method

The next method in the code-behind class we'll examine is the SetSorting() method. You may have noticed this function was called from within the query in the Page_Load event handler. This function simply returns the column to order the results by. The first part of the function simply determines if the user has selected a column by which to sort the records. If not, it uses the specified default column by which to sort the records:

If Request.Form("SortBy") Is Nothing Then Return DefaultSortColumn & " DESC"

If the user has chosen a column, the function returns the column selected by the user. SetSorting() uses a regular expression to strip any non-alphanumeric characters from the column name and sort direction passed by the user. This is a crucial security step, since the strings in question are being used in a SQL query. Failing to perform this check can permit a devastating SQL Injection attack (see Protecting Yourself From SQL Injection for more information on this topic):

Dim myRegExp As RegEx Dim EditedSortBy As String Dim EditedSortDir As String myRegExp = New RegEx("[^A-Za-z0-9_]") EditedSortBy = myRegExp.Replace(Request.Form("SortBy"),"") EditedSortDir = myRegExp.Replace(Request.Form("SortDir"),"") Return EditedSortBy & " " & EditedSortDir End If

The `SetChangeFlag` Event Handler

The SetChangeFlag event handler handles OnSelectedIndexChanged events. These events are triggered whenever the user changes the various display options in Paging.aspx (which are listed in various DropDownList Web controls). For example, if the user changes the number of records that should be displayed per page or changes how the records should be sorted, the SetChangeFlag routine is called and sets the DisplayChanged variable to a value of True. If the user then attempts to navigate to a different page, this flag tells the ChangePage routine (which we will discuss next) to display the first page of records again with the newly selected options (number of records per page, sorting, etc.). This flag is important because if any of the options change, then the pagination of the records is changed as well. Returning to the first page of records guarantees that the user is seeing a consistent, accurate representation of the records at all times.

Examining the `ChangePage` Event Handler

The ChangePage event handler handles OnClick and OnSelectedIndexChanged events triggered whenever the user has clicked a page navigation link (first, previous, next, last), has selected a specific page to which to jump, or has clicked the "Update Display" button after changing display options. The routine is able to handle multiple events (while distinguishing between them) by taking advantage of the sender argument that all ASP.NET control events pass. By converting the sender argument to a string, we are able to determine which server control in Paging.aspx triggered the ChangePage routine:

Sub ChangePage(sender As Object, e As EventArgs) Dim WhichSender As String = sender.ToString()

If the DisplayChanged flag is True or if the "Update Display" button has been clicked, the Paging() method is called. Two arguments are passed to the routine: the first tells the routine to display the first page of records again (since the display options from Paging.aspx have been changed); the second passes the number of records to display on each page:

If DisplayChanged Or WhichSender = "System.Web.UI.WebControls.Button" Then Paging(1, CType(Request.Form("SelectPageSize"),Integer))

If the display options have not changed, then the ChangePage event handler determines if one of the page navigation links was clicked or if a specific page was selected from the drop down list of pages. Again, two arguments are passed to the Paging() method. The first passes the page number that needs to be displayed. The page number is retrieved from the CommandArgument property of the LinkButton that was clicked or from the selected index property of the drop down list. (Notice that the selected index is increased by one, to account for the fact that the selected index is numbered from 0, while the pages of records are numbered from 1.) The second argument passes the number of records that must be displayed on each page:

Else If WhichSender = "System.Web.UI.WebControls.LinkButton" Then Paging(CType(sender.CommandArgument,Integer), _ CType(Request.Form("SelectPageSize"),Integer)) Else Paging(CType(sender.SelectedIndex + 1,Integer), _ CType(Request.Form("SelectPageSize"),Integer)) End If End If End Sub

Paging the Records with the `Paging()` Method

The Paging() method performs the heavy-lifting for the ASP.NET Web page - it retrieves precisely the records that are to be displayed on the selected page. It is called both on the initial load of Paging.aspx and every time the user navigates to a new page or changes the display options. The routine accepts two arguments, both optional. (These arguments were discussed above.) By default, the Paging() method displays the first page of records and includes 10 records per page.

The first block of code in the routine initializes some essential variables. First, the total number of records is determined by using the Count property of the ArrayList we created in the Page_Load event handler:

Sub Paging(Optional WhichPage As Integer = 1,Optional RecordsPerPage As Integer = 10) Dim NumItems As Integer = IDList.Count

Next, we determine the number of complete pages by dividing the number of records by the number of records per page. Note that the \ operator is used for integer division, as opposed to the /:

Dim PageSize As Integer = RecordsPerPage Dim Pages As Long = NumItems \ PageSize

Finally, we save the number of complete pages in a separate variable (we'll need this number later) and determine if there are leftover records using the Mod operator. If there are leftover records, we increase the page count by one.

Dim WholePages As Long = NumItems \ PageSize Dim Leftover As Integer = NumItems Mod PageSize If Leftover > 0 Then Pages += 1 End If

The next block of code determines which page is currently being displayed and makes sure the selected page is within the valid range of pages. (Invalid pages can only be selected if the user alters the HTML in Paging.aspx and intentionally posts invalid page numbers to the server. From a security standpoint, it is always best to be a little paranoid.) If the selected page is not within the valid range, the Paging() method is called again and reset to the first page:

Dim i As Integer Dim CurrentSelection As String Dim StartOfPage As Integer Dim EndOfPage As Integer Dim CurrentPage As Integer = WhichPage If CurrentPage > Pages Or CurrentPage < 0 Then Paging(1, RecordsPerPage)

If the selected page is within the valid range, various properties of the page navigation LinkButtons are set. If the current page is the last page, the "next" and "last" links are hidden. Otherwise, the "next" and "last" links are displayed and the CommandArgument property of each is set to pass the correct page to the ChangePage event handler. (The CommandArgument is passed to the ChangePage event handler that handles the Click events of the LinkButtons.) If the current page is the first page, the "previous" and "first" links are hidden. Otherwise, they are displayed and the CommandArgument property of each is set to the pass the correct page:

Else If CurrentPage = Pages Then NextLink.Visible = false LastLink.Visible = false Else NextLink.Visible = true LastLink.Visible = true NextLink.CommandArgument = CurrentPage + 1 LastLink.CommandArgument = Pages End If If CurrentPage = 1 Then PreviousLink.Visible = false FirstLink.Visible = false Else PreviousLink.Visible = true FirstLink.Visible = true PreviousLink.CommandArgument = CurrentPage - 1 FirstLink.CommandArgument = 1 End If

Now that we have our page navigation links in place, we need to populate the page selection drop down list with the proper range of pages. We do this by filling a new ArrayList with the sequence of available pages. We then use databinding to bind this list of pages to the drop down list control. Finally, we set the SelectedIndex property of the control so that the current page is selected:

Dim JumpPageList = new ArrayList Dim x As Integer For x = 1 To Pages JumpPageList.Add(x) Next JumpPage.DataSource = JumpPageList JumpPage.Databind() JumpPage.SelectedIndex = CurrentPage - 1

The next block of code simply sets the Text property of two Label controls to display the total number of records and number of pages to the user:

RecordCountLabel.Text = NumItems PageCountLabel.Text = Pages

With the navigation and information above being displayed, the routine can now determine which records should be retrieved from the database for the current page. Remember that the IDList ArrayList contains all the primary key values available. We need to determine which subset of these values belongs to the current page. We do this using the following calculations:

StartOfPage = PageSize * (CurrentPage - 1) EndOfPage = Min( (PageSize * (CurrentPage - 1)) + (PageSize - 1), _ ((WholePages * PageSize) + LeftOver - 1) )

The StartOfPage and EndOfPage are actually the starting and ending indices of the ArrayList that delimit the selected page. We subtract 1 in the calculations to account for the fact that the ArrayList is numbered starting at 0, while the pages are numbered from 1. Calculating the StartOfPage is relatively easy. The EndOfPage calculation is a bit more complex. Because the actual number of records available to display on the last page of data may be less than the number of records the user has selected to view per page, we use the Min function to retrieve whichever figure is less: the theoretical ending index of the page or the total number of records available minus 1 (to account for the zero-based nature of the ArrayList).

To retrieve the subset of values delimited by the indices above, we use the GetRange() method to select the correct subset, convert this subset to an array, and then join the values into a comma-delimited string:

Dim CurrentSubset As String = Join( IDList.GetRange( StartOfPage , (EndOfPage - StartOfPage + 1) ).ToArray , "," )

(For those of you unfamiliar with the GetRange() method, it accepts two arguments: the starting index and the number of items, starting from this index, to retrieve. Thanks to Scott Mitchell for suggesting the ToArray()/Join combination to easily convert the subset to a string.)

At this point, we have the string of primary key values that comprise the selected page. All we need to do is retrieve the records that correspond to these values from the table and bind them to the Repeater control in Paging.aspx. This is easily accomplished using an IN clause in our query and another SqlDataReader:

Dim Conn As SqlConnection Dim Query As String Dim SqlComm As SqlCommand Conn = New SqlConnection(ConnString) Query = "SELECT " & ColumnsToRetrieve & " FROM " & TableName & _ " WHERE " & PrimaryKeyColumn & " IN ('" & _ CurrentSubSet.Replace(",","','") & _ "') ORDER BY " & SetSorting() SqlComm = New SqlCommand(Query,Conn) Conn.Open() myRepeater.DataSource = SqlComm.ExecuteReader() myRepeater.Databind() Conn.Close() Conn = Nothing End If End Sub

There are two important points to note in the code above. First, we use the Replace() method to add single quotes around the primary key values in CurrentSubSet. This prevents the query from failing if the primary key values are not integers. (Remember we are trying to avoid any assumptions about the primary key column that will limit the usefulness of our paging technique.) Second, the query is sorted using the SetSorting routine discussed earlier. By sorting the primary key values and the actual records we're retrieving by the same criteria, we allow these records to be sorted by any column in the table. Our paging technique does not require that the records be sorted by the primary key.

Conclusion

Using the code above, you can implement a paging technique that will work with any of the data controls or with no control at all. (All you would have to do is bind the records retrieved in the Paging() method to a different control or store them in a DataReader/DataSet and loop through and display them yourself.) The code is a bit more complex then the DataGrid's paging methods, but infinitely more flexible, accurate, efficient, and extensible.

For more information on paging in ASP.NET, be sure to check out Creating a Generic Page Control.

Happy Programming!

By Andrew Merlino

Attachments

Download the complete source code (in ZIP format)