Creating a Pageable, Sortable DataGrid

 

Scott Mitchell

August 2003

Applies to:
    Microsoft® ASP.NET

Summary: Learn how to build a sortable DataGrid and a pageable DataGrid; learn the steps necessary to combine these two techniques into creating a single sortable, pageable DataGrid. The DataGrid Web control allows ASP.NET developers the ability to quickly and easily display data. In addition to being able to simply display data, the DataGrid also allows for sorting, paging, and editing of its data. While adding sorting or paging support to the DataGrid is relatively straightforward, creating both a pageable and sortable DataGrid is a bit more challenging. (18 printed pages)

Download PageableSortableDataGridDemo.msi.

Contents

Introduction
Creating a Sortable DataGrid
Creating a Pageable DataGrid
Creating a Sortable, Pageable DataGrid
Conclusion

Introduction

One of the most useful aspects of Microsoft® ASP.NET is the ease with which data can be displayed in a Web page. ASP.NET contains three data Web controls—the DataGrid, DataList, and Repeater—each designed to allow for rich data display. The most commonly used of the three data Web controls is the DataGrid, due in large part to its handy built-in feature set. Specifically, with just setting a few properties and creating a couple of event handlers, the DataGrid can provide sorting, paging, or editing support. For more information on the differences among the three data Web controls, along with what control to choose when, be sure to read, Deciding When to Use the DataGrid, DataList or Repeater.

While creating either a sortable or pageable DataGrid is straightforward, creating a DataGrid that is both pageable and sortable is a bit more of a challenge. In this article we'll start by examining how to create a sortable and pageable DataGrid, and then examine the steps needed to combine these two features into one DataGrid. The first two sections examine how to add sorting functionality and paging functionality to the DataGrid Web control; if you are already well-versed in this area, feel free to skip ahead to the final section, which ties these two features together in a single DataGrid.

Creating a Sortable DataGrid

Before we get started with creating a sortable DataGrid, we first need a DataGrid that simply displays some data. For the purposes of this article, let's create a simple DataGrid that displays the contents of theProductstable in the Northwind database.

Note   The Northwind database is a standard database shipped with Microsoft database products like Microsoft SQL Server(TM) and Microsoft Access.

To follow along, create a new ASP.NET Web page namedSortableDataGrid.aspxand start by adding a DataGrid with itsIDproperty set to dgProducts. Next, set theAutoGenerateColumnsproperty to False and configure the DataGrid so that it uses three BoundColumns to display the ProductName, UnitPrice andUnitsInStockfields. All that remains left to be done is to query the database and bind the results to the DataGrid. The following code shows thePage Loadevent handler and a custom method, BindData(), which should be added to the code-behind class to get everything working.

private void Page_Load(object sender, System.EventArgs e)
{
   BindData();
}

private void BindData()
{
   // Connect to the Database
   SqlConnection myConnection = new SqlConnection(connection string);

   // Retrieve the SQL query results and bind it to the Repeater
   string SQL_QUERY = "SELECT ProductName, UnitPrice, UnitsInStock " +
                      "FROM Products";
   SqlCommand myCommand = new SqlCommand(SQL_QUERY, myConnection);

   myConnection.Open();
   dgProducts.DataSource = myCommand.ExecuteReader();
   dgProducts.DataBind();
   myConnection.Close();
}

Note that in order for this code to work you need to import theSystem.Data.SqlClientnamespace in your code-behind class. You will also have to change the connection string in theSqlConnectionconstructor to the connection string to your database. Once you have entered this code, build the solution and then test the Web page out by visiting it through your Web browser. Figure 1 shows a screenshot of SortableDataGrid.aspx (of course the appearance of the DataGrid could easily be made more aesthetically appealing by using the Auto Format tool in Microsoft Visual Studio® .NET).

Aa479006.aspnet-pageablesortable-01(en-us,MSDN.10).gif

Figure 1. SortableDataGrid.aspx, when viewed through a browser

