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 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);
}
internal void RegisterAssignment(PropGraphNodeDescriptor lhsNode, AnalysisExpression rhs)
{
if (lhsNode != null && rhs != null && Utils.IsTypeForAnalysis(rhs.GetAnalysisType()))
{
this.StatementProcessor.RegisterAssignment(lhsNode, rhs.GetAnalysisNode());
}
}
private void ProcessReturnAnalysisExpression(AnalysisExpression analysisExpression)
{
if (this.RetVar != null && analysisExpression != null)
{
analysisExpression.ProcessAssignment(this.RetVar, this);
}
}
internal void RegisterCallLHS(VariableNode lhsNode, AnalysisExpression rhsExpression)
{
Contract.Assert(rhsExpression.GetAnalysisNode() is AnalysisCallNode);
var callNode = (AnalysisCallNode)rhsExpression.GetAnalysisNode();
this.StatementProcessor.RegisterCallLHS(callNode, lhsNode);
}
internal void RegisterAssignment(AnalysisExpression lhs, AnalysisExpression rhs)
{
Contract.Assert(lhs is Field || lhs is Identifier);
if (lhs != null)
{
this.RegisterAssignment(lhs.GetAnalysisNode(), rhs);
}
}
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));
}
}
public void 联表sql生成()
{
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;
var uName = "cc";
//w,w2 作为表别名
Dictionary <string, string> tabalis = new Dictionary <string, string>();
tabalis.Add(typeof(LockPers).FullName, "w");
tabalis.Add(typeof(Users).FullName, "w2");
//时间类型现在获取不到
Expression <Func <LockPers, Users, bool> > expression = (w, w2) =>
w.Name == Name &&
w.InsertTime >= startDate &&
w.InsertTime < endDate
//&& w.InsertTime > // SM.DateStr(InserTime)
//&& SM.In(w.EditCount, arrEditCount)
&& w.IsDel == IsDel &&
w2.UserName == uName
;
StringBuilder sql = null;
DynamicParameters spars = null;
AnalysisExpression.JoinExpression(expression, tabalis, 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));
}
Console.WriteLine("++_+++++++");
}
public void 添加数据复杂语句测试lt()
{
string colm1 = "remake", val1 = " (select 666) ";
System.Linq.Expressions.Expression <Func <LockPers, bool[]> > fiesExps =
p => new bool[] { p.Content == "aa", p.IsDel == true, SM.Sql(colm1, val1), SM.Sql(p.Name, " (select 1) ") }; //
System.Linq.Expressions.LambdaExpression colmvalambda = fiesExps as System.Linq.Expressions.LambdaExpression;
System.Linq.Expressions.NewArrayExpression arryexps = colmvalambda.Body as System.Linq.Expressions.NewArrayExpression;
StringBuilder sb = new StringBuilder();
Dapper.DynamicParameters spars = new Dapper.DynamicParameters();
int num = 1;
string exgl = "=";
sb.Append(" ( ");
List <string[]> customColmval = new List <string[]>();
var lmbdParmName = colmvalambda.Parameters[0].Name;
foreach (var p in arryexps.Expressions)
{
if (p.NodeType == System.Linq.Expressions.ExpressionType.Equal)
{
AnalysisExpression.BinaryExprssRowSqlParms(p, sb, spars, num++, exgl, (name, parmasName, exglstr) => string.Format("{0},", name)); //" {0} {2} @{0}{1} "
}
else if (p.NodeType == System.Linq.Expressions.ExpressionType.Call)
{
string[] arrColmval = new string[2]; // 0 column 1 value
System.Linq.Expressions.MethodCallExpression method = p as System.Linq.Expressions.MethodCallExpression;
int i = 0;
tempstart:
//meb = method.Arguments[0] as System.Linq.Expressions.MemberExpression;
if (method.Arguments[i] is System.Linq.Expressions.ConstantExpression)
{
// 参数名/插入值 直接赋值
arrColmval[i] = (method.Arguments[i] as System.Linq.Expressions.ConstantExpression).Value.ToString();
}
else if (method.Arguments[i] is System.Linq.Expressions.MemberExpression)
{// 参数名/插入值 传入的时 变量
var meb = method.Arguments[i] as System.Linq.Expressions.MemberExpression;
if (meb.Expression is System.Linq.Expressions.ParameterExpression &&
(meb.Expression as System.Linq.Expressions.ParameterExpression).Name == lmbdParmName)
{ // lambda表达式的参数成员 表示字段参数名 只取成员名称不取值
arrColmval[i] = (method.Arguments[i] as System.Linq.Expressions.MemberExpression).Member.Name;
}
else
{// 外部传入的变量
arrColmval[i] = AnalysisExpression.GetMemberValue(method.Arguments[i] as System.Linq.Expressions.MemberExpression).ToString();
}
}
//(method.Arguments[i] as System.Linq.Expressions.MemberExpression).Member.Name;
else
{
throw new Exception(p.ToString() + " 插入语句未能解析");
}
if (++i < 2)
{
goto tempstart;
}
customColmval.Add(arrColmval);
}
else
{
throw new Exception(p.ToString() + " 插入语句未能解析");
}
}
sb.Remove(sb.Length - 1, 1);
// 拼接子查询插入的 参数名
sb.Append((spars.ParameterNames.Count() > 0 && customColmval.Count > 0 ? ", " : string.Empty) + string.Join(",", customColmval.Select(p => p[0]).ToList <string>()));
sb.AppendFormat(") values ({0}{1}) "
, string.Join(",", spars.ParameterNames.ToList <string>().Select(p => "@" + p).ToList <string>())
, (spars.ParameterNames.Count() > 0 && customColmval.Count > 0 ? ", " : string.Empty) + string.Join(",", customColmval.Select(p => p[1]).ToList <string>())
);
Console.WriteLine(sb);
return; // ############################################################
}
