Data Management

Implement custom paging with SQL Server 2005

Tony Patton show how to use the .NET ListView and GridView controls with a SQL Server 2005 backend to implement custom paging, in which only a subset of the data is retrieved for display.

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 behavior 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 favorite 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.

Tony Patton began his professional career as an application developer earning Java, VB, Lotus, and XML certifications to bolster his knowledge.

---------------------------------------------------------------------------------------

Get weekly development tips in your inbox TechRepublic's free Visual Studio Developer newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically subscribe today!

About

Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a productio...

6 comments
MadestroITSolutions
MadestroITSolutions

Excellent article Tony. I played with MySQL a few years ago and remember it having a LIMIT clause which allowed for result set partition. This article has brought back a question I always had. Why doesn't Microsoft implement a similar approach? Obviously there must be a good reason, perhaps some limitation on the design... LIMIT has been there for years (who knows it probably isn't there anymore!). Does anyone out there know why we can't have a MySQL's LIMIT equivalent clause in MSSQL? I am no DB guru. Perhaps someone out there knows the answer to this? :)

tjc_tek
tjc_tek

1 - You can turn off EnableViewState to eliminate the overhead. 2. - It is much easier to manage a record using the RowDataBound event of the gridview or ItemDataBound event of the datagrid. 3. - You'd have to manage all the html output if you handled all the text/html yourself. The event-driven model allows for a concise, api exposed paradigm that is far easier to work with then all the text output you'd have to deal with. Especially when you put edit controls in the grids.

Justin James
Justin James

The last few of Tony's articles have been really good introductions to the lastest components for binding data to ASP.Net pages. I know that a lot of programmers (including myself, usually) shy away from the databound controls for a variety of reasons. Is this something you use? Why or why not? J.Ja

Justin James
Justin James

I know what you mean. The problem is, this kind of functionality is... problematic. It carries a lot of performance issues and overhead with it. Sadly, there is no high performance way to perform paging on a data set that is potentially constantly being edited, as well as making sense to the user. Take the following data set: ID Name 1 John 4 George 7 Paul 10 Ringo Now, imagine that User 1 runs: SELECT ID, Name from Table1 ORDER BY ID LIMIT 0, 2 which returns: ID Name 1 John 4 George User 2 then runs: INSERT INTO Table1 VALUE(5, 'Yoko') So now, the table is: ID Name 1 John 4 George 7 Paul 10 Ringo 5 Yoko User 1 then runs: SELECT ID, Name from Table1 ORDER BY ID LIMIT 2, 2 The question is, should the DB return: ID Name 4 George 7 Paul or should it return: ID Name 5 Yoko 7 Paul And if someone did "INSERT INTO Table1 VALUES(3, 'Linda')" before User 1's second query it gets even worse! The first version makes sense from the angle of, "well, this would be the second page in the dataset as measure by the fact that it is the 'second page' in the context of the query that generated the 'first page'." Which then generates the next question: at what point do I invalidate the first query's ability to "lock in" a particular version of the table and refresh those pages? It gets even trickier when you have an application server running as one DB user, but representing thousandsands ofactual users. If User 1 has been working all day, so the system never kills his "view" of the data, no other users will ever see new data. The second version of the results is 100% accurate and up-to-the-minute, but it is confusing as to why the results are jumping around a lot to the users. The DB server is nearly always going to be a better palce to do this, because DB systems are specialized in this stuff. But the application server has the awareness of users and sessions, and is much more likely to make good decisions regarding the invalidating the generated pages. So the choices are to: a) dump massive amounts of data to the app server, let it cache it, and have it (inefficently) handle paging itself, but in a way that makes sense to the apps and users b) have the DB server do it, and attempt to manage consistency in a transactional environment c) have the DB server do it, but not try to relate any query to any other query, causing confusion in a high volume environment It really is a no win situation. Still does not limit the inclusion of a LIMIT clause, of course. Just a reminder to be careful what you wish for". :) J.Ja

BOUND4DOOM
BOUND4DOOM

I do use the Grid View and the Repeater controls. Repeater controls are a no brainer really for a lot of things. Of course you have to customize basically everything but is easy enough to implement. I use repeaters for menus, even dynamic Java driven menus or web based tree views. If you have a DataSet with actual Foreign keys and stuff set up in there and you bind you can really do some cool dynamic stuff with very little coding. I stayed away from DataGrid and DataList but I have found some uses for the GridView. Enough to actually say I like GridView. I find myself Overriding the OnPageIndexChanging and the OnPageOnSorting. Mainly because I am still a firm believer in using the querystring to pass paging and sorting information so when people email a link to a page then they email what they are seeing or so people and use refresh without getting prompted if they want to resubmit their form. However if you just want to display data in a table like grid, which really I think thats what it is designed for I do nothing else with it. I find that Gridview even overriding some of the things in there is faster and more efficient than completely creating something custom. Gridview is actually very flexible I find from completely styling the whole thing with style sheets to creating dynamic rows.

albertopag
albertopag

Personally I keep myself away from the gridview-datagrid and the bulk of viewstate it generates. Paging trough the results is easy to implement but overweighted for the final user. When you have to customize the outlook, you have to dive yourself into the template-type cells, and that is not very different from doing the same with customized hand-made html code. I save the Data Repeater, because it provides low weight and flexibility. Individual, single databound objects such as a textbox, it is something I never use.