aspx使用存储过程分页
首先我们看一下最终效果图。
代码和数据库都已经放到百度云盘中http://pan.baidu.com/share/link?shareid=392195&uk=3627406265
1、在数据库中添加存储过程PR_LoadPageData
存储过程的基本语法可以到博客园http://www.cnblogs.com/vic_lu/archive/2011/06/24/2088883.html学习
这里我们需要一个输出参数@total,利用select @total=COUNT(1) from dbo.TblTeacher得到,也可以用Count(*)
Count(*)会根据所有列中字节最短的列进行统计总行数,我们也可以直接用count(1),这相当于在查询结果添加一个新列(值都为1),根据1出现的总行数来统计,某些情况下性能会比count(*)高。
create proc PR_LoadPageData @pageIndex int, @pageSize int, @total int out as select * from (select ROW_NUMBER() over(order by TeacherID) as num,* from TblTeacher)as tbl where tbl.num between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize select @total=COUNT(1) from dbo.TblTeacher
2、添加TblTeacher类
public class TblTeacher { public int TeacherID { get; set; } public string TeacherNum { get; set; } public string TeacherName { get; set; } public int CollegeID { get; set; } public string Role { get; set; } public string Password { get; set; } }
3、执行存储过程将当前页数据查询出来
这里我们使用SqlDataAdapter 接收数据库数据,因为用SqlDataReader的话,必须先调用SqlDataReader的Close方法后,才能获取输出参数的值或者返回值,比较麻烦。所以我们可以用SqlDataAdapter 。但实际上,我们想要的是一个List<TblTeacher> 集合,希望将数据表转换为集合。这里我们需要自己写一个DataTableToList()方法(我是使用动软代码生成器,选择TblTeacher表使用单表生成,生成BLL,生成的BLL中有DataTableToList()方法,直接拷贝即可)
private List<TblTeacher> LoadDataByPage(int pageIndex, int pageSize, out int total) { SqlParameter totalPara = new SqlParameter("@total",SqlDbType.Int); DataSet ds = new DataSet(); string connStr=ConfigurationManager.ConnectionStrings["sql"].ConnectionString; using (SqlConnection conn=new SqlConnection(connStr)) { conn.Open(); using (SqlDataAdapter da=new SqlDataAdapter("PR_LoadPageData",conn)) { //设置执行类型是存储过程 da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.AddWithValue("@pageSize",pageSize); da.SelectCommand.Parameters.AddWithValue("@pageIndex", pageIndex); totalPara.Direction = ParameterDirection.Output; da.SelectCommand.Parameters.Add(totalPara); da.Fill(ds); } } total = (int)totalPara.Value; return DataTableToList(ds.Tables[0]); } private List<TblTeacher> DataTableToList(DataTable dt) { List<TblTeacher> modelList = new List<TblTeacher>(); int rowsCount = dt.Rows.Count; if (rowsCount > 0) { TblTeacher model; for (int n = 0; n < rowsCount; n++) { model = new TblTeacher(); if (dt.Rows[n]["TeacherID"] != null && dt.Rows[n]["TeacherID"].ToString() != "") { model.TeacherID = int.Parse(dt.Rows[n]["TeacherID"].ToString()); } if (dt.Rows[n]["TeacherNum"] != null && dt.Rows[n]["TeacherNum"].ToString() != "") { model.TeacherNum = dt.Rows[n]["TeacherNum"].ToString(); } if (dt.Rows[n]["TeacherName"] != null && dt.Rows[n]["TeacherName"].ToString() != "") { model.TeacherName = dt.Rows[n]["TeacherName"].ToString(); } if (dt.Rows[n]["CollegeID"] != null && dt.Rows[n]["CollegeID"].ToString() != "") { model.CollegeID = int.Parse(dt.Rows[n]["CollegeID"].ToString()); } if (dt.Rows[n]["Role"] != null && dt.Rows[n]["Role"].ToString() != "") { model.Role = dt.Rows[n]["Role"].ToString(); } if (dt.Rows[n]["Password"] != null && dt.Rows[n]["Password"].ToString() != "") { model.Password = dt.Rows[n]["Password"].ToString(); } modelList.Add(model); } } return modelList; }
4、添加分页类:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class Pager { public static string ShowPageNavigate(int pageSize, int currentPage, int totalCount) { string redirectTo = ""; pageSize = pageSize == 0 ? 3 : pageSize; var totalPages = Math.Max((totalCount + pageSize - 1) / pageSize, 1); //总页数 var output = new StringBuilder(); if (totalPages > 1) { if (currentPage != 1) {//处理首页连接 output.AppendFormat("<a class='pageLink' href='{0}?pageIndex=1&pageSize={1}'>首页</a> ", redirectTo, pageSize); } if (currentPage > 1) {//处理上一页的连接 output.AppendFormat("<a class='pageLink' href='{0}?pageIndex={1}&pageSize={2}'>上一页</a> ", redirectTo, currentPage - 1, pageSize); } else { output.Append("<span class='pageLink'>上一页</span>"); } //output.Append(" "); int currint = 5; for (int i = 0; i <= 10; i++) {//一共最多显示10个页码,前面5个,后面5个 if ((currentPage + i - currint) >= 1 && (currentPage + i - currint) <= totalPages) { if (currint == i) {//当前页处理 output.AppendFormat("<a class='cpb' href='{0}?pageIndex={1}&pageSize={2}'>{3}</a> ", redirectTo, currentPage, pageSize, currentPage); } else {//一般页处理 output.AppendFormat("<a class='pageLink' href='{0}?pageIndex={1}&pageSize={2}'>{3}</a> ", redirectTo, currentPage + i - currint, pageSize, currentPage + i - currint); } } output.Append(" "); } if (currentPage < totalPages) {//处理下一页的链接 output.AppendFormat("<a class='pageLink' href='{0}?pageIndex={1}&pageSize={2}'>下一页</a> ", redirectTo, currentPage + 1, pageSize); } else { output.Append("<span class='pageLink'>下一页</span>"); } output.Append(" "); if (currentPage != totalPages) { output.AppendFormat("<a class='pageLink' href='{0}?pageIndex={1}&pageSize={2}'>末页</a> ", redirectTo, totalPages, pageSize); } output.Append(" "); } output.AppendFormat("第{0}页 / 共{1}页", currentPage, totalPages);//这个统计加不加都行 return output.ToString(); } }
5、获取教师信息集合
//添加一个教师信息集合的属性,aspx页面模版继承自aspx.cs,可以访问到listModel public List<TblTeacher> listModel { get; set; } public string ShowNavPage { get; set; } protected void Page_Load(object sender, EventArgs e) { int total; //如果get没有传递pageIndex,则默认显示第一页 int pageIndex = Request.QueryString["pageIndex"] == null ? 1 : int.Parse(Request.QueryString["pageIndex"]); int pageSize = 10;//设置每页显示10行 listModel = LoadDataByPage(pageIndex, pageSize, out total); ShowNavPage = Pager.ShowPageNavigate(pageSize, pageIndex, total); }
6、将分页显示listModel:
<div> <table> <thead><th>教师工号</th><th>姓名</th><th>学院</th><th>角色</th><th>操作</th></thead> <%for (int i = 0; i <listModel.Count; i++) {%> <tr> <td><%=listModel[i].TeacherNum %></td> <td><%=listModel[i].TeacherName %></td> <td><%=listModel[i].CollegeID%></td> <td><%=listModel[i].Role%></td> <td><a class="aEditTeacher" href="EditTeacher.aspx?teacherID=<%= listModel[i].TeacherID %>">修改</a> <a href="TeacherList.aspx?action=del&collegeID=<%=listModel[i].CollegeID%>&teacherID=<%= listModel[i].TeacherID %>">删除</a></td> </tr> <%} %> </table> </div> <div id="NavPageDiv"> <%=ShowNavPage%> </div>
7、运行后,效果如下
8、为表格添加样式,直接引入tableStyle.css即可
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
9、再为分页导航条添加样式,引入NavPager.css,并在层NavPageDiv添加class=“paginator”(这样就可以引用NavPager.css中的样式),最终效果图如上
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
.paginator { font: 12px Arial, Helvetica, sans-serif; padding: 10px 20px 10px 0; margin: 0px; } .paginator a { border: solid 1px #ccc; color: #0063dc; cursor: pointer; text-decoration: none; } .paginator a:visited { padding: 1px 6px; border: solid 1px #ddd; background: #fff; text-decoration: none; } .paginator .cpb { border: 1px solid #F50; font-weight: 700; color: #F50; background-color: #ffeee5; } .paginator a:hover { border: solid 1px #F50; color: #f60; text-decoration: none; } .paginator a, .paginator a:visited, .paginator .cpb, .paginator a:hover { float: left; height: 16px; line-height: 16px; min-width: 10px; _width: 10px; margin-right: 5px; text-align: center; white-space: nowrap; font-size: 12px; font-family: Arial,SimSun; padding: 0 3px; }