Introduction
This example shows how to use the SQL Server 2005 ROW_NUMBER function for paging, and gives a better look by customizing the HTML in the code-behind.
Using the code
First, we have to select the records to list. In normal paging techniques, we take all the records into a dataset and then using a PagedDataSource to show the appropriate page. The main disadvantage of this technique is that we are storing all the records in memory to just show a few records in one page. Each time you click the page number, it will select all the records. To overcome this headache, we can use the ROW_NUMBER function of SQL Server 2005 effectively.
The snippet below shows the Stored Procedure to select the specific records; we are passing two parameters:
@intCurrentPage int - This parameter is used to select the records of a page.@intRecordPerPage int - This parameter is used to get the number of records to show in one page.
For example, if we pass 1 as @intCurrentPage and 10 as @intRecordPerPage, then the result will be records from 1 to 10. This Stored Procedure connects to the AdventureWorks database and selects records from the Production.Product table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[uspFetchCustomer]
@intCurrentPage int,
@intRecordPerPage int
AS
BEGIN
SET NOCOUNT ON;
declare @intStartRec int
set @intStartRec=(@intCurrentPage*@intRecordPerPage)-@intRecordPerPage
declare @intEndRec int
set @intEndRec=(@intCurrentPage*@intRecordPerPage)+1
Begin
Select * from
(
select ROW_NUMBER() OVER (ORDER BY ProductID) as SlNo,ProductID,
[Name],ProductNumber,Color
from Production.Product
) Temp
Where SlNo>@intStartRec and slno<@intEndRec
select COUNT(ProductID) as TotalRecords
from Production.Product
End
END
The output of this Stored Procedure
Execute the Stored Procedure by passing: @intCurrentPage=1, @intRecordPerPage=10.
First Result Set
SlNo ProductID Name ProductNumber StandardCost
1 1 Adjustable Race AR-5381 0.00
2 2 Bearing Ball BA-8327 0.00
3 3 BB Ball Bearing BE-2349 0.00
4 4 Headset Ball Bearings BE-2908 0.00
5 316 Blade BL-2036 0.00
6 317 LL Crankarm CA-5965 0.00
7 318 ML Crankarm CA-6738 0.00
8 319 HL Crankarm CA-7457 0.00
9 320 Chainring Bolts CB-2903 0.00
10 321 Chainring Nut CN-6137 0.00
TotalRecords
504
The first result set is the records of the first page. The second result set is the total number of records.
Execute this Stored Procedure in the AdventureWorks database and create a connection string for connecting to AdventureWorks.
connectionString ="Data Source=[SERVER NAME];
Initial Catalog=AdventureWorks;User ID=[USERNAME]; Password=[PASSWORD]" />
Using the result in the code-behind
Now we have the Stored Procedure ready in the database. Start implementing our listing and paging methods. To show the listing, we are not using any data controls, we just write directly to the ASPX page.
For this, first we have to declare two strings in the code-behind:
Protected strList As String Protected strPages As String
The string variable strList holds the HTML for the records, and the string strPages will hold the HTML for paging.
Fill the DataReader
First, we have to get the page index. We can store the page index in a hidden text field (txtHidPageIndex); by default, the first page will be shown. Using this hidden field, we can store the page index clicked by the user, and can access the hidden field in the server side to know the page number. We need a server control to listen to the clicking of the page. I am adding a link button control (lnkPaging) to the page to initiate the clicking and to do some server side action.
Write a JavaScript function for accepting the page number and set that page number to the hidden field. After that, do a post back of the link button so that we can perform some server side action. The Click event of the link button can be initiated by the doPostBack method of ASP.NET.
function doPaging(intPageIndex)
{
document.getElementById('txtHidPageIndex').value=intPageIndex;
__doPostBack('lnkPaging','');
}
The code-behind of the link button (lnkPaging) looks like this:
Protected Sub lnkPaging_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles lnkPaging.Click
DoSearch(Val(txtHidPageIndex.Value))
End Sub
This snippet below shows how to fill the reader with records of this page:
Private Sub DoSearch(Optional ByVal intPageIndex As Integer = 1)
Dim intRecordPerPage As Integer = 10
Dim objCon As New SqlConnection(_
ConfigurationManager.ConnectionStrings("ConString").ConnectionString)
Dim objCmd As New SqlCommand()
objCmd.Connection = objCon
Dim objReader As SqlDataReader
objCmd.CommandType = CommandType.StoredProcedure
objCmd.CommandText = "uspFetchProduct"
objCmd.Parameters.Add(New SqlParameter("@intCurrentPage", intPageIndex))
objCmd.Parameters.Add(New SqlParameter("@intRecordPerPage", intRecordPerPage))
objCon.Open()
objReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
I assigned 10 records to show in one page. If you want the user to customize the records per page, you can add an option in the settings of your website and take the value to set as the record per page. Now, we have the result in the reader. Create a StringBuilder to store the HTML created.
Dim sbHTML As New StringBuilder
sbHTML.Append("YOUR HEADER INFORMATION")
While objReader.Read
sbHTML.Append("THE LISTING COLUMNS ")
After filling the HTML for the listing, we can assign it to the protected variable strList.
strList = sbHTML.ToString
Paging for the records
Now comes the main thing: how to do the paging. For paging, first we need the total number of records. Take this number from the reader.
objReader.NextResult()
Dim intTotalRecords As Integer = 0
If objReader.Read Then
intTotalRecords = Val(objReader(0))
End If
Now, the variable intTotalRecords contains the total number of records. The second task is to find the number of pages. This is shown in the snippet below:
Dim intReminder As Integer = intTotalRecords Mod intRecordPerPage
Dim intTotalPages As Integer = intTotalRecords \ intRecordPerPage
If intReminder > 0 Then
intTotalPages += 1
End If
Now we have to find the starting record and the ending record for each page. We are assuming that the page numbers shown in the list contains five pages. So the user will get the First and Previous buttons, and then buttons for the next five pages, and then the Next and Last buttons.
Dim intPagesShowing As Integer = 5
Dim intStartPage As Integer
Dim intEndPage As Integer
intStartPage = IIf(intPageIndex <= intPagesShowing, 1, intPageIndex)
If intTotalPages > intPagesShowing And intTotalPages > intStartPage Then
intEndPage = IIf((intStartPage + intPagesShowing - 1) > intTotalPages, _
intTotalPages, (intStartPage + intPagesShowing - 1))
Else
intEndPage = intTotalPages
End If
Now we can hide or disable the First, Previous, Next, and Last buttons by checking the page index and the number of pages remaining:
Dim bShowFirst As Boolean = False
Dim bShowPrevious As Boolean = False
Dim bShowNext As Boolean = False
Dim bShowLast As Boolean = False
If intPageIndex > 1 Then
bShowPrevious = True
bShowFirst = True
End If
If intEndPage < intTotalPages Then
bShowNext = True
bShowLast = True
End If
Now we can add page numbers, which is from the start page to the last page:
For iPage As Integer = intStartPage To intEndPage
sbHTML.Append(iPage)
Next
After filling the HTML for paging, store the StringBuilder value in the protected string strPages. Paging will show only if there is more than one page.
If intTotalPages > 1 Then
strPages = sbHTML.ToString
End If
Points of interest
When using this method, we don't need any data controls. We are creating and managing the HTML for listing and paging. This technique will give you maximum flexibility in your page.
