ASP.NET 4 in VB 2010 : The Data Controls - Sorting and Paging the GridView

5/23/2013 8:11:39 PM

The GridView is a great all-in-one solution for displaying all kinds of data, but it becomes a little unwieldy as the number of fields and rows in your data source grows. Dense grids contribute to large pages that are slow to transmit over the network and difficult for the user to navigate. The GridView has two features that address these issues and make data more manageable: sorting and paging.

Both sorting and paging can be performed by the database server, provided you craft the right SQL using the Order By and Where clauses. In fact, sometimes this is the best approach for performance. However, the sorting and paging provided by the GridView and SqlDataSource is easy to implement and thoroughly flexible. These techniques are particularly useful if you need to show the same data in several ways and you want to let the user decide how the data should be ordered.

1. Sorting

The GridView sorting features allow the user to reorder the results in the GridView by clicking a column header. It's convenient—and easy to implement.

Although you may not realize it, when you bind to a DataTable, you actually use another object called the DataView. The DataView sits between the ASP.NET web page binding and your DataTable. Usually it does little aside from providing the information from the associated DataTable. However, you can customize the DataView so it applies its own sort order. That way, you can customize the data that appears in the web page, without needing to actually modify your data.

You can create a new DataView object by hand and bind the DataView directly to a data control such as the GridView. However, the GridView and SqlDataSource controls make it even easier. They provide several properties you can set to control sorting. Once you've configured these properties, the sorting is automatic, and you still won't need to write any code in your page class.

To enable sorting, you must set the GridView.AllowSorting property to True. Next, you need to define a SortExpression for each column that can be sorted. In theory, a sort expression takes the form used in the ORDER BY clause of a SQL query and can use any syntax that's understood by the data source control. In practice, you'll almost always use a single field name to sort the grid using the data in that column. For example, here's how you could define the ProductName column so it sorts by alphabetically ordering rows:

<asp:BoundField DataField="ProductName" HeaderText="Product Name"
 SortExpression="ProductName" />

Note that if you don't want a column to be sort-enabled, you simply don't set its SortExpression property. Figure 1 shows an example with a grid that has sort expressions for all three columns, and is currently sorted by product name.

Figure 1. Sorting the GridView

Once you've associated a sort expression with the column and set the AllowSorting property to True, the GridView will render the headers with clickable links, as shown in Figure 16-10. However, it's up to the data source control to implement the actual sorting logic. How the sorting is implemented depends on the data source you're using.

Not all data sources support sorting, but the SqlDataSource does, provided the DataSourceMode property is set to DataSet (the default), not DataReader. In DataReader mode, the records are retrieved one at a time, and each record is stuffed into the bound control (such as a GridView) before the SqlDataSource moves to the next one. In DataSet mode, the entire results are placed in a DataSet and then the records are copied from the DataSet into the bound control. If the data needs to be sorted, the sorting happens between these two steps—after the records are retrieved but before they're bound in the web page.


The sort is according to the data type of the column. Numeric and date columns are ordered from smallest to largest. String columns are sorted alphanumerically without regard to case. Columns that contain binary data cannot be sorted. However, if you click a column header twice, the second click reverses the sort order, putting the records in descending order. (Click a third time to switch it back to ascending order.)

1.1. Sorting and Selecting

If you use sorting and selection at the same time, you'll discover another issue. To see this problem in action, select a row, and then sort the data by any column. You'll see that the selection will remain, but it will shift to a new item that has the same index as the previous item. In other words, if you select the second row and perform a sort, the second row will still be selected in the new page, even though this isn't the record you selected.

To fix this problem, you must simply set the GridView.EnablePersistedSelection property True. Now, ASP.NET will ensure that the selected item is identified by its data key. As a result, the selected item will remain selected, even if it moves to a new position in the GridView after a sort.

2. Paging

Often, a database search will return too many rows to be realistically displayed in a single page. If the client is using a slow connection, an extremely large GridView can take a frustrating amount of time to arrive. Once the data is retrieved, the user may find out it doesn't contain the right content anyway or that the search was too broad and they can't easily wade through all the results to find the important information.

The GridView handles this scenario with an automatic paging feature. When you use automatic paging, the full results are retrieved from the data source and placed into a DataSet. Once the DataSet is bound to the GridView, however, the data is subdivided into smaller groupings (for example, with 20 rows each), and only a single batch is sent to the user. The other groups are abandoned when the page finishes processing. When the user moves to the next page, the same process is repeated—in other words, the full query is performed once again. The GridView extracts just one group of rows, and the page is rendered.

