GRIDVIEW的筛选逻辑

最新推荐文章于 2023-09-25 22:23:42 发布

CF西西

最新推荐文章于 2023-09-25 22:23:42 发布

阅读量1.4k

点赞数

CC 4.0 BY-SA版权

文章标签： string null filter textbox sql methods

本文链接：https://blog.youkuaiyun.com/xiaoguang44/article/details/6671347

本文介绍了一个用于GRIDVIEW的筛选逻辑实现方法，该方法通过不同的筛选器选项来生成SQL筛选条件。具体包括如何处理字符串匹配（如包含、不包含、以...开头等），数值比较（等于、不等于、大于、小于等）以及空值检查。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

GRIDVIEW的筛选逻辑，其中funs为字段对应筛选器选项的名称，columns为对应字段的名称，vals为textbox所填的筛选数值，upper为筛选器的个数

  #region Helper Methods
    public static string FilterCondition(string[] funcs,string[] columns,string[] vals,int upper)
    {
        string filterCondition = "";
        for (int i = 0; i < upper; i++)
        {
            if (funcs[i] != String.Empty && funcs[i] != null)
            {
                switch (funcs[i])
                {
                    case "Contains":
                        filterCondition += columns[i] + " LIKE '%" + vals[i] + "%'";
                        break;
                    case "DoesNotContain":
                        filterCondition += columns[i] + " NOT LIKE '%" + vals[i] + "%'";
                        break;
                    case "StartsWith":
                        filterCondition += columns[i] + " LIKE '" + vals[i] + "%'";
                        break;
                    case "EndsWith":
                        filterCondition += "rtrim("+columns[i]+")" + " LIKE '%" + vals[i] + "'";
                        break;
                    case "EqualTo":
                        filterCondition += columns[i] + " = " + vals[i];
                        break;
                    case "NotEqualTo":
                        filterCondition += columns[i] + " != " + vals[i];
                        break;
                    case "GreaterThan":
                        filterCondition += columns[i] + " > " + vals[i];
                        break;
                    case "LessThan":
                        filterCondition += columns[i] + " < " + vals[i];
                        break;
                    //case "GreaterThanOrEqualTo":
                    //    filterCondition += Columns[i] + " >= " + Vals[i];
                    //    break;
                    //case "LessThanOrEqualTo":
                    //    filterCondition += Columns[i] + " <= " + Vals[i];
                    //    break;
                    //case "Between":

                    //    break;
                    //case "NotBetween":

                    //    break;
                    case "IsEmpty":
                        filterCondition += columns[i] + " = ''";
                        break;
                    case "NotIsEmpty":
                        filterCondition += columns[i] + " != ''";
                        break;
                    case "IsNull":
                        filterCondition += columns[i] + " is null ";
                        break;
                    case "NotIsNull":
                        filterCondition += columns[i] + " is not null";
                        break;
                    default:
                        break;
                }
                //filterCondition added like clause and next filter is not null 
                //appand " collate SQL_Latin1_General_Cp1_CS_AS and "
                if ((funcs[i] == "Contains" || funcs[i] == "DoesNotContain" || funcs[i] == "StartsWith" ||
                     funcs[i] == "EndsWith") &&(i < upper - 1 && funcs[i + 1] != null && filterCondition != String.Empty &&
                     funcs[i + 1] != String.Empty && funcs[i + 1] != "NoFilter"))
                    filterCondition += " collate SQL_Latin1_General_Cp1_CS_AS and ";
                //filterCondition added non-like clause and next filter is not null 
                //appand " and "
                else if(i < upper - 1 && funcs[i + 1] != null && filterCondition != String.Empty &&
                     funcs[i + 1] != String.Empty && funcs[i + 1] != "NoFilter")
                    filterCondition += " and ";
            }
        }
        //only filterCondition not empty and has % in the last clause that need appand collate SQL_Latin1_General_Cp1_CS_AS
        if (filterCondition != String.Empty && filterCondition.LastIndexOf('%')>filterCondition.Length-3)
            filterCondition += " collate SQL_Latin1_General_Cp1_CS_AS ";
        return filterCondition;
    }
    #endregion