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 表联表分页测试()
{
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 六表联表分页测试()
{
LockPers lpmodel = new LockPers()
{
Name = "%蛋蛋%", IsDel = false
};
Users umodel = new Users()
{
UserName = "******"
};
SynNote snmodel = new SynNote()
{
Name = "%木头%"
};
Expression <Func <LockPers, Users, SynNote, SynNote, SynNote, SynNote, bool> > where = PredicateBuilder.WhereStart <LockPers, Users, SynNote, SynNote, SynNote, SynNote>();
where = where.And((lpw, uw, sn, snn, s5, s6) => lpw.Name.Contains(lpmodel.Name));
where = where.And((lpw, uw, sn, snn, s5, s6) => lpw.IsDel == lpmodel.IsDel);
where = where.And((lpw, uw, sn, snn, s5, s6) => uw.UserName == umodel.UserName);
where = where.And((lpw, uw, sn, snn, s5, s6) => sn.Name.Contains(snmodel.Name));
// SM.LimitCount,
DapperSqlMaker <LockPers, Users, SynNote, SynNote, SynNote, SynNote> query = LockDapperUtilsqlite <LockPers, Users, SynNote, SynNote, SynNote, SynNote>
.Selec()
.Column((lp, u, s, sn, s5, s6) => // )null查询所有字段
new { lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel, u.UserName, s.Content, s.Name })
.FromJoin(JoinType.Left, (lpp, uu, snn, snnn, s5, s6) => uu.Id == lpp.UserId
, JoinType.Inner, (lpp, uu, snn, snnn, s5, s6) => uu.Id == snn.UserId && snn.Id == snn.UserId
, JoinType.Inner, (lpp, uu, snn, snnn, s5, s6) => snnn.Id == snn.UserId
, JoinType.Inner, (lpp, uu, snn, snnn, s5, s6) => snnn.Id == s5.UserId
, JoinType.Inner, (lpp, uu, snn, snnn, s5, s6) => s5.Id == s6.UserId)
.Where(where) //(lpp, uu, snn, snnn) => uu.Id == snn.UserId && snnn.Id == snn.UserId)//)
.Order((lp, w, sn, snn, s5, s6) => 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 双表联表分页测试()
{
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); // 查询结果
}