private string GetFilterText(FilterGroup group) { string sql = ""; bool isFirst = true; if (group.Filters != null && group.Filters.Count > 0) { foreach (var filter in group.Filters) { // catch empty filter groups if ((filter is FilterGroup && (((FilterGroup)filter).Filters == null || ((FilterGroup)filter).Filters.Count == 0)) == false) { if (isFirst == true) { isFirst = false; } else { sql += " " + FilterUtilities.GetSqlGroupOperator(group.Operatror); } if (filter is FilterGroup) { sql += "(" + GetFilterText((FilterGroup)filter) + ")"; } else if (filter is FilterComparison) { sql += GetFilterText((FilterComparison)filter); } else { throw new Exception(String.Format("Unknown filter: '{0}'", filter.GetType().Name)); } } } } return(sql); }
private string GetFilterText(FilterComparison filter) { if (filter == null || String.IsNullOrEmpty(filter.FieldName)) { return(""); } // catch null values if (filter.Value == null) { if (filter.Operator == FilterComparisonOperatorEnum.Equal || filter.Operator == FilterComparisonOperatorEnum.ExactlyEqual) { return(String.Format(" {0} IS NULL", Connection.EscapeObjectName(filter.FieldName))); } else if (filter.Operator == FilterComparisonOperatorEnum.NotEqual || filter.Operator == FilterComparisonOperatorEnum.ExactlyNotEqual) { return(String.Format(" {0} IS NOT NULL", Connection.EscapeObjectName(filter.FieldName))); } } FieldDefinition field = null; if (Entity != null && Entity.EntityType.IsAssignableFrom(typeof(Record))) { // an entity definition is available // try to find the filed field = Entity.FieldDefinitions.Where(f => f.DbColumnName == filter.FieldName).FirstOrDefault(); } if (field != null) { // handle the request for a known field in the current entity string text = ""; // create a SQL parameter DbParameter param = _FactoryCommand.CreateParameter(); // generate a unique parameter name from a GUID param.ParameterName = Guid.NewGuid().ToString().Replace('-', '_'); // check whether a different DbType is set if (field.DbType != null) { param.DbType = (DbType)field.DbType; } try { // get the type for the conversion (not nullable) Type notNullableType = Nullable.GetUnderlyingType(field.MemberType) ?? field.MemberType; // convert the value to the type of the member param.Value = Convert.ChangeType(filter.Value, notNullableType); } catch (Exception ex) { throw new Exception(String.Format( "Error while converting the value '{0}' into '{1}' while creating a filter for the field '{2}'.", filter.Value, field.MemberType.Name, filter.FieldName), ex); } // add the parameter to the local collection Parameters.Add(param); if (field.MemberType == typeof(string) && filter.Operator == FilterComparisonOperatorEnum.ExactlyEqual) { text = String.Format(" {0} = @{1} {2}", Connection.EscapeObjectName(filter.FieldName), param.ParameterName, GetCaseSensivityCollation()); } else if (field.MemberType == typeof(string) && filter.Operator == FilterComparisonOperatorEnum.ExactlyNotEqual) { text = String.Format(" {0} <> @{1} {2}", Connection.EscapeObjectName(filter.FieldName), param.ParameterName, GetCaseSensivityCollation()); } else { text = String.Format(" {0} {1} @{2}", Connection.EscapeObjectName(filter.FieldName), FilterUtilities.GetSqlComparisonOperator(filter.Operator), param.ParameterName); } return(text); } else { // handle the request for an unknow filed, an unknown entity type or an anonymous record data type string text = ""; // create a SQL parameter DbParameter param = _FactoryCommand.CreateParameter(); // generate a unique parameter name from a GUID param.ParameterName = Guid.NewGuid().ToString().Replace('-', '_'); param.Value = filter.Value == null ? DBNull.Value : filter.Value; // add the parameter to the local collection Parameters.Add(param); if (filter.Value is string && filter.Operator == FilterComparisonOperatorEnum.ExactlyEqual) { text = String.Format(" {0} = @{1} {2}", Connection.EscapeObjectName(filter.FieldName), param.ParameterName, GetCaseSensivityCollation()); } else if (filter.Value is string && filter.Operator == FilterComparisonOperatorEnum.ExactlyNotEqual) { text = String.Format(" {0} <> @{1} {2}", Connection.EscapeObjectName(filter.FieldName), param.ParameterName, GetCaseSensivityCollation()); } else { text = String.Format(" {0} {1} @{2}", Connection.EscapeObjectName(filter.FieldName), FilterUtilities.GetSqlComparisonOperator(filter.Operator), param.ParameterName); } return(text); } }