.Net Mvc+Easyui 分页

.Net Mvc+Easyui 分页

注：操作方法涉及Dapper + Autofac+三层架构

小白一个写的有不足之处还请提出一下

分页方法为基础方法，返回string字符串，所以可以不使用框架跳过框架直接把返回值给mvc进行页面数据传输

示例结果

---

Model层

namespace MyModel
{
    /// <summary>
    /// 用户信息类
    /// </summary>
    public  class UserInfo
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Password { get; set; }
        public string Phone { get; set; }
        public string Address { get; set; }
        public string Status { get; set; }
    }
}

---

Common层

namespace MyCommon
{
    /// <summary>
    /// 分页实体类
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class Pagination<T>
    {
        private int currPage; // 当前页
        private int totalPage; // 总页
        private int total; // 总记录数
        private int pageSize; // 页大小
        private List<T> rows; //数据

        /// <summary>
        ///  当前页
        /// </summary>
        public int CurrPage
        {
            get { return currPage; }
            set { currPage = value; }
        }
        /// <summary>
        ///  总页数
        /// </summary>
        public int TotalPage
        {
            get { return totalPage; }
            set { totalPage = value; }
        }
        /// <summary>
        ///  总记录数
        /// </summary>
        public int Total
        {
            get { return total; }
            set { total = value; }
        }
        /// <summary>
        ///  分页大小
        /// </summary>
        public int PageSize
        {
            get { return pageSize; }
            set { pageSize = value; }
        }
        /// <summary>
        /// 分页数据
        /// </summary>
        public List<T> Rows
        {
            get { return rows; }
            set { rows = value; }
        }
    }
}

---

分页方法

注意使用Autofac必须有实现IDAL、IBLL中的泛型T否则报错

DAL层

namespace MyDAL
{
    /// <summary>
    /// DAL层公共服务类
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class BaseDal<T> : IBaseDal<T> where T : class, new()
    {
        SqlConfig config = new SqlConfig();
        //获取结果数量（看不懂sql方法看博主的《Dapper.net的简单封装》）
        public int GetInfoCounts(string sql, object parameter = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            return config.GetInfoCounts(sql, parameter, transaction, buffered, commandTimeout, commandType);
        }
        //分页方法
        public string GetPagination(Pagination<T> page, string table, string orderby = "", string where = "")
        {
            if (orderby != "")
            {
                orderby = " order by " + orderby;
            }
            if (where != "")
            {
                where = " where " + where;
            }
            string sqlCount = string.Format("select count(*) as rows from {0} {1} {2}", table, where, orderby);
            //获取查询结果的计数
            int counts = GetInfoCounts(sqlCount);
            int startRows = (page.CurrPage - 1) * page.PageSize;//起始序号
            int endRows = page.CurrPage * page.PageSize;//结束序号
            string sqlList = string.Empty;
            //mysql 分页查询语句
            if (startRows == 1)
            {
                sqlList = string.Format("select * from (select @rowno:=@rowno + 1 as rownumber,a.* from {0} a,(select @rowno:=0) b {1} {2} ) tmptable  where tmptable.rownumber>={3} and tmptable.rownumber<={4}", table, where, orderby, startRows, endRows);
            }
            else
            {
                sqlList = string.Format("select * from (select @rowno:=@rowno + 1 as rownumber,a.* from {0} a,(select @rowno:=0) b {1} {2}) tmptable  where tmptable.rownumber>{3} and tmptable.rownumber<={4}", table, where, orderby, startRows, endRows);
            }

            List<T> list = GetInfoList(sqlList).ToList();//转化成lsit集合形式
            page.Rows = list;
            page.Total = counts;
            //生成json格式 此处引用到Newtonsoft.Json
            string json = JsonConvert.SerializeObject(page, new JsonSerializerSettings { ContractResolver = new CamelCasePropertyNamesContractResolver() });
            return json;
        }
    }
}

注意：一定要具体有实现泛型可以在子类种实现否则使用Autofac会报错，例：

namespace MyDAL
{
    public partial class HandleUser : BaseDal<UserInfo>
    {
    }
}

IDAL层

namespace MyIDAL
{
    /// <summary>
    /// DAL层公共服务接口
    /// </summary>
    /// <typeparam name="T">强类型类</typeparam>
    public interface IBaseDal<T> where T : class, new()
    {
        string GetPagination(Pagination<T> page, string table, string orderby = "", string where = "");

    }
}