To allow the user to skip from one page to another, the GridView displays a group of pager controls at the bottom of the grid. These pager controls could be previous/next links (often displayed as < and >) or number links (1, 2, 3, 4, 5, . . . ) that lead to specific pages. If you've ever used a search engine, you've seen paging at work.

By setting a few properties, you can make the GridView control manage the paging for you. Table 1 describes the key properties.

Table 1. Paging Members of the GridView
AllowPagingEnables or disables the paging of the bound records. It is False by default.
PageSizeGets or sets the number of items to display on a single page of the grid. The default value is 10.
PageIndexGets or sets the zero-based index of the currently displayed page, if paging is enabled.
PagerSettingsProvides a PagerSettings object that wraps a variety of formatting options for the pager controls. These options determine where the paging controls are shown and what text or images they contain. You can set these properties to fine-tune the appearance of the pager controls, or you can use the defaults.
PagerStyleProvides a style object you can use to configure fonts, colors, and text alignment for the paging controls.
PageIndexChanging and PageIndexChanged eventsOccur when one of the page selection elements is clicked, just before the PageIndex is changed (PageIndexChanging) and just after (PageIndexChanged).

To use automatic paging, you need to set AllowPaging to True (which shows the page controls), and you need to set PageSize to determine how many rows are allowed on each page.

Here's an example of a GridView control declaration that sets these properties:

<asp:GridView ID="GridView1" runat="server" DataSourceID="sourceProducts"
 PageSize="10" AllowPaging="True" >

This is enough to start using paging. Figure 2 shows an example with ten records per page (for a total of eight pages).

Figure 2. Paging the GridView Paging and Selection

By default, paging and selection don't play nicely together. If you enable both for the GridView, you'll notice that the same row position remains selected as you move from one page to another. For example, if you select the first row on the first page and then move to the second page, the first row on the second page will become selected. To fix this quirk, set the GridView.EnablePersistedSelection property to True. Now, as you move from one page to another, the selection will automatically be removed from the GridView (and the SelectedIndex property will be set to −1). But if you move back to the page that held the originally selected row, that row will be re-selected. This behavior is intuitive, and it neatly ensures that your code won't be confused by a selected row that isn't currently visible.


When you use paging, every time a new page is requested, the full DataSet is queried from the database. This means paging does not reduce the amount of time required to query the database. In fact, because the information is split into multiple pages and you need to repeat the query every time the user moves to a new page, the database load actually increases. However, because any given page contains only a subset of the total data, the page size is smaller and will be transmitted faster, reducing the client's wait. The end result is a more responsive and manageable page.

You can use paging in certain ways without increasing the amount of work the database needs to perform. One option is to cache the entire DataSet in server memory. That way, every time the user moves to a different page, you simply need to retrieve the data from memory and rebind it, avoiding the database altogether.

  •  ASP.NET State Management : The View State of a Page (part 4) - Keeping the View State on the Server
  •  ASP.NET State Management : The View State of a Page (part 3) - Changes in the ASP.NET View State
  •  ASP.NET State Management : The View State of a Page (part 2) - Programming Web Forms Without View State
  •  ASP.NET State Management : The View State of a Page (part 1) - The StateBag Class, Common Issues with View State
  •  ASP.NET State Management : Customizing Session State Management
  •  Top 10 Web Log Analysers (Part 2)
  •  Top 10 Web Log Analysers (Part 1)
  •  ASP.NET 4 in VB 2010 : The Data Controls - Editing with the GridView
  •  ASP.NET 4 in VB 2010 : The Data Controls - Selecting a GridView Row
  •  Microsoft Content Management Server Development : Building SharePoint Web Parts - Building the Presentation Layer
    Top 10
    Review : Sigma 24mm f/1.4 DG HSM Art
    Review : Canon EF11-24mm f/4L USM
    Review : Creative Sound Blaster Roar 2
    Review : Philips Fidelio M2L
    Review : Alienware 17 - Dell's Alienware laptops
    Review Smartwatch : Wellograph
    Review : Xiaomi Redmi 2
    Extending LINQ to Objects : Writing a Single Element Operator (part 2) - Building the RandomElement Operator
    Extending LINQ to Objects : Writing a Single Element Operator (part 1) - Building Our Own Last Operator
    3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
    Popular Tags
    Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8