コード例 #1
0
        private static (string, DynamicParameters) WhereString(string where, DynamicParameters parameters, string colName, FilterMetadata filterMeta)
        {
            bool valueIsEmpty   = filterMeta.value == null || (filterMeta.value.GetType() == typeof(string) && string.IsNullOrEmpty(filterMeta.value));
            bool valueToIsEmpty = filterMeta.valueTo == null || (filterMeta.valueTo.GetType() == typeof(string) && string.IsNullOrEmpty(filterMeta.valueTo));

            if (valueIsEmpty && filterMeta.matchMode != IN_RANGE)
            {
                return(where, parameters);
            }

            switch (filterMeta.matchMode)
            {
            case EQUALS:
                where = where + $" AND {colName} = :{colName}";
                parameters.Add(colName, filterMeta.value);
                break;

            case NOT_EQUAL:
                where = where + $" AND {colName} != :{colName}";
                parameters.Add(colName, filterMeta.value);
                break;

            case IN:
                var list = string.Empty;
                if (filterMeta.value.GetType().Name == "JArray")
                {
                    string[] arr = filterMeta.value.ToObject <string[]>();
                    list = string.Join(",", arr.Select(x => string.Format("'{0}'", x)));
                }
                else
                {
                    list = string.Join(",", filterMeta.value);
                }
                where = where + $" AND {colName} in ({list})";
                break;

            case CONTAINS:
                where = where + $" AND lower({colName}) LIKE lower(:{colName})";
                parameters.Add(colName, string.Format("%{0}%", filterMeta.value));
                break;

            case NOT_CONTAINS:
                where = where + $" AND lower({colName}) NOT LIKE lower(:{colName})";
                parameters.Add(colName, string.Format("%{0}%", filterMeta.value));
                break;

            case STARTS_WITH:
                where = where + $" AND lower({colName}) LIKE lower(:{colName})";
                parameters.Add(colName, string.Format("{0}%", filterMeta.value));
                break;

            case ENDS_WITH:
                where = where + $" AND lower({colName}) LIKE lower(:{colName})";
                parameters.Add(colName, string.Format("%{0}", filterMeta.value));
                break;

            case IS_EMPTY:
                where = where + $" AND {colName} IS NULL";
                break;

            case IS_NOT_EMPTY:
                where = where + $" AND {colName} IS NOT NULL";
                break;

            default:
                where = where + $" AND {colName} = :{colName}";
                parameters.Add(colName, filterMeta.value);
                break;
            }
            return(where, parameters);
        }
コード例 #2
0
        private static (string, DynamicParameters) WhereNumeric(string where, DynamicParameters parameters, string colName, FilterMetadata filterMeta)
        {
            bool valueIsEmpty   = filterMeta.value == null || (filterMeta.value.GetType() == typeof(string) && string.IsNullOrEmpty(filterMeta.value));
            bool valueToIsEmpty = filterMeta.valueTo == null || (filterMeta.valueTo.GetType() == typeof(string) && string.IsNullOrEmpty(filterMeta.valueTo));

            if (valueIsEmpty && filterMeta.matchMode != IN_RANGE)
            {
                return(where, parameters);
            }
            if (filterMeta.value.GetType() == typeof(string))
            {
                filterMeta.value = RestHelper.GetDouble(filterMeta.value);
            }

            switch (filterMeta.matchMode)
            {
            case EQUALS:
                where = where + $" AND {colName} = :{colName}";
                parameters.Add(colName, filterMeta.value);
                break;

            case NOT_EQUAL:
                where = where + $" AND {colName} != :{colName}";
                parameters.Add(colName, filterMeta.value);
                break;

            case IN:
                var list = string.Join(",", filterMeta.value);
                where = where + $" AND {colName} in ({list})";
                break;

            case IN_RANGE:
                if (valueIsEmpty)
                {
                    where = where + $" AND {colName} < :{colName}";
                    parameters.Add(colName, filterMeta.valueTo);
                }
                else if (valueToIsEmpty)
                {
                    where = where + $" AND {colName} > :{colName}";
                    parameters.Add(colName, filterMeta.value);
                }
                else
                {
                    where = where + $" AND {colName} > :{colName} AND {colName} < :{colName}TO";
                    parameters.Add(colName, filterMeta.value);
                    parameters.Add(colName + "TO", filterMeta.valueTo);
                }
                break;

            case LESS_THAN:
                where = where + $" AND {colName} < :{colName}";
                parameters.Add(colName, filterMeta.value);
                break;

            case LESS_THAN_OR_EQUAL:
                where = where + $" AND {colName} <= :{colName}";
                parameters.Add(colName, filterMeta.value);
                break;

            case GREATER_THAN:
                where = where + $" AND {colName} > :{colName}";
                parameters.Add(colName, filterMeta.value);
                break;

            case GREATER_THAN_OR_EQUAL:
                where = where + $" AND {colName} >= :{colName}";
                parameters.Add(colName, filterMeta.value);
                break;

            case IS_EMPTY:
                where = where + $" AND {colName} IS NULL";
                break;

            case IS_NOT_EMPTY:
                where = where + $" AND {colName} IS NOT NULL";
                break;

            default:
                where = where + $" AND {colName} = :{colName}";
                parameters.Add(colName, filterMeta.value);
                break;
            }
            return(where, parameters);
        }