? .net通用CMS快速开发框架——问题:Dapper通用的多表联合分页查询怎么破?_澳门亚博资讯网 澳门亚博,澳门亚博,yabo88真人娱乐
文章故事
首页 | 爱情文章 | 亲情文章 | 友情文章 | 生活随笔 | 校园文章 | 经典文章 | 人生哲理 | 励志文章 | 搞笑文章 | 心情日记 | 英语文章 | 会员中心
当前位置:文章故事>爱情文章>文章内容 经典美文欣赏

运城斡肯商贸有限公司_.net通用CMS快速开发框架——问题:Dapper通用的多表联合分页查询怎么破?

最近在弄一个东东,类似那种CMS的后台管理系统,方便作为其它项目的初始化框架用的。

现在遇到个问题,如标题所示:Dapper通用的多表联合分页查询怎么破?

单表的话很简单就可以实现,多表不通用的话也可以很方便的实现,那么如果多表通用的话,怎么办呢?

难道只能通过拼接sql或者使用存储过程吗?我先来展示下我的实现方式,希望你有更好的方式,然后同我分享一下,以便解决我的困扰。

因为本来就是做的传统的CMS类似的项目,所以技术选型也是比较传统,抛弃了mvvm的js框架、webapi接口、以及nosql缓存。

技术选型:MVC5、Mysql、Dapper、Autofac、Layui、阿里巴巴矢量库、T4(后面补上)。

  • MVC5:目前.net开发的主流web框架。
  • Mysql:轻量免费功能强大的关系型数据库。
  • Dapper:据说是性能最好的.net ORM框架。
  • Autofac:据说是性能最好的.net IOC框架。
  • Layui:经典的模块化UI框架,还是很适合我们这样的后端开发人员的。
  • 阿里巴巴矢量库:丰富的矢量图标库。
  • T4:强大的代码生成模板。

我选择的都是轻量级比较干净的东东来组合的框架。

我选择由外入内的方式来阐述我现在遇到的问题。以用户管理界面为例,我讲只列出涉及到用户分页查询的代码,将会省略其它代码.....

大致上的效果如下图所示:

经典的多层架构

Global.asax.cs代码,Dapper自动注入。

  public class MvcApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);

            //创建autofac管理注册类的容器实例
            var builder = new ContainerBuilder();
            SetupResolveRules(builder);
            //使用Autofac提供的RegisterControllers扩展方法来对程序集中所有的Controller一次性的完成注册 支持属性注入
            builder.RegisterControllers(Assembly.GetExecutingAssembly()).PropertiesAutowired();        

            // 把容器装入到微软默认的依赖注入容器中
            var container = builder.Build();
            DependencyResolver.SetResolver(new AutofacDependencyResolver(container));
        }
        private static void SetupResolveRules(ContainerBuilder builder)
        {
            //WebAPI只用引用services和repository的接口,不用引用实现的dll。
            //如需加载实现的程序集,将dll拷贝到bin目录下即可,不用引用dll
            var iServices = Assembly.Load("RightControl.IService");
            var services = Assembly.Load("RightControl.Service");
            var iRepository = Assembly.Load("RightControl.IRepository");
            var repository = Assembly.Load("RightControl.Repository");

            //根据名称约定(服务层的接口和实现均以Services结尾),实现服务接口和服务实现的依赖
            builder.RegisterAssemblyTypes(iServices, services)
              .Where(t => t.Name.EndsWith("Service"))
              .AsImplementedInterfaces().PropertiesAutowired();

            //根据名称约定(数据访问层的接口和实现均以Repository结尾),实现数据访问接口和数据访问实现的依赖
            builder.RegisterAssemblyTypes(iRepository, repository)
              .Where(t => t.Name.EndsWith("Repository"))
              .AsImplementedInterfaces().PropertiesAutowired();
        }
    }

