private void appendFilterAtom2WhereSb(FltAtomExprData filterAtom, string preffixToAppend, string collName) { if (filterAtom == null) { return; } var fltValue = filterAtom.Value; var isCustomValue = false; var pField = filterAtom.Field;// StringUtils.FirstCharacterToLower(filterAtom.Field); if (filterAtom.Operator == FilterFieldOperator.Between) { var filterBigger = filterAtom.Clone() as FltAtomExprData; filterBigger.Operator = FilterFieldOperator.GreaterEqualThan; filterBigger.Value = filterBigger.Value.BeforeString(";"); var filterSmaller = filterAtom.Clone() as FltAtomExprData; filterSmaller.Operator = FilterFieldOperator.LessEqualThan; filterSmaller.Value = filterSmaller.Value.AfterString(";"); var group = Filter.Filter.And(filterBigger, filterSmaller); filterExprAppendWhereAndParams(group, preffixToAppend, collName); return; } if (filterAtom.Operator == FilterFieldOperator.In && (filterAtom.Values == null || filterAtom.Values.Count == 0) && filterAtom.Value.IsEmpty()) { filterAtom = Filter.Filter.AlwaysFalse(); } if (filterAtom.Field == "1") { _whereStringBuilder.Append(preffixToAppend); _whereStringBuilder.Append(" (1=" + filterAtom.Value + ")"); return; } if (pField == null) { filterAtom.Field = ""; filterAtom.Operator = FilterFieldOperator.IsNull; } _whereStringBuilder.Append(preffixToAppend); _whereStringBuilder.Append("("); var fld = pField; if (filterAtom.Operator != FilterFieldOperator.Contains && filterAtom.Operator != FilterFieldOperator.StartsWith && filterAtom.Operator != FilterFieldOperator.EndsWith) { if (collName.IsNotEmpty()) { _whereStringBuilder.Append(collName); _whereStringBuilder.Append("."); } _whereStringBuilder.Append(fld); } else { // fld = $"{collName}.{fld}"; if (collName.IsNotEmpty()) { _whereStringBuilder.Append(collName); _whereStringBuilder.Append("."); } _whereStringBuilder.Append(fld); } _whereStringBuilder.Append(" "); switch (filterAtom.Operator) { case FilterFieldOperator.Equal: _whereStringBuilder.Append("="); break; case FilterFieldOperator.GreaterThan: _whereStringBuilder.Append(">"); break; case FilterFieldOperator.GreaterEqualThan: _whereStringBuilder.Append(">="); break; case FilterFieldOperator.In: _whereStringBuilder.Append("IN"); break; case FilterFieldOperator.LessThan: _whereStringBuilder.Append("<"); break; case FilterFieldOperator.LessEqualThan: _whereStringBuilder.Append("<="); break; case FilterFieldOperator.Like: case FilterFieldOperator.Contains: case FilterFieldOperator.StartsWith: case FilterFieldOperator.EndsWith: _whereStringBuilder.Append("LIKE"); break; case FilterFieldOperator.NotEqual: _whereStringBuilder.Append("<>"); break; case FilterFieldOperator.IsNull: _whereStringBuilder.Append("IS NULL"); break; case FilterFieldOperator.IsNotNull: _whereStringBuilder.Append("IS NOT NULL"); break; } _whereStringBuilder.Append(" "); if (filterAtom.Operator == FilterFieldOperator.In || filterAtom.Operator == FilterFieldOperator.Like || filterAtom.Operator == FilterFieldOperator.Contains || filterAtom.Operator == FilterFieldOperator.StartsWith || filterAtom.Operator == FilterFieldOperator.EndsWith) { _whereStringBuilder.Append("("); } if (filterAtom.Operator == FilterFieldOperator.In) { var valuesList = new List <string>(); valuesList = filterAtom.Values ?? filterAtom.Value.SplitCharList(';'); for (var i = 0; i < valuesList.Count; i++) { if (i > 0) { _whereStringBuilder.Append(","); } _whereStringBuilder.Append("@"); _whereStringBuilder.Append(FieldName2ParamName(pField + i, filterAtom)); Parameters.Add(FieldName2ParamName(pField + i, filterAtom), valuesList[i]); } } else if (filterAtom.Operator != FilterFieldOperator.IsNull && filterAtom.Operator != FilterFieldOperator.IsNotNull) { if (!isCustomValue) { _whereStringBuilder.Append("@"); _whereStringBuilder.Append(FieldName2ParamName(pField, filterAtom)); object value = fltValue; var column = _table.AvailableColumns.FirstOrDefault(c => c.Name == fld); var fieldType = column?.Type ?? PropertyType.String; //if date type field, and less than operator, and minutes and hours is 00:00, add one day, so that the day which less than should be, would be included if (fieldType == PropertyType.DateTime) { DateTime tmpDt = DateTime.MinValue; bool parsed = false; if (DateTime.TryParse(fltValue, CultureInfo.CurrentCulture, DateTimeStyles.None, out tmpDt)) { parsed = true; } else if (DateTime.TryParse(fltValue, out tmpDt)) { parsed = true; } else { string[] formats = { "M/d/yyyy h:mm:ss tt", "M/d/yyyy h:mm tt", "MM/dd/yyyy hh:mm:ss", "M/d/yyyy h:mm:ss", "M/d/yyyy hh:mm tt", "M/d/yyyy hh tt", "M/d/yyyy h:mm", "M/d/yyyy h:mm", "MM/dd/yyyy hh:mm", "M/dd/yyyy hh:mm", "dd.MM.yyyy" }; if (DateTime.TryParseExact(fltValue, formats, new CultureInfo("en-US"), DateTimeStyles.None, out tmpDt)) { parsed = true; } } if (parsed) { if (filterAtom.Operator == FilterFieldOperator.LessEqualThan) { if (tmpDt.Hour == 0 && tmpDt.Minute == 0) { tmpDt = tmpDt.AddDays(1); } } value = tmpDt.ToString("s"); } } if (filterAtom.Operator == FilterFieldOperator.Contains) { value = $"%{value}%"; } else if (filterAtom.Operator == FilterFieldOperator.StartsWith) { value = $"{value}%"; } else if (filterAtom.Operator == FilterFieldOperator.EndsWith) { value = $"%{value}"; } Parameters[FieldName2ParamName(pField, filterAtom)] = value; } else { fltValue = fltValue.Replace("'", "''"); _whereStringBuilder.Append(fltValue); } } if (filterAtom.Operator == FilterFieldOperator.In || filterAtom.Operator == FilterFieldOperator.Like || filterAtom.Operator == FilterFieldOperator.Contains || filterAtom.Operator == FilterFieldOperator.StartsWith || filterAtom.Operator == FilterFieldOperator.EndsWith) { _whereStringBuilder.Append(")"); } _whereStringBuilder.Append(")"); }
private void appendFilterAtom2WhereSb(FltAtomExprData filterAtom, string preffixToAppend, string collName) { if (filterAtom == null) { return; } var fltValue = filterAtom.Value; var isCustomValue = false; var pField = filterAtom.Field;// StringUtils.FirstCharacterToLower(filterAtom.Field); if (filterAtom.Operator == FilterFieldOperator.Between) { var filterBigger = filterAtom.Clone() as FltAtomExprData; filterBigger.Operator = FilterFieldOperator.GreaterEqualThan; filterBigger.Value = filterBigger.Value.BeforeString(";"); var filterSmaller = filterAtom.Clone() as FltAtomExprData; filterSmaller.Operator = FilterFieldOperator.LessEqualThan; filterSmaller.Value = filterSmaller.Value.AfterString(";"); var group = Filter.Filter.And(filterBigger, filterSmaller); filterExprAppendWhereAndParams(group, preffixToAppend, collName); return; } if (filterAtom.Operator == FilterFieldOperator.In && (filterAtom.Values == null || filterAtom.Values.Count == 0) && filterAtom.Value.IsEmpty()) { filterAtom = Filter.Filter.AlwaysFalse(); } if (filterAtom.Field == "1") { _whereStringBuilder.Append(preffixToAppend); _whereStringBuilder.Append(" (1=" + filterAtom.Value + ")"); return; } if (pField == null) { filterAtom.Field = ""; filterAtom.Operator = FilterFieldOperator.IsNull; } _whereStringBuilder.Append(preffixToAppend); _whereStringBuilder.Append("("); var fld = pField; if (filterAtom.Operator != FilterFieldOperator.Contains && filterAtom.Operator != FilterFieldOperator.StartsWith && filterAtom.Operator != FilterFieldOperator.EndsWith) { if (collName.IsNotEmpty()) { _whereStringBuilder.Append(collName); _whereStringBuilder.Append("."); } _whereStringBuilder.Append(fld); } else { fld = $"{collName}.{fld}"; } _whereStringBuilder.Append(" "); switch (filterAtom.Operator) { case FilterFieldOperator.Equal: _whereStringBuilder.Append("="); break; case FilterFieldOperator.GreaterThan: _whereStringBuilder.Append(">"); break; case FilterFieldOperator.GreaterEqualThan: _whereStringBuilder.Append(">="); break; case FilterFieldOperator.In: _whereStringBuilder.Append("IN"); break; case FilterFieldOperator.LessThan: _whereStringBuilder.Append("<"); break; case FilterFieldOperator.LessEqualThan: _whereStringBuilder.Append("<="); break; //In document DB it's different //case FilterFieldOperator.Like: //case FilterFieldOperator.Contains: //case FilterFieldOperator.StartsWith: //case FilterFieldOperator.EndsWith: // _whereStringBuilder.Append("LIKE"); break; case FilterFieldOperator.NotEqual: _whereStringBuilder.Append("<>"); break; case FilterFieldOperator.IsNull: _whereStringBuilder.Append("IS NULL"); break; case FilterFieldOperator.IsNotNull: _whereStringBuilder.Append("IS NOT NULL"); break; } _whereStringBuilder.Append(" "); if (filterAtom.Operator == FilterFieldOperator.In) { _whereStringBuilder.Append("("); } if (filterAtom.Operator == FilterFieldOperator.In) { List <string> valuesList = new List <string>(); if (filterAtom.Values != null) { valuesList = filterAtom.Values; } else { valuesList = filterAtom.Value.SplitCharList(';'); } for (int i = 0; i < valuesList.Count; i++) { if (i > 0) { _whereStringBuilder.Append(","); } _whereStringBuilder.Append("@"); _whereStringBuilder.Append(FieldName2ParamName(pField + i, filterAtom)); Parameters.Add(FieldName2ParamName(pField + i, filterAtom), valuesList[i]); } } else if (filterAtom.Operator == FilterFieldOperator.StartsWith || filterAtom.Operator == FilterFieldOperator.EndsWith || filterAtom.Operator == FilterFieldOperator.Contains) { var func = ""; if (filterAtom.Operator == FilterFieldOperator.StartsWith) { func = "STARTSWITH"; } else if (filterAtom.Operator == FilterFieldOperator.EndsWith) { func = "ENDSWITH"; } else if (filterAtom.Operator == FilterFieldOperator.Contains) { func = "CONTAINS"; } _whereStringBuilder.Append($" {func}({fld},@{FieldName2ParamName(pField, filterAtom)}) "); Parameters[FieldName2ParamName(pField, filterAtom)] = fltValue; } else if (filterAtom.Operator != FilterFieldOperator.IsNull && filterAtom.Operator != FilterFieldOperator.IsNotNull) { if (!isCustomValue) { _whereStringBuilder.Append("@"); _whereStringBuilder.Append(FieldName2ParamName(pField, filterAtom)); object value = fltValue; if (filterAtom.PropertyType == PropertyType.Integer) { var isNumberTry = fltValue.ToInt(int.MaxValue); if (isNumberTry != int.MaxValue) { value = isNumberTry; } } else if (filterAtom.PropertyType == PropertyType.Float) { var isNumberTry = fltValue.ToDouble(double.NaN); if (isNumberTry != double.NaN) { value = isNumberTry; } } Parameters[FieldName2ParamName(pField, filterAtom)] = value; } else { fltValue = fltValue.Replace("'", "''"); _whereStringBuilder.Append(fltValue); } } if (filterAtom.Operator == FilterFieldOperator.In) { _whereStringBuilder.Append(")"); } _whereStringBuilder.Append(")"); }