Using row_number () to paginate your data with SQL Server 2005 and ASP

Using row_number () to paginate your data with SQL Server 2005 and ASP

Jason Witty, 2005

4.67 (Votes)
Rate:
Vote 1vote 2vote 3vote 4vote 5
With the release of SQL Server 2005, Microsoft introduces the long overdue row_number () function. In this article, we'll walk through a C # implementation of pagination using the Row_number () method.
Is your email address OK? Signed up for our newsletters but your email address is either unconfirmed, or have not been reconfirmed in a long Time. Confirmation email sent so we can confirm your e-mail address and start sending you newsletters Again. Alternatively, you can update your subscriptions.
Download Source files-667 Kb
Sample Image-row_number.gif

View Demo

Introduction
One of the great weaknesses of SQL Server is its inability to handle data pagination well. The solution to the problem is always to select all of your results and programmatically hide the results do not wan T to show. As the user clicked Next or Previous, you would again select all of the rows and only display what is the user asked for. Though the desired interface is achieved through this method, it is terribly inefficient. Why couldn ' t do we select only the data that we wanted? If you had ever had to write a search this used "like" against thousands of records, you know how terribly slow SQL Serve R could perform.

During My SQL Server, I would search endlessly for a solution to this problem. I tried implementations of using a select top where my last row is greater than a parameter. This works with some cases, like when ordering is a primary key or by date. Otherwise, this failed because of the existence of duplicate data. I also tried building stored procedures that used crazy for loops to try and accomplish this. In every instance, I would always hits a brick wall. The client would request a feature that I could not support with my method and I would always default back to the poor per Formance of selecting all of the rows (or many of them) and handling the paging scheme programmatically.

Throughout this process, I often theorized of a SQL Server function that could add a sequential row number to my result Set and allow me to use a WHERE clause against so row the only selects what rows I needed. After a bit of the found, I, the This function does in fact exist. The only problem is, it existed only in oracle! I was enraged, what could something so useful is simply left out of SQL Server?

A few years pass by and Microsoft releases. NET which offers a partial solution to the problem. ASP. Offers ability to output-cache the results of your Web control. So essentially, you can select all of the rows once and as you page through the results, pulling each subsequent set from the Cached results. This seems to partially solve the performance problem though is still faced with making the initial selection. But what if the want to view live changing data? As you decrease your cache time, your performance gets worse, as you increase it, your data gets old. Eventually, fall back on your tired old method again.

With the release of SQL Server 2005, Microsoft introduces the long overdue row_number () function to solve this problem. In this article, we'll walk through a C # implementation of pagination using the Row_number () method.

The Code
The first step is writing your stored procedure. The SQL code for using row_number () are not as intuitive as might think. When I originally attempted to does this, I tried to simply use the row_number () function like I would newid (). I quickly found out, that is not the going to work. After some, I came up with the stored procedure below. Though I would has rather seen a more intuitive syntax than what's below when your think about it, it does make sense. I Suppose they did not want to hide logic from the programmer and ask him to accept that something magical simply happens. In the following project, I'll use a database of all zip codes in the states.

Hide Copy Code
CREATE PROCEDURE [dbo]. [Sp_getzipcodes]
--Add The parameters for the stored procedure here
@start int = 0

As
BEGIN
--SET NOCOUNT on added to prevent extra result sets from
--interfering with SELECT STATEMENTS.
Set NOCOUNT on
select TOP * from

select Zip,city,state,latitude,longitude,timezone,dst,
Row_number () over (ORDER by Zip) as num

) as a
WHERE num > @start
END
Now so you have your stored pro Cedure, you'll need to display the results on a Web site. In our example, we use a GridView control, but essentially this would work with any control because we set the parameter in Our SQL data source like so:

Hide Copy Code
<asp:sqldatasource id= "SqlDataSource1" runat= "Server"
connectionstring= "<%$ connectionstrings:personalconnectionstring%>"
Selectcommand= "Sp_getzipcodes" selectcommandtype= "StoredProcedure" >
<SelectParameters>
<asp:querystringparameter name= "Start"
Querystringfield= "Start" defaultvalue= "0"/>
</SelectParameters>
</asp:SqlDataSource>
Finally, you need to build your pagination controls. For this project, we accomplish the setting a literal in our Code-behind page.

Hide Copy Code
if ((request.querystring["start"] = = null) |
(request.querystring["start"] = = "0"))
{
Paging. Text = "<< prev | <a href = \ "? start=20\" >next >></a> ";
}
Else
{
int start = Convert.ToInt32 (request.querystring["Start"]) + 1;
int next = Convert.ToInt32 (request.querystring["Start"]) + results;
int prev = Convert.ToInt32 (request.querystring["Start"])-results;
if (Next > Max)
{
paging. Text = @ "<a href =" "start=" + prev +
@ "" "><< prev</a> | next >></a>";
}
Else
{
paging. Text = @ "<a href =" "start=" + prev +
@ "" "><< prev</a> | <a href =" "? start=" +
Next + @ "" ">next >></a>";
}
}
That's about it. Download the source code to get the full project. Included in the download are the Visual Studio 2005 project, SQL Server stored procedures, and the ZIP code database in CSV Format.

Http://www.codeproject.com/Articles/12338/Using-ROW-NUMBER-to-paginate-your-data-with-SQL-Se

Using row_number () to paginate your data with SQL Server 2005 and ASP