public void In测试2()
{
//var Name = "测试bool修改2";
//2 in 声明数组变量当参数传入 走参数化查询
var arrEditCount = new int[5] {
22, 2, 3, 5, 1
}; //
Expression <Func <LockPers, bool> > expression = w => SM.In(w.EditCount, arrEditCount)
//&& w.Name == Name
&& (w.Prompt.Contains("%hou%") || w.IsDel == false);
StringBuilder sql = null;
DynamicParameters spars = null;
AnalysisExpression.VisitExpression(expression, ref sql, ref spars);
foreach (var name in spars.ParameterNames)
{
Console.WriteLine(name);
WriteJson(spars.Get <object>(name));
}
Console.WriteLine(sql);
var example = " ";
Console.WriteLine(example);
// var datearr = new DateTime[2] { new DateTime(18, 11, 28), new DateTime(18, 11, 22) };
// && SM.In(w.UpdateTime, datearr)
// //and EditCount in ('18/11/28', '18/11/22')
// Assert.Fail(); //断言
//var objs = LockDapperUtilsqlite<LockPers>.Get(expression);
//WriteJson(objs);
}
public void 查询条件添加不通的where()
{
var Name = "测试bool修改2";
//var Content = "测试bool修改2";
//var arrEditCount = new int[5] { 22, 2, 3, 5, 1 }; //
DateTime?startDate = new DateTime(2018, 10, 17);
DateTime?endDate = ((DateTime)startDate).AddDays(1);
var IsDel = false;
//时间类型现在获取不到
Expression <Func <LockPers, bool> > expression = w =>
w.Name == Name &&
w.InsertTime >= startDate &&
w.InsertTime < endDate
//&& w.InsertTime > // SM.DateStr(InserTime)
//&& SM.In(w.EditCount, arrEditCount)
&& w.IsDel == IsDel;
StringBuilder sql = null;
DynamicParameters spars = null;
AnalysisExpression.VisitExpression(expression, ref sql, ref spars);
Console.WriteLine(sql);
// expression = w => w.Content == Content;
foreach (var name in spars.ParameterNames)
{
Console.WriteLine(name);
WriteJson(spars.Get <object>(name));
}
}
public void bool测试()
{
Expression <Func <LockPers, bool> > expression = t => t.Name.Contains("%蛋蛋%") && t.IsDel == false;
StringBuilder sql = null;
DynamicParameters spars = null;
AnalysisExpression.VisitExpression(expression, ref sql, ref spars);
Console.WriteLine(sql);
var objs3 = LockDapperUtilTest <LockPers> .New.Get(expression);
WriteJson(objs3);
}
public void 括号优先级连接数据库测试()
{
Expression <Func <LockPers, bool> > expression = w =>
w.IsDel != true
;
StringBuilder sql = null;
DynamicParameters spars = null;
AnalysisExpression.VisitExpression(expression, ref sql, ref spars);
Console.WriteLine(sql);
var example = "IsDel != 1 and (name like '%蛋蛋%' or name like '%大疆%' or Prompt like '%_%') and EditCount in (1, 2, 3) and InsertTime > '2017-11-01 00:00:00' ";
Console.WriteLine(example);
}
public void 查询条件添加不通的where2()
{
//1
//Expression<Func<LockPers, bool>> expression1 = w => w.Name == "";
//Expression<Func<LockPers, bool>> expression2 = w => w.IsDel == true;
//Expression<Func<LockPers, bool>> expression3 = w => w.Content == "";
//Expression<Func<LockPers, bool>> expression4 = expression3.And(expression1.Or(expression2));
//2
var arrEditCount = new int[6] {
22, 2, 3, 5, 1, 11
}; //
DateTime?startDate = new DateTime(2018, 10, 17);
DateTime?endDate = ((DateTime)startDate).AddDays(1);
DateTime?startDate2 = new DateTime(2018, 3, 17);
DateTime?endDate2 = ((DateTime)startDate).AddDays(1);
Expression <Func <LockPers_, bool> > where = PredicateBuilder.WhereStart <LockPers_>();
where = where.And(w => SM.In(w.EditCount, arrEditCount));
where = where.And(w => w.InsertTime >= startDate && w.InsertTime < endDate || w.InsertTime >= startDate2 && w.InsertTime < endDate2);
where = where.And(w => w.IsDel == false);
//where = where.Or(w => ());
//3 or里面括号问题
//int? i1 = 1;
//int? i2 = 2;
//int? i3 = 3;
//Expression<Func<LockPers_, bool>> where = PredicateBuilder.WhereStart<LockPers_>();
//where = where.And( ww => ww.EditCount == i1);
//where = where.And(ww => ww.EditCount == i2);
//where = where.Or(ww => (ww.EditCount == i3 && ww.IsDel == true));
StringBuilder sql = null;
DynamicParameters spars = null;
AnalysisExpression.VisitExpression(where, ref sql, ref spars);
Console.WriteLine(sql);
foreach (var name in spars.ParameterNames)
{
Console.WriteLine(name);
WriteJson(spars.Get <object>(name));
}
}
public void 括号优先级()
{
// 1
Expression <Func <LockPers, bool> > expression = w =>
(w.Id == "1" && (w.Name == "2" || w.Prompt == "3") && w.Content == "4" || w.Id == "5") &&
(w.IsDel != false && (w.IsDel == false || w.IsDel == true));
StringBuilder sql = null;
DynamicParameters spars = null;
AnalysisExpression.VisitExpression(expression, ref sql, ref spars);
Console.WriteLine(sql);
var example = "( w.Id == '1' && (w.Name == '2' || w.Prompt == '3') && w.Content == '4' || w.Id == '5') && w.IsDel != '0'";
Console.WriteLine(example);
//// 2
//sql = null;
//spars = null;
//expression = w =>
// ( (w.Id == "1" || w.Name == "2" ) && (w.Prompt == "3" || w.Content == "4" ) || w.Id == "5")
// && w.IsDel != "0";
//AnalysisExpression.VisitExpression(expression, ref sql, ref spars);
//Console.WriteLine(sql);
//// 3
//sql = null;
//spars = null;
//expression = w =>
// w.IsDel != "0" && w.Id == "1" || w.Name == "2" || w.Prompt == "3" || w.Content == "4" || w.Id == "5";
//AnalysisExpression.VisitExpression(expression, ref sql, ref spars);
//Console.WriteLine(sql);
//// 4
//sql = null;
//spars = null;
//expression = w =>
// w.IsDel != "0" && w.Id == "1" && w.Name == "2" && (w.Prompt == "3" || w.Id == "5") && w.Content == "4";
//AnalysisExpression.VisitExpression(expression, ref sql, ref spars);
//Console.WriteLine(sql);
foreach (var name in spars.ParameterNames)
{
Console.WriteLine(name);
WriteJson(spars.Get <object>(name));
}
}
public void In测试1()
{
// 1 in 表达式内创建数组(少量) 会直接转成sql 不走参数化
Expression <Func <LockPers, bool> > expression = t => SM.In(t.Name, new string[] { "马", "码" }) &&
t.Name == "农码一生" && t.Prompt == "男" || t.Name.Contains("11");
StringBuilder sql = null;
DynamicParameters spars = null;
AnalysisExpression.VisitExpression(expression, ref sql, ref spars);
Console.WriteLine(sql);
var example = " Name in ('马','码') and Name = @Name0 and Prompt = @Prompt1 or Name like @Name2 ";
Console.WriteLine(example);
var sqlstr = " Name in ('马','码') && Name == '农码一生' && Prompt == '男' || Name like '11' ";
Console.WriteLine(sqlstr);
}
public void 括号测试2()
{
Expression <Func <LockPers, bool> > expression = w =>
w.Id == "1" && w.Name == "2" ||
(w.IsDel != false && w.IsDel == true);
StringBuilder sql = null;
DynamicParameters spars = null;
AnalysisExpression.VisitExpression(expression, ref sql, ref spars);
Console.WriteLine(sql);
var example = " w.Id == '1' && w.Name == '2' || (w.IsDel != false && w.IsDel == true) ";
Console.WriteLine(example);
foreach (var name in spars.ParameterNames)
{
Console.WriteLine(name);
WriteJson(spars.Get <object>(name));
}
}
