public void 字段别名和查询字段中直接拼接sql() { //1. 查询的实体类 字段别名 为匿名类型成员名 //2. (Column和Order)中直接拼接sql 用SM.Sql 或者直接写入 字符串值 //3. (2)的字段别名 也要写在字符串里 注意:这里匿名类型成员名只是为了符合语法,不会被解析成别名 //4. Order倒序标记方法 SM.OrderDesc() string umodelall = "b.*"; LockPers lpmodel = new LockPers() { IsDel = false }; Users umodel = new Users() { UserName = "******" }; Expression <Func <LockPers, Users, bool> > where = PredicateBuilder.WhereStart <LockPers, Users>(); where = where.And((lpw, uw) => lpw.IsDel == lpmodel.IsDel); where = where.And((lpw, uw) => uw.UserName == umodel.UserName); DapperSqlMaker <LockPers, Users> query = LockDapperUtilsqlite <LockPers, Users> .Selec() .Column((lp, u) => new { a = "LENGTH(a.Prompt) as len", b = SM.Sql(umodelall), lpid = lp.Id, lp.Name, lp.Prompt }) .FromJoin(JoinType.Left, (lpp, uu) => uu.Id == lpp.UserId) .Where(where) .Order((lp, w) => new { a = SM.OrderDesc(lp.EditCount), lp.Id }); var result = query.ExecuteQuery(); WriteJson(result); // 查询结果 WriteSqlParams(query.RawSqlParams()); //打印sql和参数 }
public void 表别名() { var Name = "测试bool修改2"; var IsDel = false; var uName = "cc"; Tuple <StringBuilder, DynamicParameters> result = LockDapperUtilsqlite <LockPers, Users> .Selec() .Column((lp, u) => new { lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel, u.UserName }) .FromJoin(JoinType.Left, (lpp, uu) => uu.Id == lpp.UserId) .Where((lpw, uw) => lpw.Name == Name && lpw.IsDel == IsDel && uw.UserName == uName) .Order((lp, w) => new { lp.EditCount, lp.Name }) .RawSqlParams(); //.Excute(); Console.WriteLine(result.Item1); foreach (var name in result.Item2.ParameterNames) { Console.WriteLine(name); WriteJson(result.Item2.Get <object>(name)); } //var obj = LockDapperUtilTest.Queryable<LockPers, Users>(JoinType.Left,(lp, u) => u.Id == lp.UserId); //var obj2 = LockDapperUtilTest.Queryable<Users, LockPers>(JoinType.Left,(u, lp) => u.Id == lp.UserId); //var obj = LockDapperUtilTest.Queryable1<LockPers, Users>((lp, u) => new object[] { // "JoinType.Left", u.Id == lp.UserId }); Expression <Func <LockPers, bool> > expression = t => t.Name.Contains("%蛋蛋%") && t.IsDel == false; //var list = LockDapperUtilTest<LockPers>.New.Get(expression); //WriteJson(list); }
public void Or开头查询条件() { LockPers lpmodel = new LockPers(); lpmodel.IsDel = false; Users umodel = new Users(); umodel.UserName = "******"; string serh = "%%"; Expression <Func <LockPers, bool> > where = PredicateBuilder.WhereStart <LockPers>(); //where = where.Or((lpw, uw) => lpw.IsDel == lpmodel.IsDel); //where = where.Or((lpw, uw) => uw.UserName == umodel.UserName); where = where.And(m => m.IsDel != true); where = where.And(m => (m.Name.Contains(serh) || m.Prompt.Contains(serh))); DapperSqlMaker <LockPers> query = LockDapperUtilsqlite <LockPers> .Selec() .Column() .From() .Where(where) .Order((lp) => new { a = SM.OrderDesc(lp.EditCount), lp.Id }); var rawsqlparms = query.RawSqlParams(); WriteSqlParams(rawsqlparms); //打印sql和参数 var result = query.ExecuteQuery(); WriteJson(result); // 查询结果 }
public void 查询首行首列() { string updatesql = " select Content from SynNote "; var ef = LockDapperUtilsqlite.New().ExecuteScalar <string>(updatesql, null); WriteJson(ef); }
public void 查询() { string updatesql = " select * from SynNote "; var ef = LockDapperUtilsqlite.New().Query <SynNote_>(updatesql, null); WriteJson(ef); }
public void 单表分页查询测试() { LockPers lpmodel = new LockPers() { Name = "%蛋蛋%", IsDel = false }; Expression <Func <LockPers, bool> > where = PredicateBuilder.WhereStart <LockPers>(); where = where.And((lpw) => lpw.Name.Contains(lpmodel.Name)); where = where.And((lpw) => lpw.IsDel == lpmodel.IsDel); DapperSqlMaker <LockPers> query = LockDapperUtilsqlite <LockPers> .Selec() .Column() // lp => new { lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel }) // null查询所有字段 .From() .Where(where) //lp => lp.Name == lpmodel.Name && lp.IsDel == lpmodel.IsDel ) .Order(lp => new { lp.EditCount, lp.Name }); // .ExecuteQuery(); var result = query.ExecuteQuery(); WriteJson(result); // 查询结果 Tuple <StringBuilder, DynamicParameters> resultsqlparams = query.RawSqlParams(); WriteSqlParams(resultsqlparams); int page = 1, rows = 3, records; var result2 = query.LoadPagelt(page, rows, out records); WriteJson(result2); // 查询结果 }
public void Test() { //string txt = "多排序字段升序降序"; //mixtxt(txt); int i = 0; var list3old = LockDapperUtilsqlite.New().GetAll <LockPers>(); foreach (var item in list3old) { Random r = new Random(DateTime.Now.Millisecond); int x = r.Next(1, 5); if (x < 3) { continue; } item.Name = "蛋蛋" + item.Name; //item.Name = mixtxt(item.Name); //item.Content = mixtxt(item.Content); //item.Prompt = mixtxt(item.Prompt); LockDapperUtilsqlite <LockPers> .Cud.Update(item); i++; } Console.WriteLine(i + " 修改"); }
public void 查询首行() { SynNote_ sn = new SynNote_(); sn.Id = 3; string updatesql = " select * from SynNote where \"Id\" = @Id"; var ef = LockDapperUtilsqlite.New().QueryFirst <SynNote_>(updatesql, sn); WriteJson(ef); }
public void 查询首行首列列测试() { var first = LockDapperUtilsqlite <Users, Skin> .Selec() .Column((a, b) => new { Value = b.Value }) .FromJoin(JoinType.Inner, (a, b) => a.SkinId == b.Id) .Where((a, b) => a.Id == 1 && a.UserName == "cc") .ExecuteScalar <string>(); WriteJson(first); // 查询结果 }
public void 查表简单查询测试() { var first = LockDapperUtilsqlite <LockPers> .Selec() .Column() .From() .Where(a => a.IsDel == true) .ExecuteQuery <LockPers>(); WriteJson(first); // 查询结果 }
public void 除数据_含子查询_测试lt() { var sql = " UserId = ( select Id from users where UserName = '******' )"; var delete = LockDapperUtilsqlite <LockPers> .Delet().Where(p => p.Name == "H$E22222" && SM.SQL(sql) && SM.SQL(" IsDel = '1' ")); var efrow = delete.ExecuteDelete(); Console.WriteLine(efrow); Console.WriteLine(delete.RawSqlParams().Item1); }
public void 根据主键ID更新整个实体lt() { var model = LockDapperUtilsqlite <LockPers> .Selec().Column().From().Where(p => p.Name == "测试bool修改2 xxxxxx").ExecuteQuery <LockPers>().FirstOrDefault(); model.Content = "棉花棉花棉花棉花棉花"; model.ContentOld = "忽略Write(false)标记字段"; model.Prompt = "xxxxxxxxxxx"; var issucs = DapperFuncs.New.Updat <LockPers>(model); Console.WriteLine(issucs); }
public void 更新部分字段_含子查询_测试lt() { string colm = "img", val = "(select value from skin limit 1 offset 1)"; DateTime cdate = DateTime.Now; var update = LockDapperUtilsqlite <Users> .Updat().EditColumn(p => new bool[] { p.UserName == "几十行代码几十个错 调一步改一步....", p.Password == "bug制造者" , p.CreateTime == cdate, SM.Sql(p.Remark, "(select '奥德赛 终于改好了')") }).Where(p => p.Id == 6 && SM.SQL("IsDel == 0")); Console.WriteLine(update.RawSqlParams().Item1); var efrow = update.ExecuteUpdate(); Console.WriteLine(efrow); }
public void 根据主键ID更新整个实体lt() { var model = LockDapperUtilsqlite <LockPers> .Selec().Column().From().Where(p => p.Name == "蛋蛋03-/,53-')").ExcuteSelect <LockPers>().FirstOrDefault(); model.Content = "棉花棉花棉花棉花棉花"; model.ContentOld = "忽略Write(false)标记字段"; model.Prompt = "xxxxxxxxxxx"; var issucs = LockDapperUtilsqlite <LockPers> .Cud.Updat(model); Console.WriteLine(issucs); }
public void 添加部分字段_含子查询_测试lt() { string colm = "img", val = "(select value from skin limit 1 offset 1)"; DateTime cdate = DateTime.Now; var insert = LockDapperUtilsqlite <Users> .Inser().AddColumn(p => new bool[] { p.UserName == "木头人1 名称必须唯一", p.Password == "666", p.CreateTime == cdate , SM.Sql(colm, val), SM.Sql(p.Remark, "(select '荒野高尔夫')") }); Console.WriteLine(insert.RawSqlParams().Item1); var efrow = insert.ExecuteInsert(); Console.WriteLine(efrow); }
public void 修改() { SynNote_ sn = new SynNote_(); sn.Content = "备注333"; sn.IsDel = false; sn.Name = "棉花多读懂多多多多好多好多好多好多"; sn.NoteDate = DateTime.Now; sn.UserId = 2; sn.Id = 3; string updatesql = "update SynNote set \"Content\" = @Content, \"NoteDate\" = @NoteDate, \"Name\" = @Name, \"UserId\" = @UserId, \"IsDel\" = @IsDel where \"Id\" = @Id"; var ef = LockDapperUtilsqlite.New().Execute(updatesql, sn); Console.WriteLine(ef); }
public void 表联表分页测试() { var arruser = new int[2] { 1, 2 }; // string uall = "b.*", pn1 = "%蛋蛋%", pn2 = "%m%"; LockPers lpmodel = new LockPers() { IsDel = false }; Users umodel = new Users() { UserName = "******" }; SynNote snmodel = new SynNote() { Name = "木头" }; Expression <Func <LockPers, Users, SynNote, bool> > where = PredicateBuilder.WhereStart <LockPers, Users, SynNote>(); where = where.And((l, u, s) => (l.Name.Contains(pn1) || l.Name.Contains(pn2))); where = where.And((lpw, uw, sn) => lpw.IsDel == lpmodel.IsDel); where = where.And((l, u, s) => u.UserName == umodel.UserName); where = where.And((l, u, s) => s.Name == snmodel.Name); where = where.And((l, u, s) => SM.In(u.Id, arruser)); DapperSqlMaker <LockPers, Users, SynNote> query = LockDapperUtilsqlite <LockPers, Users, SynNote> .Selec() .Column((lp, u, s) => //null) //查询所有字段 new { lp.Name, lpid = lp.Id, x = "LENGTH(a.Prompt) as len", b = SM.Sql(uall), scontent = s.Content, sname = s.Name }) .FromJoin(JoinType.Left, (lpp, uu, snn) => uu.Id == lpp.UserId , JoinType.Inner, (lpp, uu, snn) => uu.Id == snn.UserId) .Where(where) .Order((lp, w, sn) => new { lp.EditCount, x = SM.OrderDesc(lp.Name), sn.Content }); Tuple <StringBuilder, DynamicParameters> resultsqlparams = query.RawSqlParams(); WriteSqlParams(resultsqlparams); // 打印sql和参数 var result = query.ExecuteQuery(); WriteJson(result); // 查询结果 int page = 2, rows = 3, records; var result2 = query.LoadPagelt(page, rows, out records); WriteJson(result2); // 查询结果 }
public void 查询所有数据() { // 4表 DapperSqlMaker <LockPers, Users, SynNote, SynNote> query4 = LockDapperUtilsqlite <LockPers, Users, SynNote, SynNote> .Selec() .Column() // null查询所有字段 // (lp, u, s, sn) => new { lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel, u.UserName, s.Content, s.Name }) .FromJoin(JoinType.Left, (lpp, uu, snn, snnn) => uu.Id == lpp.UserId , JoinType.Inner, (lpp, uu, snn, snnn) => uu.Id == snn.UserId && snn.Id == snn.UserId , JoinType.Inner, (lpp, uu, snn, snnn) => snnn.Id == snn.UserId); var result4 = query4.ExecuteQuery(); WriteJson(result4); // 查询结果 // 3表 DapperSqlMaker <LockPers, Users, SynNote> query3 = LockDapperUtilsqlite <LockPers, Users, SynNote> .Selec() .Column() //null 查询所有字段 // (lp, u, s) => new { lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel, u.UserName, s.Content, s.Name }) .FromJoin(JoinType.Left, (lpp, uu, snn) => uu.Id == lpp.UserId , JoinType.Inner, (lpp, uu, snn) => uu.Id == snn.UserId); var result3 = query3.ExecuteQuery(); WriteJson(result3); // 查询结果 // 2表 DapperSqlMaker <LockPers, Users> query2 = LockDapperUtilsqlite <LockPers, Users> .Selec() .Column() //null 查询所有字段 //(lp, u) => new { lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel, u.UserName }) //null查询所有字段 .FromJoin(JoinType.Left, (lpp, uu) => uu.Id == lpp.UserId); var result2 = query2.ExecuteQuery(); WriteJson(result2); // 查询结果 // 1 表 DapperSqlMaker <LockPers> query = LockDapperUtilsqlite <LockPers> .Selec() .Column() // null查询所有字段// lp => new { lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel }) .From(); var result = query.ExecuteQuery(); WriteJson(result); // 查询结果 }
public void 五表联表分页测试() { LockPers lpmodel = new LockPers() { Name = "%蛋蛋%", IsDel = false }; Users umodel = new Users() { UserName = "******" }; SynNote snmodel = new SynNote() { Name = "%木头%" }; Expression <Func <LockPers, Users, SynNote, SynNote, SynNote, bool> > where = PredicateBuilder.WhereStart <LockPers, Users, SynNote, SynNote, SynNote>(); where = where.And((lpw, uw, sn, snn, s5) => lpw.Name.Contains(lpmodel.Name)); where = where.And((lpw, uw, sn, snn, s5) => lpw.IsDel == lpmodel.IsDel); where = where.And((lpw, uw, sn, snn, s5) => uw.UserName == umodel.UserName); where = where.And((lpw, uw, sn, snn, s5) => sn.Name.Contains(snmodel.Name)); // SM.LimitCount, DapperSqlMaker <LockPers, Users, SynNote, SynNote, SynNote> query = LockDapperUtilsqlite <LockPers, Users, SynNote, SynNote, SynNote> .Selec() .Column((lp, u, s, sn, s5) => // )null查询所有字段 new { lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel, u.UserName, s.Content, s.Name }) .FromJoin(JoinType.Left, (lpp, uu, snn, snnn, s5) => uu.Id == lpp.UserId , JoinType.Inner, (lpp, uu, snn, snnn, s5) => uu.Id == snn.UserId && snn.Id == snn.UserId , JoinType.Inner, (lpp, uu, snn, snnn, s5) => snnn.Id == snn.UserId , JoinType.Inner, (lpp, uu, snn, snnn, s5) => snnn.Id == s5.UserId) .Where(where) //(lpp, uu, snn, snnn) => uu.Id == snn.UserId && snnn.Id == snn.UserId)//) .Order((lp, w, sn, snn, s5) => new { lp.EditCount, lp.Name, sn.Content }); var result = query.ExecuteQuery(); WriteJson(result); // 查询结果 Tuple <StringBuilder, DynamicParameters> resultsqlparams = query.RawSqlParams(); WriteSqlParams(resultsqlparams); // 打印sql和参数 int page = 2, rows = 2, records; var result2 = query.LoadPagelt(page, rows, out records); WriteJson(result2); // 查询结果 }
public void lock下拉刷新分页测试() { //m.IsDel != true m.Name != "xx" string name = "%a%"; var nobj = new { name = name }; int records; int page = 2, rows = 20; string rownm = " (SELECT COUNT(*) FROM LockPers AS t2 WHERE t2.Name < a.Name ) + (SELECT COUNT(*) FROM LockPers AS t3 WHERE t3.Name = a.Name AND t3.rowid < a.rowid ) +1 AS rowNum"; var query = LockDapperUtilsqlite <LockPers> .Selec().Column(p => new { t = "datetime(a.InsertTime) as InsertTimestr", b = SM.Sql(rownm), p.Id, p.Name, p.Content, p.Prompt, p.EditCount }) .From().Where(m => m.IsDel != true && m.Name.Contains(nobj.name)).Order(m => new { m.Name }); //Tuple<StringBuilder, Dapper.DynamicParameters> ru = query.RawSqlParams(); //var list = query.ExecuteQuery<LockPers>(); Tuple <StringBuilder, DynamicParameters, StringBuilder> ru = query.RawLimitSqlParams(); var list = query.LoadPagelt(page, rows, out records); }
public void 先查再修改指定字段() { LockPers p = new LockPers() { Id = "028e7910-6431-4e95-a50f-b9190801933b" }; var query = LockDapperUtilsqlite <LockPers> .Selec().Column(c => new { c.Content, c.EditCount }).From().Where(m => m.Id == p.Id); var old = query.ExcuteSelect <LockPers>().FirstOrDefault(); old._IsWriteFiled = true; // 标记开始记录赋值字段 注意上面查询LockPers 要再默认构造函数里把 标识改为false 查出的数据不要记录赋值字段 old.Name = "蛋蛋蛋蛋H$E22222"; old.Prompt = "好多多读都多大"; old.UpdateTime = DateTime.Now; var id = old.Id; var t = LockDapperUtilsqlite <LockPers> .Cud.Update(old, w => w.Id == p.Id); }
public void Like查询条件() { LockPers lpm = new LockPers(); Users um = new Users(); um.UserName = "******"; lpm.Users = um; PredicateBuilder.WhereStart <LockPers, Users>(); var lpnobj = new { UserName = "******" }; var query = LockDapperUtilsqlite <LockPers, Users> .Selec().Column().FromJoin(JoinType.Left, (p, u) => p.UserId == u.Id) .Where((p, u) => u.UserName.Contains(lpnobj.UserName)); var rawsqlparms = query.RawSqlParams(); WriteSqlParams(rawsqlparms); //打印sql和参数 var re = query.ExecuteQuery(); }
public void 双表联表分页测试() { LockPers lpmodel = new LockPers() { Name = "%蛋蛋%", IsDel = false }; Users umodel = new Users() { UserName = "******" }; Expression <Func <LockPers, Users, bool> > where = PredicateBuilder.WhereStart <LockPers, Users>(); where = where.And((lpw, uw) => lpw.Name.Contains(lpmodel.Name)); where = where.And((lpw, uw) => lpw.IsDel == lpmodel.IsDel); where = where.And((lpw, uw) => uw.UserName == umodel.UserName); DapperSqlMaker <LockPers, Users> query = LockDapperUtilsqlite <LockPers, Users> .Selec() .Column((lp, u) => new { lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel, u.UserName }) //null查询所有字段 .FromJoin(JoinType.Left, (lpp, uu) => uu.Id == lpp.UserId) .Where(where) //(lp, u) => lp.Name == lpmodel.Name && lp.IsDel == lpmodel.IsDel || u.UserName == umodel.UserName ) .Order((lp, w) => new { lp.EditCount, lp.Name }); // .ExecuteQuery(); var result = query.ExecuteQuery(); WriteJson(result); // 查询结果 Tuple <StringBuilder, DynamicParameters> resultsqlparams = query.RawSqlParams(); WriteSqlParams(resultsqlparams); int page = 1, rows = 3, records; var result2 = query.LoadPagelt(page, rows, out records); WriteJson(result2); // 查询结果 }
public void 多排序字段升序降序() { // 倒序方法 SM.OrderDesc(lp.EditCount) LockPers lpmodel = new LockPers(); lpmodel.Name = "%蛋蛋%"; lpmodel.IsDel = false; Users umodel = new Users(); umodel.UserName = "******"; DapperSqlMaker <LockPers, Users> query = LockDapperUtilsqlite <LockPers, Users> .Selec() .Column() //(lp, u) => new { SM.LimitCount , lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel, u.UserName }) //null查询所有字段 .FromJoin(JoinType.Left, (lpp, uu) => uu.Id == lpp.UserId) .Where((lp, u) => lp.Name.Contains(lpmodel.Name) && lp.IsDel == lpmodel.IsDel && u.UserName == umodel.UserName) .Order((lp, w) => new { a = SM.OrderDesc(lp.EditCount), lp.Id }); //.ExecuteQuery(); var result = query.ExecuteQuery(); WriteJson(result); // 查询结果 }
public void 拼接不同查询条件() { LockPers lpmodel = new LockPers(); lpmodel.IsDel = false; Users umodel = new Users(); umodel.UserName = "******"; Expression <Func <LockPers, Users, bool> > where = PredicateBuilder.WhereStart <LockPers, Users>(); where = where.And((lpw, uw) => lpw.IsDel == lpmodel.IsDel); where = where.And((lpw, uw) => uw.UserName == umodel.UserName); DapperSqlMaker <LockPers, Users> query = LockDapperUtilsqlite <LockPers, Users> .Selec() .Column() .FromJoin(JoinType.Left, (lpp, uu) => uu.Id == lpp.UserId) .Where(where) .Order((lp, w) => new { a = SM.OrderDesc(lp.EditCount), lp.Id }); var result = query.ExecuteQuery(); WriteJson(result); // 查询结果 }