最近在弄一个东东,类似那种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 classMvcApplication : System.Web.HttpApplication
{protected voidApplication_Start()
{
AreaRegistration.RegisterAllAreas();
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);//创建autofac管理注册类的容器实例
var builder = newContainerBuilder();
SetupResolveRules(builder);//使用Autofac提供的RegisterControllers扩展方法来对程序集中所有的Controller一次性的完成注册 支持属性注入
builder.RegisterControllers(Assembly.GetExecutingAssembly()).PropertiesAutowired();//把容器装入到微软默认的依赖注入容器中
var container =builder.Build();
DependencyResolver.SetResolver(newAutofacDependencyResolver(container));
}private static voidSetupResolveRules(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 classBaseController : Controller
{//GET: Base
public virtualActionResult Index()
{returnView();
}
UserController:
public classUserController : BaseController
{privateIUserService service;publicUserController(IUserService _service)
{
service=_service;
}///
///加载数据列表///
/// 页面实体信息
/// 查询条件
///
[HttpGet]publicJsonResult List(PageInfo pageInfo, UserModel filter)
{var result =service.GetListByFilter(filter, pageInfo);returnJson(result, JsonRequestBehavior.AllowGet);
}
PageInfo:
public classPageInfo
{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:
usingDapperExtensions;usingSystem;usingSystem.ComponentModel.DataAnnotations;namespaceRightControl.Model
{
[Table("t_User")]public classUserModel: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 classEntity
{
[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()
{dynamicGetListByFilter(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, objectfilter)
{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 }, outtotal);returnPager.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 dynamicGetListByFilter(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);returnGetPageUnite(baseRepository, pageInfo, _where, filter);
}
IBaseRepository:
public interface IBaseRepository where T : class, new()
{
IEnumerable GetByPageUnite(SearchFilter filter, out longtotal);
}
IUserRepository:
public interface IUserRepository : IBaseRepository{
}
BaseRepository:
public class BaseRepository: IBaseRepository where T :class, new()
{public IEnumerable GetByPageUnite(SearchFilter filter, out longtotal)
{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= newStringBuilder();
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();return reader.Read();
}
}
Index视图:
@{
Layout = "~/Views/Shared/_LayoutList.cshtml";
}
Table用户名称: