public void QueryByConstant(object fieldValue)
{
var paramId = NextParamId();
WhereConditions.Add(Adapter.Parameter(paramId));
AddParameter(paramId, fieldValue);
}
public void Or()
{
if (WhereConditions.Count > 0)
{
WhereConditions.Add(" OR ");
}
}
public void And()
{
if (WhereConditions.Count > 0)
{
WhereConditions.Add(" AND ");
}
}
public void AddDSCondition(ActDSConditon.eCondition wCond, string wColName, ActDSConditon.eOperator wOper, string wValue, List <string> mColName)
{
ActDSConditon ADSC = new ActDSConditon();
ObservableList <string> Condition = new ObservableList <string>();
if (wCond != ActDSConditon.eCondition.EMPTY)
{
foreach (ActDSConditon.eCondition item in Enum.GetValues(typeof(ActDSConditon.eCondition)))
{
if (item.ToString() != "EMPTY")
{
Condition.Add(item.ToString());
}
}
}
List <string> colNames = new List <string>();
foreach (string sColName in mColName)
{
colNames.Add(sColName);
}
ADSC.PossibleCondValues = Condition;
ADSC.PossibleColumnValues = colNames;
WhereConditions.Add(ADSC);
ADSC.wCondition = wCond;
ADSC.wTableColumn = wColName;
ADSC.wOperator = wOper;
ADSC.wValue = wValue;
}
public void QueryByFieldComparison(string leftTableName, string leftFieldName, string op,
string rightTableName, string rightFieldName)
{
var newCondition =
$"{Adapter.Field(leftTableName, leftFieldName)} {op} {Adapter.Field(rightTableName, rightFieldName)}";
WhereConditions.Add(newCondition);
}
public void QueryByFieldLike(string tableName, string fieldName, string fieldValue)
{
var paramId = NextParamId();
var newCondition = $"{Adapter.Field(tableName, fieldName)} LIKE {Adapter.Parameter(paramId)}";
WhereConditions.Add(newCondition);
AddParameter(paramId, fieldValue);
}
public void Case()
{
if (SelectionList.Count > 0)
{
throw new Exception("Case should not be called twice");
}
WhereConditions.Add(" CASE ");
}
/// <summary>
/// 该方法没有对sql注入进行参数化过滤
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public virtual void Where(string expression)
{
if (string.IsNullOrEmpty(expression))
{
throw new ArgumentNullException("必须传递参数 expression");
}
WhereConditions.Add($"({expression})");
}
public void QueryByFieldLike(string tableName, string fieldName, string fieldValue)
{
var paramId = NextParamId();
var newCondition = string.Format("{0} LIKE {1}",
Adapter.Field(tableName, fieldName),
Adapter.Parameter(paramId));
WhereConditions.Add(newCondition);
AddParameter(paramId, fieldValue);
}
public void QueryByFieldComparison(string leftTableName, string leftFieldName, string op,
string rightTableName, string rightFieldName)
{
var newCondition = string.Format("{0} {1} {2}",
Adapter.Field(leftTableName, leftFieldName),
op,
Adapter.Field(rightTableName, rightFieldName));
WhereConditions.Add(newCondition);
}
public void QueryByIsIn(string tableName, string fieldName, IEnumerable <object> values)
{
var paramIds = values.Select(x =>
{
var paramId = NextParamId();
AddParameter(paramId, x);
return(Adapter.Parameter(paramId));
});
var newCondition = $"{Adapter.Field(tableName, fieldName)} IN ({string.Join(",", paramIds)})";
WhereConditions.Add(newCondition);
}
public void QueryByIsIn <T>(string tableName, string fieldName, IEnumerable <T> values)
{
var paramIds = values.Select(x =>
{
var paramId = NextParamId();
AddParameter(paramId, x);
return(Adapter.Parameter(paramId));
});
var newCondition = string.Format("{0} IN ({1})", Adapter.Field(tableName, fieldName), string.Join(",", paramIds));
WhereConditions.Add(newCondition);
}
public void QueryByIsIn(string tableName, string fieldName, SqlBuilderBase sqlQuery)
{
var innerQuery = sqlQuery.CommandText;
foreach (var param in sqlQuery.CommandParameters)
{
var innerParamKey = "Inner" + param.Key;
innerQuery = Regex.Replace(innerQuery, param.Key, innerParamKey);
AddParameter(innerParamKey, param.Value);
}
var newCondition = string.Format("{0} IN ({1})", Adapter.Field(tableName, fieldName), innerQuery);
WhereConditions.Add(newCondition);
}
public void QueryByIsIn(string tableName, string fieldName, SqlLamBase sqlQuery)
{
var innerQuery = sqlQuery.QueryString;
foreach (var param in sqlQuery.QueryParameters)
{
var innerParamKey = "Inner" + param.Key;
innerQuery = Regex.Replace(innerQuery, param.Key, innerParamKey);
AddParameter(innerParamKey, param.Value);
}
var newCondition = $"{Adapter.Field(tableName, fieldName)} IN ({innerQuery})";
WhereConditions.Add(newCondition);
}
public void QueryBetween <T>(string tableName, string fieldName, T start, T end, bool not)
{
var paramStart = NextParamId();
AddParameter(paramStart, start);
var pStart = Adapter.Parameter(paramStart);
var paramEnd = NextParamId();
AddParameter(paramEnd, end);
var pEnd = Adapter.Parameter(paramEnd);
var newCondition = $"({ Adapter.Field(tableName, fieldName) } { (not ? "NOT " : "") }BETWEEN { pStart } AND { pEnd })";
WhereConditions.Add(newCondition);
}
public void DeExpression()
{
if (WhereExpressions.Count > 0)
{
foreach (var item in WhereExpressions)
{
DbExpressionVisitor expression = new DbExpressionVisitor();
expression.Visit(item.Body);
WhereConditions.Add(expression.SqlText.Builder.ToString().ToLower());
foreach (var p in expression.SqlText.Parameters)
{
AddParameter(p.Name, p.Value);
}
}
}
}
public SqlCondition AddCondition(ExpressionOperation operation, string exp)
{
var condition = new SqlCondition(operation, exp);
if (_currentWhereExp != null)
{
_currentWhereExp.AddCondition(condition);
}
else
{
if (WhereConditions == null)
{
WhereConditions = new List <SqlConditionItem>();
}
WhereConditions.Add(condition);
}
return(condition);
}
public SqlExpression AddWhereExp(ExpressionOperation operation)
{
if (WhereConditions == null)
{
WhereConditions = new List <SqlConditionItem>();
}
var condition = new SqlExpression(operation);
if (_currentWhereExp != null)
{
_currentWhereExp.AddCondition(condition);
}
else
{
WhereConditions.Add(condition);
}
_currentWhereExp = condition;
return(condition);
}
public void Then()
{
WhereConditions.Add(" THEN ");
}
internal void AddWhereCondition <TProperty>(Expression <Func <T, TProperty> > propertyExpression, Comparison comparison, object value)
{
WhereConditions.Add(new WhereCondition(GetColumnName(propertyExpression), value, comparison));
}
public void End()
{
WhereConditions.Add(" END ");
}
public void QueryByFieldNotNull(string tableName, string fieldName)
{
WhereConditions.Add(string.Format("{0} IS NOT NULL", Adapter.Field(tableName, fieldName)));
}
public void QueryByField(string tableName, string fieldName)
{
WhereConditions.Add(Adapter.Field(tableName, fieldName));
}
public void Not()
{
WhereConditions.Add(" NOT ");
}
public void LessThan(bool hasEqual)
{
WhereConditions.Add($" <{ (hasEqual ? "=" : "") } ");
}
public void GreaterThan(bool hasEqual)
{
WhereConditions.Add($" >{ (hasEqual ? "=" : "") } ");
}
public void QueryByFieldNotNull(string tableName, string fieldName)
{
WhereConditions.Add($"{Adapter.Field(tableName, fieldName)} IS NOT NULL");
}
public void EndExpression()
{
WhereConditions.Add(")");
}
public void BeginExpression()
{
WhereConditions.Add("(");
}
public void Else()
{
WhereConditions.Add(" ELSE ");
}