為減少代碼量，這里實現(xiàn)一個基于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 /// 刪除行數(shù)據(jù)19 /// </summary>20 /// <typeparam name="T"></typeparam>21 /// <param name="entity"></param>22 /// <returns></returns>23 bool Delete(T entity);24 /// <summary>25 /// 刪除表所有數(shù)據(jù)26 /// </summary>27 /// <typeparam name="T"></typeparam>28 /// <returns></returns>29 bool DeleteAll();30 /// <summary>31 /// 獲取行數(shù)據(jù)32 /// </summary>33 /// <typeparam name="T"></typeparam>34 /// <param name="id"></param>35 /// <returns></returns>36 T Get(object id);37 /// <summary>38 /// 獲取表的所有數(shù)據(jù)39 /// </summary>40 /// <typeparam name="T"></typeparam>41 /// <returns></returns>42 IEnumerable<T> GetAll();43 /// <summary>44 /// 添加行數(shù)據(jù)45 /// </summary>46 /// <typeparam name="T"></typeparam>47 /// <param name="entity"></param>48 /// <returns></returns>49 long Insert(T entity);50 /// <summary>51 /// 更新行數(shù)據(jù)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">每頁顯示條數(shù)</param>64 /// <param name="param">參數(shù)</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 }
接口實現(xiàn)：
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 /// 刪除行數(shù)據(jù) 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 /// 刪除表所有數(shù)據(jù) 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 /// 獲取行數(shù)據(jù) 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 /// 獲取表的所有數(shù)據(jù) 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 /// 添加行數(shù)據(jù) 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 /// 更新行數(shù)據(jù) 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">每頁顯示條數(shù)</param> 96 /// <param name="param">參數(shù)</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">每頁顯示條數(shù)</param> 7 /// <param name="param">參數(shù)</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 /// 結(jié)果14 /// </summary>15 public IEnumerable<T> Data { get; set; }16 17 /// <summary>18 /// 分頁數(shù)據(jù)，包含數(shù)據(jù)總行數(shù)、當前頁碼、頁數(shù)據(jù)行數(shù)和總的分頁數(shù)19 /// </summary>20 public Paged Paged { get; set; }21 }
Paged類：
1 /// <summary> 2 /// 分頁數(shù)據(jù) 3 /// </summary> 4 public class Paged 5 { 6 /// <summary> 7 /// 當前頁碼 8 /// </summary> 9 public int PageIndex { get; set; }10 /// <summary>11 /// 頁數(shù)據(jù)行數(shù)12 /// </summary>13 public int PageSize { get; set; }14 /// <summary>15 /// 總行數(shù)16 /// </summary>17 public long TotalRow { get; set; }18 /// <summary>19 /// 總的分頁數(shù)20 /// </summary>21 public int TotalPage22 {23 get24 {25 if (this.TotalRow > 0 && this.PageSize > 0)26 return (int)Math.Ceiling((decimal)this.TotalRow / this.PageSize);27 else28 return 0;29 }30 }31 }
在IServiceCollection容器中注冊：
services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
