分页的几种写法
第一种、从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://images.cnblogs.com/OutliningIndicators/ContractedBlock.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://images.cnblogs.com/OutliningIndicators/ContractedBlock.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
所有问题都会有一定程度的抽象和假设