在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);
                }
                sql.Append(" order by p.[DateUpdated] desc");
               // NetDiskContext.Current.Context.Response.Write(sql.ToString());
                //NetDiskContext.Current.Context.Response.End();
                OleDbCommand MyComm = new OleDbCommand(sql.ToString(), conn);
                List<int> IDs = new List<int>(); //獲取主題ID列表
                conn.Open();
                using (OleDbDataReader dr = MyComm.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        IDs.Add((int)dr[0]);
                   
                    }
                }
              
                TotalRecords=IDs.Count;//返回記錄總數
                if (TotalRecords < 1)
                    return list;
                int pageLowerBound = p.PageSize * p.PageIndex - p.PageSize;//記錄索引
                int pageUpperBound = pageLowerBound + p.PageSize ;
                StringBuilder sb = new StringBuilder();
                if (TotalRecords >= pageLowerBound)
                    for (int i = pageLowerBound; i < TotalRecords && i < pageUpperBound; i++)
                    {
                        sb.AppendFormat("{0},", IDs[i]);//構造ID in() 條件,取其中一頁
                    }
                else return list; //如沒有記錄返回空表
                if(sb.Length>1)
                sb.Remove(sb.Length - 1, 1);//刪除最後一個逗號
            MyComm.CommandText = string.Format("SELECT b.* , c.Account as Account FROM blog_Content b, Blog_Config  c where b.BlogID=c.BlogID and b.[ID] in ({0}) order by b.dateadded desc", sb.ToString());
                using (OleDbDataReader dr = MyComm.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        list.Add(DataHelp.LoadDayBook(dr));
                    }
                }
                return list;
            }
         }