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); // 查询结果
}
