June 2, 2010 in ASP.Net by Alan Holmes

ASP.Net Data Reader Paging with Page Numbers

No Ratings Yet

 Loading ...

This Tutorial will explain how to create paging in ASP.Net. As well as create the standard next and previous links, it will also output page numbers.

Tags: ASP.Net, Data Reader, Page Numbers, Paging

About the Author: Alan Holmes

Hi I'm Alan Holmes, the owner of AHdevelopment. I have been doing web development for many years, and I created this site so that I can finally start archiving my development solutions in one central place, as well as making them available for others to find.

Alan Holmes has written 8 article(s).

The Idea

I recently wrote an article for creating paging with numbers for ASP. I have now converted this into ASP.Net code, which can be found below. It uses the same ideas and structure as the ASP version, so for further details, read here, I shall only be providing the code in this article.

The Code

'Default Paging Values
Dim intTotalRecords as Integer = 0	'Total Records
Dim intNumPages as Integer = 0		'Number of pages
Dim intPageSize as Integer = -1		'Amount per page, -1 means all
Dim intPageNumber as Integer = 1	'The current page to display

'News Table Values
Dim strFrom as String = "Tbl_News"
Dim strPrimKey as String = "fldNewsID"
Dim strColumns as String = "fldNewsID, fldTitle, fldAbstract, fldDate"
Dim strWhere as String = ""
Dim strOrderBy as String = "fldDate desc"

'Get the current page number from the querystring
Dim intCurPage as Integer = 1
if (trim(request("p")) <> "") and (isnumeric(request("p"))) then
	intCurPage = request("p")
end if 'if (trim(request("p")) <> "") and (isnumeric(request("p"))) then

'Calls the function to set the current paging values
SetPagingValues(5, intCurPage, strFrom, strPrimKey, strWhere)

'calls the function to get the paged sql
strSQL = CreatePagedSQL(strFrom, strPrimKey, strColumns, strWhere, strOrderBy)

'Get the records from the database
Dim dtrNewsList As SqlDataReader
dtrNewsList = getDTR(strSQL)

If dtrNewsList.HasRows Then
	While dtrNewsList.Read()
		response.Write("<div class=""NewsList"">")
		response.Write("<span class=""Title"">" & dtrNewsList("fldTitle").ToString & "</span>")
		response.Write("<span class=""Date"">" & dtrNewsList("fldDate").ToString & "</span>")
		response.Write("<span class=""Abstract"">" & dtrNewsList("fldAbstract").ToString & "</span>")
		response.Write("</div>")
	End While 'While dtrNewsList.Read()
End If 'If dtrNewsList.HasRows Then

'close the object
dtrNewsList.Close()
dtrNewsList = Nothing

'Function to fill the data reader
Public Function getDTR(ByVal sql As String) As SqlDataReader
	Dim sqlConn As SqlConnection
	Dim cmdTemp As SqlCommand
	Dim dtr As SqlDataReader

	'create connection
	sqlConn = New SqlConnection("Database Connection String")

	'get data from database
	cmdTemp = New SqlCommand(sql, sqlConn)
	sqlConn.Open()
	dtr = cmdTemp.ExecuteReader(CommandBehavior.CloseConnection)

	'Write to log file
	objErrors.Log(strErrorLocation, strError)
	dtr = Nothing

	'return data reader
	Return dtr
End Function 'Public Function getDTR(ByVal sql As String) As SqlDataReader

Sub SetPagingValues(intTempPageSize, intTempPageNumber, strFrom, strPrimKey, strWhere)
	Dim strSQLCount as String = ""
	Dim sngTempNumPages as Integer = 0
	Dim intTempNumPages as Integer = 1
	Dim dtrSQLCount As SqlDataReader

	'Set the Page Size (number of records to show per page)
	If (Trim(intTempPageSize) <> "") And (isnumeric(intTempPageSize)) Then
		intPageSize = intTempPageSize
	Else 'If (Trim(intTempPageSize) <> "") And (isnumeric(intTempPageSize)) Then
		'not valid number, so set to -1
		'IE all records
		intPageSize = -1
	End If 'If (Trim(intTempPageSize) <> "") And (isnumeric(intTempPageSize)) Then

	'Set the page number (the current page being viewed)
	If (Trim(intTempPageNumber) <> "") And (isnumeric(intTempPageNumber)) Then
		intPageNumber = intTempPageNumber
	Else 'If (Trim(intTempPageNumber) <> "") And (isnumeric(intTempPageNumber)) Then
		'if not a valid number, set page number to 1
		intPageNumber = 1
	End If 'If (Trim(intTempPageNumber) <> "") And (isnumeric(intTempPageNumber)) Then

	'SQL to get the total number of records in table
	strSQLCount = "SELECT Count(*) As RecCount From " & strFrom & " WHERE (1=1) "

	'If given add the where clause
	If Trim(strWhere) <> "" Then
		strSQLCount = strSQLCount & " AND " & strWhere
	End If 'If Trim(strWhere) <> "" Then

	'open the sql into a DTR
	dtrSQLCount = getDTR(strSQLCount)

	'Set the total records value
	If dtrSQLCount.HasRows Then
		dtrSQLCount.Read()
		intTotalRecords = dtrSQLCount("RecCount")
	Else 'If dtrSQLCount.HasRows Then
		intTotalRecords = 0
	End If 'If dtrSQLCount.HasRows Then

	'close the DTR
	dtrSQLCount.Close()
	dtrSQLCount = Nothing

	'if Total Records and Page size is greater than 0
	'then work out the number of pages
	If (intTotalRecords > 0) And (intPageSize > 0) Then
		sngTempNumPages = intTotalRecords / intPageSize
		intTempNumPages = RoundUp(sngTempNumPages)
	Else 'If (intTotalRecords > 0) And (intPageSize > 0) Then
		intTempNumPages = 1
	End If 'If (intTotalRecords > 0) AND (intPageSize > 0) Then

	'set the num pages
	intNumPages = intTempNumPages

	'if Page number is greater than number of pages, then set to
	'number of pages
	If cint(intPageNumber) > cint(intNumPages) Then
		intPageNumber = intNumPages
	End If 'If intPageNumber > intNumPages Then
