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

Insus.NET在前段时间，曾分享过一篇文章的上一篇和下一篇导航http://www.2cto.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">