Mikel
转载：http://www.cnblogs.com/sonven/archive/2009/07/27/1531836.html
分页存储过程
Alter procedure [dbo].[sys_getPagerData]
@pageSize int, —数据条数,注意从1开始
@pageIndex int,—页数索引
@fields nvarchar(100),—选取字段
@Conditions nvarchar(50),—选择条件
@Chart nvarchar(50), —要选取的表
@recordCount int output, —记录总数
@pageCount int output —页面条数
AS
declare @SQL nvarchar(300)—SQL语句
declare @SkipCount int; —要跳过查询的记录数
/* 获取记录总数 */
declare @getRecordSQL nvarchar(100)
set @getRecordSql='select @recordcount=count(1) from '+@chart
exec sp_executesql @getRecordSql,N'@recordCount int output',@recordCount output
/* 计算总页数 */
select @pagecount=floor(@recordCount/@pageSize);
if(@recordCount%@pageSize!=0)set @pagecount=@pageCount+1
/* 计算要跳过查询的数目 */
if(@pagecount=1 or @pagecount=0)set @skipCount=0;
else set @skipCount=@pageSize*(@pageCount–1);
set @sql='select top '+convert(nvarchar,@pageSize)+' '+
@fields+' from '+@Chart
+' where(id not in(select top '+
convert(nvarchar,(@skipCount))+' id from '+@Chart+
' order by id desc))'
/*附加查询条件*/
if(@Conditions is not null and @Conditions!='')
set @sql=@sql+' and '+@Conditions
—添加 print @sql可以监视执行的Sql语句–
execute(@sql)
public ActionResult CateList(string page)
{
//分页大小
int pageSize = 15;
//总页数
int recordCount = (int)Helper.DataControl.FactoryCommand("select count(0) from articles where",CommandType.Text).ExecuteScalar();
int pageCount = recordCount / pageSize + (recordCount % pageSize == 0 ? 0 : 1);
//当前页数
int currentPage = page == null ? 0 :Convert.ToInt32(page);
if (currentPage <1) currentPage = 1;
else if (currentPage > pageCount) currentPage = pageCount;
//分页信息
TempData["pagedInfo"] = "<a href=\"" + Request.Path + "?page=" + (currentPage == 1 ? 1 : currentPage – 1) +
"\">上一页</a> | <a href=\"" + Request.Path + "?page=" + (currentPage == pageCount ? pageCount : currentPage + 1) +
"\">下一页</a> <input type=\"text\" size=\"3\" id=\"tbpage\"> <a href=\"#\" onclick=\"location.href='" + Request.Path +
"?page='+tbpage.value;\">跳页</a> "+
"第" + (currentPage) + "页/共" + pageCount + "页 每页" + pageSize +
"条记录/共" + recordCount + "条!";
//计算要跳过的页
int skipCount =recordCount<=pageSize?0:pageSize * (currentPage –1);
IEnumerable<Models.Article> articles = Common.DataContext.Articles.Skip(skipCount).Take(pageSize).OrderByDescending(a=>a.AddDate);
//注意:要先使用Skip(int)后再使用Take(int)
return View(articles);
}
分页存储过程
Alter procedure [dbo].[sys_getPagerData]
@pageSize int, —数据条数,注意从1开始
@pageIndex int,—页数索引
@fields nvarchar(100),—选取字段
@Conditions nvarchar(50),—选择条件
@Chart nvarchar(50), —要选取的表
@recordCount int output, —记录总数
@pageCount int output —页面条数
AS
declare @SQL nvarchar(300)—SQL语句
declare @SkipCount int; —要跳过查询的记录数
/* 获取记录总数 */
declare @getRecordSQL nvarchar(100)
set @getRecordSql='select @recordcount=count(1) from '+@chart
exec sp_executesql @getRecordSql,N'@recordCount int output',@recordCount output
/* 计算总页数 */
select @pagecount=floor(@recordCount/@pageSize);
if(@recordCount%@pageSize!=0)set @pagecount=@pageCount+1
/* 计算要跳过查询的数目 */
if(@pagecount=1 or @pagecount=0)set @skipCount=0;
else set @skipCount=@pageSize*(@pageCount–1);
set @sql='select top '+convert(nvarchar,@pageSize)+' '+
@fields+' from '+@Chart
+' where(id not in(select top '+
convert(nvarchar,(@skipCount))+' id from '+@Chart+
' order by id desc))'
/*附加查询条件*/
if(@Conditions is not null and @Conditions!='')
set @sql=@sql+' and '+@Conditions
—添加 print @sql可以监视执行的Sql语句–
execute(@sql)
广告:http://www.cnyolee.com/(讲述有理的网站)
http://www.rsion.com/
在ASP.NET MVC中分页
public ActionResult CateList(string page) {
//分页大小 int pageSize = 15; //总页数 int recordCount = (int)Helper.DataControl.FactoryCommand("select count(0) from articles where",CommandType.Text).ExecuteScalar(); int pageCount = recordCount / pageSize + (recordCount % pageSize == 0 ? 0 : 1); //当前页数 int currentPage = page == null ? 0 :Convert.ToInt32(page); if (currentPage <1) currentPage = 1; else if (currentPage > pageCount) currentPage = pageCount; //分页信息 TempData["pagedInfo"] = "<a href=\"" + Request.Path + "?page=" + (currentPage == 1 ? 1 : currentPage – 1) + "\">上一页</a> | <a href=\"" + Request.Path + "?page=" + (currentPage == pageCount ? pageCount : currentPage + 1) + "\">下一页</a> <input type=\"text\" size=\"3\" id=\"tbpage\"> <a href=\"#\" onclick=\"location.href='" + Request.Path + "?page='+tbpage.value;\">跳页</a> "+ "第" + (currentPage) + "页/共" + pageCount + "页 每页" + pageSize + "条记录/共" + recordCount + "条!"; //计算要跳过的页 int skipCount =recordCount<=pageSize?0:pageSize * (currentPage –1); IEnumerable<Models.Article> articles = Common.DataContext.Articles.Skip(skipCount).Take(pageSize).OrderByDescending(a=>a.AddDate); //注意:要先使用Skip(int)后再使用Take(int) return View(articles); }
