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;
}
}