第一种、从DataSet里筛选分页行的数据
private DataSet Pageing(DataTable dt, int pageIndex, int pageSize, out int totalCount)
{
DataSet reDs = new DataSet();
DataTable reDt = dt.Clone();
DataRow totalRow = reDt.NewRow();
totalCount = dt.Rows.Count;
if (pageIndex > -1)
{
int start = pageSize * pageIndex;
int sum = pageSize;
//页码超出数据范围,输出第一页数据
if (totalCount < start) start = 0;
if (totalCount < sum + start)
{
sum = totalCount - start;
}
for (int i = start; i < start + sum; i++)
{
reDt.ImportRow(dt.Rows[i]);
}
}
reDs.Tables.Add(reDt);
reDs.RemotingFormat = SerializationFormat.Binary;
return reDs;
}
第二种、直接SQL语句ROW_NUMGER() OVER(ORDER BY T.ID DESC)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
select * from ( select ROW_NUMBER() over(order by t.id desc ) as Row,t.* from TB_CompanyArea t where t.CreatedTime<'2016-03-30 23:59:59.000 ' )tt where tt.Row between startIndex and endIndex
第三种、最简单写法
select top @pageSize * from company where id not in
(select top @pageSize*(@pageIndex-1) id from company)
第四种、用存储过程
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
IF EXISTS (SELECT * FROM sysobjects where name='P_student') DROP PROCEDURE P_student go CREATE PROCEDURE P_student @startIndex INT, @pageSize INT AS begin WITH studentList AS ( SELECT ROW_NUMBER() OVER (ORDER BY O.stuid ) Row, O.stuid,O.stuname,O.stuage,O.stuinfo from student O) SELECT Row, stuid,stuname,stuage,stuinfo FROM studentList WHERE Row between @startIndex and @startIndex+@pageSize-1 end