.NetCore学习笔记：二、基于Dapper的泛型Repository
为减少代码量，这里实现一个基于Dapper的泛型Repository。
这里需要引用Dapper.dll和Dapper.Contrib.dll。
接口定义：
1 /// <summary> 2 /// Repository接口 3 /// </summary> 4 /// <typeparam name="T"></typeparam> 5 public interface IRepository<T> where T : class, IEntity 6 { 7 /// <summary> 8 /// 查询 9 /// </summary> 10 /// <typeparam name="T"></typeparam> 11 /// <param name="sql"></param> 12 /// <param name="param"></param> 13 /// <param name="commandType"></param> 14 /// <returns></returns> 15 IEnumerable<T> Query(string sql, object param = null, CommandType? commandType = null); 16 17 /// <summary> 18 /// 删除行数据 19 /// </summary> 20 /// <typeparam name="T"></typeparam> 21 /// <param name="entity"></param> 22 /// <returns></returns> 23 bool Delete(T entity); 24 /// <summary> 25 /// 删除表所有数据 26 /// </summary> 27 /// <typeparam name="T"></typeparam> 28 /// <returns></returns> 29 bool DeleteAll(); 30 /// <summary> 31 /// 获取行数据 32 /// </summary> 33 /// <typeparam name="T"></typeparam> 34 /// <param name="id"></param> 35 /// <returns></returns> 36 T Get(object id); 37 /// <summary> 38 /// 获取表的所有数据 39 /// </summary> 40 /// <typeparam name="T"></typeparam> 41 /// <returns></returns> 42 IEnumerable<T> GetAll(); 43 /// <summary> 44 /// 添加行数据 45 /// </summary> 46 /// <typeparam name="T"></typeparam> 47 /// <param name="entity"></param> 48 /// <returns></returns> 49 long Insert(T entity); 50 /// <summary> 51 /// 更新行数据 52 /// </summary> 53 /// <typeparam name="T"></typeparam> 54 /// <param name="entity"></param> 55 /// <returns></returns> 56 bool Update(T entity); 57 58 /// <summary> 59 /// 分页方法 60 /// </summary> 61 /// <param name="sql"></param> 62 /// <param name="pageIndex">当前页码</param> 63 /// <param name="pageSize">每页显示条数</param> 64 /// <param name="param">参数</param> 65 /// <param name="transaction"></param> 66 /// <param name="commandTimeout"></param> 67 /// <returns></returns> 68 PagedResult<T> GetPageList(string sql, int pageIndex, int pageSize, object param = null); 69 }
接口实现：
1 /// <summary> 2 /// 泛型Repository 3 /// </summary> 4 /// <typeparam name="T"></typeparam> 5 public class Repository<T> : IRepository<T> where T : class, IEntity 6 { 7 private IUnitOfWork _unitOfWork; 8 9 public Repository(IUnitOfWork unitOfWork) 10 { 11 _unitOfWork = unitOfWork; 12 } 13 /// <summary> 14 /// 查询 15 /// </summary> 16 /// <typeparam name="T"></typeparam> 17 /// <param name="sql"></param> 18 /// <param name="param"></param> 19 /// <param name="commandType"></param> 20 /// <returns></returns> 21 public IEnumerable<T> Query(string sql, object param = null, CommandType? commandType = null) 22 { 23 var r = _unitOfWork.DbConnection.Query<T>(sql, param: param, transaction: _unitOfWork.DbTransaction, commandType: commandType); 24 return r; 25 } 26 /// <summary> 27 /// 删除行数据 28 /// </summary> 29 /// <typeparam name="T"></typeparam> 30 /// <param name="entityToDelete"></param> 31 /// <returns></returns> 32 public bool Delete(T entity) 33 { 34 var r = _unitOfWork.DbConnection.Delete<T>(entity, _unitOfWork.DbTransaction); 35 return r; 36 } 37 /// <summary> 38 /// 删除表所有数据 39 /// </summary> 40 /// <typeparam name="T"></typeparam> 41 /// <returns></returns> 42 public bool DeleteAll() 43 { 44 var r = _unitOfWork.DbConnection.DeleteAll<T>(_unitOfWork.DbTransaction); 45 return r; 46 } 47 /// <summary> 48 /// 获取行数据 49 /// </summary> 50 /// <typeparam name="T"></typeparam> 51 /// <param name="id"></param> 52 /// <returns></returns> 53 public T Get(object id) 54 { 55 var r = _unitOfWork.DbConnection.Get<T>(id, _unitOfWork.DbTransaction); 56 return r; 57 } 58 /// <summary> 59 /// 获取表的所有数据 60 /// </summary> 61 /// <typeparam name="T"></typeparam> 62 /// <returns></returns> 63 public IEnumerable<T> GetAll() 64 { 65 var r = _unitOfWork.DbConnection.GetAll<T>(_unitOfWork.DbTransaction); 66 return r; 67 } 68 /// <summary> 69 /// 添加行数据 70 /// </summary> 71 /// <typeparam name="T"></typeparam> 72 /// <param name="entity"></param> 73 /// <returns></returns> 74 public long Insert(T entity) 75 { 76 var r = _unitOfWork.DbConnection.Insert<T>(entity, _unitOfWork.DbTransaction); 77 return r; 78 } 79 /// <summary> 80 /// 更新行数据 81 /// </summary> 82 /// <typeparam name="T"></typeparam> 83 /// <param name="entity"></param> 84 /// <returns></returns> 85 public bool Update(T entity) 86 { 87 var r = _unitOfWork.DbConnection.Update<T>(entity, _unitOfWork.DbTransaction); 88 return r; 89 } 90 /// <summary> 91 /// 分页方法 92 /// </summary> 93 /// <param name="sql"></param> 94 /// <param name="pageIndex">当前页码</param> 95 /// <param name="pageSize">每页显示条数</param> 96 /// <param name="param">参数</param> 97 /// <param name="transaction"></param> 98 /// <param name="commandTimeout"></param> 99 /// <returns></returns> 100 public PagedResult<T> GetPageList(string sql, int pageIndex, int pageSize, object param = null) 101 { 102 var pagingResult = _unitOfWork.DbConnection.GetPageList<T>(sql, pageIndex, pageSize, param: param, transaction: _unitOfWork.DbTransaction); 103 return pagingResult; 104 } 105 }
GetPageList为Dapper的一个分页扩展：
1 /// <summary> 2 /// 分页方法 3 /// </summary> 4 /// <param name="sql"></param> 5 /// <param name="pageIndex">当前页码</param> 6 /// <param name="pageSize">每页显示条数</param> 7 /// <param name="param">参数</param> 8 /// <param name="transaction"></param> 9 /// <param name="commandTimeout"></param> 10 /// <returns></returns> 11 public static PagedResult<T> GetPageList<T>(this IDbConnection connection, string sql, int pageIndex, int pageSize, object param = null, IDbTransaction transaction = null, int? commandTimeout = null) 12 { 13 if (pageIndex < 1) pageIndex = 1; 14 if (pageSize < 1) pageSize = 20; 15 var startRow = (pageIndex - 1) * pageSize; 16 17 //MySql分页 18 sql = $"SELECT * FROM ({sql}) tt1 LIMIT {startRow},{pageSize}; SELECT COUNT(1) FROM ({sql}) tt2;"; 19 20 PagedResult<T> pagingResult = new PagedResult<T>(); 21 pagingResult.Paged.PageIndex = pageIndex; 22 pagingResult.Paged.PageSize = pageSize; 23 using (var result = connection.QueryMultiple(sql, param: param, transaction, commandTimeout)) 24 { 25 var list = result.Read<T>(); 26 var totalCount = result.Read<long>().FirstOrDefault(); 27 pagingResult.Data = list; 28 pagingResult.Paged.TotalRow = totalCount; 29 } 30 return pagingResult; 31 }
PagedResult类：
1 /// <summary> 2 /// 分页模型 3 /// </summary> 4 /// <typeparam name="T"></typeparam> 5 public class PagedResult<T> 6 { 7 public PagedResult() 8 { 9 this.Paged = new Paged(); 10 } 11 12 /// <summary> 13 /// 结果 14 /// </summary> 15 public IEnumerable<T> Data { get; set; } 16 17 /// <summary> 18 /// 分页数据，包含数据总行数、当前页码、页数据行数和总的分页数 19 /// </summary> 20 public Paged Paged { get; set; } 21 }
Paged类：
1 /// <summary> 2 /// 分页数据 3 /// </summary> 4 public class Paged 5 { 6 /// <summary> 7 /// 当前页码 8 /// </summary> 9 public int PageIndex { get; set; } 10 /// <summary> 11 /// 页数据行数 12 /// </summary> 13 public int PageSize { get; set; } 14 /// <summary> 15 /// 总行数 16 /// </summary> 17 public long TotalRow { get; set; } 18 /// <summary> 19 /// 总的分页数 20 /// </summary> 21 public int TotalPage 22 { 23 get 24 { 25 if (this.TotalRow > 0 && this.PageSize > 0) 26 return (int)Math.Ceiling((decimal)this.TotalRow / this.PageSize); 27 else 28 return 0; 29 } 30 } 31 }
在IServiceCollection容器中注册：
services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
