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.
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:
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:
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.