Making the DataGrid inSortableDataGrid.aspxsortable, starts with setting the DataGrid'sAllowSortingproperty to True. When this property is set the True, the DataGrid renders the header of each column as a LinkButton, which has the effect of rendering each column header as a hyperlink. The end user can specify what column she wants the DataGrid results sorted by simply by clicking on the appropriate column header's hyperlink.

When a column header's hyperlink is clicked, the ASP.NET Web page is posted back and the DataGrid'sSortCommandevent fires. It is our responsibility, as the developer of this ASP.NET Web page, to create an event handler for the DataGrid'sSortCommandevent and to wire up the DataGrid'sSortCommandevent to this created event handler. It is the responsibility of this event handler to determine the column the end user wants to have the data sorted by, and then to resort the underlying data and rebind it to the DataGrid.

Before writing the code for theSortCommandevent handler, we must first add the event handler. Using a tool like Visual Studio .NET makes this step unimaginatively simple. Just click on thedgProductsDataGrid in the Designer to edit its properties. Next, click on the lightning bolt icon at the top of the Properties pane to view the DataGrid's events. Scroll to theSortCommandevent and enter the name of the event handler you wish to have associated with this event, Visual Studio .NET will do the rest! Figure 2 shows the event listing in the Properties pane.

Aa479006.aspnet-pageablesortable-02(en-us,MSDN.10).gif

Figure 2. Creating an event handler for the DataGrid's SortCommand event

As Figure 2 shows, I have decided to name this event handler dgProducts_Sort, although any legal function name would have sufficed. Once you enter an event handler name into the appropriate event textbox, Visual Studio .NET automatically creates the event handler function shell in the code-behind class and wires the event to the event handler.

If you are not using Visual Studio .NET, then you will need to perform these two steps manually. Start by creating the shell of the event handler, which can be seen below:

private void dgProducts _Sort(object source,
                              DataGridSortCommandEventArgs e)
{
  // we'll add the code here in a bit!
}

Next, wire up the DataGrid'sSortCommandevent to the event handler. This can be done in one of two ways:

  • By addingOnSortCommand="dgProducts_Sort"in the DataGrid's declaration (that is, adding this inside the<asp:DataGrid>start tag in the HTML portion)
  • By programmatically assigning the DataGrid's event to the event handler in theInitializeComponent()method in the code-behind class. In C# this is accomplished with the following syntax:
dgProducts.SortCommand += 
       new DataGridSortCommandEventHandler(dgProducts_Sort);

With Microsoft Visual Basic® .NET, the syntax looks like:

AddHandler dgProducts.SortCommand, AddressOf dgProducts_Sort

Once the event handler has been created and wired up to the DataGrid'sSortCommandevent, we're ready to add the code for the event handler. Essentially, we need to determine the column by which the end user wants to sort the results, and requery the database to retrieve the results in the desired order. We can determine what column the end user clicked on that caused the postback by examining theSortExpressionproperty of theDataGridSortCommandEventArgsparameter passed into thedgProducts_Sortevent handler.

Realize that each DataGrid column has aSortExpressionvalue associated with it. When the DataGrid'sAutoGenerateColumnsproperty is set to True, each column is automatically assigned aSortExpressionvalue equal to the name of theDataSourcefield the column is displaying. WhenAutoGenerateColumnsis set to False, we must explicitly specify the value of theSortExpressionproperty. If we do not set theSortExpressionproperty for a particular column, then the column header won't display as a hyperlink; therefore, the end user won't be able to sort the DataGrid by that particular column. To demonstrate this, only set theSortExpressionproperties of the first two BoundColumns. While you can set theSortExpressionproperty to any value, set theSortExpressionof each of the first two BoundColumn to the same value as the DataField property. After doing so, your DataGrid declaration should look like the following:

   <asp:DataGrid id="dgProducts" runat="server" AllowSorting="True"
                     AutoGenerateColumns="False" ...>
      <Columns>
         <asp:BoundColumn DataField="ProductName" 
                  HeaderText="Product Name"  
                  SortExpression="ProductName"></asp:BoundColumn>
         <asp:BoundColumn DataField="UnitPrice" HeaderText="Unit Price" 
                  DataFormatString="{0:c}" 
                  SortExpression="UnitPrice"></asp:BoundColumn>
         <asp:BoundColumn DataField="UnitsInStock" 
                  HeaderText="Units In Stock" DataFormatString="{0:d}">
         </asp:BoundColumn>
      </Columns>
   </asp:DataGrid>

