分页的几种写法

第一种、从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)

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

View Code

第三种、最简单写法

select top @pageSize * from company where id not in

(select top @pageSize*(@pageIndex-1) id from company)

第四种、用存储过程

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

View Code