1
分页
1.1
数据库中存储过程
已知 当前页 pageIndex 页容量 pageSize
求 总页数 pageCount 和 当前页的数据
//例子：
create proc usp_Example
@pageIndex int,---------参数之间用 , 分割 最后一个参数后 必须没有 ,
@pageSize int,
@pageCount int output
as
begin
declare @count int
select @count=count(*) from <tableName>
set @pageCount=ceiling(@count*1.0/@pageSize)
select * from
(select *,row_number() over(order by <columnName> asc/desc) as 编号 from <tableName>) tempTable
where 编号 between (@pageIndex -1)*@pageSize+1 and @pageIndex*@pageSize
and
--测试
declare @count int
exec usp_Example 1,2,@count output
print @count
1.2 SQLHelper中
private static DataTable ExecuteDataTable(string sql,CommandType ct,params SqlParameter[] param)
{
using(SqlConnection conn = new SqlConnection(connStr))
{
using(SqlCommand cmd= new SqlCommand(sql,conn))
{
cmd.Parameters.AddRange(param);
cmd.CommandType=ct;
conn.Open();
try{
using(SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}catch(Exception ex)
{
throw ex;
}
}
}
}
1.3 DAL 中
public List<Model> GetModelsByProc(int pageIndex,int pageSize,out int pageCount)
{
SqlParameter[] param={
new SqlParameter("@pageIndex",SqlDbType.Int){Value=pageIndex},
new SqlParameter("@pageSize",SqlDbType.Int){Value=PageSize},
new SqlParametre("@pageCount",SqlDbType.Int){Direction=ParameterDirection.OutPut}
};
DataTable dt =SqlHelper.ExecuteDataTable("ProcName",CommandType.StoredProcdure,param);
pageCount=param[2].Value;
List<Model> list=new List<Model>();
foreach(DataRow row in dt.Rows)
{
list.Add(row);
}
return list;
}
1.4 BLL
public List<Model> GetModelsByProc(int pageIndex,int pageSize,out int pageCount)
{
return new DAL().GetModelsByProc(pageIndex,pageSize,out pageCount);
}
1.5 UI
.CS界面
protected string table;
private int pageIndex=1;//默认第一页
private int pageSize=3;//默认每页显示3条数据 可从前台 获取进行设定
private int pageCount;//总页数
protected string pageBar;
//生成表格
private string CreateDataTable()
{
List<Model> list=new BLL().GetModelsByProc(pageIndex,pageSize,out pageCount);
StringBuilder sb=new StringBuilder();
sb.Append("<table>");
sb.Append("<tr><th>Column1</th><th>Column2</th><th>Column3</th></tr>");
for(int i=0;i<list.Count;i++)
{
Model model=List[i];
sb.Append("<tr>");
sb.Append("<td><a href='ModelDetails.aspx?id="+model.ID+"'>modelContent1</a></td>");
sb.Append("<td><a href='MySelf.aspx?id="+model.ID+"'@action=delete>modelContent2<a></td>");
sb.Append("<td><a href='WaterMaker.ashx?id="+model.ID+"'>modelContent2</a></td>");
sb.Append("</tr>");
}
sb.Append("</table>");
return sb.ToString();
}
//创建分页栏
private string CreatePageBar()
{
StringBuilder sb =new StringBuilder();
sb.Append("<a href='MySelf.aspx?page=1'>首页</a>");
int num=pageIndex;
num=num>1?num-1:1;
sb.Append(" <a href='MySelf.aspx?page="+num+"'>上一页</a>");
num=num<pageCount?num+1:pageCount;
sb.Append(" <a href='MySelf.aspx?page="+num+"'>下一页</a>");
sb.Append(" <a href='MySelf.aspx?page="+pageCount+"></a>");
sb.Append(" "+pageIndex+"/"+pageCount+"");
return sb.ToString();
}
//页面加载
protected void Page_Load(object sender,EventArgs e)
{
//每次加载都执行
string page=Request.QueryString["page"];
if(int.tryParse(page,out pageIndex))
{
pageIndex=1;
}
table=CreateDataTable();
pageBar=CreatePageBar();
}
1.6 .aspx 前台页面
<%=table%>
<%-pageBar%>