asp.net 7.分页
分页
SQL:
select * from( select *,row_number()over(order by id) as num from T_userInfo) as t where t.num >=1 and t.num <=4
数据层(UserInfoDal):
/// <summary> /// 根据指定的范围,获取指定的数据 /// </summary> /// <param name="start"></param> /// <param name="end"></param> /// <returns></returns> public List<UserInfo> GetPageList(int start,int end) { string sql = "select * from(select *,row_number()over(order by id) as num from UserInfo) as t where t.num>=@start and t.num<=@end"; SqlParameter[] pars = { new SqlParameter("@start",SqlDbType.Int), new SqlParameter("@end",SqlDbType.Int) }; pars[0].Value = start; pars[1].Value = end; DataTable da=SqlHelper.GetDataTable(sql, CommandType.Text, pars); List<UserInfo> list = null; if (da.Rows.Count > 0) { list = new List<UserInfo>(); UserInfo userInfo = null; foreach (DataRow row in da.Rows) { userInfo = new UserInfo(); LoadEntity(userInfo, row); list.Add(userInfo); } } return list; } /// <summary> /// 获取总的记录数 /// </summary> /// <returns></returns> public int GetRecordCount() { string sql = "select count(*) from UserInfo"; return Convert.ToInt32(SqlHelper.ExecuteScalar(sql,CommandType.Text)); }
业务层(UserInfoService):
/// <summary> /// 计算获取数据的访问,完成分页 /// </summary> /// <param name="pageIndex">当前页码</param> /// <param name="pageSize">每页显示的记录数据</param> /// <returns></returns> public List<UserInfo> GetPageList(int pageIndex,int pageSize) { int start=(pageIndex-1)*pageSize+1; int end = pageIndex * pageSize; return UserInfoDal.GetPageList(start, end); } /// <summary> /// 获取总的页数 /// </summary> /// <param name="pageSize">每页显示的记录数</param> /// <returns></returns> public int GetPageCount(int pageSize) { int recoredCount = UserInfoDal.GetRecordCount();//获取总的记录数. int pageCount =Convert.ToInt32(Math.Ceiling((double)recoredCount / pageSize)); return pageCount; }
SqlHelper 添加ExecuteScalar方法(返回第一行第一列):
public static object ExecuteScalar(string sql, CommandType type, params SqlParameter[] pars) { using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { if (pars != null) { cmd.Parameters.AddRange(pars); } cmd.CommandType = type; conn.Open(); return cmd.ExecuteScalar(); } } }
UI层(首页/前页/下页/尾页):
<a href="NewList.aspx?pageIndex=1">首页</a> | <a href="NewList.aspx?pageIndex=<%=PageIndex-1<1?1:PageIndex-1%>"> 前页</a> | <a href="NewList.aspx?pageIndex=<%=PageIndex+1>PageCount?PageCount:PageIndex+1%>">后页</a> | <a href="NewList.aspx?pageIndex=<%=PageCount%>"> 尾页 </a> 页次:<%=PageIndex%>/<%=PageCount%>页
using CZBK.ItcastProject.Model; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace CZBK.ItcastProject.WebApp._2015_5_30 { public partial class NewList : System.Web.UI.Page { public string StrHtml { get; set; } public int PageIndex { get; set; } public int PageCount { get; set; } protected void Page_Load(object sender, EventArgs e) { int pageSize=5; int pageIndex; if(!int.TryParse(Request.QueryString["pageIndex"],out pageIndex)) { pageIndex=1; } BLL.UserInfoService UserInfoService = new BLL.UserInfoService(); int pagecount = UserInfoService.GetPageCount(pageSize);//获取总页数 PageCount = pagecount; //对当前页码值范围进行判断 pageIndex = pageIndex < 1 ? 1 : pageIndex; pageIndex = pageIndex > pagecount ? pagecount : pageIndex; PageIndex = pageIndex; List<UserInfo>list= UserInfoService.GetPageList(pageIndex,pageSize);//获取分页数据 StringBuilder sb = new StringBuilder(); foreach (UserInfo userInfo in list) { sb.AppendFormat("<li><span>{0}</span><a href='#' target='_blank'>{1}</a></li>",userInfo.RegTime.ToShortDateString(),userInfo.UserName); } StrHtml = sb.ToString(); } } }
You are never too old to set another goal or to dream a new dream!!!