/// <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
});
}
}
}