BLL层

namespace MyBLL
{
    /// <summary>
    /// BLL层公共服务类
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class BaseServices<T> : IBaseServices<T> where T : class, new()
    {
        //注意：博主使用Autofac进行控制反转和依赖注入（看不懂可以看博主对此的有关介绍）
        public IBaseDal<T> dal;
        public BaseServices(IBaseDal<T> _dal)
        {
            dal = _dal;
        }
        public string GetPagination(Pagination<T> page, string table, string orderby = "", string where = "")
        {
            string result = dal.GetPagination( page,table,orderby,where);
            return result;
        }
    }

}

注意：一定要具体实现泛型可以在子类种实现否则使用Autofac会报错，例：

namespace MyBLL
{
    public class HandleUser : BaseServices<UserInfo>
    {
        public readonly IBaseDal<UserInfo> dao;
        public HandleUser(IBaseDal<UserInfo> _dao) : base(_dao)
        {
            dao = _dao;
        }
    }
}

IBLL层

namespace MyIBLL
{
    /// <summary>
    /// BLL层公共服务接口
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public interface IBaseServices<T> where T : class, new()
    {
        /// <summary>
        /// 分页操作方法
        /// </summary>
        /// <param name="page">分页类</param>
        /// <param name="table">表名称</param>
        /// <param name="orderby">排序字段（不需要order by）</param>
        /// <param name="where">条件语句（不需要where）</param>
        /// <returns></returns>
        string GetPagination(Pagination<T> page, string table, string orderby = "", string where = "");
    }
}

---

Mvc 层

//注意：博主对easyui的分页控件的返回值进行了修改(方便自动填充分页类)js两处（行：10749,12291）
//原本返回字段：page、rows  修改返回字段：CurrPage、PageSize
//注意：博主使用Autofac进行控制反转和依赖注入（看不懂可以看博主对此的有关介绍）
public IBaseServices<UserInfo> services;
public HomeController(IBaseServices<UserInfo> _services)
{
    this.services = _services;
}
public string GetList(Pagination<UserInfo> page)//当前页，每页大小
{
     string table = "mylove.userinfo";
     string result = services.GetPagination(page, table);
     return result;
}

---

Html代码

<body>
    <table id="table"></table>
</body>

---

JS代码

$(function () {
    jsTbShow();  
});

function jsTbShow() {
    $("#table").datagrid({
        url: 'http://localhost:39176/Home/GetList',
        loadMsg: "请稍等 …",
        rownumbers: true,
        pageSize: 20,
        pageList: [10, 20, 30, 40, 50],
        pageNumber: 1,
        pagination: true,
        fit: true,
        striped: true,
        singleSelect: false,
        nowrap: true, // 数据长度超出列宽时将会自动截取
        fitColumns: true,
        toolbar: [{
            iconCls: 'icon-add',
            text: '新增',
            handler: function () {
                dataAdd();
            }
        }, '-', {
            iconCls: 'icon-edit',
            text: '编辑',
            handler: function () {
                dataEdit();
            }
        }, '-', {
            iconCls: 'icon-remove',
            text: '删除',
            handler: function () {
                dataDelete();
            }
        }, '-', {
            iconCls: 'icon-group',
            text: '分配权限',
            handler: function () {
                fpqx();
            }
        }],
        loadFilter: function (data) {
            if (data.success == false) {
                //webframe.$.messager.alert('错误', data.msg, 'warning');
            } else {
                return data;
            }
        },
        onLoadError: function (jqXHR, textStatus, errorThrown) {
            alert("cuowu");
        },
        onLoadSuccess: function (data) {
        },
        columns: [[{
            field: 'ID',
            checkbox: true
        }, {
            field: 'name',
            title: '姓名',
            align: 'center',
            width: '18%'

        }, {
            field: 'status',
            title: '状态',
            align: 'center',
            width: '8%',
            formatter: function (value, row, index) {
                if (value == "1") {
                    return '<img alt="" src="/images/ok.png">';
                } else {
                    return '<img alt="" src="/images/error.png" >';
                }
            }
        }, {
            field: 'password',
            title: '密码',
            align: 'center',
            width: '20%'
        }, {
            field: 'phone',
            title: '手机号',
            align: 'center',
            width: '10%'
        }, {
            field: 'address',
            title: '地址',
            align: 'center',
            width: '20%'
        }]]
    });
}

---