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.totalrowcount = 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<>));