找到你要的答案

Q:How to transform Telerik grid filter expression to SQL query?

Q:如何Telerik网格过滤表达转化为SQL查询?

I'm using Telerik grid component with filter capability in ASP.NET MVC 3 project. I need to translate the filter expression which is coming from grid to SQL clause. For example: "ID~eq~1" would become "WHERE ID = 1".

Is there any build-in way to do this?

I'm using Telerik grid component with filter capability in ASP.NET MVC 3 project. I need to translate the filter expression which is coming from grid to SQL clause. For example: "ID~eq~1" would become "WHERE ID = 1".

这样做有什么办法吗?

answer1: 回答1:

I use this method which handles nested AND and OR conditions:

public static string GridFiltersToSQL(string filterBy)
{
    if (string.IsNullOrEmpty(filterBy) || filterBy == "~")
        return "";


    string filtersOut = "";

    var filtersAnd = filterBy.Split(new string[] { "~and~" }, StringSplitOptions.None);

    for (int i = 0; i < filtersAnd.Length; i++)
    {
        var filtersAndOr = new List<string>();

        if (filtersAnd[i].Contains("~or~"))
        {
            filtersAndOr = filtersAnd[i].Split(new string[] { "~or~" }, StringSplitOptions.None).ToList();
        }
        else
        {
            filtersAndOr.Add(filtersAnd[i]);
        }

        for (int k = 0; k < filtersAndOr.Count; k++)
        {
            filtersAndOr[k] = filtersAndOr[k].Replace("datetime", "");
            filtersAndOr[k] = filtersAndOr[k].Replace("T00-00-00", "");

            if (filtersAndOr[k].Contains("~eq~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~eq~true", " = 1");
                filtersAndOr[k] = filtersAndOr[k].Replace("~eq~false", " = 0");

                filtersAndOr[k] = filtersAndOr[k].Replace("~eq~", " = ");
            }
            else if (filtersAndOr[k].Contains("~ne~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~ne~", " <> ");
            }
            else if (filtersAndOr[k].Contains("~startswith~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~startswith~", " LIKE ");

                var j = filtersAndOr[k].LastIndexOf("'");
                if (j != -1)
                {
                    filtersAndOr[k] = filtersAndOr[k].Insert(j, "%");
                }
            }
            else if (filtersAndOr[k].Contains("~substringof~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~substringof~", " LIKE ");

                var j = filtersAndOr[k].IndexOf("'");
                if (j != -1)
                {
                    filtersAndOr[k] = filtersAndOr[k].Insert(j + 1, "%");
                }

                j = filtersAndOr[k].LastIndexOf("'");
                if (j != -1)
                {
                    filtersAndOr[k] = filtersAndOr[k].Insert(j, "%");
                }
            }
            else if (filtersAndOr[k].Contains("~notsubstringof~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~notsubstringof~", " NOT LIKE ");

                var j = filtersAndOr[k].IndexOf("'");
                if (j != -1)
                {
                    filtersAndOr[k] = filtersAndOr[k].Insert(j + 1, "%");
                }

                j = filtersAndOr[k].LastIndexOf("'");
                if (j != -1)
                {
                    filtersAndOr[k] = filtersAndOr[k].Insert(j, "%");
                }
            }
            else if (filtersAndOr[k].Contains("~endswith~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~endswith~", " LIKE ");

                var j = filtersAndOr[k].IndexOf("'");
                if (j != -1)
                {
                    filtersAndOr[k] = filtersAndOr[k].Insert(j + 1, "%");
                }
            }
            else if (filtersAndOr[k].Contains("~lt~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~lt~", " < ");
            }
            else if (filtersAndOr[k].Contains("~le~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~le~", " <= ");
            }
            else if (filtersAndOr[k].Contains("~gt~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~gt~", " > ");
            }
            else if (filtersAndOr[k].Contains("~ge~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~ge~", " >= ");
            }
        }

        filtersAnd[i] = string.Join(" OR ", filtersAndOr);
    }

    filtersOut = string.Join(" AND ", filtersAnd);

    return filtersOut;
}

我使用这个方法处理嵌套和和条件:

public static string GridFiltersToSQL(string filterBy)
{
    if (string.IsNullOrEmpty(filterBy) || filterBy == "~")
        return "";


    string filtersOut = "";

    var filtersAnd = filterBy.Split(new string[] { "~and~" }, StringSplitOptions.None);

    for (int i = 0; i < filtersAnd.Length; i++)
    {
        var filtersAndOr = new List<string>();

        if (filtersAnd[i].Contains("~or~"))
        {
            filtersAndOr = filtersAnd[i].Split(new string[] { "~or~" }, StringSplitOptions.None).ToList();
        }
        else
        {
            filtersAndOr.Add(filtersAnd[i]);
        }

        for (int k = 0; k < filtersAndOr.Count; k++)
        {
            filtersAndOr[k] = filtersAndOr[k].Replace("datetime", "");
            filtersAndOr[k] = filtersAndOr[k].Replace("T00-00-00", "");

            if (filtersAndOr[k].Contains("~eq~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~eq~true", " = 1");
                filtersAndOr[k] = filtersAndOr[k].Replace("~eq~false", " = 0");

                filtersAndOr[k] = filtersAndOr[k].Replace("~eq~", " = ");
            }
            else if (filtersAndOr[k].Contains("~ne~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~ne~", " <> ");
            }
            else if (filtersAndOr[k].Contains("~startswith~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~startswith~", " LIKE ");

                var j = filtersAndOr[k].LastIndexOf("'");
                if (j != -1)
                {
                    filtersAndOr[k] = filtersAndOr[k].Insert(j, "%");
                }
            }
            else if (filtersAndOr[k].Contains("~substringof~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~substringof~", " LIKE ");

                var j = filtersAndOr[k].IndexOf("'");
                if (j != -1)
                {
                    filtersAndOr[k] = filtersAndOr[k].Insert(j + 1, "%");
                }

                j = filtersAndOr[k].LastIndexOf("'");
                if (j != -1)
                {
                    filtersAndOr[k] = filtersAndOr[k].Insert(j, "%");
                }
            }
            else if (filtersAndOr[k].Contains("~notsubstringof~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~notsubstringof~", " NOT LIKE ");

                var j = filtersAndOr[k].IndexOf("'");
                if (j != -1)
                {
                    filtersAndOr[k] = filtersAndOr[k].Insert(j + 1, "%");
                }

                j = filtersAndOr[k].LastIndexOf("'");
                if (j != -1)
                {
                    filtersAndOr[k] = filtersAndOr[k].Insert(j, "%");
                }
            }
            else if (filtersAndOr[k].Contains("~endswith~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~endswith~", " LIKE ");

                var j = filtersAndOr[k].IndexOf("'");
                if (j != -1)
                {
                    filtersAndOr[k] = filtersAndOr[k].Insert(j + 1, "%");
                }
            }
            else if (filtersAndOr[k].Contains("~lt~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~lt~", " < ");
            }
            else if (filtersAndOr[k].Contains("~le~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~le~", " <= ");
            }
            else if (filtersAndOr[k].Contains("~gt~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~gt~", " > ");
            }
            else if (filtersAndOr[k].Contains("~ge~"))
            {
                filtersAndOr[k] = filtersAndOr[k].Replace("~ge~", " >= ");
            }
        }

        filtersAnd[i] = string.Join(" OR ", filtersAndOr);
    }

    filtersOut = string.Join(" AND ", filtersAnd);

    return filtersOut;
}
parsing  telerik-grid  telerik-mvc