Insus.NET在前段时间，曾分享过一篇文章的上一篇和下一篇导航http://www.zzzyk.com/kf/201203/123721.html 是使用了用户控件实现，并拉入文章显示的页面。这样子，显示文章最基本需要去读取三次数据库获取数据，一次是获取文章信息，另两次是在用户控件被使用，是获取前一篇和下一篇的标题和ID。为了改善这个性能的问题，故产生此篇：
去除用户控件，直接在读取文章时，把上一篇的ID与标题与下一篇的ID与标题一起获取。这样一次获取，节能60%。
可以参考获取文章的存储过程：
usp_Article_GetByPrimaryKey
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Insus.NET
-- Create date: 2012-03-05
-- Update date: 2012-03-17;2012-03-23
-- Description:    Get an article by article primary key.
-- =============================================
CREATE PROCEDURE [dbo].[usp_Article_GetByPrimaryKey]
(
    @ArticleId INT
)
AS
WITH a AS
(
SELECT [ArticleId],[ArticleTypeId],[ArticleTypeName],[Subject],[PrevArticleId],--前一篇ArticleId
[NextArticleId] --下一篇ArticleId
FROM [dbo].[udf_Article]() --表函数
WHERE [ArticleId] = @ArticleId
)
SELECT a.[ArticleId],a.[ArticleTypeId],[ArticleTypeName],a.[Subject],
[PrevArticleId],a1.[Subject] AS [PrevSubject], --把前一篇的标题列出
[NextArticleId],a2.[Subject] AS [NextSubject] --把下一篇的标题列出
FROM a
LEFT JOIN [dbo].[Article] AS a1 ON (a.[PrevArticleId] = a1.[ArticleId])
LEFT JOIN [dbo].[Article] AS a2 ON (a.[NextArticleId] = a2.[ArticleId]);
复制代码

上面代码中，有一个表函数：
udf_Article
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Insus.NET
-- Create date: 2012-03-05
-- Update date: 2012-03-17
-- Description:    Article details.
-- =============================================
CREATE FUNCTION [dbo].[udf_Article]
()
RETURNS TABLE
AS
RETURN
(
    SELECT [ArticleId],at.[ArticleTypeId],[ArticleTypeName],[Subject],
    (SELECT MAX([ArticleId]) FROM [dbo].[Article] AS a1 WHERE a1.[ArticleId] < a.[ArticleId]) AS [PrevArticleId],
    (SELECT MIN([ArticleId]) FROM [dbo].[Article] AS a1 WHERE a1.[ArticleId] > a.[ArticleId]) AS [NextArticleId]
    FROM [dbo].[Article] AS a
    LEFT JOIN [dbo].[ArticleType] AS at ON (a.[ArticleTypeId] = at.[ArticleTypeId])
)

ArticleView.aspx，Repeater控件有实现OnItemDataBound事件。:
View Code
<asp:Repeater ID="RepeaterArticleView" runat="server" OnItemDataBound="RepeaterArticleView_ItemDataBound">
        <ItemTemplate>
            
            上一篇：<asp:HyperLink ID="HyperLinkPrev" runat="server" Target="_blank"></asp:HyperLink>
            <br />
            下一篇：<asp:HyperLink ID="HyperLinkNext" runat="server" Target="_blank"></asp:HyperLink>
        </ItemTemplate>
    </asp:Repeater>

ArticleView.aspx.vb:
View Code
Protected Sub RepeaterArticleView_ItemDataBound(sender As Object, e As RepeaterItemEventArgs)
Dim drv As DataRowView = DirectCast(e.Item.DataItem, DataRowView)

        If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
            If e.Item.FindControl("HyperLinkPrev") IsNot Nothing Then
                Dim prevLink As HyperLink = DirectCast(e.Item.FindControl("HyperLinkPrev"), HyperLink)

                If String.IsNullOrEmpty(drv("PrevArticleId").ToString()) Then
                    prevLink.Text = "无"
                Else
                    prevLink.Text = drv("PrevSubject")
                    prevLink.NavigateUrl = "~/ArticleView.aspx?ID=" & drv("PrevArticleId")
                End If

End If

If e.Item.FindControl("HyperLinkNext") IsNot Nothing Then
Dim nextLink As HyperLink = DirectCast(e.Item.FindControl("HyperLinkNext"), HyperLink)

                If String.IsNullOrEmpty(drv("NextArticleId").ToString()) Then
                    nextLink.Text = "无"
                Else
                    nextLink.Text = drv("NextSubject")
                    nextLink.NavigateUrl = "~/ArticleView.aspx?ID=" & drv("NextArticleId")
                End If
            End If
        End If
    End Sub

摘自 Insus.NET

补充：Web开发 , ASP.Net ,

文章的上一篇和下一篇导航 V2