本文详细介绍了在SQLServer中使用存储过程进行翻页查询的具体实现方式，并提供了在Access中因不支持存储过程而采取的替代方案，通过程序逻辑实现了类似的功能。
Create PROC blog_GetPagedPosts( @PageIndex int, @PageSize int, @BlogID int=0, @PostType int=-1, @CategoryID int=-1, @Hiding bit =0, @Count int output )asDECLARE @PageLowerBound intDECLARE @PageUpperBound intSET @PageLowerBound = @PageSize * @PageIndex - @PageSizeSET @PageUpperBound = @PageLowerBound + @PageSize + 1Create 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] 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; }
}转帖请注明出处..深Q
扫一扫
41
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
