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 Update()
{
// 1. Update (set和where里不能有相同字段)
LockPers pset = new LockPers(true);
pset.Name = "修改95 只修改Name字段";
LockPers pwhere = new LockPers(true);
pwhere.Content = "7fa867c5b404547797614abe57341844";
//var efrwostest2 = LockDapperUtilTest.Update<LockPers>(pset, pwhere);
// 2.Update
var efrowsupdate2 = LockDapperUtilTest <LockPers> .New.Update(
set =>
{
set.Name = "修改95 修改Name和Content字段";
set.Prompt = "BMWWWWWWWWWWWWWWW";
}
, where => where.Content == "xxxxxxxoooooooo");
// 3.Update
// where 字段参数名 会 和 set 字段参数名重复, set字段名统一加_
var efrowsupdate3 = LockDapperUtilTest <LockPers> .New.Update(
set =>
{
set.Name = "修改95 修改Name和Content字段";
set.Prompt = "BMWWWWWWWWWWWWWWW";
}
,
where => SM.In(where.Name, new string[] { "马", "码" }) &&
where.Name == "农码一生" && where.Prompt == "男" || where.Name.Contains("11"));
}
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 单表分页查询测试()
{
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 单表分页查询测试MS()
{
LockPers lpmodel = new LockPers();
lpmodel.Name = "%蛋蛋%";
lpmodel.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 = LockDapperUtilmssql <LockPers>
.Selec()
.RowRumberOrderBy(lp => new { lp.EditCount, lp.Name })
.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();
Console.WriteLine(resultsqlparams.Item1.ToString()); // sql
foreach (var name in resultsqlparams.Item2.ParameterNames)
{
WriteJson(name + " -- " + resultsqlparams.Item2.Get <object>(name)); // 参数 -- 值
}
int page = 1, rows = 3, records;
var result2 = query.LoadPagems(page, rows, out records);
WriteJson(result2); // 查询结果
}
public void 表达式测试()
{
LockPers lpmodel = new LockPers()
{
IsDel = false, Prompt = "测试xxxxxx"
};
Users umodel = new Users()
{
UserName = "******"
};
//Expression<Func<LockPers, Users, object>> fiesExps = (lp, u) => new { a2 = SM.AppendSql(lp.Prompt), len = "LENGTH(a.Prompt)" };
//LambdaExpression fieslambda = fiesExps as LambdaExpression;
//Delegate fiesdlg = fieslambda.Compile();
//var o = fiesdlg.DynamicInvoke(lpmodel, umodel);
//ParameterExpression fp1 = fieslambda.Parameters[0];
//NewExpression fiesnew = fieslambda.Body as NewExpression;
//ConstructorInfo fnc = fiesnew.Constructor;
//var newargs1 = fiesnew.Arguments.First();
//MethodCallExpression method = newargs1 as MethodCallExpression;
//var methodarg1 = method.Arguments[0];
//MemberExpression meb = methodarg1 as MemberExpression;
//var mebexp1 = meb.Expression;
//ParameterExpression param1 = mebexp1 as ParameterExpression;
// ####################
Expression <Func <LockPers, Users, bool> > boolExps2 = (lp, u) => SM.AppendSql2(lpmodel.Prompt) && u.UserName == umodel.UserName;
LambdaExpression boolambda2 = boolExps2 as LambdaExpression;
BinaryExpression bexp2 = boolambda2.Body as BinaryExpression;
MethodCallExpression mebleft2 = bexp2.Left as MethodCallExpression;
var mebleft2Arg1 = mebleft2.Arguments[0] as MemberExpression;
var mebleft2Arg1Params1 = mebleft2Arg1.Expression as ParameterExpression;
//AnalysisExpression.GetMemberValue
// ####################
//Expression<Func<LockPers, Users, bool>> boolExps= (lp, u) => lp.Prompt == lpmodel.Prompt && u.UserName == umodel.UserName;
//LambdaExpression boolambda = boolExps as LambdaExpression;
//BinaryExpression bexp = boolambda.Body as BinaryExpression;
//BinaryExpression binarygleft = bexp.Left as BinaryExpression;
//MemberExpression constMember = binarygleft.Right as MemberExpression; //右边变量名 constMember.Member.Name
//MemberExpression constMember2 = constMember.Expression as MemberExpression; //右边变量所在的类
//ConstantExpression constant = constMember2.Expression as ConstantExpression; //右边变量所在的类
//var constValue = constant.Value.GetType().GetField(constMember2.Member.Name).GetValue(constant.Value);
return;
}
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 四表联表分页测试MS()
{
LockPers lpmodel = new LockPers()
{
Name = "%蛋蛋%", IsDel = false
};
Users umodel = new Users()
{
UserName = "******"
};
SynNote snmodel = new SynNote()
{
Name = "%木头%"
};
Expression <Func <LockPers_ms, Users_ms, SynNote_ms, SynNote_ms, bool> > where = PredicateBuilder.WhereStart <LockPers_ms, Users_ms, SynNote_ms, SynNote_ms>();
where = where.And((lpw, uw, sn, snn) => lpw.Name.Contains(lpmodel.Name));
where = where.And((lpw, uw, sn, snn) => lpw.IsDel == lpmodel.IsDel);
where = where.And((lpw, uw, sn, snn) => uw.UserName == umodel.UserName);
where = where.And((lpw, uw, sn, snn) => sn.Name.Contains(snmodel.Name));
// SM.LimitCount,
DapperSqlMaker <LockPers_ms, Users_ms, SynNote_ms, SynNote_ms> query =
LockDapperUtilmssql <LockPers_ms, Users_ms, SynNote_ms, SynNote_ms>
.Selec()
.RowRumberOrderBy((lp, u, s, sn) => new { lp.Id, a = u.Id })
.Column((lp, u, s, sn) =>
new { SM.LimitCount, lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel, u.UserName, s.Content, s.Name })//null查询所有字段
.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)
.Where(where); //(lpp, uu, snn, snnn) => uu.Id == snn.UserId && snnn.Id == snn.UserId)//)
// .Order((lp, w, sn, snn) => new { lp.EditCount, lp.Name, sn.Content });
Tuple <StringBuilder, DynamicParameters> resultsqlparams = query.RawSqlParams();
WriteSqlParams(resultsqlparams);
var result = query.ExecuteQuery();
WriteJson(result); // 查询结果
int page = 2, rows = 2, records;
var result2 = query.LoadPagems(page, rows, out records);
WriteJson(result2); // 查询结果
}
public void Update测试2()
{
LockPers set = new LockPers()
{
Content = "测试bool修改2"
};
set.Name = "测试bool修改2";
set.IsDel = true;
var issucs = LockDapperUtilTest <LockPers> .New.Update(
set,
w => w.Name == "测试bool修改" && w.IsDel == true
);
Console.WriteLine(issucs);
}
public void 更新部分字段2测试lt()
{
LockPers set = new LockPers()
{
_IsWriteFiled = true, Content = "方法外部赋值修改字段实体"
};
set.Name = "测试bool修改2";
set.IsDel = true;
set.ContentOld = "忽略Write(false)标记字段";
var issucs = DBSqliteFuncs.New.Update <LockPers>(
set,
w => w.Name == "测试bool修改2" && w.IsDel == true
);
Console.WriteLine(issucs);
}
public void 更新部分字段2测试lt()
{
LockPers set = new LockPers()
{
Content = "方法外部赋值修改字段实体"
};
set.Name = "测试bool修改2";
set.IsDel = true;
set.ContentOld = "忽略Write(false)标记字段";
var issucs = LockDapperUtilsqlite <LockPers> .Cud.Update(
set,
w => w.Name == "测试bool修改2" && w.IsDel == true
);
Console.WriteLine(issucs);
}
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 双表联表分页测试MS()
{
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 = LockDapperUtilmssql <LockPers, Users>
.Selec()
.RowRumberOrderBy((lp, u) => new { lp.EditCount, lp.Name })
.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.LoadPagems(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); // 查询结果
}
