在access中模拟sql server存储过程翻页

时　间:2008-08-21 08:07:56
作　者:umvsoft整理   ID：16  城市：江阴
摘　要:在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 ");//构造查询条件
                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);
 &

Access软件网QQ交流群 (群号:483923997) 　　　　　　Access源码网店