/// <summary> /// Decorates a string value with wildcard characters based on the <see cref="SqlWildcardDecoration"/> provided. /// </summary> /// <param name="value">A string value.</param> /// <param name="decoration">Specifies the type of decoration to use.</param> /// <returns></returns> public static string DecorateValue(string value, SqlWildcardDecoration decoration) { if (value.Contains('*') || value.Contains('%')) { return(value.Replace("*", "%")); } if (decoration == SqlWildcardDecoration.BeginsWith) { return(string.Concat(value, "%")); } else if (decoration == SqlWildcardDecoration.Contains) { return(string.Concat("%", value, "%")); } else if (decoration == SqlWildcardDecoration.EndsWith) { return(string.Concat("%", value)); } else { return(value); } }
/// <summary> /// Adds the given filter to a query builder where clause. /// </summary> /// <param name="where"></param> /// <param name="filter"></param> /// <param name="valueType"></param> /// <param name="valueTypeSize"></param> /// <param name="stringMatchType"></param> /// <param name="performDataTypeConversion"></param> /// <param name="valueConverter"></param> /// <returns></returns> public static IDbQueryWhereClause AddFilter(this Csg.Data.IDbQueryWhereClause where, ListFilter filter, System.Data.DbType valueType, int?valueTypeSize = null, SqlWildcardDecoration stringMatchType = SqlWildcardDecoration.BeginsWith, bool performDataTypeConversion = true, Func <object, object> valueConverter = null) { Csg.ListQuery.Sql.Internal.Extensions.AddFilter(where, filter.Name, filter.Operator ?? ListFilterOperator.Equal, filter.Value, valueType: valueType, valueTypeSize: valueTypeSize, stringMatchType: stringMatchType, performDataTypeConversion: performDataTypeConversion, valueConverter: valueConverter); return(where); }
/// <summary> /// Initializes a new instance. /// </summary> /// <param name="table">The table source for the column.</param> /// <param name="columnName">The database column to compare.</param> /// <param name="oper">The wildcard decoration type to use.</param> /// <param name="value">The value compare the column with.</param> public SqlStringMatchFilter(ISqlTable table, string columnName, SqlWildcardDecoration oper, string value) : base(table, columnName, System.Data.DbType.String) { this.Operator = oper; this.Value = value; }
/// <summary> /// Creates a filter using the given generic filter operator /// </summary> /// <param name="where"></param> /// <param name="columnName"></param> /// <param name="operator"></param> /// <param name="value"></param> /// <param name="valueType"></param> /// <param name="valueTypeSize"></param> /// <param name="stringMatchType"></param> /// <param name="performDataTypeConversion"></param> /// <param name="valueConverter"></param> public static void AddFilter(this Csg.Data.IDbQueryWhereClause where, string columnName, ListFilterOperator @operator, object value, System.Data.DbType valueType, int?valueTypeSize = null, SqlWildcardDecoration stringMatchType = SqlWildcardDecoration.BeginsWith, bool performDataTypeConversion = true, Func <object, object> valueConverter = null) { var root = where.Root; IEnumerable <object> values = ValueHelpers.GetFilterValues(value, valueType, shouldPerformDataTypeConversion: performDataTypeConversion, valueConverter: valueConverter); bool isMutliValued = values != null && values.Count() > 1; var firstValue = values.Count() > 0 ? values.First() : null; var hasValues = values != null && values.Count() > 0; var dataType = valueType; int? dataTypeSize = valueTypeSize; if (!hasValues) { return; } //handle LIKE or EQUALS filtering against nchar and char data types if (dataType == DbType.AnsiStringFixedLength) { dataType = DbType.AnsiString; dataTypeSize = null; } else if (dataType == DbType.StringFixedLength) { dataType = DbType.String; dataTypeSize = null; } if (@operator == ListFilterOperator.Like) { if (isMutliValued) { var filterCollection = new Csg.Data.Sql.SqlFilterCollection(); filterCollection.Logic = Csg.Data.Sql.SqlLogic.Or; foreach (var filterValue in values) { filterCollection.Add(new Csg.Data.Sql.SqlStringMatchFilter(root, columnName, stringMatchType, filterValue.ToString()) { DataType = dataType, Size = dataTypeSize }); } where.AddFilter(filterCollection); } else if (firstValue == null) { where.AddFilter(new Csg.Data.Sql.SqlNullFilter(root, columnName, true)); } else { where.AddFilter(new Csg.Data.Sql.SqlStringMatchFilter(root, columnName, stringMatchType, firstValue.ToString()) { DataType = dataType, Size = dataTypeSize }); } } else if (@operator == ListFilterOperator.Between) { if (!isMutliValued || values.Count() != 2) { throw new Exception(string.Format("Error while processing the filter values for column '{0}': A between filter must have exactly two values.", columnName)); } var valueArray = values.ToArray(); if (valueArray[0] != null) { where.AddFilter(new Csg.Data.Sql.SqlCompareFilter(root, columnName, Csg.Data.Sql.SqlOperator.GreaterThanOrEqual, dataType, valueArray[0]) { Size = dataTypeSize }); } if (valueArray[1] != null) { where.AddFilter(new Csg.Data.Sql.SqlCompareFilter(root, columnName, Csg.Data.Sql.SqlOperator.LessThanOrEqual, dataType, valueArray[1]) { Size = dataTypeSize }); } } else if (@operator == ListFilterOperator.IsNull) { var filterCollection = new Csg.Data.Sql.SqlFilterCollection(); filterCollection.Logic = Csg.Data.Sql.SqlLogic.Or; foreach (var filterValue in values) { filterCollection.Add(new Csg.Data.Sql.SqlNullFilter(root, columnName, Convert.ToBoolean(filterValue)) { DataType = dataType }); } where.AddFilter(filterCollection); } else { var nativeOperator = @operator.ToSqlOperator(); // Handle filtering on a list for exact matches if (isMutliValued && nativeOperator == Csg.Data.Sql.SqlOperator.Equal) { where.AddFilter(new Csg.Data.Sql.SqlListFilter(root, columnName, dataType, values) { Size = dataTypeSize }); } // or not-in else if (isMutliValued && nativeOperator == Csg.Data.Sql.SqlOperator.NotEqual) { where.AddFilter(new Csg.Data.Sql.SqlListFilter(root, columnName, dataType, values) { Size = dataTypeSize, NotInList = true }); } // handle filtering on a list for other operators (rare case) else if (isMutliValued) { var filterCollection = new Csg.Data.Sql.SqlFilterCollection(); filterCollection.Logic = Csg.Data.Sql.SqlLogic.Or; foreach (var filterValue in values) { filterCollection.Add(root, columnName, nativeOperator, dataType, filterValue); } where.AddFilter(filterCollection); } // handle filtering on NULL else if (firstValue == null) { //TODO: Should we handle filtering on null this way, or add an explicit IsNull on the filter object? where.AddFilter(new Csg.Data.Sql.SqlNullFilter(root, columnName, @operator == ListFilterOperator.Equal ? true : false)); } // handle filtering on exactly one value else { where.AddFilter(new Csg.Data.Sql.SqlCompareFilter(root, columnName, nativeOperator, dataType, firstValue) { Size = dataTypeSize }); } } }