sql server中翻页存储过程:
Create PROC blog_GetPagedPosts
(
@PageIndex int,
@PageSize int,
@BlogID int=0,
@PostType int=-1,
@CategoryID int=-1,
@Hiding bit =0,
@Count int output
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
Create Table #IDs
(
TempID int IDENTITY (1, 1) NOT NULL,
EntryID int not null
)
Insert into #IDs(EntryID) select DISTINCT [ID] from view_Content where CategoryID=@CategoryID and blogID=@BlogID order by [ID] desc
Select vc.*
FROM View_Content vc
INNER JOIN #IDS tmp ON (vc .[ID] = tmp.EntryID)
Where tmp.TempID > @PageLowerBound
AND tmp.TempID < @PageUpperBound and vc.Hiding=0
orDER BY tmp.TempID
Select @Count=COUNT(*) FROM #IDS
Select @Count=COUNT(*) FROM #IDS
Drop TABLE #IDS
return @Count
GO
在Access中由于不支持存储过程,不能建立临时表只能在程序中实现
Access中实现如下,这也是我在myblog Access版中使用的:
public List<DayBook> GetPagedPost(PagedPost p, out int TotalRecords)
{
List<DayBook> list = new List<DayBook>();
using (OleDbConnection conn = GetOleDbConnection())
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("select [ID] from blog_Content as p ");//构造查询条件
if (p.CategoryID > 0)
{
sql.AppendFormat(",blog_Categories AS c, blog_Links AS l Where c.CategoryID=l.CategoryID and (p.ID=l.PostID ) and c.CategoryID={1} and p.BlogID={0} ",p.BlogID, p.CategoryID);
}
else
{
sql.AppendFormat(" where p.blogID={0} ", p.BlogID);
}
if (p.PostType != PostType.Undeclared)
{
sql.AppendFormat(" and p.PostType={0} ", (int)p.PostType);
&