email article printer friendly

Page and Sort Resultsets With ASP.NET
by Mauro Sant'Anna

September 2002 Issue

Technology Toolbox: VB.NET, ASP.NET

Mauro Sant'Anna

Suppose you're building an application that includes a database query function. You'd like to display query results on a Web page. However, you often get results with several hundred lines. You could display the entire data set in a grid, but the page would be too long. It would be better if readers could page through the resultset and change the sort order by clicking on the column with the field name.

ASP.NET's DataGrid component supports all this, but consider how often the application must go back to the database server and fetch data. You might want to refresh the data at every click on the page. You're welcome to do so, but it stresses the database server, and it's often unnecessary. The data might not change when users simply page through the resultset. And sometimes the changes are irrelevant. For example, you might deliver a price list that changes only once a month, or a shopping cart that only users change. In these situations, why not store the resultset in a session variable and do the paging without going to the database server? It's not hard to set up your app this way.

I'll assume you have an ASP.NET page already with a DataGrid, a DataView, and a way to fill the dataset—perhaps from a SqlDataAdapter. Now change these properties of the DataGrid: DataSource to DataView1, AllowSorting to true, and AlowPaging to true.

You need to handle two DataGrid1 events: PageIndexChanged and SortCommand. First, you must intercept Page_Load, which is called every time the page is loaded. Go to the database the first time the page is loaded (and only the first), get the data set, and store it in a session variable:

Dim DS As DataSet
If IsPostBack Then
   DS = CType(Session("MyDS"), _
      DataSet)
Else
   DS = GetResultSet()
   Session("MyDS") = DS
   Session("NewPage") = 0
   Session("SortCommand") = ""
End If
DataView1.Table = DS.Tables(0)
UpdateView()

This code also initializes two other session variables to help you do the paging. The GetResultSet method fetches data from the database:

Private Function GetResultSet() _
   As DataSet
   Dim DS As New DataSet()
   SqlDataAdapter1.Fill(DS)
   Return DS
End Function