/// <summary>
/// Adds a string pattern matching filter to the query's where clause.
/// </summary>
/// <param name="query"></param>
/// <param name="fieldName"></param>
/// <param name="operator"></param>
/// <param name="value"></param>
/// <returns></returns>
public static IDbQueryWhereClause StringMatch(this IDbQueryWhereClause query, string fieldName, Csg.Data.Sql.SqlWildcardDecoration @operator, DbString value)
{
var filter = new Csg.Data.Sql.SqlStringMatchFilter(query.Root, fieldName, @operator, value.Value);
if (value.IsAnsi && value.IsFixedLength)
{
filter.DataType = DbType.AnsiStringFixedLength;
}
else if (value.IsAnsi)
{
filter.DataType = DbType.AnsiString;
}
else if (value.IsFixedLength)
{
filter.DataType = DbType.StringFixedLength;
}
else
{
filter.DataType = DbType.String;
}
if (value.Length >= 0)
{
filter.Size = value.Length;
}
query.AddFilter(filter);
return(query);
}
/// <summary>
/// Creates a WHERE clause comparison for a field and value in the form ([fieldName] [operator] [value]), where the value data type is <see cref="String"/>.
/// </summary>
/// <param name="where">The query builder instance</param>
/// <param name="fieldName">The name of the field to use as the expression on the left of the operator.</param>
/// <param name="operator">The comparison operator to use.</param>
/// <param name="value">The value of the parameter created for the right side of the operator.</param>
/// <param name="isAnsi">Is the database field an ANSI string or Unicode string?</param>
/// <param name="length">Is the database field fixed length or variable length?</param>
/// <returns></returns>
public static IDbQueryWhereClause FieldMatch(this IDbQueryWhereClause where, string fieldName, Csg.Data.Sql.SqlOperator @operator, string value, bool isAnsi = false, int?length = null)
{
var filter = new Csg.Data.Sql.SqlCompareFilter <string>(where.Root, fieldName, @operator, value);
if (isAnsi && length.HasValue && length.Value > 0)
{
filter.DataType = DbType.AnsiStringFixedLength;
}
else if (isAnsi)
{
filter.DataType = DbType.AnsiString;
}
else if (length.HasValue && length.Value > 0)
{
filter.DataType = DbType.StringFixedLength;
}
else
{
filter.DataType = DbType.String;
}
if (length.HasValue && length.Value >= 0)
{
filter.Size = length.Value;
}
where.AddFilter(filter);
return(where);
}
/// <summary>
/// Creates a T-SQL WHERE NOT IN filter comparing a table column to a list of matching values.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <param name="fieldName">The field name to compare against the list</param>
/// <param name="values">A list of values to compare</param>
/// <param name="useLiteralNumbers">When true, numeric types will rendered as literals intead of as parameters.</param>
/// <returns></returns>
public static IDbQueryWhereClause FieldNotIn <T>(this IDbQueryWhereClause where, string fieldName, IEnumerable <T> values, bool useLiteralNumbers = false)
{
where.AddFilter(new Csg.Data.Sql.SqlListFilter <T>(where.Root, fieldName, values)
{
NotInList = true, UseLiteralNumbers = useLiteralNumbers
});
return(where);
}
/// <summary>
/// Adds a (SELECT COUNT({countFieldName}) FROM {sqlText} WHERE {subQueryConditions}) {subQueryOperator} {countValue} condition to the query;
/// </summary>
/// <param name="where"></param>
/// <param name="sqlText"></param>
/// <param name="countColumnName"></param>
/// <param name="operator"></param>
/// <param name="countValue"></param>
/// <param name="subQueryWhere"></param>
/// <returns></returns>
public static IDbQueryWhereClause SubQueryCount(this IDbQueryWhereClause where, string sqlText, string countColumnName, SqlOperator @operator, int countValue, Action <IDbQueryWhereClause> subQueryWhere)
{
var sqf = new SqlCountFilter(where.Root, SqlTable.Create(sqlText), countColumnName, @operator, countValue);
var sqfWhere = new DbQueryWhereClause(sqf.SubQueryTable, SqlLogic.And);
subQueryWhere(sqfWhere);
sqfWhere.ApplyTo(sqf.SubQueryFilters);
where.AddFilter(sqf);
return(where);
}
public static void PhoneNumber(IDbQueryWhereClause where, ListFilter filter, ListFieldMetadata config)
{
var sqf = new Csg.Data.Sql.SqlSubQueryFilter(where.Root, "dbo.PersonPhoneNumber");
sqf.ColumnName = "PersonID";
sqf.SubQueryColumn = "PersonID";
sqf.SubQueryFilters.Add(new Csg.Data.Sql.SqlStringMatchFilter(sqf.SubQueryTable, "PhoneNumber", Csg.Data.Sql.SqlWildcardDecoration.Contains, filter.Value.ToString()));
sqf.SubQueryFilters.Add(new Csg.Data.Sql.SqlColumnCompareFilter(sqf.SubQueryTable, "PersonID", Csg.Data.Sql.SqlOperator.Equal, sqf.Table));
where.AddFilter(sqf);
}
/// <summary>
/// Adds an EXISTS(selectStatement) filter critera.
/// </summary>
/// <param name="where"></param>
/// <param name="innerQuery">The value to render as the inner SELECT statement</param>
/// <returns></returns>
public static IDbQueryWhereClause Exists(this IDbQueryWhereClause where, string sqlText, Action <IDbQueryWhereClause> subQueryFilters)
{
var innerTable = SqlTable.Create(sqlText);
var innerQuery = new SqlSelectBuilder(innerTable);
var innerWhere = new DbQueryWhereClause(innerTable, SqlLogic.And);
innerQuery.Columns.Add(new SqlRawColumn("1"));
subQueryFilters(innerWhere);
innerWhere.ApplyTo(innerQuery.Filters);
where.AddFilter(new Sql.SqlExistFilter(innerQuery));
return(where);
}
/// <summary>
/// Creates a WHERE clause comparison for a field and value in the form ([fieldName] [operator] [value])
/// </summary>
/// <typeparam name="TValue"></typeparam>
/// <param name="where"></param>
/// <param name="fieldName">The name of the field to use as the expression on the left of the operator.</param>
/// <param name="operator">The comparison operator to use.</param>
/// <param name="value">The value of the parameter created for the right side of the operator.</param>
/// <param name="dbType">The data type of the database field.</param>
/// <param name="size">The size of the database field (for fixed length data types).</param>
/// <returns></returns>
public static IDbQueryWhereClause FieldMatch <TValue>(this IDbQueryWhereClause where, string fieldName, Csg.Data.Sql.SqlOperator @operator, TValue value, DbType?dbType = null, int?size = null)
{
if (value is IDbTypeProvider)
{
where.AddFilter(new Csg.Data.Sql.SqlCompareFilter(where.Root, fieldName, @operator, ((IDbTypeProvider)value).GetDbType(), value));
}
else
{
where.AddFilter(new Csg.Data.Sql.SqlCompareFilter <TValue>(where.Root, fieldName, @operator, value)
{
DataType = dbType.HasValue ? dbType.Value : DbConvert.TypeToDbType(typeof(TValue)),
Size = size,
});
}
return(where);
}
/// <summary>
/// Adds a {columnName} IN | NOT IN (SELECT {subQueryColumnName} FROM {sqlText} WHERE {SubQueryConditions})
/// </summary>
/// <param name="where"></param>
/// <param name="columnName"></param>
/// <param name="sqlText"></param>
/// <param name="subQueryColumnName"></param>
/// <param name="condition"></param>
/// <param name="subQueryFilters"></param>
/// <returns></returns>
internal static IDbQueryWhereClause FieldSubQuery(IDbQueryWhereClause where, string columnName, string sqlText, string subQueryColumnName, SubQueryMode condition, Action <IDbQueryWhereClause> subQueryFilters)
{
var subQueryFilter = new Csg.Data.Sql.SqlSubQueryFilter(where.Root, SqlTable.Create(sqlText))
{
ColumnName = columnName,
Condition = condition,
SubQueryColumn = subQueryColumnName,
};
var subQueryWhere = new DbQueryWhereClause(subQueryFilter.SubQueryTable, SqlLogic.And);
subQueryFilters(subQueryWhere);
subQueryWhere.ApplyTo(subQueryFilter.SubQueryFilters);
where.AddFilter(subQueryFilter);
return(where);
}
// Where -------------------------------------
private string GetSqlText_WhereClause(
IDbQueryWhereClause clause,
IDbSingleQuery query = null,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
string queryString = "";
if (clause != null)
{
if (clause?.Expression != null)
{
string expression = Scope?.Interpreter.Evaluate(clause.Expression, scriptVariableSet, log)?.ToString() ?? "";
queryString += expression;
}
if (clause.IdFields?.Count > 0)
{
queryString = queryString.If(!string.IsNullOrEmpty(queryString), " (" + queryString + ") ");
foreach (DbField field in clause.IdFields)
{
if (!string.IsNullOrEmpty(queryString))
{
queryString += " and ";
}
queryString += GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.NameEqualsValue,
scriptVariableSet: scriptVariableSet, log: log);
}
}
queryString = queryString.If(!string.IsNullOrEmpty(queryString), " where " + queryString);
}
return(queryString);
}
/// <summary>
/// Adds a condition to the given query's where clause using the given values.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="fieldName"></param>
/// <param name="operator"></param>
/// <param name="date"></param>
/// <returns></returns>
public static IDbQueryWhereClause FieldMatch <T>(this IDbQueryWhereClause query, string fieldName, SqlOperator @operator, DbDate <T> date) where T : struct
{
query.AddFilter(new Csg.Data.Sql.SqlCompareFilter(query.Root, fieldName, @operator, date.GetDbType(), date.Value));
return(query);
}
/// <summary>
/// Creates a WHERE clause equality comparison for a field and value in the form ([fieldName] = [equalsValue])
/// </summary>
/// <typeparam name="TValue">The data type of the right operand</typeparam>
/// <param name="where">The query builder instance</param>
/// <param name="fieldName">The name of the field to use as the expression on the left of the operator.</param>
/// <param name="equalsValue">The value of the parameter created for the right side of the operator.</param>
/// <param name="dbType">The data type of the database field.</param>
/// <param name="size">The size of the database field (for fixed length data types).</param>
/// <returns></returns>
public static IDbQueryWhereClause FieldEquals <TValue>(this IDbQueryWhereClause where, string fieldName, TValue equalsValue, DbType?dbType = null, int?size = null)
{
return(where.FieldMatch <TValue>(fieldName, SqlOperator.Equal, equalsValue, dbType: dbType, size: size));
}
/// <summary>
/// Adds a range condition to the given query's where clause.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="fieldName"></param>
/// <param name="begin"></param>
/// <param name="end"></param>
/// <returns></returns>
public static IDbQueryWhereClause FieldBetween <T>(this IDbQueryWhereClause query, string fieldName, DbDate <T> begin, DbDate <T> end) where T : struct
{
query.FieldMatch(fieldName, SqlOperator.GreaterThanOrEqual, begin).FieldMatch(fieldName, SqlOperator.LessThanOrEqual, end);
return(query);
}
/// <summary>
/// Creates a WHERE clause equality comparison for a field and value in the form ([fieldName] = [value]), where the value data type is <see cref="String"/>.
/// </summary>
/// <param name="where">The query builder instance</param>
/// <param name="fieldName">The name of the field to use as the expression on the left of the operator.</param>
/// <param name="equalsValue">The value of the parameter created for the right side of the operator.</param>
/// <param name="isAnsi">Is the database field an ANSI string or Unicode string?</param>
/// <param name="length">Is the database field fixed length or variable length?</param>
/// <returns></returns>
public static IDbQueryWhereClause FieldEquals(this IDbQueryWhereClause where, string fieldName, string equalsValue, bool isAnsi = false, int?length = null)
{
return(where.FieldMatch(fieldName, SqlOperator.Equal, equalsValue, isAnsi, length));
}
/// <summary>
/// Adds an equals condition to the given query's where clause for the given field and string value.
/// </summary>
/// <param name="query"></param>
/// <param name="fieldName"></param>
/// <param name="equalsValue"></param>
/// <returns></returns>
public static IDbQueryWhereClause FieldEquals(this IDbQueryWhereClause query, string fieldName, DbString equalsValue)
{
return(query.FieldMatch(fieldName, SqlOperator.Equal, equalsValue));
}
/// <summary>
/// Adds a {columnName} NOT IN (SELECT {subQueryColumnName} FROM {sqlText} WHERE {SubQueryConditions})
/// </summary>
/// <param name="where"></param>
/// <param name="columnName">The column name on the table the condition is being added to.</param>
/// <param name="sqlText">SQL text that defines the sub query.</param>
/// <param name="subQueryColumnName">The column to select from the sub query and match against <paramref name="columnName"/></param>
/// <param name="subQueryFilters">A set of filters to narrow the sub-query</param>
/// <returns></returns>
public static IDbQueryWhereClause FieldNotInSubQuery(this IDbQueryWhereClause where, string columnName, string sqlText, string subQueryColumnName, Action <IDbQueryWhereClause> subQueryFilters)
{
return(FieldSubQuery(where, columnName, sqlText, subQueryColumnName, SubQueryMode.NotInList, subQueryFilters));
}
/// <summary>
/// Creates a WHERE clause equality comparison for a field not equal to NULL ([fieldName] IS NOT NULL)
/// </summary>
/// <param name="where">The query builder instance</param>
/// <param name="fieldName">The name of the field to use as the expression on the left of the operator.</param>
/// <returns></returns>
public static IDbQueryWhereClause FieldIsNotNull(this IDbQueryWhereClause where, string fieldName)
{
where.AddFilter(new SqlNullFilter(where.Root, fieldName, false));
return(where);
}
/// <summary>
/// Adds an EXISTS(selectStatement) filter critera.
/// </summary>
/// <param name="where"></param>
/// <param name="innerQuery">The value to render as the inner SELECT statement</param>
/// <returns></returns>
public static IDbQueryWhereClause Exists(this IDbQueryWhereClause where, SqlSelectBuilder innerQuery)
{
where.AddFilter(new Csg.Data.Sql.SqlExistFilter(innerQuery));
return(where);
}
/// <summary>
/// Creates a WHERE clause comparison for a field and value in the form ([fieldName] [operator] [value]), where the value data type is <see cref="DateTimeOffset"/>.
/// </summary>
/// <param name="where">The query builder instance</param>
/// <param name="fieldName">The name of the field to use as the expression on the left of the operator.</param>
/// <param name="operator">The comparison operator to use.</param>
/// <param name="value">The value of the parameter created for the right side of the operator.</param>
/// <returns></returns>
public static IDbQueryWhereClause FieldMatch(this IDbQueryWhereClause where, string fieldName, SqlOperator @operator, DateTimeOffset value)
{
where.AddFilter(new Csg.Data.Sql.SqlCompareFilter(where.Root, fieldName, @operator, DbType.DateTimeOffset, value));
return(where);
}
/// <summary>
/// Creates a WHERE clause comparison for a field and value in the form ([fieldName] >= [begin] AND [fieldName] <= [end]), where the value data type is <see cref="DateTime"/>.
/// </summary>
/// <param name="where">The query builder instance</param>
/// <param name="fieldName">The name of the field to use as the expression on the left of the operator.</param>
/// <param name="begin">The begin value of the range.</param>
/// <param name="end">The end value of the range.</param>
/// <param name="dbType">The data type of the database field.</param>
/// <param name="size">The size of the database field (for fixed length data types).</param>
/// <returns></returns>
public static IDbQueryWhereClause FieldBetween <TValue>(this IDbQueryWhereClause where, string fieldName, TValue begin, TValue end, DbType?dbType = null, int?size = null)
{
return(where
.FieldMatch(fieldName, SqlOperator.GreaterThanOrEqual, begin, dbType: dbType, size: size)
.FieldMatch(fieldName, SqlOperator.LessThanOrEqual, end, dbType: dbType, size: size));
}
/// <summary>
/// Creates a WHERE clause comparison for a field and value in the form ([fieldName] >= [begin] AND [fieldName] <= [end]), where the value data type is <see cref="DateTimeOffset"/>.
/// </summary>
/// <param name="where">The query builder instance</param>
/// <param name="fieldName">The name of the field to use as the expression on the left of the operator.</param>
/// <param name="begin">The begin date/time of the range.</param>
/// <param name="end">The end date/time of the range.</param>
/// <returns></returns>
public static IDbQueryWhereClause FieldBetween(this IDbQueryWhereClause where, string fieldName, DateTimeOffset begin, DateTimeOffset end)
{
return(where
.FieldMatch(fieldName, SqlOperator.GreaterThanOrEqual, begin)
.FieldMatch(fieldName, SqlOperator.LessThanOrEqual, end));
}