aps.net MVC3分页

初学asp.net MVC3所写分页代码，数据库为mysql，采用Razor视图引擎

  数据表staff结构如下:

  +----------+-------------+------+-----+---------+----------------
| Field    | Type        | Null | Key | Default | Extra
+----------+-------------+------+-----+---------+----------------
| id       | int(11)     | NO   | PRI | NULL    | auto_increment
| sname    | varchar(32) | NO   |     |         |
| spwd     | varchar(32) | NO   |     |         |
| age      | int(11)     | NO   |     | 0       |
| birthday | date        | NO   |     | NULL    |
| salay    | double      | NO   |     | NULL    |
+----------+-------------+------+-----+---------+----------------

 1. DAL（数据访问层）

public class MysqlDAL 
    {
        //从配置文件中获取数据库连接字符串
        private static string conString = ConfigurationManager.ConnectionStrings["mysqlCon"].ConnectionString;
        /// <summary>
        /// 执行dml
        /// </summary>
        /// <param name="sql">执行dml的sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static int ExecuteToNonQuery(string sql,params MySqlParameter[] parameters)
        {
            using (MySqlConnection conn = new MySqlConnection(conString))
            {
                conn.Open();
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 执行dql，返回一行一列的记录
        /// </summary>
        /// <param name="sql">执行dql的sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static object ExecuteToScalar(string sql, params MySqlParameter[] parameters)
        {
            using (MySqlConnection conn = new MySqlConnection(conString))
            {
                conn.Open();
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }
        /// <summary>
        /// 执行dql语句，返回datatable对象
        /// </summary>
        /// <param name="sql">执行dql的sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static DataTable ExecuteToDataTable(string sql, params MySqlParameter[] parameters)
        {
            using (MySqlConnection conn = new MySqlConnection(conString))
            {
                conn.Open();
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    DataSet dataset=new DataSet();
                    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                    adapter.Fill(dataset);
                    return dataset.Tables[0];
                }
            }
        }
    }

2. BI（service层）

 public class StaffService
    {
        /// <summary>
        /// 带分页的职员信息查询
        /// </summary>
        /// <param name="pageInfo">分页相关信息</param>
        /// <returns>职员信息datatable</returns>
        public DataTable StaffListByPage(PageInfo pageInfo)
        {
            string sql = "select id,sname,age,birthday,salay from staff limit " + (pageInfo.PageNow - 1) * pageInfo.PageSize + "	    ," +pageInfo.PageSize;
            try
            {
                return MysqlDAL.ExecuteToDataTable(sql);
            }
            catch (Exception e)
            {

                throw new Exception(e.Message);
            }
        }
        /// <summary>
        /// 获取总的记录数
        /// </summary>
        /// <returns>总记录数</returns>
        public int GetTotalCount()
        {
            string sql = "select count(*) from staff";
            try
            {
                return Convert.ToInt32(MysqlDAL.ExecuteToScalar(sql));
            }
            catch (Exception e)
            {

                throw new Exception(e.Message);
            }
        }
        /// <summary>
        /// 初始化PageInfo
        /// </summary>
        /// <param name="pageInfo"></param>
        /// <param name="pageNow">当前页数</param>
        /// <returns></returns>
        public PageInfo InitPageInfo(PageInfo pageInfo, string pageNow)
        {
            pageInfo.PageNow = Convert.ToInt32(pageNow);
            pageInfo.RowCount = this.GetTotalCount();
            pageInfo.PageSize = 15;
            pageInfo.PageCount = ((pageInfo.RowCount - 1) / pageInfo.PageSize + 1);
            if (pageInfo.PageNow >= pageInfo.PageCount)
            {
                pageInfo.PageNow = pageInfo.PageCount;
            }
            if (pageInfo.PageNow <= 1)
            {
                pageInfo.PageNow = 1;
            }
            pageInfo.DtInfo = this.StaffListByPage(pageInfo);
            return pageInfo;
        }
    }

3. Controller层

 public class StaffListController : Controller
    {
        /// <summary>
        /// 显示所有职员分页
        /// </summary>
        /// <param name="pageNow">当前页数</param>
        /// <returns>返回StaffListByPage视图</returns>
        public ActionResult StaffListBypage(string pageNow)
        {
            if (string.IsNullOrEmpty(pageNow))
            {
                pageNow = 1+"";
            }
            StaffService staffService=new StaffService();
            StaffListByPageModel staffListByPageModel=new StaffListByPageModel();
            PageInfo pageInfo=new PageInfo();

            staffListByPageModel.PageInfors = staffService.InitPageInfo(pageInfo,pageNow);
            return View(staffListByPageModel);
        }
    }

4. Model层

     a. 分页datatable数据model

/// <summary>
    /// 分页显示职员Models
    /// </summary>
    public class StaffListByPageModel
    {
        private PageInfo pageInfors;
        public PageInfo PageInfors { set; get; }
    }

     b. 分装分页信息model

/// <summary>
    /// <param name="pageSize">每页记录数</param>
    /// <param name="pageCount">总页数</param>
    /// <param name="pageNow">当前页数</param>
    /// <param name="rowCount">总记录数</param>
    /// <param name="dtInfo">记录datatable集合</param>
    /// </summary>
    public class PageInfo
    {
        private int pageSize;
        private int pageCount;
        private int pageNow;
        private int rowCount;
        private DataTable dtInfo;

        public int PageSize { set; get; }
        public int PageCount { set; get; }
        public int PageNow { set; get; }
        public int RowCount { set; get; }
        public DataTable DtInfo { set; get; }

    }

5. View（视图层）

@using System.Data
@model MvcPractice.Models.StaffListByPageMode
@{
    Layout = null;
}
<!DOCTYPE html>
<html>
<head>
    <title>StaffListByPage</title>
    <style type="text/css">
        a{
            text-decoration: none;  
        }
        a:hover {
            text-decoration: underline;
            color: red;
        }
        span {
            color: red;
        }
    </style>
</head>
<body>
    <div>
        <h1>职员信息列表（分页）</h1>
        <p><a href="http://localhost:25882/">返回主页面</a></p>
        <table style="border: 1px solid green;width:500px;">
            @{
                <tr style="border: 1px solid green"><th>姓名</th><th>年龄</th><th>生日</th><th>薪水</th><th>操作</th></tr>
                for (int i = 0; i < @Model.PageInfors.DtInfo.Rows.Count; i++)
                {
                    DataRow dr = @Model.PageInfors.DtInfo.Rows[@i];
                    <tr style="border: 1px solid green"><td style="border: 1px solid green">@dr["sname"]</td>
                        <td style="border: 1px solid green">@dr["age"]</td>
                        <td style="border: 1px solid green">@dr["birthday"]</td>
                        <td style="border: 1px solid green">@dr["salay"]</td>
                        <td style="border: 1px solid green"><a href="javascript:void(0)" οnclick="check(@dr["id"],@Model.PageInfors.PageNow)">删除</a> 
                        <a href="javascript:void(0)" οnclick="gotoupdate(@dr["id"],@Model.PageInfors.PageNow)">编辑</a></td></tr>
                }
            }
        </table>
        <div style="width:500px;text-align: right;margin-top: 10px;">
            @Html.ActionLink("首页", "StaffListBypage")  
            @Html.ActionLink("上一页", "StaffListBypage", new { pageNow = (@Model.PageInfors.PageNow - 1) })  
            @Html.ActionLink("下一页", "StaffListBypage", new { pageNow = (@Model.PageInfors.PageNow + 1) })  
            @Html.ActionLink("末页", "StaffListBypage", new { pageNow = @Model.PageInfors.PageCount })  
            当前第 <span>@Model.PageInfors.PageNow</span> 页,共 <span>@Model.PageInfors.PageCount</span> 页
        </div>
    </div>
    <script type="text/javascript">
        function check(deleteid,pageNow) {
            if (window.confirm("确定要删除吗？")) {
                window.location.href = "/StaffList/DeleteStaff?deleteid="+deleteid+"&pageNow="+pageNow;
            }
        }
        function gotoupdate(updateid,pageNow) {
            window.location.href='/StaffList/GetStaff?updateid=' + updateid + '&pageNow=' + pageNow;
        }
    </script>
</body>
</html>