Now, in thedgProducts_Sortevent handler we need to determine the value of theSortExpressionand then rebind the data to the DataGrid in the properly sorted order. There are a myriad of ways to accomplish this, the simplest being just to requery the entire data sorted by the field specified by the SortExpression. This way isn't necessarily the most efficient, but is acceptable for theProductstable, which has only 77 records. This requerying can be accomplished by altering theBindData()method to accept a string parameter, the name of the column to sort the results by. This updated version ofBindData()is shown now:

private void BindData(string orderBy)
{
   // Connect to the Database
   SqlConnection myConnection = new SqlConnection(connection string);
         
   // Retrieve the SQL query results and bind it to the DataGrid
   string SQL_QUERY = "SELECT ProductName, UnitPrice, UnitsInStock " +
                      "FROM Products ORDER BY " + orderBy;
   SqlCommand myCommand = new SqlCommand(SQL_QUERY, myConnection);

   myConnection.Open();
   dgProducts.DataSource = myCommand.ExecuteReader();
   dgProducts.DataBind();
   myConnection.Close();
}

With this new version of BindData(), ourdgProducts_Sortevent handler requires only one line of code, which can be seen below:

private void dgProducts_Sort(object source,
          System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{
   BindData(e.SortExpression);
}

All that remains left to do is to update thePage Loadevent handler. First, we only want to call theBindData()method when the page is not being posted back, since on subsequent postbacks, the dgProducts_Sort event handler will make theBindData()call. Second, our Page Load event handler is using the old version of the BindData() method, the one that takes no input parameters—we need to update this so that we pass in the field name we want the DataGrid initially sorted by.

private void Page_Load(object sender, System.EventArgs e)
{
   if (!Page.IsPostBack)
      BindData("ProductName");
}

After all of these changes have been made, build the solution and test it out. Figure 3 showsSortableDataGrid.aspxwhen first visited, while Figure 4 shows the Web page after the user has clicked on the Unit Price column header hyperlink. In both figures, note that the DataGrid cannot be sorted by the Units In Stock column because we did not provide aSortExpressionproperty for this BoundColumn.

Aa479006.aspnet-pageablesortable-03(en-us,MSDN.10).gif

Figure 3. The DataGrid, sorted by the Product Name column

Aa479006.aspnet-pageablesortable-04(en-us,MSDN.10).gif

Figure 4. The DataGrid, sorted by the Unit Price column

Creating a Pageable DataGrid

As with creating a sortable DataGrid, to create a pageable DataGrid it is always best to start by first creating a DataGrid that merely displays the data. Since we accomplished this in the first part of the previous section, I won't touch upon the topic here further than saying create an ASP.NET Web page namedPageableDataGrid.aspxand copy both the DataGrid declaration in the HTML section and the code from thePage_LoadandBindData()methods in the code-behind class from theSortableDataGrid.aspxWeb page prior to the point where we started discussing adding sorting capabilities.

While you can copy in thePage Loadevent handler verbatim, you will need to make a small change to theBindData()method. Specifically, rather than binding aSqlDataReaderto the DataGrid you will need to use either aDataTableor DataSet. We'll discuss why this is the case in a bit.

Before we tackle adding paging support to a DataGrid, it's important to realize that the DataGrid has two forms of paging support:

  • Default paging
  • Custom paging

These models of paging offer a tradeoff: default paging is easier to implement than custom paging, but custom paging provides improved performance. The reason for this tradeoff follows from the fact that with default paging you always bind the entireDataSourcethat you want to page through to the DataGrid each and every time the user navigates from one page of data to another. With default paging, the DataGrid itself is responsible for determining how many records and precisely what records in theDataSourceneed to be displayed. Custom paging, on the other hand, requires that precisely only those records that are to be displayed in the current page of data be in theDataSourcethat is bound to the DataGrid.

Default paging is simpler to implement because you don't have to do anything fancy when a user navigates from one page of data to the next. That is, you just bind the results of a SQL query to the DataGrid and let the DataGrid worry about what records to display. With custom paging, you have to use either tricky SQL statements or complicated stored procedures to pick out the precise set of records you want to display for a particular page. Custom paging offers better performance over default paging because only those records that need to be displayed for a particular page of data are accessed. With default paging, every time a user views a different page of data, all records are retrieved. Additionally, default paging requires that you bind either aDataTableor DataSet object to the DataGrid; that is, you can't use a DataReader. This is because the DataGrid needs to be able to determine how many records are in theDataSourceto be able to determine how many total pages of data exist.

For this article, we'll focus on using default paging, since it is easier to implement. However, we lose nothing by focusing strictly on default paging, as the technique we'll examine in the next section for creating a sortable, pageable DataGrid will work just as well with both default and custom paging.

The first step to making a DataGrid pageable using the default paging model is to set the DataGrid'sAllowPagingproperty to True. This property, when set to True, will, by default, display a navigational interface at the bottom of the DataGrid. This navigational interface allows the end user to step through the pages of data displayed in the DataGrid. By default, the navigational interface uses LinkButtons to render a previous and next set of hyperlinks. When the end user clicks on one of these hyperlinks, the ASP.NET Web page is posted back and the DataGrid'sPageIndexChangedevent fires. It is our job to create an event handler for this event that updates the DataGrid'sCurrentPageIndexproperty and rebinds the data to the DataGrid.

The DataGrid'sCurrentPageIndexproperty indicates what page of data to display. In addition to this property, there are a couple of other properties that are germane to paging:

  • PageSize—This property indicates how many records to show per page, and defaults to a value of 10.
  • PageCount—This property indicates the total number of pages of data.

So, thePageIndexChangedevent handler merely needs to set theCurrentPageIndexproperty to the page selected by the user, and the rebind the DataGrid (by calling BindData()). That's it! We can determine the page index wants to view by referencing theNewPageIndexproperty of theDataGridPageChangedEventArgsobject passed into this event handler.

When the DataGrid's DataBind() method is called in BindData(), the DataGrid will use the CurrentPageIndex property to determine what page of data to display. It will then use the number of records to display per page to compute what record should appear as the first record in the page. The DataGrid will then navigate to this record and display that record and the next PageSize records.

Take a moment to add an event handler nameddgProducts Pageto the DataGrid'sPageIndexChangedevent using the techniques discussed in the previous section. The source code for this event handler is shown below:

   private void dgProducts_Page(object source,
               System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
   {
      dgProducts.CurrentPageIndex = e.NewPageIndex;
      BindData();
   }

Again, as with theSortableDataGrid.aspxWeb page, we want to only have thePage Loadevent handler bind the data to the DataGrid on the first page load and not on subsequent postbacks. The following code shows the updatedPage Loadevent handler andBindData()method. Note that theBindData()method binds aDataTableto the DataGrid, as opposed to a SqlDataReader. Recall that with default paging you cannot use a DataReader, but instead must use aDataTableor DataSet; using a DataReader will result in an exception. The reason you cannot use a DataReader is because the DataGrid needs to be able to determine how many total records are in the DataSource so it can determine how many total pages there are. Since the DataReader supports forward-only access, the DataGrid cannot determine how many total records it contains. Therefore, it is imperative with default paging that an object whose number of records can be determined—such as theDataTableor DataSet—be used.

private void Page_Load(object sender, System.EventArgs e)
{
   if (!Page.IsPostBack)
      BindData();
}

private void BindData()
{
   // Connect to the Database
   SqlConnection myConnection = new SqlConnection(connection string);

   // Retrieve the SQL query results and bind it to the DataGrid
   string SQL_QUERY = "SELECT ProductName, UnitPrice, UnitsInStock " +
                      "FROM Products";
   SqlCommand myCommand = new SqlCommand(SQL_QUERY, myConnection);

   // Use a DataTable – required for default paging
   SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);

   DataTable myTable = new DataTable();
   myAdapter.Fill(myTable);

   dgProducts.DataSource = myTable;
   dgProducts.DataBind();
   myConnection.Close();
}

Figure 5 shows PageableDataGrid.aspx when first visited, showing the first ten records of the Products table. Figure 6 shows the second page of data, which is displayed after the user clicks the > hyperlink in the navigational interface from Figure 5.

Aa479006.aspnet-pageablesortable-05(en-us,MSDN.10).gif

Figure 5. The first page of data is displayed.

Aa479006.aspnet-pageablesortable-06(en-us,MSDN.10).gif

Figure 6. The second page of data is shown.

This demonstration does not show how to customize the navigational interface of the pageable DataGrid. However, this can be easily customized to display alternate text for the next and previous hyperlinks, as well as displaying a list of page numbers rather than next and previous links. In fact, with a bit of coding, you can create a custom paging interface that provides both next and previous links as well as a list of pages. For more information on these matters, be sure to check out the following articles:

Creating a Sortable, Pageable DataGrid

At this point we have looked at how to create a sortable DataGrid and a pageable DataGrid. Let's now turn our attention to melding these two features into a single sortable, pageable DataGrid. The difficulty in merging the two features into a single DataGrid stems from the fact that each approach uses a different form of the BindData() method. The sortable DataGrid passes in a string parameter into BindData(), which indicates what column of the DataGrid to sort by. The pageable DataGrid, on the other hand, does no such thing.

An initial attempt at reconciling this difference is to use the BindData() version that accepts the string parameter and have the DataGrid's PageIndexChanged event handler always pass in some predefined string value. For example, since the PageIndexChanged event handler must call BindData(), passing in a string, we could use the following code for this event handler:

private void dgProducts_Page(object source, 
             System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
   dgProducts.CurrentPageIndex = e.NewPageIndex;
   BindData("ProductName");
}

However, this approach won't work. Consider the following sequence of events:

  1. A user visits the page, and is shown the first page of data, sorted by theProductNamefield.
  2. A user clicks on the Unit Price column header hyperlink, displaying the first page of data sorted by Unit Price.
  3. The user clicks the > navigational hyperlink, requesting the next page of data. This causes thePageIndexChangedevent handler to execute upon postback, which retrieves the data sorted by ProductName. Hence, the user is now viewing the second page of data sorted byProductNameinstead of the second page of data sorted by UnitPrice.

We need some way to remember the field that we want to sort by when callingBindData()from thePageIndexChangedevent handler. There are a number of ways to save state across round trips to the server with ASP.NET; however, since we only need to save this information for the current Web page, and not for the current user, the best approach is to store the column the user wishes to sort by in the Web page's ViewState.

A thorough discussion of ViewState is far beyond the scope of this article. However, let's take a brief moment to discuss its utility for us in creating a sortable, pageable DataGrid. First, realize all controls in an ASP.NET Web page have a ViewState, and when these controls are placed in a Web Form (the <form runat="server">) their ViewState is saved in a hidden HTML form field. We can programmatically insert objects into the ViewState. These inserted objects are then saved along with the rest of the ViewState content, meaning that these values are remembered across postbacks. For a much more detailed look at ViewState be sure to read Susan Warren's excellent article, Taking a Bite Out of ASP.NET ViewState.

The syntax for inserting and retrieving elements into the ViewState is as follows:

// C#
object o = ViewState[key];   // retrieval
ViewState[key] = o;      // assignment

' VB.NET
Dim o as Object = ViewState(key)   ' retrieval
ViewState(key) = o         'assignment

Here, key is a string key providing a name for the object being stored in the ViewState.

Now, whenever the user clicks on a DataGrid column's header hyperlink we'll do three things:

  1. Reset the DataGrid'sCurrentPageIndexto 0
  2. Store theSortExpressionproperty of theDataGridSortCommandEventArgsparameter to the ViewState with key value SortExprValue.
  3. Call BindData(), passing in theSortExpressionvalue.

Realize that step 1 will cause the DataGrid to return to the first page of data whenever the user clicks a DataGrid column's header hyperlink. This makes sense, because if a user is currently viewing, say, page 3 of the results sorted by Product Name, if the user opts to see the results sorted by Unit Price they should be taken to the first page of results, thereby seeing the cheapest items. If we did not resetCurrentPageIndexto 0 in theSortCommandevent handler, then the user, upon opting to sort by Unit Price, would be shown the third page of the data when sorted by theUnitPricefield, which would like be confusing.

This newSortCommandevent handler can be seen below:

private void dgProducts_Sort(object source, 
          System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{
   dgProducts.CurrentPageIndex = 0;
   ViewState["SortExprValue"] = e.SortExpression;
   BindData(e.SortExpression);
}

With this ViewState variable, thePageIndexChangedevent handler becomes very easy to write. We simply set the DataGrid'sPageIndexChangedproperty to theNewPageIndexproperty of the DataGridPageChangedEventArgs, as we did before, and then callBindData()passing in the value of the ViewState object SortExprValue.

private void dgProducts_Page(object source, 
           System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
   dgProducts.CurrentPageIndex = e.NewPageIndex;
   BindData(ViewState["SortExprValue"].ToString());
}

One last final touch is to update thePage_Loadevent handler so that the ViewStateSortExprValueobject is set to the column that we want the DataGrid to be sorted on by default. This small change yields the following newPage_Loadevent handler:

private void Page_Load(object sender, System.EventArgs e)
{
   if (!Page.IsPostBack)
   {
      ViewState["SortExprValue"] = "ProductName";
      BindData(ViewState["SortExprValue"].ToString());
   }
}

And that's all there is to it! The remainder of the article provides some screenshots demonstrating the sortable, pageable DataGrid in action. Note that you can download the complete source code for all of the ASP.NET demos at the end of this article.

Figure 7 shows a screenshot of the sortable, pageable DataGrid when first visited through a browser. Figure 8 shows a screenshot of the second page of data, sorted by Product Name. Figure 9 shows the output immediately after the user opts to order the results by the Unit Price, while Figure 10 shows a screenshot of the second page of data, ordered by Unit Price.

Aa479006.aspnet-pageablesortable-07(en-us,MSDN.10).gif

Figure 7. The sortable, pageable DataGrid, when first visited (sorted by Product Name)

Aa479006.aspnet-pageablesortable-08(en-us,MSDN.10).gif

Figure 8. The second page of data, when sorted by Product Name

Aa479006.aspnet-pageablesortable-09(en-us,MSDN.10).gif

Figure 9. The first page of data, when sorted by Unit Price

Aa479006.aspnet-pageablesortable-10(en-us,MSDN.10).gif

Figure 10. The second page of data, when sorted by Unit Price

Conclusion

Creating either a pageable or sortable DataGrid is relatively simple and straightforward. However, combining both aspects into a single DataGrid is a bit trickier since we need to remember what column the DataGrid is sorted by when paging through the data. As we saw in this article, we can employ the ViewState to help us remember theDataSourcefield by which the DataGrid is ordered. While this article focused on using default paging, the techniques are applicable for custom paging as well.

If you have questions about this article, the techniques discussed, or the code samples examined, I invite you to email me. I can be reached at mitchell@4guysfromrolla.com.

Happy Programming!

About the author

Scott Mitchell, author of five ASP/ASP.NET books and founder of 4GuysFromRolla.com, has been working with Microsoft Web technologies for the past five years. An active member in the ASP and ASP.NET community, Scott is passionate about ASP and ASP.NET and enjoys helping others learn more about these exciting technologies. For more on the DataGrid, DataList, and Repeater controls check out Scott's book ASP.NET Data Web Controls Kick Start (ISBN: 0672325012).

© Microsoft Corporation. All rights reserved.