Insus.NET在前段时间，曾分享过一篇是使用了用户控件实现，并拉入文章显示的页面。这样子，显示文章最基本需要去读取三次数据库获取数据，一次是获取文章信息，另两次是在用户控件被使用，是获取前一篇和下一篇的标题和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