在Access中模拟sql server存储过程翻页_数据库技巧

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 ");//构造查询条件