BaseController:

   public class BaseController : Controller
    {// GET: Base
        public virtual ActionResult Index()
        {
            return View();
        }

UserController:

    public class UserController : BaseController
    {
        private IUserService service;

        public UserController(IUserService _service)
        {
            service = _service;
        }
        /// 
        /// 加载数据列表
        /// 
        /// 页面实体信息
        /// 查询条件
        /// 
        [HttpGet]
        public JsonResult List(PageInfo pageInfo, UserModel filter)
        {
            var result = service.GetListByFilter(filter, pageInfo);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

PageInfo:

    public class PageInfo
    {
        public int page { get; set; }
        public int limit { get; set; }
        /// 
        /// 排序字段 CreateOn
        /// 
        public string field { get; set; }
        /// 
        /// 排序方式 asc desc
        /// 
        public string order { get; set; }
        /// 
        /// 返回字段逗号分隔
        /// 
        public string returnFields { get; set; }
        public string prefix { get; set; }
    }
View Code

UserModel:

using DapperExtensions;
using System;
using System.ComponentModel.DataAnnotations;

namespace RightControl.Model
{
    [Table("t_User")]
    public class UserModel:Entity
    {
        /// 
        /// 用户名
        /// 
        [Display(Name = "用户名")]
        public string UserName { get; set; }
        /// 
        /// 真实名称
        /// 
        [Display(Name = "真实名称")]
        public string RealName { get; set; }
        /// 
        /// 密码
        /// 
        public string PassWord { get; set; }
        /// 
        /// 创建者
        /// 
        public int CreateBy { get; set; }
        /// 
        /// 角色ID
        /// 
        public int RoleId { get; set; }
        /// 
        /// 更新时间
        /// 
        [Display(Name = "更新时间")]
        public DateTime UpdateOn { get; set; }
        [Computed]
        public string RoleName { get; set; }
    }
}
View Code

Entity:

    public class Entity
    {
        [DapperExtensions.Key(true)]
        public virtual int Id { get; set; }
        /// 
        /// 创建时间
        /// 
        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd HH:mm:ss}")]
        [Display(Name = "创建时间")]
        public DateTime CreateOn { get; set; }
        /// 
        /// 菜单状态(1:启用,0:禁用)
        /// 
        public bool Status { get; set; }
        #region 查询条件
        [Computed]
        public string StartEndDate { get; set; }
        #endregion
    }

IBaseService:

    public interface IBaseService where T : class, new()
    {
        dynamic GetListByFilter(T filter, PageInfo pageInfo);
    }

IUserService:

    public interface IUserService : IBaseService
    {
    ...
    }

BaseService:

    public abstract class BaseService where T : class, new()
    {
        public IBaseRepository baseRepository{get; set;}
public dynamic GetPageUnite(IBaseRepository repository, PageInfo pageInfo, string where, object filter)
        {
            string _orderBy = string.Empty;
            if (!string.IsNullOrEmpty(pageInfo.field))
            {
                _orderBy = string.Format(" ORDER BY {0} {1}", pageInfo.prefix+pageInfo.field, pageInfo.order);
            }
            else
            {
                _orderBy = string.Format(" ORDER BY {0}CreateOn desc",pageInfo.prefix);
            }
            long total = 0;
            var list = repository.GetByPageUnite(new SearchFilter { pageIndex = pageInfo.page, pageSize = pageInfo.limit, returnFields = pageInfo.returnFields, param = filter, where = where, orderBy = _orderBy }, out total);

            return Pager.Paging(list, total);
        }
        protected  string CreateWhereStr(Entity filter, string _where)
        {
            if (!string.IsNullOrEmpty(filter.StartEndDate) && filter.StartEndDate != " ~ ")
            {
                var dts = filter.StartEndDate.Trim().Split("~");
                var start = dts[0].Trim();
                var end = dts[1].Trim();
                if (!string.IsNullOrEmpty(start))
                {
                    _where += string.Format(" and CreateOn>="{0}"", start + " 00:00");
                }
                if (!string.IsNullOrEmpty(end))
                {
                    _where += string.Format(" and CreateOn<="{0}"", end + " 59:59");
                }
            }

            return _where;
        }
    }

UserService:

    public class UserService: BaseService, IUserService
    {
        public IUserRepository repository { get; set; }//属性注入
        public dynamic GetListByFilter(UserModel filter, PageInfo pageInfo)
        {
            pageInfo.prefix = "u.";
            string _where = " t_User u INNER JOIN t_role r on u.RoleId=r.Id";
            if (!string.IsNullOrEmpty(filter.UserName))
            {
                _where += string.Format(" and {0}UserName=@UserName",pageInfo.prefix);
            }
            if (!string.IsNullOrEmpty(pageInfo.order))
            {
                pageInfo.order = pageInfo.prefix + pageInfo.order;
            }
            pageInfo.returnFields = string.Format("{0}Id,{0}UserName,{0}RealName,{0}CreateOn,{0}`PassWord`,{0}`Status`,{0}RoleId,r.RoleName",pageInfo.prefix);
            return GetPageUnite(baseRepository, pageInfo, _where, filter);
        }

IBaseRepository:

 public interface IBaseRepository where T : class, new()
    {
        IEnumerable GetByPageUnite(SearchFilter filter, out long total);
    }

IUserRepository:

    public interface IUserRepository : IBaseRepository
    {

    }

BaseRepository:

    public class BaseRepository: IBaseRepository where T :class, new()
    {
public IEnumerable GetByPageUnite(SearchFilter filter, out long total)
        {
            using (var conn = MySqlHelper.GetConnection())
            {
                return conn.GetByPageUnite(filter.pageIndex, filter.pageSize, out total, filter.returnFields, filter.where, filter.param, filter.orderBy, filter.transaction, filter.commandTimeout);
            }
        }

    }

UserRepository:

    public class UserRepository : BaseRepository, IUserRepository
    {
    }

最后的分页代码:

        /// 
        /// 获取分页数据
        /// 
        public static IEnumerable GetByPageUnite(this IDbConnection conn, int pageIndex, int pageSize, out long total, string returnFields = null, string where = null, object param = null,
string orderBy = null, IDbTransaction transaction = null, int? commandTimeout = null) { int skip = 0; if (pageIndex > 0) { skip = (pageIndex - 1) * pageSize; } StringBuilder sb = new StringBuilder(); sb.AppendFormat("SELECT COUNT(1) FROM {0};", where); sb.AppendFormat("SELECT {0} FROM {1} {2} LIMIT {3},{4}", returnFields, where, orderBy, skip, pageSize); using (var reader = conn.QueryMultiple(sb.ToString(), param, transaction, commandTimeout)) { total = reader.ReadFirst<long>(); return reader.Read(); } }

Index视图:

@{
    Layout = "~/Views/Shared/_LayoutBase.cshtml";
}

DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>Tabletitle>
head>

<body>
    <div class="admin-main">
            <blockquote class="layui-elem-quote p10">
                <div class="layui-form-item" style="margin-bottom:0px;">
                    <label class="layui-form-label">用户名称:label>
                    <div class="layui-input-inline">
                        <input name="UserName" id="UserName" lay-verify="url" autocomplete="off" class="layui-input">
                    div>
                    <a href="javascript:;" class="layui-btn layui-btn-sm" id="btnSearch"  data-type="reload">
                        <i class="layui-icon">i> 搜索
                    a>
                    <div style="float:right;">
                        <a href="javascript:;" class="layui-btn layui-btn-sm" id="btnAdd">
                            <i class="layui-icon">i> 添加用户
                        a>
                    div>
                div>
            blockquote>
            <div class="layui-field-box">
                <table id="defaultTable" lay-filter="defaultruv">table>
                <script type="text/html" id="switchTpl">
                    {{#  if(d.Status == 1){ }}
                    <i class="layui-icon green">&#xe618;</i>启用
                        {{#  } else { }}
                    <i class="layui-icon red" style="font-size:18px;">&#x1006;</i> 停用
                        {{#  } }}
                        
                        @*<input type="checkbox" name="Status"  value="{{d.Id}}"  lay-skin="switch" lay-text="开启|禁用" lay-filter="statusSwitch" {{ d.Status == 1 ? "checked" : "" }}>*@
script>
                <script type="text/html" id="bar">
                    <a class="layui-btn layui-btn-normal layui-btn-xs" lay-event="detail">查看</a>
                    <a class="layui-btn layui-btn-xs" lay-event="edit">编辑</a>
                    <a class="layui-btn layui-btn-danger layui-btn-xs" lay-event="del">删除</a>
                script>
            div>
    div>
    <script>
            layui.config({
                base: "/plugins/app/"
            });

            layui.use(["table", "common", "form"], function () {
                var table = layui.table,
                    form = layui.form,
                    common = layui.common;
                //表格
                table.render({
                    id: "defaultReload"
                   , elem: "#defaultTable"
                   , height: "full-112" //高度最大化减去差值
                   , url: "/Permissions/User/List" //数据接口
                   , page: true //开启分页
                   , cols: [[ //表头
                     { checkbox: true, fixed: true },
                     { field: "Id", title: "Id", width: 80, fixed: "left" }
                     , { field: "UserName", title: "用户名称", sort: true }
                     , { field: "RealName", title: "真实姓名" }
                     , { field: "RoleName", title: "角色名称" }
                     , { field: "Status", title: "状态", templet: "#switchTpl", unresize: true, width: 100, align:"center" }
                     , { field: "CreateOn", title: "创建时间", sort: true, templet: "
{{showDate(d.CreateOn)}}
" } , { field: "", title: "操作", toolbar: "#bar" } ]] }); var $ = layui.$, active = { reload: function () { //执行重载 table.reload("defaultReload", { page: { curr: 1 //重新从第 1 页开始 } , where: { UserName: $("#UserName
Copyright ? 2007-2014 澳门亚博资讯网 版权所有.情感文章,散文随笔,美文故事在线阅读