oa_mvc_easyui_分页(4)

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>