/// <summary>
/// Append supplied clause to the overall filter.
/// </summary>
/// <param name="column">Name of the element to filter on.</param>
/// <param name="op">Comparison operation.</param>
/// <param name="value">Value to compare to element.</param>
/// <param name="appendAsOr">When true, new filter added as an "or" to the previous filters, otherwise appended as an "and".</param>
/// <param name="first">When true, this is the first filter \ group so there is nothing to append a conjunction to, otherwise there is.</param>
public void AppendPredicate(string column, DSSearchOperatorEnum op, object value, bool appendAsOr, bool first = false)
{
if (Parameterize)
{
// convert the values to parameters
if (op == DSSearchOperatorEnum.In)
{
var list = value as List <object>;
if (list != null)
{
var paramList = new List <object>();
foreach (var o in list)
{
var paramName = AddParam(o);
paramList.Add(new DSParam(paramName));
}
value = paramList;
}
}
else
{
var paramName = AddParam(value);
value = new DSParam(paramName);
}
}
if (!first)
{
AppendConjunction(appendAsOr);
}
_filterClause.AppendFormat("{0} {1}{2}", column, GetOperatorText(op), GetValueText(op, value));
}
/// <summary>
/// Convert the supplied operator to the SQL expression to use.
/// </summary>
/// <param name="op">The operator to convert.</param>
/// <returns>The SQL expression for the supplied operator.</returns>
/// <exception cref="DSSearchBuildException">Throws when operator is not handled.</exception>
private string GetOperatorText(DSSearchOperatorEnum op)
{
switch (op)
{
case DSSearchOperatorEnum.Contains:
case DSSearchOperatorEnum.EndsWith:
case DSSearchOperatorEnum.StartsWith:
return("LIKE");
case DSSearchOperatorEnum.DoesNotEqual:
return("<>");
case DSSearchOperatorEnum.Equals:
return("=");
case DSSearchOperatorEnum.GreaterThan:
return(">");
case DSSearchOperatorEnum.GreaterThanOrEqual:
return(">=");
case DSSearchOperatorEnum.In:
return("IN");
case DSSearchOperatorEnum.IsBlank:
return("IS NULL");
case DSSearchOperatorEnum.IsNotBlank:
return("IS NOT NULL");
case DSSearchOperatorEnum.LessThan:
return("<");
case DSSearchOperatorEnum.LessThanOrEqual:
return("<=");
}
throw new DSSearchBuildException(string.Format("Unknown Operator: {0}", op));
}
/// <summary>
/// Convert the supplied value to the necessary SQL expression based on the supplied operator.
/// </summary>
/// <param name="op">The operator used to determine conversion.</param>
/// <param name="value">The value to convert.</param>
/// <returns>The SQL expression for the supplied value based on the supplied operator.</returns>
/// <exception cref="DSSearchBuildException">Throws when operator is not handled or value is incorrect type.</exception>
private string GetValueText(DSSearchOperatorEnum op, object value)
{
switch (op)
{
case DSSearchOperatorEnum.Contains:
return(FormatStringValue(" '%{0}%'", " '%'+{0}+'%'", value));
case DSSearchOperatorEnum.EndsWith:
return(FormatStringValue(" '%{0}'", " '%'+{0}", value));
case DSSearchOperatorEnum.Equals:
case DSSearchOperatorEnum.DoesNotEqual:
case DSSearchOperatorEnum.GreaterThan:
case DSSearchOperatorEnum.GreaterThanOrEqual:
case DSSearchOperatorEnum.LessThan:
case DSSearchOperatorEnum.LessThanOrEqual:
return(string.Format(" {0}", FormatValue(value)));
case DSSearchOperatorEnum.In:
var list = value as List <object>;
if (list == null)
{
throw new DSSearchBuildException(string.Format("IN Operator requires a List but received {0}.", value.GetType()));
}
return(string.Format(" ({0})", string.Join(", ", list.Select(i => FormatValue(i)))));
case DSSearchOperatorEnum.IsBlank:
case DSSearchOperatorEnum.IsNotBlank:
return(string.Empty);
case DSSearchOperatorEnum.StartsWith:
return(FormatStringValue(" '{0}%'", " {0}+'%'", value));
}
throw new DSSearchBuildException(string.Format("Unknown Operator: {0}", op));
}
