private string GetWhereExpression(MethodCallExpression methodCallExpression)
{
if (methodCallExpression.Arguments.Count <= 1)
{
return(null);
}
var exp = methodCallExpression.Arguments[1];
var querybuiler = InstanceFactory.GetQueryBuilder(this.context.CurrentConnectionConfig);
querybuiler.LambdaExpressions = InstanceFactory.GetLambdaExpressions(this.context.CurrentConnectionConfig);
querybuiler.Builder = InstanceFactory.GetSqlbuilder(this.context.CurrentConnectionConfig);
querybuiler.Builder.Context = querybuiler.Context;
querybuiler.Builder.QueryBuilder = querybuiler;
querybuiler.Context = this.context;
var expValue = querybuiler.GetExpressionValue(exp, ResolveExpressType.WhereMultiple);
var paramterName = (exp as LambdaExpression).Parameters[0].Name;
var sql = expValue.GetResultString();
sql = sql.Replace(querybuiler.Builder.GetTranslationColumnName(paramterName) + ".", "");
if (querybuiler.Parameters != null && querybuiler.Parameters.Count > 0)
{
foreach (var item in querybuiler.Parameters)
{
sql = sql.Replace(item.ParameterName, item.Value.ObjToString().ToSqlValue());
}
}
return(sql);
}
public virtual ISugarQueryable <T> UnionAll <T>(params ISugarQueryable <T>[] queryables) where T : class, new()
{
var sqlBuilder = InstanceFactory.GetSqlbuilder(this.Context.CurrentConnectionConfig);
Check.Exception(queryables.IsNullOrEmpty(), "UnionAll.queryables is null ");
int i = 1;
List <KeyValuePair <string, List <SugarParameter> > > allItems = new List <KeyValuePair <string, List <SugarParameter> > >();
foreach (var item in queryables)
{
var sqlObj = item.ToSql();
string sql = sqlObj.Key;
UtilMethods.RepairReplicationParameters(ref sql, sqlObj.Value.ToArray(), i);
if (sqlObj.Value.IsValuable())
{
allItems.Add(new KeyValuePair <string, List <SugarParameter> >(sql, sqlObj.Value));
}
else
{
allItems.Add(new KeyValuePair <string, List <SugarParameter> >(sql, new List <SugarParameter>()));
}
i++;
}
var allSql = sqlBuilder.GetUnionAllSql(allItems.Select(it => it.Key).ToList());
var allParameters = allItems.SelectMany(it => it.Value).ToArray();
var resulut = this.Queryable <ExpandoObject>().AS(UtilMethods.GetPackTable(allSql, "unionTable"));
resulut.AddParameters(allParameters);
return(resulut.Select <T>("*"));
}
public virtual List <T> SqlQuery <T>(string sql, params SugarParameter[] parameters)
{
var builder = InstanceFactory.GetSqlbuilder(this.Context.CurrentConnectionConfig);
builder.SqlQueryBuilder.sql.Append(sql);
if (parameters != null && parameters.Any())
{
builder.SqlQueryBuilder.Parameters.AddRange(parameters);
}
var dataReader = this.GetDataReader(builder.SqlQueryBuilder.ToSqlString(), builder.SqlQueryBuilder.Parameters.ToArray());
List <T> result = this.DbBind.DataReaderToList <T>(typeof(T), dataReader);
builder.SqlQueryBuilder.Clear();
if (this.Context.Ado.DataReaderParameters != null)
{
foreach (IDataParameter item in this.Context.Ado.DataReaderParameters)
{
var parameter = parameters.FirstOrDefault(it => item.ParameterName.Substring(1) == it.ParameterName.Substring(1));
if (parameter != null)
{
parameter.Value = item.Value;
}
}
this.Context.Ado.DataReaderParameters = null;
}
return(result);
}
public Tuple <List <T>, List <T2>, List <T3> > SqlQuery <T, T2, T3>(string sql, object parameters = null)
{
var parsmeterArray = this.GetParameters(parameters);
this.Context.InitMppingInfo <T>();
var builder = InstanceFactory.GetSqlbuilder(this.Context.CurrentConnectionConfig);
builder.SqlQueryBuilder.sql.Append(sql);
if (parsmeterArray != null && parsmeterArray.Any())
{
builder.SqlQueryBuilder.Parameters.AddRange(parsmeterArray);
}
using (var dataReader = this.GetDataReaderNoClose(builder.SqlQueryBuilder.ToSqlString(), builder.SqlQueryBuilder.Parameters.ToArray()))
{
List <T> result = this.DbBind.DataReaderToListNoUsing <T>(typeof(T), dataReader);
NextResult(dataReader);
List <T2> result2 = this.DbBind.DataReaderToListNoUsing <T2>(typeof(T2), dataReader);
NextResult(dataReader);
List <T3> result3 = this.DbBind.DataReaderToListNoUsing <T3>(typeof(T3), dataReader);
builder.SqlQueryBuilder.Clear();
if (this.Context.Ado.DataReaderParameters != null)
{
foreach (IDataParameter item in this.Context.Ado.DataReaderParameters)
{
var parameter = parsmeterArray.FirstOrDefault(it => item.ParameterName.Substring(1) == it.ParameterName.Substring(1));
if (parameter != null)
{
parameter.Value = item.Value;
}
}
this.Context.Ado.DataReaderParameters = null;
}
return(Tuple.Create <List <T>, List <T2>, List <T3> >(result, result2, result3));
}
}
public KeyValuePair <string, SugarParameter[]> ConditionalModelToSql(List <ConditionalModel> models)
{
if (models.IsNullOrEmpty())
{
return(new KeyValuePair <string, SugarParameter[]>());
}
StringBuilder builder = new StringBuilder();
List <SugarParameter> parameters = new List <SugarParameter>();
var sqlBuilder = InstanceFactory.GetSqlbuilder(this.Context.CurrentConnectionConfig);
foreach (var item in models)
{
var index = models.IndexOf(item);
var type = index == 0?"":"AND";
string temp = " {0} {1} {2} {3} ";
string parameterName = string.Format("{0}Conditional{1}{2}", sqlBuilder.SqlParameterKeyWord, item.FieldName, index);
switch (item.ConditionalType)
{
case ConditionalType.Equal:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "=", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.Like:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "LIKE", parameterName);
parameters.Add(new SugarParameter(parameterName, "%" + item.FieldValue + "%"));
break;
case ConditionalType.GreaterThan:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), ">", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.GreaterThanOrEqual:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), ">=", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.LessThan:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.LessThanOrEqual:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<=", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
default:
break;
}
}
return(new KeyValuePair <string, SugarParameter[]>(builder.ToString(), parameters.ToArray()));
}
protected DeleteableProvider <T> CreateDeleteable <T>() where T : class, new()
{
var reval = InstanceFactory.GetDeleteableProvider <T>(this.CurrentConnectionConfig);
var sqlBuilder = InstanceFactory.GetSqlbuilder(this.CurrentConnectionConfig);;
reval.Context = this.Context;
reval.SqlBuilder = sqlBuilder;
sqlBuilder.DeleteBuilder = reval.DeleteBuilder = InstanceFactory.GetDeleteBuilder(this.CurrentConnectionConfig);
sqlBuilder.DeleteBuilder.Builder = sqlBuilder;
sqlBuilder.DeleteBuilder.LambdaExpressions = InstanceFactory.GetLambdaExpressions(this.CurrentConnectionConfig);
sqlBuilder.Context = reval.SqlBuilder.DeleteBuilder.Context = this.Context;
return(reval);
}
protected ISugarQueryable <T> CreateQueryable <T>(ISugarQueryable <T> result) where T : class, new()
{
var sqlBuilder = InstanceFactory.GetSqlbuilder(CurrentConnectionConfig);
result.Context = this.Context;
result.SqlBuilder = sqlBuilder;
result.SqlBuilder.QueryBuilder = InstanceFactory.GetQueryBuilder(CurrentConnectionConfig);
result.SqlBuilder.QueryBuilder.Builder = sqlBuilder;
result.SqlBuilder.Context = result.SqlBuilder.QueryBuilder.Context = this.Context;
result.SqlBuilder.QueryBuilder.EntityType = typeof(T);
result.SqlBuilder.QueryBuilder.EntityName = typeof(T).Name;
result.SqlBuilder.QueryBuilder.LambdaExpressions = InstanceFactory.GetLambdaExpressions(CurrentConnectionConfig);
return(result);
}
protected DeleteableProvider <T> CreateDeleteable <T>() where T : class, new()
{
this.SugarActionType = SugarActionType.Delete;
var result = InstanceFactory.GetDeleteableProvider <T>(this.CurrentConnectionConfig);
var sqlBuilder = InstanceFactory.GetSqlbuilder(this.CurrentConnectionConfig);;
result.Context = this;
result.SqlBuilder = sqlBuilder;
sqlBuilder.DeleteBuilder = result.DeleteBuilder = InstanceFactory.GetDeleteBuilder(this.CurrentConnectionConfig);
sqlBuilder.DeleteBuilder.Builder = sqlBuilder;
sqlBuilder.DeleteBuilder.LambdaExpressions = InstanceFactory.GetLambdaExpressions(this.CurrentConnectionConfig);
sqlBuilder.Context = result.SqlBuilder.DeleteBuilder.Context = this;
return(result);
}
protected ISugarQueryable <T> CreateQueryable <T>(ISugarQueryable <T> result)
{
Check.Exception(typeof(T).IsClass() == false || typeof(T).GetConstructors().Length == 0, "Queryable<{0}> Error ,{0} is invalid , need is a class,and can new().", typeof(T).Name);
var sqlBuilder = InstanceFactory.GetSqlbuilder(CurrentConnectionConfig);
result.Context = this.Context;
result.SqlBuilder = sqlBuilder;
result.SqlBuilder.QueryBuilder = InstanceFactory.GetQueryBuilder(CurrentConnectionConfig);
result.SqlBuilder.QueryBuilder.Builder = sqlBuilder;
result.SqlBuilder.Context = result.SqlBuilder.QueryBuilder.Context = this;
result.SqlBuilder.QueryBuilder.EntityType = typeof(T);
result.SqlBuilder.QueryBuilder.EntityName = typeof(T).Name;
result.SqlBuilder.QueryBuilder.LambdaExpressions = InstanceFactory.GetLambdaExpressions(CurrentConnectionConfig);
return(result);
}
protected UpdateableProvider <T> CreateUpdateable <T>(T[] UpdateObjs) where T : class, new()
{
var reval = InstanceFactory.GetUpdateableProvider <T>(this.CurrentConnectionConfig);
var sqlBuilder = InstanceFactory.GetSqlbuilder(this.CurrentConnectionConfig);;
reval.Context = this.Context;
reval.EntityInfo = this.Context.EntityMaintenance.GetEntityInfo <T>();
reval.SqlBuilder = sqlBuilder;
reval.UpdateObjs = UpdateObjs;
sqlBuilder.UpdateBuilder = reval.UpdateBuilder = InstanceFactory.GetUpdateBuilder(this.CurrentConnectionConfig);
sqlBuilder.UpdateBuilder.Builder = sqlBuilder;
sqlBuilder.UpdateBuilder.LambdaExpressions = InstanceFactory.GetLambdaExpressions(this.CurrentConnectionConfig);
sqlBuilder.Context = reval.SqlBuilder.UpdateBuilder.Context = this.Context;
reval.Init();
return(reval);
}
protected InsertableProvider <T> CreateInsertable <T>(T[] insertObjs) where T : class, new()
{
var result = InstanceFactory.GetInsertableProvider <T>(this.CurrentConnectionConfig);
var sqlBuilder = InstanceFactory.GetSqlbuilder(this.CurrentConnectionConfig);;
result.Context = this;
result.EntityInfo = this.Context.EntityMaintenance.GetEntityInfo <T>();
result.SqlBuilder = sqlBuilder;
result.InsertObjs = insertObjs;
sqlBuilder.InsertBuilder = result.InsertBuilder = InstanceFactory.GetInsertBuilder(this.CurrentConnectionConfig);
sqlBuilder.InsertBuilder.Builder = sqlBuilder;
sqlBuilder.InsertBuilder.LambdaExpressions = InstanceFactory.GetLambdaExpressions(this.CurrentConnectionConfig);
sqlBuilder.Context = result.SqlBuilder.InsertBuilder.Context = this;
result.Init();
return(result);
}
public virtual List <T> SqlQuery <T>(string sql, params SugarParameter[] parameters)
{
var builder = InstanceFactory.GetSqlbuilder(this.Context.CurrentConnectionConfig);
builder.SqlQueryBuilder.sql.Append(sql);
if (parameters != null && parameters.Any())
{
builder.SqlQueryBuilder.Parameters.AddRange(parameters);
}
using (var dataReader = this.GetDataReader(builder.SqlQueryBuilder.ToSqlString(), builder.SqlQueryBuilder.Parameters.ToArray()))
{
var reval = this.DbBind.DataReaderToList <T>(typeof(T), dataReader, builder.SqlQueryBuilder.Fields);
builder.SqlQueryBuilder.Clear();
return(reval);
}
}
public virtual ISugarQueryable <T, T2> Queryable <T, T2>(
ISugarQueryable <T> joinQueryable1, ISugarQueryable <T2> joinQueryable2, JoinType joinType, Expression <Func <T, T2, bool> > joinExpression) where T : class, new() where T2 : class, new()
{
Check.Exception(joinQueryable1.QueryBuilder.Take != null || joinQueryable1.QueryBuilder.Skip != null || joinQueryable1.QueryBuilder.OrderByValue.HasValue(), "joinQueryable1 Cannot have 'Skip' 'ToPageList' 'Take' Or 'OrderBy'");
Check.Exception(joinQueryable2.QueryBuilder.Take != null || joinQueryable2.QueryBuilder.Skip != null || joinQueryable2.QueryBuilder.OrderByValue.HasValue(), "joinQueryable2 Cannot have 'Skip' 'ToPageList' 'Take' Or 'OrderBy'");
var sqlBuilder = InstanceFactory.GetSqlbuilder(base.Context.CurrentConnectionConfig);
sqlBuilder.Context = base.Context;
InitMppingInfo <T, T2>();
var types = new Type[] { typeof(T2) };
var queryable = InstanceFactory.GetQueryable <T, T2>(base.CurrentConnectionConfig);
queryable.Context = base.Context;
queryable.SqlBuilder = sqlBuilder;
queryable.QueryBuilder = InstanceFactory.GetQueryBuilder(base.CurrentConnectionConfig);
queryable.QueryBuilder.JoinQueryInfos = new List <JoinQueryInfo>();
queryable.QueryBuilder.Builder = sqlBuilder;
queryable.QueryBuilder.Context = base.Context;
queryable.QueryBuilder.EntityType = typeof(T);
queryable.QueryBuilder.LambdaExpressions = InstanceFactory.GetLambdaExpressions(base.CurrentConnectionConfig);
//master
var shortName1 = joinExpression.Parameters[0].Name;
var sqlObj1 = joinQueryable1.ToSql();
string sql1 = sqlObj1.Key;
UtilMethods.RepairReplicationParameters(ref sql1, sqlObj1.Value.ToArray(), 0);
queryable.QueryBuilder.EntityName = sqlBuilder.GetPackTable(sql1, shortName1);;
queryable.QueryBuilder.Parameters.AddRange(sqlObj1.Value);
//join table 1
var shortName2 = joinExpression.Parameters[1].Name;
var sqlObj2 = joinQueryable2.ToSql();
string sql2 = sqlObj2.Key;
UtilMethods.RepairReplicationParameters(ref sql2, sqlObj2.Value.ToArray(), 1);
queryable.QueryBuilder.Parameters.AddRange(sqlObj2.Value);
var exp = queryable.QueryBuilder.GetExpressionValue(joinExpression, ResolveExpressType.WhereMultiple);
queryable.QueryBuilder.JoinQueryInfos.Add(new JoinQueryInfo()
{
JoinIndex = 0, JoinType = joinType, JoinWhere = exp.GetResultString(), TableName = sqlBuilder.GetPackTable(sql2, shortName2)
});
return(queryable);
}
protected UpdateableProvider <T> CreateUpdateable <T>(T[] UpdateObjs) where T : class, new()
{
var result = InstanceFactory.GetUpdateableProvider <T>(this.CurrentConnectionConfig);
var sqlBuilder = InstanceFactory.GetSqlbuilder(this.CurrentConnectionConfig);;
result.Context = this;
result.EntityInfo = this.Context.EntityMaintenance.GetEntityInfo <T>();
result.SqlBuilder = sqlBuilder;
result.UpdateObjs = UpdateObjs;
sqlBuilder.UpdateBuilder = result.UpdateBuilder = InstanceFactory.GetUpdateBuilder(this.CurrentConnectionConfig);
sqlBuilder.UpdateBuilder.Builder = sqlBuilder;
sqlBuilder.UpdateBuilder.LambdaExpressions = InstanceFactory.GetLambdaExpressions(this.CurrentConnectionConfig);
sqlBuilder.Context = result.SqlBuilder.UpdateBuilder.Context = this;
result.Init();
var ignoreColumns = result.EntityInfo.Columns.Where(it => it.IsOnlyIgnoreUpdate).ToList();
if (ignoreColumns != null && ignoreColumns.Any())
{
result = (UpdateableProvider <T>)result.IgnoreColumns(ignoreColumns.Select(it => it.PropertyName).ToArray());
}
return(result);
}
public ISugarQueryable <T> SqlQueryable <T>(string sql) where T : class, new()
{
var sqlBuilder = InstanceFactory.GetSqlbuilder(this.Context.CurrentConnectionConfig);
return(this.Queryable <T>().AS(sqlBuilder.GetPackTable(sql, sqlBuilder.GetDefaultShortName())).Select("*"));
}
/// <summary>
/// Get datebase time
/// </summary>
/// <returns></returns>
public DateTime GetDate()
{
var sqlBuilder = InstanceFactory.GetSqlbuilder(this.Context.CurrentConnectionConfig);
return(this.Ado.GetDateTime(sqlBuilder.FullSqlDateNow));
}
public KeyValuePair <string, SugarParameter[]> ConditionalModelToSql(List <IConditionalModel> models, int beginIndex = 0)
{
if (models.IsNullOrEmpty())
{
return(new KeyValuePair <string, SugarParameter[]>());
}
StringBuilder builder = new StringBuilder();
List <SugarParameter> parameters = new List <SugarParameter>();
var sqlBuilder = InstanceFactory.GetSqlbuilder(this.Context.CurrentConnectionConfig);
var mainIndex = 0;
var indexTree = 0;
foreach (var model in models)
{
if (model is ConditionalModel)
{
var item = model as ConditionalModel;
if (item.FieldName == $"[value=sql{UtilConstants.ReplaceKey}]")
{
builder.Append(item.FieldValue);
continue;
}
var index = mainIndex + beginIndex;
var type = index == 0 ? "" : "AND";
if (beginIndex > 0)
{
type = null;
}
string temp = " {0} {1} {2} {3} ";
string parameterName = string.Format("{0}Condit{1}{2}", sqlBuilder.SqlParameterKeyWord, item.FieldName, index);
if (parameterName.Contains("."))
{
parameterName = parameterName.Replace(".", "_");
}
if (parameterName.Contains("["))
{
parameterName = parameterName.Replace("[", "_");
}
if (parameterName.Contains("]"))
{
parameterName = parameterName.Replace("]", "_");
}
if (parameterName.Contains(this.SqlTranslationLeft))
{
parameterName = parameterName.Replace(this.SqlTranslationLeft, "_");
}
string oldName = item.FieldName;
item.FieldName = GetTranslationColumnName(item.FieldName);
switch (item.ConditionalType)
{
case ConditionalType.Equal:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "=", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.Like:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "LIKE", parameterName);
parameters.Add(new SugarParameter(parameterName, "%" + item.FieldValue + "%"));
break;
case ConditionalType.GreaterThan:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), ">", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.GreaterThanOrEqual:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), ">=", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.LessThan:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.LessThanOrEqual:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<=", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.In:
if (item.FieldValue == null)
{
item.FieldValue = string.Empty;
}
var inValue1 = ("(" + item.FieldValue.Split(',').ToJoinSqlInVals() + ")");
if (item.CSharpTypeName.HasValue() && UtilMethods.IsNumber(item.CSharpTypeName))
{
inValue1 = inValue1.Replace("'", "");
}
else if (inValue1.Contains("'null'"))
{
inValue1 = inValue1.Replace("'null'", "null");
}
else if (inValue1.Contains("[null]"))
{
inValue1 = inValue1.Replace("[null]", "null");
}
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "IN", inValue1);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.NotIn:
if (item.FieldValue == null)
{
item.FieldValue = string.Empty;
}
var inValue2 = ("(" + item.FieldValue.Split(',').ToJoinSqlInVals() + ")");
if (item.CSharpTypeName.HasValue() && UtilMethods.IsNumber(item.CSharpTypeName))
{
inValue2 = inValue2.Replace("'", "");
}
else if (inValue2.Contains("'null'"))
{
inValue2 = inValue2.Replace("'null'", "null");
}
else if (inValue2.Contains("[null]"))
{
inValue2 = inValue2.Replace("[null]", "null");
}
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "NOT IN", inValue2);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.LikeLeft:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "LIKE", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue + "%"));
break;
case ConditionalType.NoLike:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), " NOT LIKE", parameterName);
parameters.Add(new SugarParameter(parameterName, "%" + item.FieldValue + "%"));
break;
case ConditionalType.LikeRight:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "LIKE", parameterName);
parameters.Add(new SugarParameter(parameterName, "%" + item.FieldValue));
break;
case ConditionalType.NoEqual:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<>", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.IsNullOrEmpty:
builder.AppendFormat(" {0} (({1}) OR ({2})) ", type, item.FieldName.ToSqlFilter() + " IS NULL ", item.FieldName.ToSqlFilter() + " = '' ");
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.IsNot:
if (item.FieldValue == null)
{
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), " IS NOT ", "NULL");
}
else
{
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<>", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
}
break;
case ConditionalType.EqualNull:
if (GetFieldValue(item) == null)
{
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), " IS ", " NULL ");
}
else
{
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "=", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
}
break;
case ConditionalType.InLike:
var array = (item.FieldValue + "").Split(',').ToList();
List <string> sqls = new List <string>();
int i = 0;
foreach (var val in array)
{
var itemParameterName = $"{ parameterName}{index}{i}";
sqls.Add(item.FieldName.ToSqlFilter() + " LIKE " + itemParameterName);
parameters.Add(new SugarParameter(itemParameterName, "%" + val + "%"));
i++;
}
builder.Append($" {type} ({string.Join(" OR ", sqls)}) ");
break;
default:
break;
}
item.FieldName = oldName;
}
else if (model is ConditionalCollections)
{
var item = model as ConditionalCollections;
if (item != null && item.ConditionalList.HasValue())
{
foreach (var con in item.ConditionalList)
{
var index = item.ConditionalList.IndexOf(con);
var isFirst = index == 0;
var isLast = index == (item.ConditionalList.Count - 1);
if (models.IndexOf(item) == 0 && index == 0 && beginIndex == 0)
{
builder.AppendFormat(" ( ");
}
else if (isFirst)
{
builder.AppendFormat(" {0} ( ", con.Key.ToString().ToUpper());
}
List <IConditionalModel> conModels = new List <IConditionalModel>();
conModels.Add(con.Value);
var childSqlInfo = ConditionalModelToSql(conModels, 1000 * (1 + index) + models.IndexOf(item));
if (!isFirst && con.Value.FieldName != $"[value=sql{UtilConstants.ReplaceKey}]")
{
builder.AppendFormat(" {0} ", con.Key.ToString().ToUpper());
}
builder.Append(childSqlInfo.Key);
parameters.AddRange(childSqlInfo.Value);
if (isLast)
{
builder.Append(" ) ");
}
else
{
}
}
}
}
else
{
var item = model as ConditionalTree;
BuilderTree(builder, item, ref indexTree, parameters, ref mainIndex);
}
mainIndex++;
}
return(new KeyValuePair <string, SugarParameter[]>(builder.ToString(), parameters.ToArray()));
}
public KeyValuePair <string, SugarParameter[]> ConditionalModelToSql(List <IConditionalModel> models, int beginIndex = 0)
{
if (models.IsNullOrEmpty())
{
return(new KeyValuePair <string, SugarParameter[]>());
}
StringBuilder builder = new StringBuilder();
List <SugarParameter> parameters = new List <SugarParameter>();
var sqlBuilder = InstanceFactory.GetSqlbuilder(this.Context.CurrentConnectionConfig);
foreach (var model in models)
{
if (model is ConditionalModel)
{
var item = model as ConditionalModel;
var index = models.IndexOf(item) + beginIndex;
var type = index == 0 ? "" : "AND";
if (beginIndex > 0)
{
type = null;
}
string temp = " {0} {1} {2} {3} ";
string parameterName = string.Format("{0}Conditional{1}{2}", sqlBuilder.SqlParameterKeyWord, item.FieldName, index);
if (parameterName.Contains("."))
{
parameterName = parameterName.Replace(".", "_");
}
if (parameterName.Contains("["))
{
parameterName = parameterName.Replace("[", "_");
}
if (parameterName.Contains("]"))
{
parameterName = parameterName.Replace("]", "_");
}
switch (item.ConditionalType)
{
case ConditionalType.Equal:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "=", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.Like:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "LIKE", parameterName);
parameters.Add(new SugarParameter(parameterName, "%" + item.FieldValue + "%"));
break;
case ConditionalType.GreaterThan:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), ">", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.GreaterThanOrEqual:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), ">=", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.LessThan:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.LessThanOrEqual:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<=", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.In:
if (item.FieldValue == null)
{
item.FieldValue = string.Empty;
}
var inValue1 = ("(" + item.FieldValue.Split(',').ToJoinSqlInVals() + ")");
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "IN", inValue1);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.NotIn:
if (item.FieldValue == null)
{
item.FieldValue = string.Empty;
}
var inValue2 = ("(" + item.FieldValue.Split(',').ToJoinSqlInVals() + ")");
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "NOT IN", inValue2);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.LikeLeft:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "LIKE", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue + "%"));
break;
case ConditionalType.NoLike:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), " NOT LIKE", parameterName);
parameters.Add(new SugarParameter(parameterName, "%" + item.FieldValue + "%"));
break;
case ConditionalType.LikeRight:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "LIKE", parameterName);
parameters.Add(new SugarParameter(parameterName, "%" + item.FieldValue));
break;
case ConditionalType.NoEqual:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<>", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.IsNullOrEmpty:
builder.AppendFormat(" {0} (({1}) OR ({2})) ", type, item.FieldName.ToSqlFilter() + " IS NULL ", item.FieldName.ToSqlFilter() + " = '' ");
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.IsNot:
if (item.FieldValue == null)
{
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), " IS NOT ", "NULL");
}
else
{
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<>", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
}
break;
case ConditionalType.EqualNull:
if (GetFieldValue(item) == null)
{
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), " IS ", " NULL ");
}
else
{
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "=", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
}
break;
default:
break;
}
}
else
{
var item = model as ConditionalCollections;
if (item != null && item.ConditionalList.HasValue())
{
foreach (var con in item.ConditionalList)
{
var index = item.ConditionalList.IndexOf(con);
var isFirst = index == 0;
var isLast = index == (item.ConditionalList.Count - 1);
if (models.IndexOf(item) == 0 && index == 0 && beginIndex == 0)
{
builder.AppendFormat(" ( ");
}
else if (isFirst)
{
builder.AppendFormat(" {0} ( ", con.Key.ToString().ToUpper());
}
List <IConditionalModel> conModels = new List <IConditionalModel>();
conModels.Add(con.Value);
var childSqlInfo = ConditionalModelToSql(conModels, 1000 * (1 + index) + models.IndexOf(item));
if (!isFirst)
{
builder.AppendFormat(" {0} ", con.Key.ToString().ToUpper());
}
builder.Append(childSqlInfo.Key);
parameters.AddRange(childSqlInfo.Value);
if (isLast)
{
builder.Append(" ) ");
}
else
{
}
}
}
}
}
return(new KeyValuePair <string, SugarParameter[]>(builder.ToString(), parameters.ToArray()));
}
