While the PagedDataSource is useful in many situations, its default paging mechanism relies on retrieving all of the data on each and every request. Furthermore, it works by posting the request back to the page which means that search engines will not be able to index beyond the first page of data.
It is possible to make retrieval much more efficient by leveraging the ROW_NUMBER() feature of SQL Server and configuring an ObjectDataSource for custom paging (see Scott Mitchell’s article Custom Paging in ASP.NET 2.0 with SQL Server 2005) however there is no such direct means if you are using the SqlDataSource (a two-tier application architecture that's faster to implement than the three-tier ObjectDataSource, and one that I use extensively).
It is also now also possible (using ASP.NET 3.5) to add search engine friendly paging by combining a ListView control with a DataPager (instructing it to generate an SEO-friendly paging interface) and although this is a landmark development, if you're not using the ListView control or if you need more control over your paging behaviour (or you're simply using ASP.NET 2.0) the good news is that it's quite easy to roll your own reusable SEO-friendly paging control.
This article shows you how to implement super-efficient, google-style SEO-friendly paging using the Repeater control, the SqlDataSource control and custom paging.
First up, we have a stored procedure that will return our filtered, sorted and paged result set along with a count of the total number of matches:
USE [SqlPagedDataSource]
GO
/****** Object: StoredProcedure [dbo].[usp_SampleData_GetData] Script Date: 06/09/2011 14:51:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_SampleData_GetData]
@StartRow int,
@PageSize int,
@Sort varchar(50) = 'Name',
@SortDirection varchar(50) = 'Asc',
@Search varchar(255) = NULL,
@RowCount int = NULL OUTPUT
AS
BEGIN
SET @RowCount = (
SELECT
COUNT(*)
FROM
[dbo].[SampleData]
WHERE
(@Search IS NULL OR
(@Search IS NOT NULL AND
([Name] LIKE '%' + @Search + '%' OR
[Phone] LIKE '%' + @Search + '%' OR
[Email] LIKE '%' + @Search + '%'
)
)
)
)
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY
( CASE WHEN @Sort = 'Name' And @SortDirection = 'Asc' THEN [Name] END ) ASC,
( CASE WHEN @Sort = 'Name' And @SortDirection = 'Desc' THEN [Name] END ) DESC,
( CASE WHEN @Sort = 'Phone' And @SortDirection = 'Asc' THEN [Phone] END ) ASC,
( CASE WHEN @Sort = 'Phone' And @SortDirection = 'Desc' THEN [Phone] END ) DESC,
( CASE WHEN @Sort = 'Email' And @SortDirection = 'Asc' THEN [Email] END ) ASC,
( CASE WHEN @Sort = 'Email' And @SortDirection = 'Desc' THEN [Email] END ) DESC) As RowNum
,[Name]
,[Phone]
,[Email]
FROM
[dbo].[SampleData]
WHERE
(@Search IS NULL OR
(@Search IS NOT NULL AND
([Name] LIKE '%' + @Search + '%' OR
[Phone] LIKE '%' + @Search + '%' OR
[Email] LIKE '%' + @Search + '%'
)
)
)
) T
WHERE
RowNum BETWEEN @StartRow AND (@StartRow + (@PageSize - 1))
ENDNext, to access the data from the database we're going to build on the SqlDataSource control by adding some properties and extending its behaviour to support our SEO-friendly custom paging. By creating a new ‘SqlPagedDataSource’ web server control (that inherits from SqlDataSource) and directly integrating its ‘Selecting’ and ‘Selected’ events we can insert, capture and then expose the information that makes paging possible – such as inserting the current page size, handling the page number being requested and capturing the total number of known matches.
The SqlPagedDataSource control then exposes the literal pager content to any control that wants to consume it (such as our SqlPagedDataSourcePager which we'll come on to later).
Use the SqlPagedDataSource control by declaring it as you would the ordinary SqlDataSource and then specify the PageSize, PageSetLength, SortName and SortDirection properties as required:
<zyky:SqlPagedDataSource ID="pds1" runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:ConnStr %>"
CancelSelectOnNullParameter="false"
SelectCommand="usp_SampleData_GetData"
SelectCommandType="StoredProcedure"
PageSize="5"
PageSetLength="10"
SortName="Name"
SortDirection="Asc">
<SelectParameters>
</SelectParameters>
</zyky:SqlPagedDataSource>Then use the DataSourceID property to bind your databound control to the SqlPagedDataSource:
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="pds1">
<HeaderTemplate>
<ul>
</HeaderTemplate>
<ItemTemplate>
<li>
<asp:Label ID="NameDataLabel" runat="server" Text='<%#Eval("Name")%>'></asp:Label>,
<asp:Label ID="PhoneDataLabel" runat="server" Text='<%#Eval("Phone")%>'></asp:Label>,
<asp:Label ID="EmailDataLabel" runat="server" Text='<%#Eval("Email")%>'></asp:Label>
</li>
</ItemTemplate>
<FooterTemplate>
</ul>
</FooterTemplate>
</asp:Repeater>Then we have the pager itself ‘SqlPagedDataSourcePager’, it's a simple web control that you can hook up to the SqlPagedDataSource via the ‘DataSourceID’ property to render the contents of the pager on the page:
<zyky:SqlPagedDataSourcePager ID="SqlPagedDataSourcePager1" runat="server" DataSourceID="pds1"></zyky:SqlPagedDataSourcePager>The control itself has a really tiny footprint and simply inherits the Literal control to render its output:
Imports Microsoft.VisualBasic
Namespace Zyky
Public Class SqlPagedDataSourcePager
Inherits System.Web.UI.WebControls.Literal
Public Property DataSourceID() As String
Get
Return CType(ViewState("DataSourceID"), String)
End Get
Set(ByVal value As String)
ViewState("DataSourceID") = value
End Set
End Property
Protected Overrides Sub Render(ByVal writer As System.Web.UI.HtmlTextWriter)
Dim ds As Zyky.SqlPagedDataSource = Page.FindControl(DataSourceID)
If Not ds Is Nothing Then
writer.Write(ds.Contents)
End If
MyBase.Render(writer)
End Sub
End Class
End NamespaceFinally, we can output some information about the results such as the current page, the number of pages or the number of results by reading off the properties that we exposed in our SqlPagedDataSource control.
I chose to write a simple templated control to display this information:
<zyky:SqlPagedDataSourceInfoPanel ID="InfoPanel1" runat="server" DataSourceID="pds1">
<ResultTemplate>
Page
<zyky:SqlPagedDataSourceInfo ID="Info1" runat="server" DataSourceID="pds1" PropertyName="CurrentPageQuery" />
of
<zyky:SqlPagedDataSourceInfo ID="Info2" runat="server" DataSourceID="pds1" PropertyName="PageCount" />
for
<zyky:SqlPagedDataSourceInfo ID="Info3" runat="server" DataSourceID="pds1" PropertyName="Results" />
Results
</ResultTemplate>
<NoResultTemplate>
<< No Results >>
</NoResultTemplate>
</zyky:SqlPagedDataSourceInfoPanel>There you have it, a completely reusable seo-friendly custom paging interface for just about any type of databound control that you can think of. Now that’s magic!
Demo
View a live demo at http://code.zyky.com/sqlpageddatasource/Code
Download the Source CodeTrails
- Using ASP.NET 3.5's ListView and DataPager Controls: Creating an SEO-Friendly Paging Interface by Scott Mitchell (4GuysFromRolla.com)
- SEO Friendly Paging with ASP.NET 2.0 Data Controls by Peter Bromberg (EggHeadCafe.com)
- Custom Paging in ASP.NET 2.0 with SQL Server 2005 by Scott Mitchell (4GuysFromRolla.com)
