public WhereClause(string columnName = null, string alias = null, WhereOperator op = WhereOperator.None, bool filterEmpty = false, object value1 = null, object value2 = null) : base(columnName, alias)
{
Operator = op;
FilterEmpty = filterEmpty;
Value1 = value1;
Value2 = value2;
}
/// <summary>
/// Converts a WHERE operator to its corresponding string.
/// </summary>
/// <param name="op">The operator.</param>
/// <returns>The string.</returns>
private static string WhereOperatorToString(WhereOperator op)
{
switch (op)
{
case WhereOperator.Like:
return("like");
case WhereOperator.Equals:
return("=");
case WhereOperator.NotEquals:
return("<>");
case WhereOperator.GreaterThan:
return(">");
case WhereOperator.LessThan:
return("<");
case WhereOperator.GreaterThanOrEqualTo:
return(">=");
case WhereOperator.LessThanOrEqualTo:
return("<=");
case WhereOperator.IsNull:
return("is null");
case WhereOperator.IsNotNull:
return("is not null");
default:
throw new NotSupportedException();
}
}
public WherePart AddWhere(string value, string name, WhereOperator @operator)
{
var part = new WherePart(value, name != null ? name.Trim() : null, @operator);
Where.Add(part);
return(part);
}
protected internal override QueryOperator VisitWhere(WhereOperator op)
{
var o = (WhereOperator)base.VisitWhere(op);
var m = op.QueryExpressionFactory.Where(o.ElementType, o.Source, o.Predicate);
return(m);
}
private string WhereOperatorToSql(WhereOperator oper)
{
switch (oper)
{
case WhereOperator.EqualTo:
return("=");
case WhereOperator.GreaterThan:
return(">");
case WhereOperator.GreatherEqualThan:
return(">=");
case WhereOperator.In:
return("IN");
case WhereOperator.LessEqualThan:
return("<=");
case WhereOperator.LessThan:
return("<");
case WhereOperator.Like:
return("LIKE");
case WhereOperator.NotEqualTo:
return("!=");
case WhereOperator.NotIn:
return(" NOT IN");
default:
throw new ArgumentException("Invalid WhereOperator");
}
}
public void Aris_PackArrayTest()
{
dynamic evt = new ExpandoObject();
WhereOperator whereOperatorHigh = new WhereOperator("sum_flow_count_low > 1000");
WhereOperator whereOperatorLow = new WhereOperator("sum_flow_count_low > 1000");
// Dehydrate
string jsonWhere = RxKqlCommonFunctions.ToJson(whereOperatorHigh.Expression);
// Hydrate
var booleanExpression = RxKqlCommonFunctions.ToBooleanExpression(jsonWhere);
// Evaluate the current Expression
evt = new ExpandoObject();
try
{
// purposely add a value as a string and compare it to an integer, to throw a specific Exception Type
evt.sum_flow_count_high = "101112";
TestWhere(evt, whereOperatorHigh, true);
}
catch (Exception ex)
{
Type exceptionType = ex.GetType();
Assert.AreEqual(exceptionType, typeof(EvaluationTypeMismatchException));
}
evt.sum_flow_count_low = 999;
TestWhere(evt, whereOperatorLow, false);
}
public SqlQueryBuilder AndCondition(string tableAlias, string fieldName, WhereOperator whereOperator, object value, bool skipIfNull = true)
{
string condition = WhereCondition(tableAlias, fieldName, whereOperator, value, skipIfNull);
_query.Append($" AND ({condition})");
return(this);
}
public WhereParam(T value, WhereOperator whereOperator)
{
if (typeof(T).IsClass && value == null)
{
throw new NotNullableException("WhereParam cannot be null");
}
FOperator = whereOperator;
FValue = value;
}
/*===========================================================*/
/// <summary>設定欄位值與查詢條件</summary>
IWhereParams IWhereParams.SetOperator(string name, WhereOperator oper)
{
if (!_source.ContainsKey(name))
{
return(this);
}
_source[name].Operator = oper;
return(this);
}
public static WhereToken Create(WhereOperator op, string fieldName, string parameterName, WhereOptions options = null)
{
return(new WhereToken
{
FieldName = fieldName,
ParameterName = parameterName,
WhereOperator = op,
Options = options ?? WhereOptions.Default()
});
}
internal SelectBuilder Having(WhereOperator preOperator, Type[] types, LambdaExpression expression)
{
var aliases = GetAliasesForTypes(types);
var definitionsByParameterName = GetTableDefinitionByParameterName(expression, aliases);
var visitor = new Visitor(definitionsByParameterName, this);
var conditionSql = visitor.GetSql(expression.Body);
HavingSql(preOperator, conditionSql);
return(this);
}
protected void TestWhere(dynamic evt, WhereOperator whereOperator, bool expected)
{
var exp = whereOperator;
// For operators not present in Rx.Net there is runtime implementation
bool actual = exp.Evaluate(evt);
// Compare the expected result (e.g. hardcoded in the test)
// with the actual value
Assert.AreEqual(expected, actual);
}
/*===========================================================*/
/// <summary>設定欄位值與查詢條件</summary>
IWhereParams IWhereParams.SetValues <TValue>(string name, WhereOperator oper, TValue[] values)
{
if (values == null)
{
values = new TValue[0];
}
string[] strings = values.Convert <string>().ToArray();
_source[name] = new WhereParamsPair(oper, strings);
return(this);
}
public void TableActionFieldBinding()
{
RowSelectorPanelInit();
WhereColumn.Init(mAct.GetOrCreateInputParam(ActUIElement.Fields.WhereColSelector, mAct.GetInputParamValue(ActUIElement.Fields.WhereColSelector)), typeof(ActUIElement.eTableElementRunColSelectorValue), isVENeeded: false, UCselectionChange: WhereColumn_SelectionChanged);
WhereColumnTitle.Init(mAct.GetOrCreateInputParam(ActUIElement.Fields.WhereColumnTitle, mAct.GetInputParamValue(ActUIElement.Fields.WhereColumnTitle)), isVENeeded: true, UCselectionChange: WhereColumnTitle_SelectionChanged, context: Context.GetAsContext(mAct.Context));
WhereProperty.Init(mAct.GetOrCreateInputParam(ActUIElement.Fields.WhereProperty, mAct.GetInputParamValue(ActUIElement.Fields.WhereProperty)), typeof(ActUIElement.eTableElementRunColPropertyValue), isVENeeded: false, UCselectionChange: WhereProperty_SelectionChanged);
WhereOperator.Init(mAct.GetOrCreateInputParam(ActUIElement.Fields.WhereOperator, mAct.GetInputParamValue(ActUIElement.Fields.WhereOperator)), typeof(ActUIElement.eTableElementRunColOperator), isVENeeded: false, UCselectionChange: WhereOperator_SelectionChanged);
WhereColumnValue.Init(Context.GetAsContext(mAct.Context), mAct.GetOrCreateInputParam(ActUIElement.Fields.WhereColumnValue, mAct.GetInputParamValue(ActUIElement.Fields.WhereColumnValue)));
RowSelectorValue.Init(mAct.GetOrCreateInputParam(ActUIElement.Fields.LocateRowValue), isVENeeded: true, UCselectionChange: RowSelectorValue_SelectionChanged, context: Context.GetAsContext(mAct.Context));
}
/// <summary>綁定查詢欄位</summary>
public WhereBuilder <TModel, TParams> WhereBind <T>(Expression <Func <TParams, T> > find, Expression <Func <TModel, IEnumerable <T> > > columnSelector)
{
if (_param == null)
{
return(this);
}
T[] values = getValues(find);
WhereOperator oper = _param.GetOperator(find);
bindEnumerable(columnSelector, oper, values);
return(this);
}
public void OperatorTest(string value, WhereOperator expected)
{
var collection = new NameValueCollection();
collection["InvoicePrefix"] = value;
var modelBinder = new WhereParamsModelBinder();
var obj = modelBinder.CreateWhereParams(typeof(TestParamsDomain), collection);
var param = obj as WhereParams <TestParamsDomain>;
Assert.Equal(param.GetOperator(x => x.InvoicePrefix), expected);
}
public void QueryExpressionToStringTests_Simple()
{
var visitor = new QueryDebugger();
var type = typeof(int);
var lambdaAbstraction = DefaultQueryExpressionFactory.Instance.LambdaAbstraction(Expression.Lambda(Expression.Default(typeof(IEnumerable <int>))));
var lambdaAbstractionPredicate = DefaultQueryExpressionFactory.Instance.LambdaAbstraction((Expression <Func <Func <int, bool> > >)(() => _ => true));
var lambdaAbstractionSelector = DefaultQueryExpressionFactory.Instance.LambdaAbstraction((Expression <Func <Func <int, int> > >)(() => _ => _));
#pragma warning disable IDE0004 // Remove Unnecessary Cast. (Only unnecessary on C# 10 or later.)
var lambdaAbstractionCount = DefaultQueryExpressionFactory.Instance.LambdaAbstraction((Expression <Func <int> >)(() => 42));
#pragma warning restore IDE0004 // Remove Unnecessary Cast
var monadMember = (MonadMember) new MonadAbstraction(type, lambdaAbstraction);
{
var op = new FirstOperator(type, monadMember);
Assert.AreEqual("@First(@MonadAbstraction(@LambdaAbstraction(() => default(IEnumerable`1))))", visitor.Visit(op));
}
{
var op = new FirstPredicateOperator(type, monadMember, lambdaAbstractionPredicate);
Assert.AreEqual("@First(@MonadAbstraction(@LambdaAbstraction(() => default(IEnumerable`1))), @LambdaAbstraction(() => _ => True))", visitor.Visit(op));
}
{
var op = new SelectOperator(type, type, monadMember, lambdaAbstractionSelector);
Assert.AreEqual("@Select(@MonadAbstraction(@LambdaAbstraction(() => default(IEnumerable`1))), @LambdaAbstraction(() => _ => _))", visitor.Visit(op));
}
{
var op = new TakeOperator(type, monadMember, lambdaAbstractionCount);
Assert.AreEqual("@Take(@MonadAbstraction(@LambdaAbstraction(() => default(IEnumerable`1))), @LambdaAbstraction(() => 42))", visitor.Visit(op));
}
{
var op = new WhereOperator(type, monadMember, lambdaAbstractionPredicate);
Assert.AreEqual("@Where(@MonadAbstraction(@LambdaAbstraction(() => default(IEnumerable`1))), @LambdaAbstraction(() => _ => True))", visitor.Visit(op));
}
{
var ma = new MonadAbstraction(type, lambdaAbstraction);
Assert.AreEqual("@MonadAbstraction(@LambdaAbstraction(() => default(IEnumerable`1)))", visitor.Visit(ma));
}
{
var lambda = Expression.Lambda(Expression.Default(typeof(int)), Expression.Parameter(typeof(IEnumerable <int>), "p"));
var parameters = new QueryTree[] { monadMember }.ToReadOnly();
var la = new LambdaAbstraction(lambda, parameters);
Assert.AreEqual("@LambdaAbstraction(p => default(Int32), @MonadAbstraction(@LambdaAbstraction(() => default(IEnumerable`1))))", visitor.Visit(la));
}
}
public static string GetWhereClause(string fieldName1, string fieldName2, WhereOperator whereOperator, bool doNotFilterIfEmpty, object value1, object value2)
{
if (whereOperator != WhereOperator.Between && whereOperator != WhereOperator.NotBetween && whereOperator != WhereOperator.Overlap && whereOperator != WhereOperator.NotOverlap)
{
return(GetWhereClause(fieldName1, whereOperator, doNotFilterIfEmpty, value1));
}
if (doNotFilterIfEmpty && InternalIsEmpty(value1) && InternalIsEmpty(value2))
{
return(string.Empty);
}
if (value1 == null || value1 == DBNull.Value)
{
throw new ArgumentNullException(nameof(value1), "Cannot use comparison operator " + whereOperator + " for NULL values.");
}
if (value2 == null || value2 == DBNull.Value)
{
throw new ArgumentNullException(nameof(value2), "Cannot use comparison operator " + whereOperator + " for NULL values.");
}
var output = string.Empty;
var formatedValue1 = value1.ToSqlValue();
var formatedValue2 = value2.ToSqlValue();
switch (whereOperator)
{
case WhereOperator.Between:
output = fieldName1 + " BETWEEN " + formatedValue1 + " AND " + formatedValue2;
break;
case WhereOperator.NotBetween:
output = fieldName1 + " NOT BETWEEN " + formatedValue1 + " AND " + formatedValue2;
break;
case WhereOperator.Overlap:
output = "(" + fieldName1 + " >= " + formatedValue1 + " AND " + fieldName1 + " <= " + formatedValue2 + ")" +
" OR (" + fieldName2 + " >= " + formatedValue1 + " AND " + fieldName2 + " <= " + formatedValue2 + ")" +
" OR (" + fieldName1 + " <= " + formatedValue1 + " AND " + fieldName2 + " >= " + formatedValue2 + ")";
break;
case WhereOperator.NotOverlap:
output = "(" + fieldName1 + " > " + formatedValue2 + ")" +
" OR (" + fieldName2 + " < " + formatedValue1 + ")";
break;
}
return(output.Length != 0 ? "(" + output + ")" : output);
}
protected void TestWhere(dynamic evt, string expression, bool expected)
{
// If the following line passes, it usually means parsing is ok
var where = new WhereOperator(expression);
var exp = where;
// For operators not present in Rx.Net there is runtime implementation
bool actual = exp.Evaluate(evt);
// Compare the expected result (e.g. hardcoded in the test)
// with the actual value
Assert.AreEqual(expected, actual);
}
/*=====================================================================*/
private Expression getStringCondition(Expression parameter, WhereOperator oper, string[] values)
{
var containsMethod = _containsMethod.MakeGenericMethod(typeof(string));
var compareMethod = LambdaUtils.GetMethod <string>(x => x.CompareTo(""));
var zeroExpr = Expression.Constant(0);
var valueExpr = Expression.Constant(values.FirstOrDefault(), typeof(string));
var valuesExpr = Expression.Constant(values, typeof(string[]));
var compareExpr = Expression.Call(parameter, compareMethod, valueExpr);
switch (oper)
{
case WhereOperator.Contains: /* x.Contains(value) */
var stringContains = LambdaUtils.GetMethod <string>(x => x.Contains(""));
return(Expression.Call(parameter, stringContains, valueExpr));
case WhereOperator.StartsWith: /* x.StartsWith(value) */
var stringStartsWith = LambdaUtils.GetMethod <string>(x => x.StartsWith(""));
return(Expression.Call(parameter, stringStartsWith, valueExpr));
case WhereOperator.EndsWith: /* x.EndsWith(value) */
var stringEndsWith = LambdaUtils.GetMethod <string>(x => x.EndsWith(""));
return(Expression.Call(parameter, stringEndsWith, valueExpr));
case WhereOperator.Equals: /* x == value */
return(Expression.Equal(parameter, valueExpr));
case WhereOperator.NotEquals: /* x != value */
return(Expression.NotEqual(parameter, valueExpr));
case WhereOperator.LessThan: /* x < value */
return(Expression.LessThan(compareExpr, Expression.Constant(0)));
case WhereOperator.LessEquals: /* x <= value */
return(Expression.LessThanOrEqual(compareExpr, Expression.Constant(0)));
case WhereOperator.GreaterThan: /* x > value */
return(Expression.GreaterThan(compareExpr, Expression.Constant(0)));
case WhereOperator.GreaterEquals: /* x >= value */
return(Expression.GreaterThanOrEqual(compareExpr, Expression.Constant(0)));
case WhereOperator.In: /* values.Contains(x) */
return(Expression.Call(containsMethod, valuesExpr, parameter));
case WhereOperator.NotIn: /* !values.Contains(x) */
return(Expression.Not(Expression.Call(containsMethod, valuesExpr, parameter)));
}
return(null);
}
public void StringValueTest(WhereOperator oper, string expected)
{
var param = new WhereParams <InvoiceIssueDomain>();
param.SetValues(x => x.InvoicePrefix, oper, "SS");
var build = new WhereBuilder <InvoiceIssue, InvoiceIssueDomain>(_dc.InvoiceIssue, param);
build.WhereBind(x => x.InvoicePrefix, y => y.InvoicePrefix);
var query = build.Build();
var sql = query.ToString();
Assert.Contains(expected, sql);
}
public WhereClause(WhereJoin join, string field, WhereOperator op, string value, IDictionary <string, object> param)
{
Join = join;
Field = field;
Operator = op;
Value = value;
if (param != null)
{
foreach (var kvp in param)
{
Parameters.TryAdd(kvp.Key, kvp.Value);
}
}
}
public NullableWhereParam(T value, WhereOperator whereOperator)
{
if (typeof(T).IsClass && value == null)
{
FOperator = whereOperator;
IsNull = false;
return;
}
FOperator = whereOperator;
FValue = value;
if (whereOperator == WhereOperator.EqualsNull || whereOperator == WhereOperator.NotEqualsNull)
{
IsNull = false;
}
}
public static string Format(this WhereOperator op)
{
switch (op)
{
case WhereOperator.Equals: return("=");
case WhereOperator.NotEquals: return("<>");
case WhereOperator.GreaterThan: return(">");
case WhereOperator.Etc: return(">, <, etc...");
default: throw new ArgumentOutOfRangeException();
}
}
public void SubQueryTest(WhereOperator oper, string expected)
{
var param = new WhereParams <InvoiceIssueDomain>();
param.SetValues(x => x.ProductQty, oper, 1, 3);
var build = new WhereBuilder <InvoiceIssue, InvoiceIssueDomain>(_dc.InvoiceIssue, param);
build.WhereBind <int?>(x => x.ProductQty, y => y.InvoiceIssueItems.Select(z => (int?)z.Qty));
var query = build.Build();
var sql = query.ToString();
Assert.Contains(expected, sql);
}
public void DecimalValueTest(WhereOperator oper, string expected)
{
var param = new WhereParams <InvoiceIssueDomain>();
param.SetValues(x => x.Sum, oper, 1.0m, 3.0m);
var build = new WhereBuilder <InvoiceIssue, InvoiceIssueDomain>(_dc.InvoiceIssue, param);
build.WhereBind(x => x.Sum, y => y.Total);
var query = build.Build();
var sql = query.ToString();
Assert.Contains(expected, sql);
}
public void DateTimeValueTest(WhereOperator oper, string expected)
{
var param = new WhereParams <InvoiceIssueDomain>();
param.SetValues(x => x.ModifyDate, oper, DateTime.Today, DateTime.Now);
var build = new WhereBuilder <InvoiceIssue, InvoiceIssueDomain>(_dc.InvoiceIssue, param);
build.WhereBind(x => x.ModifyDate, y => y.ModifyDate);
var query = build.Build();
var sql = query.ToString();
Assert.Contains(expected, sql);
}
public void ExtendIsNotNullTest()
{
dynamic dict = new ExpandoObject();
dict.Id = 10;
dict.Inner = new
{
Id = 20
};
var outerId = new WhereOperator("isnotnull(Id)").Evaluate(dict);
var innerId = new WhereOperator("isnotnull(Inner.Id)").Evaluate(dict);
Assert.AreEqual(outerId, true);
Assert.AreEqual(innerId, true);
}
private static string InternalGetWhereOperatorAbr(WhereOperator op)
{
switch (op)
{
case WhereOperator.Equals: return("=");
case WhereOperator.NotEquals: return("<>");
case WhereOperator.GreaterThan: return(">");
case WhereOperator.GreaterThanOrEquals: return(">=");
case WhereOperator.LessThan: return("<");
case WhereOperator.LessThanOrEquals: return("<=");
case WhereOperator.Between: return("[...]");
case WhereOperator.NotBetween: return("![...]");
case WhereOperator.Overlap: return("OL");
case WhereOperator.NotOverlap: return("NOL");
case WhereOperator.Like: return("a%c");
case WhereOperator.NotLike: return("!a%c");
case WhereOperator.Contains: return("a[b]c");
case WhereOperator.NotContains: return("!a[b]c");
case WhereOperator.Begins: return("[a]b");
case WhereOperator.NotBegins: return("![a]b");
case WhereOperator.Ends: return("b[c]");
case WhereOperator.NotEnds: return("!b[c]");
case WhereOperator.In: return("(a,b,c)");
case WhereOperator.NotIn: return("!(a,b,c)");
}
return(string.Empty);
}
public void WhereTest_DecisionOnTree()
{
string dataJson = @"{
""Source"": ""WEC-CDOC"",
""OccurenceUtc"": ""2018-09-11T22:22:13.0000000Z"",
""Origin"": {},
""ArisId"": ""f57a0c50-9326-409d-85a4-f15aca0e7392"",
""Name"": ""GME Smartcard Service Accounts - SIM-00011"",
""Data"": {
""detectionDictionaryData_RxKqlDetectionItemEntityId"": ""1414"",
""Rows"": [
{""IcmTeam"" : ""a""},
{""IcmTeam"" : ""b""},
],
}
}";
var expando = JsonConvert.DeserializeObject <ExpandoObject>(dataJson);
try
{
string whereExpression = @"Data.Rows[0].IcmTeam == ""a""";
var whereOperator = new WhereOperator(whereExpression);
var result = whereOperator.Evaluate(expando);
Assert.IsTrue(result);
whereExpression = @"Data.Rows[1].IcmTeam == ""b""";
whereOperator = new WhereOperator(whereExpression);
result = whereOperator.Evaluate(expando);
Assert.IsTrue(result);
whereExpression = @"Data.Rows[0].IcmTeam != ""b""";
whereOperator = new WhereOperator(whereExpression);
result = whereOperator.Evaluate(expando);
Assert.IsTrue(result);
whereExpression = @"Data.Rows[1].IcmTeam != ""b""";
whereOperator = new WhereOperator(whereExpression);
result = whereOperator.Evaluate(expando);
Assert.IsFalse(result);
}
catch (Exception exception)
{
Console.WriteLine($"Extend Failed: {exception}");
}
}
public static string getWherOperator(WhereOperator wo, string separator, string value)
{
string opt = "";
switch (wo)
{
case WhereOperator.Bigger:
opt = ">" + separator + value + separator;
break;
case WhereOperator.BiggerEqual:
opt = ">=" + separator + value + separator;
break;
case WhereOperator.Equal:
opt = "=" + separator + value + separator;
break;
case WhereOperator.Smaller:
opt = "<" + separator + value + separator;
break;
case WhereOperator.SmallerEqual:
opt = "<=" + separator + value + separator;
break;
case WhereOperator.Like:
opt = " LIKE " + separator + "%" + value + "%" + separator;
break;
case WhereOperator.NotLike:
opt = " NOT LIKE " + separator + "%" + value + "%" + separator;
break;
case WhereOperator.LLike:
opt = " LIKE " + separator + "%" + value + separator;
break;
case WhereOperator.RLike:
opt = " LIKE " + separator + value + "%" + separator;
break;
}
return opt;
}
/// <summary>
/// Builds a WHERE clause condition.
/// </summary>
/// <param name="table">The table the column belongs to, or <c>null</c>.</param>
/// <param name="column">The column.</param>
/// <param name="op">The operator.</param>
/// <param name="parameter">The parameter name.</param>
/// <returns>The condition.</returns>
private string BuildWhereClause(string table, string column, WhereOperator op, string parameter)
{
StringBuilder sb = new StringBuilder(80);
if(table != null) {
sb.Append(builder.ObjectNamePrefix);
sb.Append(table);
sb.Append(builder.ObjectNameSuffix);
sb.Append(".");
}
sb.Append(builder.ObjectNamePrefix);
sb.Append(column);
sb.Append(builder.ObjectNameSuffix);
sb.Append(" ");
sb.Append(WhereOperatorToString(op));
if(op != WhereOperator.IsNull && op != WhereOperator.IsNotNull) {
sb.Append(" ");
if(builder.UseNamedParameters) {
sb.Append(builder.ParameterNamePrefix);
sb.Append(parameter);
sb.Append(builder.ParameterNameSuffix);
}
else sb.Append(builder.ParameterPlaceholder);
}
return sb.ToString();
}
public WherePart(string value, string name, WhereOperator @operator)
: base(value, name)
{
Operator = @operator;
}
/// <summary>
/// Adds another WHERE clause to a query.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="column">The column subject of the WHERE clause.</param>
/// <param name="op">The operator.</param>
/// <param name="parameter">The name of the parameter for the WHERE clause.</param>
/// <returns>The resulting query.</returns>
public string AndWhere(string query, string column, WhereOperator op, string parameter) {
return AndWhere(query, column, op, parameter, false, false);
}
/// <summary>
/// Builds a WHERE clause condition.
/// </summary>
/// <param name="table">The table the column belongs to, or <c>null</c>.</param>
/// <param name="column">The column.</param>
/// <param name="op">The operator.</param>
/// <param name="parameter">The parameter name.</param>
/// <returns>The condition.</returns>
private string BuildWhereClause(string table, string column, WhereOperator op, string parameter) {
StringBuilder sb = new StringBuilder(80);
if(table != null) {
OracleKeyWords.Filter(ref sb, table, builder);
sb.Append(".");
}
OracleKeyWords.Filter(ref sb, column, builder);
sb.Append(" ");
sb.Append(WhereOperatorToString(op));
if(op != WhereOperator.IsNull && op != WhereOperator.IsNotNull) {
sb.Append(" ");
if(builder.UseNamedParameters) {
sb.Append(builder.ParameterNamePrefix);
OracleKeyWords.Filter(ref sb, parameter, builder);
sb.Append(builder.ParameterNameSuffix);
}
else sb.Append(builder.ParameterPlaceholder);
}
return sb.ToString();
}
/// <summary>
/// Adds another WHERE clause to a query.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="table">The table the column belongs to.</param>
/// <param name="column">The column subject of the WHERE clause.</param>
/// <param name="op">The operator.</param>
/// <param name="parameter">The name of the parameter for the WHERE clause.</param>
/// <returns>The resulting query.</returns>
public string OrWhere(string query, string table, string column, WhereOperator op, string parameter) {
return OrWhere(query, table, column, op, parameter, false, false);
}
/// <summary>
/// Adds another WHERE clause to a query.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="column">The column subject of the WHERE clause.</param>
/// <param name="op">The operator.</param>
/// <param name="parameter">The name of the parameter for the WHERE clause.</param>
/// <param name="openBracket">A value indicating whether to open a bracket after the OR.</param>
/// <param name="closeBracket">A value indicating whether to close a bracket after the clause.</param>
/// <returns>The resulting query.</returns>
public string OrWhere(string query, string column, WhereOperator op, string parameter, bool openBracket, bool closeBracket) {
return OrWhere(query, null, column, op, parameter, openBracket, closeBracket);
}
/// <summary>
/// Adds another WHERE clause to a query.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="table">The table the column belongs to.</param>
/// <param name="column">The column subject of the WHERE clause.</param>
/// <param name="op">The operator.</param>
/// <param name="parameter">The name of the parameter for the WHERE clause.</param>
/// <param name="openBracket">A value indicating whether to open a bracket after the AND.</param>
/// <param name="closeBracket">A value indicating whether to close a bracket after the clause.</param>
/// <returns>The resulting query.</returns>
public string OrWhere(string query, string table, string column, WhereOperator op, string parameter, bool openBracket, bool closeBracket) {
StringBuilder sb = new StringBuilder(200);
sb.Append(query);
sb.Append(" or ");
if(openBracket) sb.Append("(");
sb.Append(BuildWhereClause(table, column, op, parameter));
if(closeBracket) sb.Append(")");
return sb.ToString();
}
/// <summary>
/// Converts a WHERE operator to its corresponding string.
/// </summary>
/// <param name="op">The operator.</param>
/// <returns>The string.</returns>
private static string WhereOperatorToString(WhereOperator op) {
switch(op) {
case WhereOperator.Like:
return "like";
case WhereOperator.Equals:
return "=";
case WhereOperator.NotEquals:
return "<>";
case WhereOperator.GreaterThan:
return ">";
case WhereOperator.LessThan:
return "<";
case WhereOperator.GreaterThanOrEqualTo:
return ">=";
case WhereOperator.LessThanOrEqualTo:
return "<=";
case WhereOperator.IsNull:
return "is null";
case WhereOperator.IsNotNull:
return "is not null";
default:
throw new NotSupportedException();
}
}
public WherePart AddWhere(string value, WhereOperator @operator)
{
return AddWhere(value, null, @operator);
}
public WherePart AddWhere(string value, string name, WhereOperator @operator)
{
var part = new WherePart(value, name != null ? name.Trim() : null, @operator);
Where.Add(part);
return part;
}
public static string getWherOperator(string fieldName, WhereOperator wo, string separator, string value)
{
string opt = "";
switch (wo)
{
case WhereOperator.Bigger:
opt = fieldName + ">" + separator + value + separator;
break;
case WhereOperator.BiggerEqual:
opt = fieldName + ">=" + separator + value + separator;
break;
case WhereOperator.Equal:
opt = fieldName + "=" + separator + value + separator;
break;
case WhereOperator.NotEqual:
opt = fieldName + "<>" + separator + value + separator;
break;
case WhereOperator.Smaller:
opt = fieldName + "<" + separator + value + separator;
break;
case WhereOperator.SmallerEqual:
opt = fieldName + "<=" + separator + value + separator;
break;
case WhereOperator.Like:
opt = fieldName + " LIKE " + separator + "%" + value + "%" + separator;
break;
case WhereOperator.NotLike:
opt = fieldName + " NOT LIKE " + separator + "%" + value + "%" + separator;
break;
case WhereOperator.LLike:
opt = fieldName + " LIKE " + separator + "%" + value + separator;
break;
case WhereOperator.RLike:
opt = fieldName + " LIKE " + separator + value + "%" + separator;
break;
case WhereOperator.CharIndex:
opt = " CHARINDEX(" + separator + value + separator + "," + fieldName + ")>0";
break;
case WhereOperator.NotCharIndex:
opt = " CHARINDEX(" + separator + value + separator + "," + fieldName + ")<=0";
break;
case WhereOperator.In:
opt = fieldName + " IN (" + separator + value + separator + ")";
break;
case WhereOperator.NotIn:
opt = fieldName + " NOT IN (" + separator + value + separator + ")";
break;
}
return opt;
}
