小白開學Asp.Net Core《二》 ——數據倉儲層(Repositroy) 一、歷史現象 在後端開發中,資料庫操作是最頻繁的,每一個開發人員都會接觸,甚至不少開發人員每天的工作就是與資料庫打交道。所以可見資料庫操作是多長重要。 在現在的開發過程中,大多數開發人員只是以編寫SQL語句的方式操作數據 ...
小白開學Asp.Net Core《二》
——數據倉儲層(Repositroy)
一、歷史現象
在後端開發中,資料庫操作是最頻繁的,每一個開發人員都會接觸,甚至不少開發人員每天的工作就是與資料庫打交道。所以可見資料庫操作是多長重要。
在現在的開發過程中,大多數開發人員只是以編寫SQL語句的方式操作資料庫,這種方式是操作資料庫最原始的方式,簡單高效,但是在編寫SQL語句的過程中,極容易因粗心大意寫出BUG,這樣就會出現一種現象,開發人員面對一堆SQL語句的DEBUG,而且每次都需要開發人員自己去手寫SQL語句,其開發效率極低。不同的資料庫所需的SQL語句也還是有差異的,這就需要開發人員學習不同的資料庫SQL語法。而且在項目開發中難免會遇到更換資料庫的情況,這時還需要花費大量的精力去修改SQL語句。
二、數據倉儲層
由於以上缺點,所以倉儲層因此而生。
數據倉儲層主要是對資料庫操作CRUD的封裝。使開發人員進行CRUD只需要極為簡單的代碼即可完成。本層還提供了資料庫事務的支持,為資料庫操作提供必備的保障。使用本層提供的介面,無需關心具體的業務邏輯實現,哪怕更換資料庫,也無需更改業務邏輯代碼,只需要更改簡單的資料庫配置即可。總之,本層為開發人員對資料庫的操作提供了簡單高效的操作介面。
三、繼承關係圖
(繼承圖)
(介面定義)
四、代碼實現
1 #region Async 2 /// <summary> 3 /// 查詢所有數據(無分頁,請慎用) 4 /// </summary> 5 /// <returns></returns> 6 Task<IEnumerable<T>> FindAllAsync(); 7 8 /// <summary> 9 /// 根據條件查詢數據 10 /// </summary> 11 /// <param name="predicate">條件表達式樹</param> 12 /// <param name="orderBy">排序</param> 13 /// <returns>泛型實體集合</returns> 14 Task<IEnumerable<T>> FindListByClauseAsync(Expression<Func<T, bool>> predicate, string orderBy = ""); 15 16 Task<IEnumerable<T>> PageQueryAsync(Expression<Func<T, bool>> predicate, int skip = 0, int pageSize = 15, string orderBy = ""); 17 Task<T> FindByClauseAsync(Expression<Func<T, bool>> predicate); 18 /// <summary> 19 /// 插入實體數據 20 /// </summary> 21 /// <param name="entity"></param> 22 /// <returns></returns> 23 Task<int> InsertAsync(T entity); 24 /// <summary> 25 /// 更新實體數據 26 /// </summary> 27 /// <param name="entity"></param> 28 /// <returns></returns> 29 Task<bool> UpdateAsync(T entity); 30 /// <summary> 31 /// DeleteAsync 32 /// </summary> 33 /// <param name="entity"></param> 34 /// <returns></returns> 35 Task<bool> DeleteAsync(T entity); 36 /// <summary> 37 /// DeleteAsync 38 /// </summary> 39 /// <param name="where">條件表達式</param> 40 /// <returns></returns> 41 Task<bool> DeleteAsync(Expression<Func<T, bool>> @where); 42 /// <summary> 43 /// DeleteByIdAsync 44 /// </summary> 45 /// <param name="id"></param> 46 /// <returns></returns> 47 Task<bool> DeleteByIdAsync(object id); 48 /// <summary> 49 /// DeleteByIdsAsync 50 /// </summary> 51 /// <param name="ids">ids</param> 52 /// <returns></returns> 53 Task<bool> DeleteByIdsAsync(object[] ids); 54 /// <summary> 55 /// InsertAsync 56 /// </summary> 57 /// <param name="t"></param> 58 /// <returns></returns> 59 Task<DbResult<Task<int>>> InsertAsync(List<T> t); 60 /// <summary> 61 /// DeleteByClauseAsync 62 /// </summary> 63 /// <param name="predicate"></param> 64 /// <returns></returns> 65 Task<int> DeleteByClauseAsync(Expression<Func<T, bool>> predicate); 66 67 /// <summary> 68 /// 事務 69 /// </summary> 70 /// <param name="func"></param> 71 /// <returns></returns> 72 Task<DbResult<T>> UserTranAsync(Func<T> func); 73 74 /// <summary> 75 /// 事務 76 /// </summary> 77 /// <param name="action"></param> 78 Task<DbResult<bool>> UserTranAsync(Action action); 79 #endregion
五、實現
/// <summary> /// GenericRepositoryBase /// </summary> /// <typeparam name="T"></typeparam> public class GenericSqlSugarRepositoryBase<T> : ISqlSugarRepository<T> where T : class, new() { #region Sync /// <summary> /// 根據主鍵查詢 /// </summary> /// <param name="pkValue">主鍵</param> /// <returns></returns> public T FindById(object pkValue) { using (var db = DbFactory.DB) { return db.Queryable<T>().InSingle(pkValue); } } /// <summary> /// 查詢所有數據(無分頁,請慎用) /// </summary> /// <returns></returns> public IEnumerable<T> FindAll() { using (var db = DbFactory.DB) { return db.Queryable<T>().ToList(); } } /// <summary> /// 根據條件查詢數據 /// </summary> /// <param name="predicate">條件表達式樹</param> /// <param name="orderBy">排序</param> /// <returns>泛型實體集合</returns> public IEnumerable<T> FindListByClause(Expression<Func<T, bool>> predicate, string orderBy = "") { using (var db = DbFactory.DB) { var query = db.Queryable<T>().Where(predicate); if (!string.IsNullOrEmpty(orderBy)) query.OrderBy(orderBy); return query.ToList(); } } /// <summary> /// 根據條件分頁查詢 /// </summary> /// <param name="predicate">條件表達式樹</param> /// <param name="skip"></param> /// <param name="pageSize"></param> /// <param name="orderBy"></param> /// <returns></returns> public IEnumerable<T> PageQuery(Expression<Func<T, bool>> predicate, int skip, int pageSize = 15, string orderBy = "") { using (var db = DbFactory.DB) { if (skip > 0) skip = pageSize * skip; var query = db.Queryable<T>().Where(predicate).Skip(skip).Take(pageSize); if (!string.IsNullOrEmpty(orderBy)) query = query.OrderBy(orderBy); return query.ToList(); } } /// <summary> /// 根據條件查詢數據 /// </summary> /// <param name="predicate">條件表達式樹</param> /// <returns></returns> public T FindByClause(Expression<Func<T, bool>> predicate) { using (var db = DbFactory.DB) { return db.Queryable<T>().First(predicate); } } /// <summary> /// 寫入實體數據 /// </summary> /// <param name="entity">實體類</param> /// <returns></returns> public int Insert(T entity) { using (var db = DbFactory.DB) { return db.Insertable(entity).ExecuteCommand(); } } /// <summary> /// 更新實體數據 /// </summary> /// <param name="entity"></param> /// <returns></returns> public bool Update(T entity) { using (var db = DbFactory.DB) { return db.Updateable(entity).ExecuteCommand() > 0; } } /// <summary> /// 刪除數據 /// </summary> /// <param name="entity">實體類</param> /// <returns></returns> public bool Delete(T entity) { using (var db = DbFactory.DB) { return db.Deleteable(entity).ExecuteCommand() > 0; } } /// <summary> /// 刪除數據 /// </summary> /// <param name="where">過濾條件</param> /// <returns></returns> public bool Delete(Expression<Func<T, bool>> @where) { using (var db = DbFactory.DB) { return db.Deleteable<T>(@where).ExecuteCommand() > 0; } } /// <summary> /// 刪除指定ID的數據 /// </summary> /// <param name="id"></param> /// <returns></returns> public bool DeleteById(object id) { using (var db = DbFactory.DB) { return db.Deleteable<T>(id).ExecuteCommand() > 0; } } /// <summary> /// 刪除指定ID集合的數據(批量刪除) /// </summary> /// <param name="ids"></param> /// <returns></returns> public bool DeleteByIds(object[] ids) { using (var db = DbFactory.DB) { return db.Deleteable<T>().In(ids).ExecuteCommand() > 0; } } /// <summary> /// 執行sql語句 /// </summary> /// <param name="sql">sql 語句</param> /// <param name="parameters">參數</param> /// <returns></returns> public IEnumerable<T> FindListBySql(string sql, object dynamic) { using (var db = DbFactory.DB) { return db.Ado.SqlQuery<T>(sql, dynamic); } } /// <summary> /// 批量插入 插入失敗時 事務會自動回退 /// </summary> /// <param name="t"></param> /// <returns></returns> public int Insert(List<T> t) { using (var db = DbFactory.DB) { return db.Ado.UseTran(() => db.Insertable(t.ToArray()).ExecuteCommand()).Data; } } /// <summary> /// 事務 /// </summary> /// <param name="func"></param> /// <returns></returns> public DbResult<T> UserTran(Func<T> func) { using (var db = DbFactory.DB) { return db.Ado.UseTran(func.Invoke); } } /// <summary> /// 事務 /// </summary> /// <param name="action"></param> public DbResult<bool> UserTran(Action action) { using (var db = DbFactory.DB) { return db.Ado.UseTran(action.Invoke); } } /// <summary> /// 根據條件批量刪除 /// </summary> /// <param name="predicate"></param> /// <returns></returns> public int DeleteByClause(Expression<Func<T, bool>> predicate) { using (var db = DbFactory.DB) { return db.Deleteable<T>().Where(predicate).ExecuteCommand(); } } // void ShadowCopy(object a, object b); /// <summary> /// 分頁查詢 /// </summary> /// <param name="predicate"></param> /// <param name="pagination"></param> /// <returns></returns> public List<T> FindList(Expression<Func<T, bool>> predicate, Pagination pagination) { var isAsc = pagination.sord.ToLower() == "asc"; string[] _order = pagination.sidx.Split(','); MethodCallExpression resultExp = null; using (var db = DbFactory.DB) { var tempData = db.Queryable<T>().Where(predicate).ToList().AsQueryable(); foreach (string item in _order) { string _orderPart = item; _orderPart = Regex.Replace(_orderPart, @"\s+", " "); string[] _orderArry = _orderPart.Split(' '); string _orderField = _orderArry[0]; bool sort = isAsc; if (_orderArry.Length == 2) { isAsc = _orderArry[1].ToUpper() == "ASC"; } var parameter = Expression.Parameter(typeof(T), "t"); var property = typeof(T).GetProperty(_orderField); var propertyAccess = Expression.MakeMemberAccess(parameter, property); var orderByExp = Expression.Lambda(propertyAccess, parameter); resultExp = Expression.Call(typeof(Queryable), isAsc ? "OrderBy" : "OrderByDescending", new Type[] { typeof(T), property.PropertyType }, tempData.Expression, Expression.Quote(orderByExp)); } tempData = tempData.Provider.CreateQuery<T>(resultExp); pagination.records = tempData.Count(); tempData = tempData.Skip<T>(pagination.rows * (pagination.page - 1)).Take<T>(pagination.rows).AsQueryable(); return tempData.ToList(); } } #endregion #region Async /// <summary> /// 查詢所有數據(無分頁,請慎用) /// </summary> /// <returns></returns> public async Task<IEnumerable<T>> FindAllAsync() { using (var db = DbFactory.DB) { return await db.Queryable<T>().ToListAsync(); } } /// <summary> /// 根據條件查詢數據 /// </summary> /// <param name="predicate">條件表達式樹</param> /// <param name="orderBy">排序</param> /// <returns>泛型實體集合</returns> public async Task<IEnumerable<T>> FindListByClauseAsync(Expression<Func<T, bool>> predicate, string orderBy = "") { using (var db = DbFactory.DB) { var query = db.Queryable<T>().Where(predicate); if (!string.IsNullOrEmpty(orderBy)) { query = query.OrderBy(orderBy); } return await query.ToListAsync(); } } public async Task<IEnumerable<T>> PageQueryAsync(Expression<Func<T, bool>> predicate, int skip = 0, int pageSize = 15, string orderBy = "") { using (var db = DbFactory.DB) { if (skip > 0) skip = pageSize * skip; var query = db.Queryable<T>().Where(predicate).Skip(skip).Take(pageSize); if (!string.IsNullOrEmpty(orderBy)) { query = query.OrderBy(orderBy); } return await query.ToListAsync(); } } public async Task<T> FindByClauseAsync(Expression<Func<T, bool>> predicate) { using (var db = DbFactory.DB) { return await db.Queryable<T>().FirstAsync(predicate); } } /// <summary> /// 插入實體數據 /// </summary> /// <param name="entity"></param> /// <returns></returns> public async Task<int> InsertAsync(T entity) { using (var db = DbFactory.DB) { return await db.Insertable(entity).ExecuteCommandAsync(); } } /// <summary> /// 更新實體數據 /// </summary> /// <param name="entity"></param> /// <returns></returns> public async Task<bool> UpdateAsync(T entity) { using (var db = DbFactory.DB) { return await db.Updateable(entity).ExecuteCommandAsync() > 0; } } /// <summary> /// DeleteAsync /// </summary> /// <param name="entity"></param> /// <returns></returns> public async Task<bool> DeleteAsync(T entity) { using (var db = DbFactory.DB) { return await db.Deleteable(entity).ExecuteCommandAsync() > 0; } } /// <summary> /// DeleteAsync /// </summary> /// <param name="where">條件表達式</param> /// <returns></returns> public async Task<bool> DeleteAsync(Expression<Func<T, bool>> @where) { using (var db = DbFactory.DB) { return await db.Deleteable(@where).ExecuteCommandAsync() > 0; } } /// <summary> /// DeleteByIdAsync /// </summary> /// <param name="id"></param> /// <returns></returns> public async Task<bool> DeleteByIdAsync(object id) { using (var db = DbFactory.DB) { return await db.Deleteable<T>(id).ExecuteCommandAsync() > 0; } } /// <summary> /// DeleteByIdsAsync /// </summary> /// <param name="ids">ids</param> /// <returns></returns> public async Task<bool> DeleteByIdsAsync(object[] ids) { using (var db = DbFactory.DB) { return await db.Deleteable<T>().In(ids).ExecuteCommandAsync() > 0; } } /// <summary> /// InsertAsync /// </summary> /// <param name="t"></param> /// <returns></returns> public async Task<DbResult<Task<int>>> InsertAsync(List<T> t) { using (var db = DbFactory.DB) { return await db.Ado.UseTranAsync(async () => await db.Insertable(t.ToArray()).ExecuteCommandAsync()); } } /// <summary> /// DeleteByClauseAsync /// </summary> /// <param name="predicate"></param> /// <returns></returns> public async Task<int> DeleteByClauseAsync(Expression<Func<T, bool>> predicate) { using (var db = DbFactory.DB) { return await db.Deleteable<T>().Where(predicate).ExecuteCommandAsync(); } } /// <summary> /// 事務 /// </summary> /// <param name="func"></param> /// <returns></returns> public async Task<DbResult<T>> UserTranAsync(Func<T> func) { using (var db = DbFactory.DB) { return await db.Ado.UseTranAsync(func.Invoke); } } /// <summary> /// 事務 /// </summary> /// <param name="action"></param> public async Task<DbResult<bool>> UserTranAsync(Action action) { using (var db = DbFactory.DB) { return await db.Ado.UseTranAsync(action.Invoke); } } #endregion }
本篇就到這裡,下篇將介紹業務邏輯層與倉儲庫之間的關係與實現
(本人堅信:學習是由淺到深的過程,先打基礎)
不喜勿噴!謝謝!
GitHub地址:
https://github.com/AjuPrince/Aju.Carefree