Пример #1
0
        /// <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);
            }
        }
Пример #2
0
 /// <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);
 }
Пример #3
0
 /// <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;
 }
Пример #4
0
        /// <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
                    });
                }
            }
        }