asp.net core 一个中小型项目实战的起手式——项目搭建与仓储模式下的持久层创建（1）
交流群：863563315
常规的中小型项目搭建方式一般是三层架构加上mvc与webapi作为一个主要框架，再加上一些第三方库，例如orm框架（EF、SqlSugar、Dapper等），API文档工具（Swagger）这些的应用。
接下来我们以一个数据管理平台做为实战项目来演示三层架构与仓储模式。
项目Github连接：
https://github.com/FaithGuo/DataManager
一、项目创建
首先搭建以下目录结构
1. 数据持久接口：IDAL
2.数据持久实现：DAL
3.业务逻辑接口：IBLL
4.业务逻辑实现：BLL
5.业务规则接口：IBussness
6.业务规则实现：Bussness
7.数据实体：Entity
8.模型：Model
9.公共工具：Common
10.页面UI：Web
其中我将常规三层的业务逻辑层分为了业务逻辑与业务规则两部分，这样分层的优点是，便于后期维护；缺点是工作量的增加。
二、第三方库的加入
项目搭建完了自然就是加入一些第三方框架。
这里我选择的ORM框架为SqlSugar：
分别在Common、Entity、IDAL与DAL项目依赖项中右键-管理NuGet程序包-搜索sqlSugarCore-安装(此处为5.0.0.9版本)
这个项目中我将sqlSugar数据库链接类放入到Common中，以便未来其他项目中可以直接复用Common项目，所以才在Common中引入sqlSugarCore。
首先在Common中新建BaseDbContext类：
using System; using System.Collections.Generic; using System.Configuration; using System.Text; using Microsoft.Extensions.Configuration; using SqlSugar; namespace DataManager.Common.Db { /// <summary> /// 数据库操作基类 /// </summary> public class BaseDbContext { public SqlSugarClient Db; /// <summary> /// 构造函数 /// </summary> public BaseDbContext(IConfiguration configuration) { try { //主库 var connMain = ConfigurationManager.AppSettings["ConnMain"]; //从库 var connFrom = ConfigurationManager.AppSettings["ConnFrom"]; InitDataBase(connFrom == null ? new List<string> {connMain.ToString()} : new List<string> {connMain.ToString(), connFrom.ToString()}); } catch (Exception ex) { throw new Exception("未配置数据库连接字符串"); } } /// <summary> /// 构造函数 /// </summary> /// <param name="listConnSettings"> /// 连接字符串配置Key集合,配置多个连接则是读写分离 /// </param> public BaseDbContext(List<string> listConnSettings) { try { var listConn = new List<string>(); foreach (var t in listConnSettings) { listConn.Add(ConfigurationManager.ConnectionStrings[t].ToString()); } InitDataBase(listConn); } catch { throw new Exception("未配置数据库连接字符串"); } } /// <summary> /// 构造函数 /// </summary> /// <param name="serverIp">服务器IP</param> /// <param name="user">用户名</param> /// <param name="pass">密码</param> /// <param name="dataBase">数据库</param> public BaseDbContext(string serverIp, string user, string pass, string dataBase) { InitDataBase(new List<string> {$"server={serverIp};user id={user};password={pass};persistsecurityinfo=True;database={dataBase}"}); } /// <summary> /// 初始化数据库连接 /// </summary> /// <param name="listConn">连接字符串</param> private void InitDataBase(List<string> listConn) { var connStr = ""; //主库 var slaveConnectionConfigs = new List<SlaveConnectionConfig>(); //从库集合 for (var i = 0; i < listConn.Count; i++) { if (i == 0) { connStr = listConn[i]; //主数据库连接 } else { slaveConnectionConfigs.Add(new SlaveConnectionConfig() { HitRate = i * 2, ConnectionString = listConn[i] }); } } //如果配置了 SlaveConnectionConfigs那就是主从模式,所有的写入删除更新都走主库，查询走从库， //事务内都走主库，HitRate表示权重 值越大执行的次数越高，如果想停掉哪个连接可以把HitRate设为0 var ctx = new ConfigureExternalServices(); Db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = connStr, DbType = DbType.SqlServer, IsAutoCloseConnection = true, SlaveConnectionConfigs = slaveConnectionConfigs }); Db.Ado.CommandTimeOut = 30000; //设置超时时间 Db.Aop.OnLogExecuted = (sql, pars) => //SQL执行完事件 { }; Db.Aop.OnLogExecuting = (sql, pars) => //SQL执行前事件 { }; Db.Aop.OnError = (exp) => //执行SQL 错误事件 { throw new Exception("出错SQL：" + exp.Sql + "\r\n" + exp.Message); }; Db.Aop.OnExecutingChangeSql = (sql, pars) => //SQL执行前 可以修改SQL { return new KeyValuePair<string, SugarParameter[]>(sql, pars); }; } public SqlSugarClient GetClient() => Db; } }
查询排序条件类：
using System; using System.Collections.Generic; using System.Text; using SqlSugar; namespace DataManager.Common { /// <summary> /// 排序类型 /// </summary> public enum OrderSequence { Asc, Desc } /// <summary> /// 排序枚举 /// </summary> public class OrderByClause { public string Sort { get; set; } public OrderSequence Order { get; set; } } /// <summary> /// 查询条件 /// </summary> public class QueryDescriptor { /// <summary> /// 行数 /// </summary> public int PageSize { get; set; } /// <summary> /// 页码 /// </summary> public int PageIndex { get; set; } /// <summary> /// 排序 /// </summary> public List<OrderByClause> OrderBys { get; set; } /// <summary> /// 条件 /// </summary> public List<ConditionalModel> Conditions { get; set; } } }
在IDAL中新建仓储基类接口IBaseRepository：
using System; using System.Collections.Generic; using System.Data; using System.Linq.Expressions; using DataManager.Common; using SqlSugar; namespace DataManager.IDAL { public interface IBaseRepository<T> : IDisposable where T : class, new() { #region 事务 /// <summary> /// 初始化事务 /// </summary> /// <param name="level"></param> void BeginTran(IsolationLevel level = IsolationLevel.ReadCommitted); /// <summary> /// 完成事务 /// </summary> void CommitTran(); /// <summary> /// 完成事务 /// </summary> void RollbackTran(); #endregion #region 新增 /// <summary> /// 新增 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entity"> 实体对象 </param> /// <param name="isLock">是否加锁</param> /// <returns>操作影响的行数</returns> int Add<T>(T entity, bool isLock = false) where T : class, new(); /// <summary> /// 新增 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entitys">泛型集合</param> /// <param name="isLock">是否加锁</param> /// <returns>操作影响的行数</returns> int Add<T>(List<T> entitys, bool isLock = false) where T : class, new(); /// <summary> /// 新增 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entity"> 实体对象 </param> /// <param name="isLock">是否加锁</param> /// <returns>返回实体</returns> T AddReturnEntity<T>(T entity, bool isLock = false) where T : class, new(); /// <summary> /// 新增 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entity"> 实体对象 </param> /// <param name="isLock">是否加锁</param> /// <returns>返回bool, 并将identity赋值到实体</returns> bool AddReturnBool<T>(T entity, bool isLock = false) where T : class, new(); /// <summary> /// 新增 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entitys">泛型集合</param> /// <param name="isLock">是否加锁</param> /// <returns>返回bool, 并将identity赋值到实体</returns> bool AddReturnBool<T>(List<T> entitys, bool isLock = false) where T : class, new(); #endregion #region 修改 /// <summary> /// 修改数据源 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <returns>数据源</returns> IUpdateable<T> Updateable<T>() where T : class, new(); /// <summary> /// 修改（主键是更新条件） /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entity"> 实体对象 </param> /// <param name="isLock"> 是否加锁 </param> /// <returns>操作影响的行数</returns> int Update<T>(T entity, bool isLock = false) where T : class, new(); /// <summary> /// 修改 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="update"> 字段集合 </param> /// <param name="where"> 条件 </param> /// <param name="isLock"> 是否加锁 </param> /// <returns>操作影响的行数</returns> int Update<T>(Expression<Func<T, T>> update, Expression<Func<T, bool>> where, bool isLock = false) where T : class, new(); /// <summary> /// 修改（主键是更新条件） /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entitys">泛型集合</param> /// <param name="isLock"> 是否加锁 </param> /// <returns>操作影响的行数</returns> int Update<T>(List<T> entitys, bool isLock = false) where T : class, new(); #endregion #region 删除 /// <summary> /// 删除（主键是条件） /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entity"> 实体对象 </param> /// <param name="isLock"> 是否加锁 </param> /// <returns>操作影响的行数</returns> int Delete<T>(T entity, bool isLock = false) where T : class, new(); /// <summary> /// 删除（主键是条件） /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="where"> 条件 </param> /// <param name="isLock"> 是否加锁 </param> /// <returns>操作影响的行数</returns> int Delete<T>(Expression<Func<T, bool>> where, bool isLock = false) where T : class, new(); #endregion #region 查询 /// <summary> /// 查询数据源 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <returns>数据源</returns> ISugarQueryable<T> Queryable<T>() where T : class, new(); /// <summary> /// 查询 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="whereLambda">查询表达式</param> /// <returns></returns> T Query<T>(Expression<Func<T, bool>> whereLambda) where T : class, new(); /// <summary> /// 查询集合 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="whereLambda">查询表达式</param> /// <returns>实体</returns> List<T> QueryList<T>(Expression<Func<T, bool>> whereLambda) where T : class, new(); /// <summary> /// 查询集合 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="sql">sql</param> /// <returns>实体</returns> List<T> QueryList<T>(string sql) where T : class, new(); /// <summary> /// 查询集合 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="whereLambda">查询表达式</param> /// <returns>实体</returns> DataTable QueryDataTable<T>(Expression<Func<T, bool>> whereLambda) where T : class, new(); /// <summary> /// 查询集合 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="sql">sql</param> /// <returns>实体</returns> DataTable QueryDataTable<T>(string sql) where T : class, new(); /// <summary> /// 分页查询 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="query">查询条件</param> /// <param name="totalCount">总行数</param> /// <returns>实体</returns> List<T> QueryPageList<T>(QueryDescriptor query, out int totalCount) where T : class, new(); /// <summary> /// 分页查询 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="query">查询条件</param> /// <param name="totalCount">总行数</param> /// <returns>实体</returns> DataTable QueryDataTablePageList<T>(QueryDescriptor query, out int totalCount) where T : class, new(); /// <summary> /// 查询集合 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="whereLambda">查询表达式</param> /// <returns>Json</returns> string QueryJson<T>(Expression<Func<T, bool>> whereLambda) where T : class, new(); /// <summary> /// 查询存储过程 /// </summary> /// <param name="procedureName">存储过程名称</param> /// <param name="parameters">参数</param> DataTable QueryProcedure(string procedureName, List<SugarParameter> parameters); /// <summary> /// 查询前多少条数据 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="whereLambda">查询表达式</param> /// <param name="num">数量</param> /// <returns></returns> List<T> Take<T>(Expression<Func<T, bool>> whereLambda, int num) where T : class, new(); /// <summary> /// 查询单条数据 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="whereLambda">查询表达式</param> /// <returns></returns> T First<T>(Expression<Func<T, bool>> whereLambda) where T : class, new(); /// <summary> /// 是否存在 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="whereLambda">查询表达式</param> /// <returns></returns> bool IsExist<T>(Expression<Func<T, bool>> whereLambda) where T : class, new(); /// <summary> /// 合计 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="field">字段</param> /// <returns></returns> int Sum<T>(string field) where T : class, new(); /// <summary> /// 最大值 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="field">字段</param> /// <returns></returns> object Max<T>(string field) where T : class, new(); /// <summary> /// 最小值 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="field">字段</param> /// <returns></returns> object Min<T>(string field) where T : class, new(); /// <summary> /// 平均值 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="field">字段</param> /// <returns></returns> int Avg<T>(string field) where T : class, new(); #endregion } }
在DAL中新建仓储基类的实现，在sqlSugar框架中，作者直接将SqlSugarClient默认单例，所以此处不用再做处理可以直接在仓储基类中继承BaseDbContext方便直接操作数据库：
using System; using System.Collections.Generic; using System.Data; using System.Linq.Expressions; using System.Text; using DataManager.Common; using DataManager.Common.Db; using DataManager.IDAL; using Microsoft.Extensions.Configuration; using SqlSugar; namespace DataManager.DAL { public class BaseService<T> : BaseDbContext, IBaseRepository<T> where T : class, new() { public SqlSugarClient DbContext; public BaseService(IConfiguration configuration) : base(configuration) { DbContext = GetClient(); } #region 事务 /// <summary> /// 初始化事务 /// </summary> /// <param name="level"></param> public void BeginTran(IsolationLevel level = IsolationLevel.ReadCommitted) { DbContext.Ado.BeginTran(IsolationLevel.Unspecified); } /// <summary> /// 完成事务 /// </summary> public void CommitTran() { DbContext.Ado.CommitTran(); } /// <summary> /// 完成事务 /// </summary> public void RollbackTran() { DbContext.Ado.RollbackTran(); } #endregion #region 新增 /// <summary> /// 新增 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entity"> 实体对象 </param> /// <param name="isLock">是否加锁</param> /// <returns>操作影响的行数</returns> public int Add<T>(T entity, bool isLock = false) where T : class, new() { try { var result = isLock ? DbContext.Insertable(entity).With(SqlWith.UpdLock).ExecuteCommand() : DbContext.Insertable(entity).ExecuteCommand(); return result; } catch (Exception ex) { RollbackTran(); throw new Exception(ex.Message); } } /// <summary> /// 新增 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entitys">泛型集合</param> /// <param name="isLock">是否加锁</param> /// <returns>操作影响的行数</returns> public int Add<T>(List<T> entitys, bool isLock = false) where T : class, new() { try { var result = isLock ? DbContext.Insertable(entitys).With(SqlWith.UpdLock).ExecuteCommand() : DbContext.Insertable(entitys).ExecuteCommand(); return result; } catch (Exception ex) { RollbackTran(); throw new Exception(ex.Message); } } /// <summary> /// 新增 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entity"> 实体对象 </param> /// <param name="isLock">是否加锁</param> /// <returns>返回实体</returns> public T AddReturnEntity<T>(T entity, bool isLock = false) where T : class, new() { try { var result = isLock ? DbContext.Insertable(entity).With(SqlWith.UpdLock).ExecuteReturnEntity() : DbContext.Insertable(entity).ExecuteReturnEntity(); return result; } catch (Exception ex) { RollbackTran(); throw new Exception(ex.Message); } } /// <summary> /// 新增 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entity"> 实体对象 </param> /// <param name="isLock">是否加锁</param> /// <returns>返回bool, 并将identity赋值到实体</returns> public bool AddReturnBool<T>(T entity, bool isLock = false) where T : class, new() { try { var result = isLock ? DbContext.Insertable(entity).With(SqlWith.UpdLock).ExecuteCommandIdentityIntoEntity() : DbContext.Insertable(entity).ExecuteCommandIdentityIntoEntity(); return result; } catch (Exception ex) { RollbackTran(); throw new Exception(ex.Message); } } /// <summary> /// 新增 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entitys">泛型集合</param> /// <param name="isLock">是否加锁</param> /// <returns>返回bool, 并将identity赋值到实体</returns> public bool AddReturnBool<T>(List<T> entitys, bool isLock = false) where T : class, new() { try { var result = isLock ? DbContext.Insertable(entitys).With(SqlWith.UpdLock).ExecuteCommandIdentityIntoEntity() : DbContext.Insertable(entitys).ExecuteCommandIdentityIntoEntity(); return result; } catch (Exception ex) { RollbackTran(); throw new Exception(ex.Message); } } #endregion #region 修改 /// <summary> /// 修改数据源 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <returns>数据源</returns> public IUpdateable<T> Updateable<T>() where T : class, new() { return DbContext.Updateable<T>(); } /// <summary> /// 修改（主键是更新条件） /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entity"> 实体对象 </param> /// <param name="isLock"> 是否加锁 </param> /// <returns>操作影响的行数</returns> public int Update<T>(T entity, bool isLock = false) where T : class, new() { try { var result = isLock ? DbContext.Updateable(entity).With(SqlWith.UpdLock).ExecuteCommand() : DbContext.Updateable(entity).ExecuteCommand(); return result; } catch (Exception ex) { RollbackTran(); throw new Exception(ex.Message); } } /// <summary> /// 修改 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="update"> 实体对象 </param> /// <param name="where"> 条件 </param> /// <param name="isLock"> 是否加锁 </param> /// <returns>操作影响的行数</returns> public int Update<T>(Expression<Func<T, T>> update, Expression<Func<T, bool>> where, bool isLock = false) where T : class, new() { try { var result = isLock ? DbContext.Updateable<T>().SetColumns(update).Where(where).With(SqlWith.UpdLock) .ExecuteCommand() : DbContext.Updateable<T>().SetColumns(update).Where(where).ExecuteCommand(); return result; } catch (Exception ex) { RollbackTran(); throw new Exception(ex.Message); } } /// <summary> /// 修改（主键是更新条件） /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entitys"> 实体对象集合 </param> /// <param name="isLock"> 是否加锁 </param> /// <returns>操作影响的行数</returns> public int Update<T>(List<T> entitys, bool isLock = false) where T : class, new() { try { var result = isLock ? DbContext.Updateable(entitys).With(SqlWith.UpdLock).ExecuteCommand() : DbContext.Updateable(entitys).ExecuteCommand(); return result; } catch (Exception ex) { RollbackTran(); throw new Exception(ex.Message); } } #endregion #region 删除 /// <summary> /// 删除 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="entity"> 实体对象 </param> /// <param name="isLock"> 是否加锁 </param> /// <returns>操作影响的行数</returns> public int Delete<T>(T entity, bool isLock = false) where T : class, new() { try { var result = isLock ? DbContext.Deleteable(entity).With(SqlWith.UpdLock).ExecuteCommand() : DbContext.Deleteable(entity).ExecuteCommand(); return result; } catch (Exception ex) { RollbackTran(); throw new Exception(ex.Message); } } /// <summary> /// 删除 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="where"> 条件 </param> /// <param name="isLock"> 是否加锁 </param> /// <returns>操作影响的行数</returns> public int Delete<T>(Expression<Func<T, bool>> where, bool isLock = false) where T : class, new() { try { var result = isLock ? DbContext.Deleteable<T>().Where(where).With(SqlWith.UpdLock).ExecuteCommand() : DbContext.Deleteable<T>().Where(where).ExecuteCommand(); return result; } catch (Exception ex) { RollbackTran(); throw new Exception(ex.Message); } } #endregion #region 查询 /// <summary> /// 查询数据源 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <returns>数据源</returns> public ISugarQueryable<T> Queryable<T>() where T : class, new() { return DbContext.Queryable<T>(); } /// <summary> /// 查询 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="whereLambda">查询表达式</param> /// <returns></returns> public T Query<T>(Expression<Func<T, bool>> whereLambda) where T : class, new() { return DbContext.Queryable<T>().With(SqlWith.NoLock).Where(whereLambda).First(); var str = DbContext.Queryable<T>().With(SqlWith.NoLock).Where(whereLambda).ToSql(); Console.WriteLine(str); return null; } /// <summary> /// 查询集合 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="whereLambda">查询表达式</param> /// <returns>实体</returns> public List<T> QueryList<T>(Expression<Func<T, bool>> whereLambda) where T : class, new() { return DbContext.Queryable<T>().With(SqlWith.NoLock).Where(whereLambda).ToList(); } /// <summary> /// 查询集合 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="sql">sql</param> /// <returns>实体</returns> public List<T> QueryList<T>(string sql) where T : class, new() { return DbContext.SqlQueryable<T>(sql).With(SqlWith.NoLock).ToList(); } /// <summary> /// 查询集合 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="whereLambda">查询表达式</param> /// <returns>实体</returns> public DataTable QueryDataTable<T>(Expression<Func<T, bool>> whereLambda) where T : class, new() { return DbContext.Queryable<T>().With(SqlWith.NoLock).Where(whereLambda).ToDataTable(); } /// <summary> /// 查询集合 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="sql">sql</param> /// <returns>实体</returns> public DataTable QueryDataTable<T>(string sql) where T : class, new() { return DbContext.SqlQueryable<T>(sql).With(SqlWith.NoLock).ToDataTable(); } /// <summary> /// 分页查询 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="query">查询条件</param> /// <param name="totalCount">总行数</param> /// <returns>实体</returns> public List<T> QueryPageList<T>(QueryDescriptor query, out int totalCount) where T : class, new() { if (query == null) { throw new ArgumentNullException(nameof(query)); } var listDatas = DbContext.Queryable<T>(); if (query.Conditions != null) { var conds = ParseCondition(query.Conditions); listDatas = listDatas.Where(conds); } if (query.OrderBys != null) { var orderBys = ParseOrderBy(query.OrderBys); listDatas = listDatas.OrderBy(orderBys); } totalCount = 0; var datas = listDatas.ToPageList(query.PageIndex, query.PageSize, ref totalCount); return datas; } /// <summary> /// 分页查询 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="query">查询条件</param> /// <param name="totalCount">总行数</param> /// <returns>DataTable</returns> public DataTable QueryDataTablePageList<T>(QueryDescriptor query, out int totalCount) where T : class, new() { if (query == null) { throw new ArgumentNullException(nameof(query)); } var listDatas = DbContext.Queryable<T>(); if (query.Conditions != null) { var conds = ParseCondition(query.Conditions); listDatas = listDatas.Where(conds); } if (query.OrderBys != null) { var orderBys = ParseOrderBy(query.OrderBys); listDatas = listDatas.OrderBy(orderBys); } totalCount = 0; var datas = listDatas.ToDataTablePage(query.PageIndex, query.PageSize, ref totalCount); return datas; } /// <summary> /// 查询集合 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型)</typeparam> /// <param name="whereLambda">查询表达式</param> /// <returns>Json</returns> public string QueryJson<T>(Expression<Func<T, bool>> whereLambda) where T : class, new() { return DbContext.Queryable<T>().With(SqlWith.NoLock).Where(whereLambda).ToJson(); } /// <summary> /// 查询存储过程 /// </summary> /// <param name="procedureName">存储过程名称</param> /// <param name="parameters">参数</param>re public DataTable QueryProcedure(string procedureName, List<SugarParameter> parameters) { var datas = DbContext.Ado.UseStoredProcedure().GetDataTable(procedureName, parameters); return datas; } /// <summary> /// 查询前多少条数据 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="whereLambda">查询表达式</param> /// <param name="num">数量</param> /// <returns></returns> public List<T> Take<T>(Expression<Func<T, bool>> whereLambda, int num) where T : class, new() { var datas = DbContext.Queryable<T>().With(SqlWith.NoLock).Where(whereLambda).Take(num).ToList(); return datas; } /// <summary> /// 查询单条数据 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="whereLambda">查询表达式</param> /// <returns></returns> public T First<T>(Expression<Func<T, bool>> whereLambda) where T : class, new() { var datas = DbContext.Queryable<T>().With(SqlWith.NoLock).Where(whereLambda).First(); return datas; } /// <summary> /// 是否存在 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="whereLambda">查询表达式</param> /// <returns></returns> public bool IsExist<T>(Expression<Func<T, bool>> whereLambda) where T : class, new() { var datas = DbContext.Queryable<T>().Any(whereLambda); return datas; } /// <summary> /// 合计 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="field">字段</param> /// <returns></returns> public int Sum<T>(string field) where T : class, new() { var datas = DbContext.Queryable<T>().Sum<int>(field); return datas; } /// <summary> /// 最大值 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="field">字段</param> /// <returns></returns> public object Max<T>(string field) where T : class, new() { var datas = DbContext.Queryable<T>().Max<object>(field); return datas; } /// <summary> /// 最小值 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="field">字段</param> /// <returns></returns> public object Min<T>(string field) where T : class, new() { var datas = DbContext.Queryable<T>().Min<object>(field); return datas; } /// <summary> /// 平均值 /// </summary> /// <typeparam name="T">泛型参数(集合成员的类型</typeparam> /// <param name="field">字段</param> /// <returns></returns> public int Avg<T>(string field) where T : class, new() { var datas = DbContext.Queryable<T>().Avg<int>(field); return datas; } #endregion #region 私有方法 /// <summary> /// 查询条件转换 /// </summary> /// <param name="contitons">查询条件</param> /// <returns></returns> private List<IConditionalModel> ParseCondition(List<ConditionalModel> contitons) { var conds = new List<IConditionalModel>(); foreach (var con in contitons) { if (con.FieldName.Contains(",")) { conds.Add(ParseKeyOr(con)); } else { conds.Add(new ConditionalModel() { FieldName = con.FieldName, ConditionalType = con.ConditionalType, FieldValue = con.FieldValue }); } } return conds; } /// <summary> /// 转换Or条件 /// </summary> /// <param name="condition"></param> /// <returns></returns> private ConditionalCollections ParseKeyOr(ConditionalModel condition) { var objectKeys = condition.FieldName.Split(','); var conditionalList = new List<KeyValuePair<WhereType, ConditionalModel>>(); foreach (var objKey in objectKeys) { var cond = new KeyValuePair<WhereType, ConditionalModel> (WhereType.Or, new ConditionalModel() { FieldName = objKey, ConditionalType = condition.ConditionalType, FieldValue = condition.FieldValue }); conditionalList.Add(cond); } return new ConditionalCollections { ConditionalList = conditionalList }; } /// <summary> /// 排序转换 /// </summary> /// <param name="orderBys">排序</param> /// <returns></returns> private string ParseOrderBy(List<OrderByClause> orderBys) { var conds = ""; foreach (var con in orderBys) { if (con.Order == OrderSequence.Asc) { conds += $"{con.Sort} asc,"; } else if (con.Order == OrderSequence.Desc) { conds += $"{con.Sort} desc,"; } } return conds.TrimEnd(','); } #endregion /// <summary> /// 处理 /// </summary> public void Dispose() { RollbackTran(); DbContext.Close(); } } }
至此sqlSugar前期工作完成，后期新增的实体新建对应的仓库，直接继承BaseRepository。下一篇我会将Swagger加入到项目中。
