Implement custom paging with SQL Server 2005

In previous columns, I covered the basics of using the .NET 3.5 ListView control and explained how to pair it with the DataPager control. Now I'll show you how to use these controls with a SQL Server 2005 backend to implement custom paging, in which only a subset of the data is retrieved for display.

When is data loaded?

The default behaviour of .NET data components such as ListView and GridView involves retrieving the entire results of a query when loading data for display on a page. A better approach involves loading only data that will display on the page. When a user advances to another page of data, it is loaded from the backend data source and displayed.

This approach introduces more roundtrips between the application and the database, but it is a small price to pay for avoiding the resource intensive chore of loading an entire data set for each page of data.

Note: this approach is only more efficient for large data sets.

Subsets via SQL Server 2005

There are various techniques for handling this issue, but SQL Server 2005 introduced a new feature that changed everything. SQL Server 2005 simplifies retrieving subsets of a data source via the T-SQL row_number function.

This function allows you to return a subset of query results. The partition and order by clauses of the row_number function provide what you need. Partitioning is achieved with the over clause, which determines the partitioning and ordering of the intermediary result set before the row_number function is applied.

The results of applying the row_number function is enclosed in a subquery that returns a temporary named result set. The data you need is picked from this temporary result set. To demonstrate, I use a simple database called Test that has a People table, which is created with the following T-SQL:

CREATE TABLE [dbo].[People](
[People_ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](250),
[LastName] [nvarchar](250),
[MiddleName] [nvarchar](250))

The next T-SQL snippet returns a set of five records from the People table using the row_number function. The temporary table is named PeopleRecords within the subquery. The row_number function numbers the rows in the subquery according to the ORDER BY clause. The outer or main query selects the records it needs via a WHERE clause applied to the subquery.

SELECT people_id, lastname, firstname, rownum FROM
( SELECT people_id, lastname, firstname,
row_number() over(order by lastname) AS [rownum]
FROM Test.dbo.People ) AS PeopleRecords
WHERE rownum between 3 AND 7

Five records are returned because the starting record number (3) and ending number (7) are included in the result set. The rows are numbered by their location in the ORDER BY clause in the subquery that orders the records by last name. An ASP.NET application can use this SQL Server 2005 feature to efficiently page through large data sets.

Using the row_number function in ASP.NET

You can combine the new support for retrieving data via their row numbers within a result set with the ListView and DataPager controls to allow users to page through data on a Web page without reading the entire result set for the loading of every new page.

A SQL Server 2005 stored procedure is created to implement this functionality. The stored procedure accepts two parameters: the row number to return and the size of the result set. The source for the stored procedure follows:

CREATE PROCEDURE [dbo].[GetPage] (
@row int,
@totalrows int
)
AS
BEGIN
SELECT people_id, lastname, firstname, contactnumber, rownum FROM
( SELECT people_id, lastname, firstname, contactnumber,
row_number() over(order by lastname) AS [rownum]
FROM Test.dbo.People
) AS PeopleRecords
WHERE rownum between ((@row - 1) * @totalrows + 1) AND (@row * @totalrows)
END

With the stored procedure created, the ASP.NET Web Form is created to use it. The page contains a ListView control that displays these data fields: id value, first name, last name, and contact number.

A DataPager control is added to the page to allow users to page through the data. This instance of the DataPager control uses a custom template to build the paging interface. The template contains two buttons: Previous and Next. These buttons allow the user to page through the data one set of records at a time.

The two buttons in the DataPager control are tied to the PagerCommand method in the codebehind class file for the page. The method is wired to the DataPager control via its OnPagerCommand attribute.

A ViewState variable is used to keep track of the current page being viewed. This variable is incremented by one when the Next button is selected and decremented by one when the Previous button is clicked.

The codebehind file includes a method called GetData, which is where the call to the stored procedure is made. The GetData method is called by the code for the Next and Previous buttons. The page number and size of the page are passed to the GetData method and passed to the stored procedure via parameters.

The results of calling the stored procedure are placed in a DataSet object that is used as the data source for the ListView control. Here is the code for the Web Form and the codebehind class:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Test2.aspx.cs" Inherits="WebTest.Test2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Dynamic Paging with SQL Server 2005</title>
</head>
<body>
<form id="frmDynamicSorting" runat="server">
<asp:ListView ID="ListView1" runat="server">
<LayoutTemplate>
<table runat="server">
<tr runat="server">
<td runat="server">
<table ID="itemPlaceholderContainer" runat="server" border="0" style="">
<tr runat="server" style="">
<th runat="server">People_ID</th>
<th runat="server">FirstName</th>
<th runat="server">LastName</th>
<th runat="server">ContactNumber</th>
</tr>
<tr ID="itemPlaceholder" runat="server"></tr>
</table></td></tr></table>
</LayoutTemplate>
<ItemTemplate>
<tr style="">
<td>
<asp:Label ID="People_IDLabel" runat="server" Text=\'<%# Eval("People_ID") %>\' />
</td>
<td>
<asp:Label ID="lblFName" runat="server" Text=\'<%# Eval("FirstName") %>\' />
</td>
<td>
<asp:Label ID="lblLName" runat="server" Text=\'<%# Eval("LastName") %>\' />
</td>
<td>
<asp:Label ID="lblCNumber" runat="server" Text=\'<%# Eval("ContactNumber") %>\' />
</td></tr>
</ItemTemplate>
</asp:ListView>
<asp:DataPager ID="DataPager1" runat="server" PagedControlID="ListView1" PageSize="2">
<Fields>
<asp:TemplatePagerField OnPagerCommand="PagerCommand">
<PagerTemplate>
<asp:Button ID="btnPrev" runat="server" CommandName="Previous" Text="Previous" />
<asp:Button ID="btnNext" runat="server" CommandName="Next" Text="Next" />
</PagerTemplate>
</asp:TemplatePagerField>
</Fields>
</asp:DataPager>
</form></body></html>

Here's the codebehind file:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace TechRepublic {
  public partial class DynamicPaging : System.Web.UI.Page {
    private readonly String connString = "Data Source=TestSrv;Initial Catalog=Test;User ID=Test;Password=Test";
    private SqlConnection conn = null;
    private SqlCommand comm = null;
    private SqlDataAdapter sda = null;
    private DataSet ds = new DataSet();
    protected void Page_Load(object sender, EventArgs e) {
      if (!IsPostBack) {
      ViewState["currentrow"] = 1;
      ListView1.DataSource = GetPage(1, 2);
      ListView1.DataBind();
      } 
  }
protected DataSet GetPage(int rowindex, int pagesize) {
  try {
  ds.Dispose();
  conn = new SqlConnection(connString);
  conn.Open();
  comm = new SqlCommand("GetPage", conn);
  comm.CommandType = CommandType.StoredProcedure;
  comm.Parameters.AddWithValue("@totalrows", pagesize);
  comm.Parameters.AddWithValue("@row", rowindex);
  sda = new SqlDataAdapter(comm);
  sda.Fill(ds);
  return ds;
  } 
  finally {
    if (conn.State == ConnectionState.Open) {
    conn.Close();
    }
  comm.Dispose();
  conn.Dispose();
  } 
}
protected void PagerCommand(object sender, DataPagerCommandEventArgs e) {
  int newIndex;
  switch (e.CommandName) {
    case "Next":
    newIndex = (int)ViewState["currentrow"] + 1;
    ListView1.DataSource = GetPage(newIndex, 2);
    ListView1.DataBind();
    ViewState["currentrow"] = newIndex;
    break;
    case "Previous":
    newIndex = (int)ViewState["currentrow"] - 1;
      if (newIndex < 1) newIndex = 1;
    ViewState["currentrow"] = newIndex;
    ListView1.DataSource = GetPage(newIndex, 2);
    ListView1.DataBind();
    break;
    default:
    break;
  }
}
} 
}

You can extend this example in various ways, such as providing a text box for the user to select the page size to be displayed or allowing users to skip to first and last records.

While this is a simple example, it gives you an idea of the options available with the ROW_COUNT function. The function provides a straightforward way to increase page response time by reducing the amount of data that is fetched and displayed.

Discuss your favourite features in SQL Server 2005

Have you made the move to SQL Server 2005? If so, what new functions do you find useful? Share your thoughts with the Visual Studio Developer community.