When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article
Jobs

ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
 
Print this Page!
Published: Wednesday, September 10, 2003

Paging In ASP.NET, Part 2

By Andrew Merlino


  • Read Part 1

  • 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)


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article