DevExpress - Convert "sort string"(json), "filter string" to sql

Question

I use devextreme grid with a special data source https://js.devexpress.com/Demos/WidgetsGallery/Demo/DataGrid/CustomDataSource/React/Light/ parameter "sort", "filter" successfully passed to the backend

In my backend I catch the value:

* sort -> [{"selector": "Id", "desc": true}]

* filter -> ["RoleName", "contains", "test"]

How do i convert :

* sort -> ORDER BY Id DESC 

* filter -> RoleName LIKE '%test%'

notes : that is just a simple example in the real case it could be that the string is so complex that I don't want to create its own function.

Answer 1

there is an official github from devextreme, which handles the process from the backend side. but "raw sql" only exists in the php version. must be converted manually to c#

https://github.com/DevExpress/DevExtreme.AspNet.Data https://github.com/DevExpress/DevExtreme-PHP-Data

Answer 2

convert PHP to C#

    private string _GetSimpleSqlExpr(object[] expression)
    {
       string result = "";
       int itemsCount = expression.Length;
       string fieldName = expression[0].ToString();
        if (itemsCount == 2)
        {
            var val = expression[1];
            result = String.Format("{0} = {1}", fieldName, val);
        }
        else if (itemsCount == 3)
        {
            string clause = expression[1].ToString();
            var val = expression[2];
            string pattern = "";
            switch (clause)
            {
                case "=":
                case "<>":
                case ">":
                case ">=":
                case "<":
                case "<=":
                    if (val.GetType().Name == "Int64")
                    {
                        pattern = "{0} {1} {2}";
                    }
                    else
                    {
                        pattern = "{0} {1} '{2}'";
                    }
                    break;
                case "startswith":
                    pattern = "{0} {1} '{2}%'";
                    clause = " LIKE ";
                    break;
                case "endswith":
                    pattern = "{0} {1} '%{2}'";
                    clause = " LIKE ";
                    break;
                case "contains":
                    pattern = "{0} {1} '%{2}%'";
                    clause  = " LIKE ";
                    break;
                case "notcontains":
                    pattern = "{0} {1} '%{2}%'";
                    clause = String.Format("{0} {1}", " NOT ", " LIKE ");
                    break;
                default:
                    clause = "";
                    break;
            }
            result = string.Format(pattern, fieldName, clause, val);
        }
        return result;
    }

    public string GetSqlExprByArray(object[] expression)
    {
        string result = "(";
        Boolean prevItemWasArray = false;
        int index = 0;
        foreach (var item in expression)
        {
            Console.WriteLine(item.GetType().Name);
            if(item.GetType().Name=="String")
            {
                prevItemWasArray = false;
                if(index==0)
                {
                    if(item.ToString()=="!")
                    {
                        result += " NOT ";
                        continue;
                    }
                    result += _GetSimpleSqlExpr(expression);
                    break;
                }
                string strItem = item.ToString().ToUpper();
                if (strItem == "AND" || strItem == "OR")
                {
                  result += string.Format(" {0} ", strItem);
                }
                continue;
            }

            if (item.GetType().Name == "JArray")
            {
                if (prevItemWasArray) 
                {
                   result += string.Format(" {0} ", "AND");
                }
                result += GetSqlExprByArray(((JArray)item).ToObject<object[]>());
                prevItemWasArray = true;
            }

            index++;
        }
        result += ")";
        return result;
    }