dynamicCondition高级查询sql配置

最新推荐文章于 2024-06-29 12:12:59 发布

原创最新推荐文章于 2024-06-29 12:12:59 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #.net

该博客主要介绍了如何使用`DynamicConditionHelper`类来解析JSON数据并构建高级查询的SQL语句。通过遍历JSON对象，根据不同的条件选项转换为对应的SQL操作符，并结合字段名和值生成查询条件。支持的条件包括等于、不等于、like、大于、小于、空值检查等，以及范围查询。

@dynamicCondition高级查询sql配置

dynamicCondition高级查询sql配置

public class DynamicConditionHelper
    {
        public static string GetQuerySqlStrs(string jsonData)
        {
            string sqlstr = string.Empty;
            var obj = JsonConvert.DeserializeObject<dynamic>(jsonData);
            for (int i = 0; i < obj.Count; i++)
            {
                string logicalOperator = obj[i].logicalOperator;//或且
                string conditionFieldVal = obj[i].conditionFieldVal;//字段名
                string conditionOptionVal = obj[i].conditionOptionVal;//条件
                string conditionValueVal = obj[i].conditionValueVal.value;//值             
                string conditionValueLeftVal = obj[i].conditionValueLeftVal.value;
                string conditionValueRightVal = obj[i].conditionValueRightVal.value;
                switch (conditionOptionVal)
                {
                    case "equal":
                        conditionOptionVal = " = "; 
                        conditionValueVal = "'" + conditionValueVal + "'"; break;
                    case "unequal":
                        conditionOptionVal = " <> ";
                        conditionValueVal = "'" + conditionValueVal + "'"; break;
                    case "like":
                        conditionOptionVal = " like ";
                        conditionValueVal = "'%" + conditionValueVal + "%'"; break;
                    case "start":
                        conditionOptionVal = " like "; 
                        conditionValueVal = "'" + conditionValueVal + "%'"; break;
                    case "end":
                        conditionOptionVal = " like "; 
                        conditionValueVal = "'%" + conditionValueVal + "'"; break;
                    case "greater_than":
                        conditionOptionVal = " > "; 
                        conditionValueVal = "'" + conditionValueVal + "'"; break;
                    case "less_than":
                        conditionOptionVal = " < "; 
                        conditionValueVal = "'" + conditionValueVal + "'"; break;
                    case "notempty":
                        conditionOptionVal = " is not null"; 
                        conditionValueVal = string.Empty; break;
                    case "empty":
                        conditionOptionVal = " is null"; 
                        conditionValueVal = string.Empty; break;
                    case "between":
                        conditionOptionVal = " between '" + conditionValueLeftVal + "' and '" + conditionValueRightVal + "'"; 
                        conditionValueVal = string.Empty; break;
                    default:
                        break;
                }
                string qurey = String.Format(" {0} {1} {2} {3} ", logicalOperator, conditionFieldVal, conditionOptionVal, conditionValueVal);
                sqlstr += qurey;
            }
            return sqlstr;
        }
    }