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 in
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 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 getpagedpost(pagedpost p, out int totalrecords)
        {
            list list = new 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 ids = new list(); //获取主题id列表
                conn.open();
                using (oledbdatareader dr = mycomm.executereader())
                {
                    while (dr.read())
                    {
                        ids.add((int)dr[0]);
}
                }
totalrecords=ids.count;//返回记录总数
                if (totalrecords                     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                     {
                        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