高效分页存储过程

create proc GetPageData
@pi int,//当前页码
@ps int//每页显示几条数据
as
begin
select * from (
  select ROW_NUMBER() over(order by id) as RNum,* from userinfo
) as temp
where RNum>(@pi-1)*@ps and RNum<=@pi*@ps
end
exec GetPageData 2,2

调用    

   /// <summary>
       /// 执行存储过程
       /// </summary>
       /// <param name="proName">存储过程名</param>
       /// <param name="par"></param>
       /// <returns></returns>
       public DataTable Expro(string proName, params SqlParameter[] par)
       {
           SqlDataAdapter da = new SqlDataAdapter(proName, sqlCon);
           da.SelectCommand.Parameters.AddRange(par);
           da.SelectCommand.CommandType = CommandType.StoredProcedure;
           DataTable dt = new DataTable();
           da.Fill(dt);
           return dt;
       }

 

 

 

第二种分页2

create proc GetPageData2
@pi int,//当前页码
@ps int,//每页显示几条数据
@rc float output,//返回行总数
@pc float output//返回页总数
as
begin
select @rc=count(*) from userinfo
select @pc=ceiling(@rc/@ps)
select * from (
  select ROW_NUMBER() over(order by id) as RNum,* from userinfo
) as temp
where RNum>(@pi-1)*@ps and RNum<=@pi*@ps
end

 

调用

 public DataTable GetPageList(string proName,int pageIndex, int pageSize, out int rowCount, out int pageCount)
       {
           rowCount = 0;
           pageCount = 0;
           SqlCommand com = new SqlCommand(proName,sqlCon);
           com.CommandType = CommandType.StoredProcedure;
           SqlParameter[] par = new SqlParameter[] {
               new SqlParameter("@pi",pageIndex),
               new SqlParameter("@ps",pageSize),
               new SqlParameter("@rc",rowCount),
               new SqlParameter("@pc",pageCount)
           };
           par[2].Direction = ParameterDirection.Output;
           par[3].Direction = ParameterDirection.Output;
           com.Parameters.AddRange(par);
           SqlDataAdapter da = new SqlDataAdapter();
           DataTable dt=new DataTable();
           da.SelectCommand = com;
           da.Fill(dt);
           rowCount =Convert.ToInt32(par[2].Value);
           pageCount = Convert.ToInt32(par[3].Value);
           return dt;
       }
   }