常規的中小型項目搭建方式一般是三層架構加上mvc與webapi作為一個主要框架,再加上一些第三方庫,例如orm框架(EF、SqlSugar、Dapper等),API文檔工具(Swagger)這些的應用。 接下來我們以一個數據管理平臺做為實戰項目來演示三層架構與倉儲模式。 項目Github連接: 一、 ...
常規的中小型項目搭建方式一般是三層架構加上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; } }DataManage.Common.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; } } }DataManager.Common
在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 } }DataManager.IDAL
在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