GridView Examples for ASP.NET 2.0: Paging and Sorting the GridView's Data
Click here to return to the TOC.
When displaying large amounts of data it's often best to only display a portion of the data, allowing the user to step through the data ten or so records at a time. Additionally, the end user's experience can be enhanced if they are able to sort the data by one of the columns. Creating a pageable, bi-directional sortable DataGrid in ASP.NET 1.x was possible, but required creating two event handlers and writing, at minimum, a half dozen lines of code. The good news is that with ASP.NET 2.0 you can create a pageable, bi-directional sortable DataGrid without writing a single line of code!
Note A bi-directional sortable DataGrid is one whose data can be sorted in either ascending or descending order. When a GridView is sortable, the GridView's column headers are rendered as hyperlinks. When a column's hyperlink is clicked for the first time, the data is sorted in ascending order. If the same column's hyperlink is clicked again, the data is sorted in descending order.
As with displaying master/detail data, when creating a pageable, sortable GridView whose underlying data is coming from an ObjectDataSource, you'll need to ensure that your data access layer includes methods that have input parameters that specify how to sort the data and paging details. Therefore, we'll look at two flavors of paging and sorting examples: ones that retrieve data directly from a database using a SqlDataSource control, and ones that retrieve data from a DAL using an ObjectDataSource.
The ASP.NET 1.x DataGrid had two modes for paging through data: default paging and custom paging. With both paging models you, the page developer, needed to specify the number of records to display per page and the zero-based index of the page whose records were to be displayed. Default paging worked by having the entire contents of the data to be paged through assigned to the DataGrid's DataSource property. The DataGrid then determined the correct subset of records to display, discarding the rest. While default paging was easy to implement, it carried with it a performance cost since all records to be paged through were being returned from the database. That is, if the DataGrid was paging through a total of 1,000 records, showing 10 records per page, on each and every page request all 1,000 records would be returned from the database, but only the 10 appropriate ones would be displayed.
Custom paging solved this performance issue by requiring the page developer to tell the DataGrid exactly how many total records were being paged through as well as returning the precise subset of records to display on the page. With custom paging, even if there were a total of 1,000 records to page through, if only 10 records were being displayed per page, then only 10 records would be retrieved from the database per page visit. The custom paging model offered better performance but required more code and effort on the page developer's end.
The ASP.NET 2.0 GridView can support both models of paging, but does not have an AllowCustomPaging property like the ASP.NET 1.x DataGrid. In the following two sections we'll look at how to specify the paging model used by the GridView with the SqlDataSource and ObjectDataSource.
Paging and Sorting Data from a SqlDataSource
Paging and sorting data in a GridView that comes from a SqlDataSource is a breeze with ASP.NET 2.0. As in the demos before, start by adding a SqlDataSource that accesses the desired data. Next, add a GridView. The GridView's Smart Tag contains two checkboxes: Enable Paging and Enable Sorting. To turn on sorting and/or paging, simply check the appropriate checkboxes (see Figure 21). It's that simple!
Note The SqlDataSource has a DataSourceMode property that you can set to specify if the SqlDataSource returns a DataReader or DataSet, with DataSet being the default. When creating a pageable GridView you must return a DataSet. The GridView's sorting capabilities can only be utilized with either a DataSet or a DataReader, but when sorting by returning a DataReader you must retrieve your data from a stored procedure. Furthermore, this stored procedure must accept a parameter indicating the sort expression; specify the name of this input parameter through the SqlDataSource's SortParameterName property.
.gif "Aa479347.gridview_fg21(en-us,MSDN.10).gif")
Figure 21
The ASP.NET page's declarative syntax can be seen below. As you can see, no source code is needed to create a pageable, bi-directional sortable GridView.
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="productDataSource" Runat="server"
SelectCommand="SELECT [ProductName], [UnitPrice],
[UnitsInStock], [QuantityPerUnit] FROM [Products]"
ConnectionString=
"<%$ ConnectionStrings:NWConnectionString %>">
</asp:SqlDataSource>
<asp:GridView ID=" productsGridView" Runat="server"
DataSourceID="productDataSource" AutoGenerateColumns="False"
AllowSorting="True" BorderWidth="2px" BackColor="White"
GridLines="None" CellPadding="3"
CellSpacing="1" BorderStyle="Ridge" BorderColor="White"
AllowPaging="True">
<FooterStyle ForeColor="Black"
BackColor="#C6C3C6"></FooterStyle>
<PagerStyle ForeColor="Black" HorizontalAlign="Right"
BackColor="#C6C3C6"></PagerStyle>
<HeaderStyle ForeColor="#E7E7FF" Font-Bold="True"
BackColor="#4A3C8C"></HeaderStyle>
<Columns>
<asp:BoundField HeaderText="Product"
DataField="ProductName" SortExpression="ProductName">
</asp:BoundField>
<asp:BoundField HeaderText="Unit Price"
DataField="UnitPrice" SortExpression="UnitPrice"
DataFormatString="{0:c}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Units In Stock"
DataField="UnitsInStock"
SortExpression="UnitsInStock"
DataFormatString="{0:d}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Quantity Per Unit"
DataField="QuantityPerUnit"></asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="White" Font-Bold="True"
BackColor="#9471DE"></SelectedRowStyle>
<RowStyle ForeColor="Black" BackColor="#DEDFDE"></RowStyle>
</asp:GridView>
<i>You are viewing page
<%=productsGridView.PageIndex + 1%>
of
<%=productsGridView.PageCount%>
</i>
</div>
</form>
</body>
</html>
Figures 22, 23, and 24 show the pageable, bi-directional sortable GridView in action. Figure 22 shows page 1 of the data, ordered by Product Name in ascending order. Figure 23 shows page 1, ordered by the Product Name in descending order. Figure 24 shows page 4, ordered by the Unit Price in ascending order.
Notice that the Quantity Per Unit column stands out from the other columns in that it does not have a hyperlink in its header. This means that the data cannot be sorted by Quantity Per Unit. You can specify if a column should be sortable or not by its SortExpression property. Refer back to the ASP.NET page's declarative syntax and you'll notice that the last BoundField (the Quantity Per Unit column) lacks a SortExpression attribute, while it's present for all other BoundFields.
.gif "Aa479347.gridview_fg22(en-us,MSDN.10).gif")
Figure 22
.gif "Aa479347.gridview_fg23(en-us,MSDN.10).gif")
Figure 23
.gif "Aa479347.gridview_fg24(en-us,MSDN.10).gif")
Figure 24
At the bottom of the GridView you'll find text that informs the end user what page of data they're currently viewing along with how many total pages of data exist. The GridView exposes a zero-based PageIndex property that specified the current page of data being viewed along with a PageCount property that indicates how many total pages of data are available. To display this information, simply output the necessary markup using <%=...%> delimiters to emit the server-side property values.
<i>You are viewing page <%=productsGridView.PageIndex + 1%> of <%=productsGridView.PageCount%> </i>
The downside of using a SqlDataSource as a pageable GridView's data source control is that the GridView uses the default paging model. That is, on each page request the SqlDataSource returns all of the records that are to be paged through to the GridView. The GridView then picks out the correct subset of records based on the number of records to show per page and the page index. In order to implement the custom paging model you'll need to use an ObjectDataSource.
Paging and Sorting Data from an ObjectDataSource
From the prospective of an ASP.NET page developer, creating a pageable, bi-directional sortable GridView from an ObjectDataSource is nearly identical to doing so using a SqlDataSource. The ObjectDataSource supports both the default and custom paging models. If the ObjectDataSource returns all of the data to be paged through, the default paging model will be used. To use the custom paging model, you'll need to return only the correct subset of records and specify the total number of records being paged through. We'll discuss custom paging in a moment, but let's first look at an example of using default paging with an ObjectDataSource.
In order to support paging and sorting, the data access layer classes must include additional functionality. For a pageable GridView from an ObjectDataSource the underlying DAL class's SELECT method must accept two integer inputs: the first one specifying the maximum number of records to return and the second specifying the starting record index. The code below illustrates how to enhance the ProductDAL class to include a method that can be used for paging by creating an overloaded version of the GetProducts() method. The original version that accepts no input parameters simply returns the results of the new version, which accepts the two required input parameters.
ProductDAL Class (Visual Basic)
Imports Microsoft.VisualBasic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Public Class ProductDAL
Public Shared Function GetProducts() As List(Of Product)
' returns a list of Product instances based on the
' data in the Northwind Products table
Return GetProducts(Integer.MaxValue, 0)
End Function
Public Shared Function GetProducts(ByVal maximumRows As Integer, _
ByVal startRowIndex As Integer) As List(Of Product)
' returns a list of Product instances based on the
' data in the Northwind Products table
Dim sql As String = "SELECT ProductID, ProductName, " & _
QuantityPerUnit, UnitPrice, UnitsInStock FROM Products"
Using myConnection As New _
SqlConnection(ConfigurationManager.ConnectionStrings( _
"NWConnectionString").ConnectionString)
'Place the data in a DataTable
Dim myCommand As New SqlCommand(sql, myConnection)
Dim myAdapter As New SqlDataAdapter(myCommand)
myConnection.Open()
Dim dt As New DataTable
myAdapter.Fill(dt)
Dim results As New List(Of Product)()
Dim currentIndex As Integer = startRowIndex
Dim itemsRead As Integer = 0
Dim totalRecords As Integer = dt.Rows.Count
While itemsRead < maximumRows AndAlso _
currentIndex < totalRecords
Dim product As New Product()
product.ProductID = _
Convert.ToInt32(dt.Rows(currentIndex)("ProductID"))
product.ProductName = _
dt.Rows(currentIndex)("ProductName").ToString()
product.QuantityPerUnit = _
dt.Rows(currentIndex)("QuantityPerUnit").ToString()
If dt.Rows(currentIndex) _
("UnitPrice").Equals(DBNull.Value) Then
product.UnitPrice = 0
Else
product.UnitPrice = _
Convert.ToDecimal(dt.Rows(currentIndex)("UnitPrice"))
End If
If dt.Rows(currentIndex) _
("UnitsInStock").Equals(DBNull.Value) Then
product.UnitsInStock = 0
Else
product.UnitsInStock = _
Convert.ToInt32(dt.Rows(currentIndex)("UnitsInStock"))
End If
results.Add(product)
itemsRead += 1
currentIndex += 1
End While
myConnection.Close()
Return results
End Using
End Function
End Class
ProductDAL Class (C#)
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
/// <summary>
/// Summary description for ProductDAL
/// </summary>
public class ProductDAL
{
public static List<Product> GetProducts()
{
return GetProducts(int.MaxValue, 0);
}
public static List<Product> GetProducts(int maximumRows,
int startRowIndex)
{
return GetProducts(maximumRows, startRowIndex);
}
public static List<Product> GetProducts(int maximumRows,
int startRowIndex)
{
// returns a list of Product instances based on the
// data in the Northwind Products table
string sql = @"SELECT ProductID, ProductName, QuantityPerUnit,
UnitPrice, UnitsInStock
FROM Products";
using (SqlConnection myConnection = new
SqlConnection(ConfigurationManager.ConnectionStrings[
"NWConnectionString"].ConnectionString))
{
// Place the data in a DataTable
SqlCommand myCommand = new SqlCommand(sql, myConnection);
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
myConnection.Open();
DataTable dt = new DataTable();
myAdapter.Fill(dt);
List<Product> results = new List<Product>();
int currentIndex = startRowIndex;
int itemsRead = 0;
int totalRecords = dt.Rows.Count;
while (itemsRead < maximumRows &&
currentIndex < totalRecords)
{
Product product = new Product();
product.ProductID =
Convert.ToInt32(dt.Rows[currentIndex]["ProductID"]);
product.ProductName =
dt.Rows[currentIndex]["ProductName"].ToString();
product.QuantityPerUnit =
dt.Rows[currentIndex]["QuantityPerUnit"].ToString();
if (dt.Rows[currentIndex]
["UnitPrice"].Equals(DBNull.Value))
product.UnitPrice = 0;
else
product.UnitPrice =
Convert.ToDecimal(dt.Rows[currentIndex]["UnitPrice"]);
if (dt.Rows[currentIndex]
["UnitsInStock"].Equals(DBNull.Value))
product.UnitsInStock = 0;
else
product.UnitsInStock =
Convert.ToInt32(dt.Rows[currentIndex]["UnitsInStock"]);
results.Add(product);
itemsRead++;
currentIndex++;
}
myConnection.Close();
return results;
}
}
}
The logic in the GetProducts(maximumRows, startRowIndex) method for returning the correct subset of products is rather callow—it grabs all of the products from the database and then crafts the correct List of Product instances from the set of all products. Hence, this method is tantamount to default paging, since all of the records to be paged through are retrieved from the data store, even though only a small subset of the total retrieved records are displayed.
Note More intelligent options could involve utilizing caching or techniques to grab just the appropriate subset of records from the database (for more information see Tip 2 from Rob Howard's 10 Tips for Writing High-Performance Web Applications). To accomplish custom paging with an ObjectDataSource you'll need to also create a method that returns the total number of records being paged through. This method does not accept any input parameters and should return an integer. This method is then invoked by the GridView to determine how many total records are being paged through. This information is used in rendering the paging controls and must be explicitly provided by the ObjectDataSource, as it cannot be inferred since only the specific subset of data to be displayed would be returned from the ObjectDataSource.
In order to create a sortable GridView from an ObjectDataSource, the underlying DAL class's SELECT method must accept a string parameter specifying the sort order. To accomplish this I created two additional overloaded forms of the GetProducts() method: GetProducts(SortExpression) for returning all records in a sorted order, and GetProducts(maximumRows, startRowIndex, SortExpression) for returning a particular page of records in a sorted order.
ProductDAL Class (Visual Basic)
Public Class ProductDAL
Public Shared Function GetProducts() As List(Of Product)
' returns a list of Product instances based on the
' data in the Northwind Products table
Return GetProducts(Integer.MaxValue, 0, String.Empty)
End Function
Public Shared Function GetProducts(ByVal maximumRows As Integer, _
ByVal startRowIndex As Integer) As List(Of Product)
' returns a list of Product instances based on the
' data in the Northwind Products table
Return GetProducts(maximumRows, startRowIndex, String.Empty)
End Function
Public Shared Function GetProducts(ByVal SortExpression As String) _
As List(Of Product)
Return GetProducts(Integer.MaxValue, 0, SortExpression)
End Function
Public Shared Function GetProducts(ByVal maximumRows As Integer, _
ByVal startRowIndex As Integer, _
ByVal SortExpression As String) As List(Of Product)
' returns a list of Product instances
' based on the data in the Northwind Products table
' returns a particular subset of the data
' ordered by a particular field
Dim sql As String = "SELECT ProductID, ProductName, " & _
"QuantityPerUnit, UnitPrice, UnitsInStock FROM Products"
If SortExpression <> String.Empty Then sql &= _
" ORDER BY " & SortExpression
'... The remainder of the code is identical
' to the code in GetProducts(maximumRows, startRowIndex)
' example seen earlier ...
End Function
End Class
ProductDAL Class (C#)
public class ProductDAL
{
public static List<Product> GetProducts()
{
return GetProducts(int.MaxValue, 0, string.Empty);
}
public static List<Product> GetProducts(int maximumRows,
int startRowIndex)
{
return GetProducts(maximumRows, startRowIndex, string.Empty);
}
public static List<Product> GetProducts(string SortExpression)
{
return GetProducts(int.MaxValue, 0, SortExpression);
}
public static List<Product> GetProducts(int maximumRows,
int startRowIndex, string SortExpression)
{
// returns a list of Product instances based on the
// data in the Northwind Products table
string sql = @"SELECT ProductID, ProductName,
QuantityPerUnit, UnitPrice, UnitsInStock
FROM Products";
if (SortExpression != string.Empty)
sql += " ORDER BY " + SortExpression;
//... The remainder of the code is identical to the code
// in GetProducts(maximumRows, startRowIndex) example
// seen earlier ...
}
}
In order to have the data paged appropriately in the ASP.NET page there are a couple of settings on the ObjectDataSource we must set in order to have paging work correctly:
- EnablePaging—set this to True.
- MaximumRowsParameterName—this property provides the name of the first integer parameter to the method that pages the results. It defaults to maximumRows. If you use a different parameter name in your methods, you'll need to specify that parameter name in this property.
- StartRowIndexParameterName—similar to MaximumRowsParameterName, in that this property value specifies the second integer parameter name for paging. It defaults to startRowIndex, so you only need to set this property explicitly if you use a different parameter name in your code.
- SelectCountMethod—the name of the method that returns the total number of records to be paged through. (In my example the method name was TotalNumberOfProducts.)
And one property we must set in order to have sorting work properly:
- SortParameterName—the name of the string input parameter specifying how to sort the data. In my example I used SortExpression.
The following declarative syntax shows how everything is tied together. As with creating a pageable, bi-directional sortable GridView with the SqlDataSource, doing so with the ObjectDataSource requires no code in the ASP.NET page. The main differences between the two examples are the data source control used (SqlDataSource versus ObjectDataSource) and the ObjectDataSource paging and sorting properties that must be set.
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ObjectDataSource ID="productsDataSource"
Runat="server" TypeName="ProductDAL"
SortParameterName="SortExpression"
SelectMethod="GetProducts" EnablePaging="True"
SelectCountMethod="TotalNumberOfProducts">
</asp:ObjectDataSource>
<asp:GridView ID="productsGridView" AllowPaging="True"
BorderColor="White" BorderStyle="Ridge"
CellSpacing="1" CellPadding="3" GridLines="None"
BackColor="White" BorderWidth="2px"
AutoGenerateColumns="False"
DataSourceID="productsDataSource"
Runat="server" AllowSorting="True">
<FooterStyle ForeColor="Black" BackColor="#C6C3C6"></FooterStyle>
<PagerStyle ForeColor="Black" HorizontalAlign="Right"
BackColor="#C6C3C6"></PagerStyle>
<HeaderStyle ForeColor="#E7E7FF" Font-Bold="True"
BackColor="#4A3C8C"></HeaderStyle>
<Columns>
<asp:BoundField HeaderText="Product"
DataField="ProductName"
SortExpression="ProductName"></asp:BoundField>
<asp:BoundField HeaderText="Unit Price"
DataField="UnitPrice" SortExpression="UnitPrice"
DataFormatString="{0:c}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Units In Stock"
DataField="UnitsInStock"
SortExpression="UnitsInStock"
DataFormatString="{0:d}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Quantity Per Unit"
DataField="QuantityPerUnit"></asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="White"
Font-Bold="True"
BackColor="#9471DE"></SelectedRowStyle>
<RowStyle ForeColor="Black" BackColor="#DEDFDE"></RowStyle>
</asp:GridView>
<i>You are viewing page
<%=productsGridView.PageIndex + 1%>
of
<%=productsGridView.PageCount%>
</i>
</div>
</form>
</body>
</html>
The output is identical to the database example examined earlier; refer back to Figures 22, 23, and 24 for screenshots of a pageable, bi-directional sortable GridView.