Dapper 链式查询 扩展

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模板使用方法