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

Insus.NET在前段时间，曾分享过一篇文章的上一篇和下一篇导航是使用了用户控件实现，并拉入文章显示的页面。这样子，显示文章最基本需要去读取三次数据库获取数据，一次是获取文章信息，另两次是在用户控件被使用，是获取前一篇和下一篇的标题和ID。为了改善这个性能的问题，故产生此篇：

去除用户控件，直接在读取文章时，把上一篇的ID与标题与下一篇的ID与标题一起获取。这样一次获取，节能60%。

可以参考获取文章的存储过程：

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]);

 

 上面代码中，有一个表函数：

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事件。:

<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:

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