End Sub 'Sub SetPagingValues(intTempPageSize, intTempPageNumber, strFrom, strPrimKey, strWhere)

Function CreatePagedSQL(strFrom, strPrimKey, strColumns, strWhere, strOrderBy)
	'Returns paged dataset for supplied SQL, broken down into sections
	Dim strSQL as String = ""
	Dim strTopRecords as String = ""
	Dim intIgnoreRecs as Integer = 0
	Dim strPagedWhere as String = " WHERE (1=1) "

	'Create where clause
	If Trim(strWhere) <> "" Then
		strPagedWhere += " AND " & strWhere
	End If 'If Trim(strPagedWhere) <> "" Then

	'create Order clause
	If Trim(strOrderBy) <> "" Then
		strOrderBy = " ORDER BY " & strOrderBy
	End If 'If Trim(strOrderBy) <> "" Then

	'set how many records to get
	If intPageSize > 0 Then
		strTopRecords = " TOP " & intPageSize
	End If 'If intPageSize > 0 Then

	'if not on page one, set the number of records to ignore
	If (intNumPages > 1) And (intPageNumber > 1) Then
		intIgnoreRecs = intPageSize * (intPageNumber - 1)
		strPagedWhere += " AND " & strPrimKey & " NOT IN (SELECT TOP " & intIgnoreRecs & " " & strPrimKey & " FROM " & strFrom & " " & strPagedWhere & " " & strOrderBy & ")"
	End If 'If (intNumPages > 1) And (intPageNumber > 1) Then

	'create sql
	strSQL = "SELECT " & strTopRecords & " " & strColumns & " FROM " & strFrom & " " & strPagedWhere & " " & strOrderBy

	'return the sql
	CreatePagedSQL = strSQL
end function 'Function CreatePagedSQL(strFrom, strPrimKey, strColumns, strWhere, strOrderBy)

Function CreatePagingLinks(strTempAction)
	Dim strPaging as String = ""
	Dim strCurAction as String = ""

	'only display if there is more than one page
	If (intNumPages > 1) Then
		'open the paging
		strPaging += "<div class=""Paging"" >"
		strPaging += "<span class=""label"" >page:</span>"

		'if not the first page, add the previous link
		If intPageNumber > 1 Then
			strCurAction = Replace(strTempAction, "{PageNo}", intPageNumber - 1)
			strPaging += "<a class=""Previous"" href=""" & strCurAction & """ title=""Go to the previous page"" >« Previous</a>"
		End If 'If intPageNumber > 1 Then

		'loop through and add the page numbers
		For i = 1 To intNumPages

			'set current action
			strCurAction = Replace(strTempAction, "{PageNo}", i)

			strPaging += "<a href=""" & strCurAction & """ "

			'if this is the current page, mark as active
			If cint(i) = cint(intPageNumber) Then
				strPaging += " class=""Active"" "
			End If 'If i = intPageNumber Then

			strPaging += " title=""Go to Page " & i & """>" & i & "</a> "
		Next 'For i = 1 To intNumPages

		'if not the last page, show the next link
		If cint(intPageNumber) < cint(intNumPages) Then
			strCurAction = Replace(strTempAction, "{PageNo}", intPageNumber + 1)
			strPaging += "<a class=""Next"" href=""" & strCurAction & """ title=""Go to the next page"" >Next »</a>"
		End If 'If cint(intPageNumber) < cint(intNumPages) Then

		'close the paging
		strPaging += "<div class=""clear"" ></div>"
		strPaging += "</div>"
	End If 'If (intNumPages > 1) Then

	'return paging links
	CreatePagingLinks = strPaging
End Function 'Function CreatePagingLinks(strTempAction)

Function RoundUp(intNum)
	'Rounds up the given number
	If (intNum - Fix(intNum) <> 0) Then
		RoundUp = Fix(intNum) + 1
	Else 'If (intNum - Fix(intNum) <> 0) Then
		RoundUp = intNum
	End If 'If (intNum - Fix(intNum) <> 0) Then
end function 'Function RoundUp(intNum)

Conclusion

As well as giving the user easier controls to navigation through the pages, this code should also put less strain on your database, and also should result in quicker load times for you pages.

Any feedback or improvements are welcome.