存储过程获取分页数据

-- 存储过程获取分页数据
create proc GetArticleList_1
    @recordCount int out,    -- 总记录数 输出参数
    @pageCount int out,      -- 总页数 输出参数
    @currentPage int = 1 ,   -- 当前页 默认1
    @pagesize int = 10        -- 每页记录数 默认10
as
begin
    declare @sql  nvarchar(600),@sqlTotal  nvarchar(600)
    set @recordCount=(select count(id) from article)
    
    set @pageCount=CEILING(@recordCount/@pagesize)
    IF @currentPage > @pageCount
        SELECT @currentPage = @pageCount
    
    set @sql='select top '+str(@pagesize)+' id,categoryid,sortnumber,title,addtime from article '
    set @sql=@sql+' where id not in('
    set @sql=@sql+'select top'+str((@currentPage-1)*@pagesize)+'id from article order by id desc'
    set @sql=@sql+') order by id desc'
    
    exec sp_executesql @sql    
            
end  

 

// Entity framework下使用方法
            DAL.dbEntities db = new DAL.dbEntities();
            var Params1 = new System.Data.Objects.ObjectParameter("recordCount", DbType.Int32);
            var Params2 = new System.Data.Objects.ObjectParameter("pageCount", DbType.Int32);
           
            nList = db.GetArticleList_1(Params1, Params2, nPage, nPageSize).ToList();
            //db(Params1, Params2, nPage, nPageSize);
            Response.Write("总记录数：" + Params1.Value + ";总页数：" + Params2.Value);