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 { List 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 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 |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-29 17:24 , Processed in 0.180375 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.