心得/经验/绝招
在Access中模拟sql server存储过程翻页
2008-11-20 16:52:58
and blogID=@BlogID   order by [ID] descSELECT  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=0ORDER BY tmp.TempIDSELECT @Count=COUNT(*) FROM  #IDS SELECT @Count=COUNT(*) FROM  #IDS DROP TABLE #IDSreturn @CountGO

 

在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;            }         }

首页