欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Dapper 链式查询 扩展

程序员文章站 2022-04-08 11:38:18
Dapper 链式查询扩展 DapperSqlMaker Github地址:https://github.com/mumumutou/DapperSqlMaker 欢迎大佬加入 Demo: 查询 TestsDapperSqlMaker\DapperSqlMaker.Test\ SelectDappe ......

dapper 链式查询扩展 dappersqlmaker   

github地址https://github.com/mumumutou/dappersqlmaker  欢迎大佬加入

demo:

  • 查询       testsdappersqlmaker\dappersqlmaker.test\ selectdappersqlmakertest.cs
  • 添加       testsdappersqlmaker\dappersqlmaker.test\  insertdappersqlmakertest.cs
  • 更新       testsdappersqlmaker\dappersqlmaker.test\  updatedappersqlmakertest.cs
  • 删除       testsdappersqlmaker\dappersqlmaker.test\  deletedappersqlmakertest.cs
  • 上下文类   testsdappersqlmaker\dbdappersqlmaker\  lockdapperutilsqlite.cs
简单栗子:
1.查询-联表查询,分页 
 1 public void 三表联表分页测试()
 2 {
 3     var arruser = new int[2] { 1,2 };  // 
 4     string uall = "b.*", pn1 = "%蛋蛋%", pn2 = "%m%";
 5     lockpers lpmodel = new lockpers() { isdel = false};
 6     users umodel = new users() { username = "jiaojiao" };
 7     synnote snmodel = new synnote() { name = "木头" };
 8     expression<func<lockpers, users, synnote, bool>> where = predicatebuilder.wherestart<lockpers, users, synnote>();
 9     where = where.and((l, u, s) => ( l.name.contains(pn1) || l.name.contains(pn2) ));
10     where = where.and((lpw, uw, sn) => lpw.isdel == lpmodel.isdel);
11     where = where.and((l, u, s) => u.username == umodel.username);
12     where = where.and((l, u, s) => s.name == snmodel.name );
13     where = where.and((l, u, s) => sm.in(u.id, arruser));
14 
15     dappersqlmaker<lockpers, users, synnote> query = lockdapperutilsqlite<lockpers, users, synnote>
16         .selec()
17         .column((lp, u, s) => //null)  //查询所有字段
18             new { lp.name, lpid = lp.id, x = "length(a.prompt) as len", b = sm.sql(uall)
                              , scontent = s.content, sname = s.name }) 19 .fromjoin(jointype.left, (lpp, uu, snn) => uu.id == lpp.userid 20 , jointype.inner, (lpp, uu, snn) => uu.id == snn.userid) 21 .where(where) 22 .order((lp, w, sn) => new { lp.editcount, x = sm.orderdesc(lp.name), sn.content }); 23 24 var result = query.excuteselect(); 25 writejson(result); // 查询结果 26 27 tuple<stringbuilder, dynamicparameters> resultsqlparams = query.rawsqlparams(); 28 writesqlparams(resultsqlparams); // 打印sql和参数 29 30 int page = 2, rows = 3, records; 31 var result2 = query.loadpagelt(page, rows, out records); 32 writejson(result2); // 查询结果 33 }

生成的sql :

select  a.name as name, a.id as lpid
    , length(a.prompt) as len, b.*
    , c.content as scontent, c.name as sname  
from lockpers a  
    left join  users b on  b.id = a.userid   
    inner join  synnote c on  b.id = c.userid  
where  (  a.name like @name0  or  a.name like @name1  )  
    and  a.isdel = @isdel2  and  b.username = @username3  
    and  c.name = @name4  and  b.id in @id 
order by  a.editcount, a.name desc , c.content 

 

2.更新-更新部分字段

 1 [test]
 2 public void 更新部分字段测试lt()
 3 {
 4     var issucs = lockdapperutilsqlite<lockpers>.cud.update(
 5         s =>
 6         {
 7             s.name = "测试bool修改1";
 8             s.content = "update方法内赋值修改字段";
 9             s.isdel = true;
10         },
11         w => w.name == "测试bool修改1" && w.isdel == true
12         );
13     console.writeline(issucs);
14 }

 

3.七联表以上待扩展  copy六联表修改3个文件

  • dappersqlmaker
  • template_dappersqlmaker 上下文类
  • predicatebuilder             条件拼接类        

4.实体生成t4模板使用方法