1.数据层的编写
NewListInfoDal.cs:
GetPageEntityList方法，根据start,end取出数据 --row_number() over()函数查询
LoadEntity方法：初始化，将datatable中的每行添加到对象中
GetRecordCount方法：获取T_news表中，总的条数
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using System.Data.SqlClient; using Itcast.CMS.Model; namespace Itcast.CMS.DAL { public class NewListInfoDal { /// <summary> /// 用于分页，根据start,end 取出数据 /// </summary> /// <param name="start"></param> /// <param name="end"></param> /// <returns></returns> public List<T_News> GetPageEntityList(int start,int end) { string sql = " select * from (select row_number() over(order by id) as num, * from T_News) 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 dt = DAL.SqlHelper.SelectSqlReturnDataTable(sql, CommandType.Text, pars); List<T_News> newlist_list = null; if(dt.Rows.Count>0) { newlist_list = new List<T_News>(); //遍历每一行添加到集合中 T_News newlist =null; foreach(DataRow row in dt.Rows) { newlist = new T_News(); LoadEntity(row, newlist); newlist_list.Add(newlist); } } return newlist_list; } public void LoadEntity(DataRow row,T_News newlist) { newlist.Id = Convert.ToInt32(row["id"].ToString()); newlist.Title = row["Title"] != DBNull.Value ? row["Title"].ToString() : string.Empty; newlist.Msg = row["Msg"] != DBNull.Value ? row["Msg"].ToString() : string.Empty; newlist.Author = row["Author"] != DBNull.Value ? row["Author"].ToString() : string.Empty; newlist.ImagePath = row["ImagePath"] != DBNull.Value ? row["ImagePath"].ToString() : string.Empty; newlist.SubDateTime = Convert.ToDateTime(row["SubDateTime"].ToString()); } /// <summary> /// 获取T_News表中，总的条数 /// </summary> /// <returns></returns> public int GetRecordCount() { string sql = " select count(*) from T_News "; object ob = DAL.SqlHelper.selectSqlReturnObject(sql, CommandType.Text); ob = ob != DBNull.Value ? ob : string.Empty; return Convert.ToInt32(ob.ToString()); } } }
2.业务层的编写
NewListInfoService.cs
GetPageEntityList方法：返回分页每页的数据 --公式计算出当前页和每页的条数
GetPageCount方法：获取总的页数 --总的条数/每页的条数
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Itcast.CMS.Model; using Itcast.CMS.DAL; namespace Itcast.CMS.BLL { public class NewListInfoService { DAL.NewListInfoDal NewListInfo = new DAL.NewListInfoDal(); /// <summary> /// 返回每页的数据 /// </summary> /// <param name="PageIndex">当前页</param> /// <param name="PageSize">每页的条数</param> /// <returns></returns> public List<T_News> GetPageEntityList(int PageIndex,int PageSize) { //公式计算每页的第一条，最好一条 int start = (PageIndex - 1) * PageSize + 1; int end = PageIndex * PageSize; return NewListInfo.GetPageEntityList(start, end); } /// <summary> /// 获取总的页数 /// </summary> /// <returns></returns> public int GetPageCount(int PageSize) { int recordCount = NewListInfo.GetRecordCount(); int PageCount = Convert.ToInt32(Math.Ceiling((double)recordCount / PageSize)); return PageCount; } } }
3.控制器中的Index方法
{
获取当前页码值，
设置条数，
总的页码数，
PageIndex范围判断，
获取分页数据，
ViewData数据绑定
}
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; using Itcast.CMS.Model; namespace Itcast.CMS.WebApp.Controllers { public class NewListController : Controller { // // GET: /NewList/ BLL.NewListInfoService NewListInfo = new BLL.NewListInfoService(); public ActionResult Index() { //获取当前页码值 int PageIndex = Request["PageIndex"] != null ? Convert.ToInt32(Request["PageIndex"]) : 1; //设置条数 int PageSize = 5; //总的页码数 int PageCount = NewListInfo.GetPageCount(PageSize); //PageIndex范围判断 PageIndex = PageIndex < 1 ? 1 : PageIndex; PageIndex = PageIndex > PageCount ? PageCount : PageIndex; //获取分页数据 List<T_News> list = NewListInfo.GetPageEntityList(PageIndex, PageSize); //ViewData... ViewData["newInfoList"] = list; ViewData["pageIndex"] = PageIndex; ViewData["pageCount"] = PageCount; return View(); } } }
4.生产分页的页码，Common中的PageBar类中的GetPageBar方法 --生产分页的字符
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Itcast.CMS.Common { public class PageBar { public static string GetPageBar(int pageIndex,int pageCount) { if (pageCount == 1) { return string.Empty; } int start = pageIndex - 5; start = start < 1 ? 1 : start; int end = start + 9; if (end > pageCount) { end = pageCount; start = end - 9 > 0 ? end - 9 : 1; } StringBuilder sb = new StringBuilder(); if (pageIndex > 1) { sb.Append(string.Format("<a href='?PageIndex={0}'>上一页</a>", pageIndex - 1)); } for (int i = start; i <= end; i++) { if (i == pageIndex) { sb.Append(i); } else { sb.Append(string.Format("<a href='?PageIndex={0}'>{0}</a>", i)); } } if (pageIndex < pageCount) { sb.Append(string.Format("<a href='?PageIndex={0}'>下一页</a>", pageIndex + 1)); } return sb.ToString(); } } }
5.视图中的调用，Razor引擎视图的编写
@{ Layout = null; } @using Itcast.CMS.Model @using Itcast.CMS.Common <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Index</title> <link href="~/Content/tableStyle.css" rel="stylesheet" /> <link href="~/Content/pageBar.css" rel="stylesheet" /> </head> <body> <div> @if(ViewData["newInfoList"]!=null) { <table width="100%"> <tr><th>编号</th><th>标题</th><th>作者</th><th>时间</th><th>详细</th><th>删除</th></tr> @foreach(T_News newlist in (List<T_News>)ViewData["newInfoList"]) { <tr> <td>@newlist.Id</td> <td>@newlist.Title</td> <td>@newlist.Author</td> <td>@newlist.SubDateTime</td> <td>详细</td> <td>删除</td> </tr> } </table> <div class="page_nav">@MvcHtmlString.Create(PageBar.GetPageBar((int)ViewData["pageIndex"], (int)ViewData["pageCount"]))</div> } else { <span>暂无数据</span> } </div> </body> </html>