private Statement GetDateTimeFilterClause(ColumnFilter filter) { var clauseParameters = new List <DBParameterKeyValue>(); var clauseText = string.Empty; for (var i = 0; i < filter.Values.Count(); i++) { var currentValue = filter.Values.ElementAt(i); DateTime parsedValue; if (DateTime.TryParseExact(currentValue, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedValue) || DateTime.TryParseExact(currentValue, @"yyyy-MM-dd\THH:mm:ss\Z", CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedValue) || DateTime.TryParseExact(currentValue, "yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedValue)) { // Current format sample: '2019-05-14 14:44:39' currentValue = $"{parsedValue.Year.ToString("D4")}-{parsedValue.Month.ToString("D2")}-{parsedValue.Day.ToString("D2")}"; currentValue += $" {parsedValue.Hour.ToString("D2")}:{parsedValue.Minute.ToString("D2")}:{parsedValue.Second.ToString("D2")}"; } if (i != 0) { clauseText += filter.FilterMethod == FilterMethod.Including ? " OR " : " AND "; } if (filter.FilterRigor == FilterRigor.Contains) { clauseText += $" CONVERT(varchar(19), {filter.Column.Name.SanitizeSQL()}, 20)"; if (filter.FilterMethod == FilterMethod.Excluding) { clauseText += " NOT"; } var filterValueParameter = new DBParameterKeyValue(GetParameterName("DateTimeFilterValue"), currentValue); clauseParameters.Add(filterValueParameter); clauseText += $" LIKE '%' + {filterValueParameter.Key} + '%'"; } else if (filter.FilterRigor == FilterRigor.Equals) { clauseText += $" CONVERT(DATETIME, CONVERT(VARCHAR(19), {filter.Column.Name.SanitizeSQL()}, 20)) "; if (filter.FilterMethod == FilterMethod.Excluding) { clauseText += "!"; } var filterValueParameter = new DBParameterKeyValue(GetParameterName("DateTimeFilterValue"), currentValue); clauseParameters.Add(filterValueParameter); clauseText += $"= {filterValueParameter.Key}"; } } return(new Statement(clauseText, clauseParameters)); }
private Statement GetNumberFilterClause(ColumnFilter filter) { var clauseParameters = new List <DBParameterKeyValue>(); var clauseText = string.Empty; var sanitizedColumnName = filter.Column.Name.SanitizeSQL(); for (var i = 0; i < filter.Values.Count(); i++) { var currentValue = filter.Values.ElementAt(i); if (i != 0) { clauseText += filter.FilterMethod == FilterMethod.Including ? " OR " : " AND "; } if (filter.FilterRigor == FilterRigor.Contains) { clauseText += $" CONVERT(varchar(64), {sanitizedColumnName})"; if (filter.FilterMethod == FilterMethod.Excluding) { clauseText += " NOT"; } var filterValueParameter = new DBParameterKeyValue(GetParameterName("FilterNumberValue"), currentValue); clauseParameters.Add(filterValueParameter); clauseText += $" LIKE '%' + {filterValueParameter.Key} + '%'"; } else if (filter.FilterRigor == FilterRigor.Equals) { clauseText += $" {sanitizedColumnName} "; if (filter.FilterMethod == FilterMethod.Excluding) { clauseText += "!"; } var filterValueParameter = new DBParameterKeyValue(GetParameterName("FilterNumberValue"), currentValue); clauseParameters.Add(filterValueParameter); clauseText += $"= {filterValueParameter.Key}"; } } return(new Statement(clauseText, clauseParameters)); }
private Statement getStringFilterClause(ColumnFilter filter) { var clauseParameters = new List <DBParameterKeyValue>(); var clauseText = $"( {filter.Column.Name.SanitizeSQL()} "; for (var i = 0; i < filter.Values.Count(); i++) { var currentValue = filter.Values.ElementAt(i); if (i != 0) { clauseText += filter.FilterMethod == FilterMethod.Including ? " OR" : " AND"; clauseText += $" {filter.Column.Name.SanitizeSQL()} "; } if (filter.FilterRigor == FilterRigor.Contains) { if (filter.FilterMethod == FilterMethod.Excluding) { clauseText += "NOT "; } var filterValueParameter = new DBParameterKeyValue(GetParameterName("StringFilterValue"), currentValue); clauseParameters.Add(filterValueParameter); clauseText += $"LIKE '%' + {filterValueParameter.Key} + '%'"; } else if (filter.FilterRigor == FilterRigor.Equals) { if (filter.FilterMethod == FilterMethod.Excluding) { clauseText += "!"; } var filterValueParameter = new DBParameterKeyValue(GetParameterName("StringFilterValue"), currentValue); clauseParameters.Add(filterValueParameter); clauseText += $"= {filterValueParameter.Key}"; } } clauseText += " )"; return(new Statement(clauseText, clauseParameters)); }