Introduction
Searching on the web, I found a lot of useful articles about this new feature of SQL Server 2005 and among them I mention the
Jason Witty�s article - that was a starting point for me. We have been waiting for a long time for this new feature of SQL
Server 2005. I don�t want to compare this new kind of ASP .NET paging with the well-known paging using temporary
table, RowCount method or Cursor method, used with SQL Server 2000, but rather how simple is
to paging a GridView in this way and to customize it. I don't use the built-in mechanism for paging of GridView web control.
I wrote this sample, because I needed to add custom paging for a grid view that allows easily changing the page size and go
directly to a page number without passing throughout each page, one by one. In the past, I already used this kind of paging
with data grid. To change it from datagrid to gridview I cut and paste the code and the job is done. Code behind is really a
rudimentary one and is very easily to understand.
Code
In the Size button click event, we set the current page number and the page size and the we bind the
GridView to display the data:
PageNum = 1;
PageSize = Convert.ToInt16(PageSizeTxt.Text);
BindGridView();
In the
Go button event click, where we go to a page number:
if (GoToPageTxt.Text != "")
{
int maxPage = Convert.ToInt32(TotalPages.Text);
int goToPage = Convert.ToInt32(GoToPageTxt.Text);
if (goToPage <= maxPage)
{
PageNum = goToPage;
BindGridView();
}
}
Here is the GridView HTML code:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" >
<Columns>
<asp:BoundField DataField="RowNumber" HeaderText="RowNumber" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="SalesYTD" HeaderText="SalesYTD" />
<asp:BoundField DataField="PostalCode" HeaderText="PostalCode" />
</Columns>
</asp:GridView>
I built the stored procedure with a SQL Server 2005 Books Online example. As you can see, it returns the ROW_NUMBER for the
salespeople in AdventureWorks database, based on the year-to-date sales.
My intention is not to explain how ROW_NUMBER() works, because it's already done in SQL Server 2005 Books Online
or in many articles on the web, but how to pass the parameters to a stored procedure for customizing paging using ROW_NUMBER().
I just included the Microsoft query into my stored procedure and the paging works very well.
Finally, the query returns the total number of records to calculate how many pages we can display.
The stored procedure script is shown below:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[sp_GridView_RowNumber]
(
@PageNum int,
@PageSize int,
@TotalRowsNum int output
)
AS
BEGIN
SET NOCOUNT ON;
WITH Salespeople_AdventureWorks As
(
SELECT 'RowNumber' = ROW_NUMBER() OVER(ORDER BY SalesYTD DESC),
'Name' = c.FirstName + ' ' + c.LastName, s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE ((TerritoryID IS NOT NULL) AND (s.SalesYTD <> 0))
)
SELECT *
FROM Salespeople_AdventureWorks
WHERE RowNumber BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize
ORDER BY SalesYTD DESC
SELECT @TotalRowsNum = count(*)
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE ((TerritoryID IS NOT NULL) AND (s.SalesYTD <> 0))
END
The source code includes the solution in Visual Studio 2005 and the script for the stored procedure. You have to change your connection string in the code to run the sample.
General 
News 
Question 
Answer 
Joke 
Rant 
Admin 
