Convert Kendo DataSourceRequest back into query string

Question

Is there a built-in helper in the Kendo MVC libraries anywhere that will convert a Kendo Grid's DataSourceRequest object back into a querystring from within a MVC6 controller? Something like a "model unbinder"?

If DataSourceRequest were a simple object this would be easier to roll your own, but since it has a more complex structure I was hoping there was already a "Kendo way". I could also grab the entire querystring from Request.QueryString.Value but was hoping I could easily isolate just the datasource part for cases where there are more parameters.

Something like this:

public int GetStuff([DataSourceRequest]DataSourceRequest request)
{
    var res = 0;
    try
    {
        //something like these which do not really exist?
        var kendoQS1 = request.ToQueryString(); 
        var kendoQS2 = KendoDataSourceQueryStringBuilder.FromDataSource(request);

       //do stuff with kendoQS
    }
    catch (Exception ex)
    {
        //...
    }
    return res ;
}

Whomp

Did you ever find a solution to this? – Primp

@Primp I did not. I have also since moved away from Telerik grid to mvc6-grid.azurewebsites.net since it does everything I need. – Whomp

Answer 1

Take a look at "Converting DataSourceRequest filters to SqlServer parameterized query in controller Read method" that I posted in Telerik forums.

There are three pieces that go deeper and deeper.

Reader

public ActionResult MyData_Read(DataSourceRequest request)
{
  SqlCommand command = new SqlCommand();
  string whereClause = FiltersToParameterizedQuery(request.Filters, command: command);
  ...
  ... compute order clause
  ... compute paging clause
  
  string pageQuery = "select * from MY_UNMODELED_DATABASE_OBJECT" + " " + whereClause + orderClause + pageClause;
  string countQuery = "select count(1) from MY_UNMODELED_DATABASE_OBJECT" + " " + whereClause;
  ...

FiltersToParameterizedQuery

private string FiltersToParameterizedQuery(IList<IFilterDescriptor> filters, FilterCompositionLogicalOperator compositionOperator = FilterCompositionLogicalOperator.And, SqlCommand command = null)
{
    // See https://www.telerik.com/forums/how-to-access-datasourcerequest-filters-in-controller-
 
    if (!filters.Any()) return "";
 
    string result = "(";
    string combineWith = "";
 
    foreach (var filter in filters)
    {
        if (filter is FilterDescriptor fd)
        {
            result +=
                combineWith + "("
                + DescriptorToSqlServerQuery(fd, command)
                + ")"
                ;
        }
        else if (filter is CompositeFilterDescriptor cfd)
        {
            result +=
                combineWith + "("
                + FiltersToParameterizedQuery(cfd.FilterDescriptors, cfd.LogicalOperator, command)
                + ")"
                ;
        }
 
        combineWith =
            (compositionOperator == FilterCompositionLogicalOperator.And)
            ? " and "
            : " or "
            ;
    }
     
    result += ")";
    return result;
}

and lastly

DescriptorToSqlServerQuery

private string DescriptorToSqlServerQuery (FilterDescriptor fd, SqlCommand command)
{
    string parameterName = "@PARAMETER" + command.Parameters.Count;
    string result;
 
    // Some string filter values are modified for use as parameters in a SQL LIKE clause, thus work with a copy.
    // The original value must remain unchanged for when ToDataSourceResult(request) is used later.
 
    Object filterValue = fd.Value;
 
    switch (fd.Operator)
    {
        case FilterOperator.IsLessThan:             result = "[" + fd.Member + "]" + " < " + parameterName; break;
        case FilterOperator.IsLessThanOrEqualTo:    result = "[" + fd.Member + "]" + " <= " + parameterName; break;
        case FilterOperator.IsEqualTo:              result = "[" + fd.Member + "]" + " = " + parameterName; break;
        case FilterOperator.IsNotEqualTo:           result = "[" + fd.Member + "]" + " <> " + parameterName; break;
        case FilterOperator.IsGreaterThanOrEqualTo: result = "[" + fd.Member + "]" + " >= " + parameterName; break;
        case FilterOperator.IsGreaterThan:          result = "[" + fd.Member + "]" + " > " + parameterName; break;
        case FilterOperator.StartsWith:
            filterValue = fd.Value.ToString().ToSqlSafeLikeData() + "%";
                                                    result = "[" + fd.Member + "]" + " like " + parameterName; break;
        case FilterOperator.EndsWith:
            filterValue = "%" + fd.Value.ToString().ToSqlSafeLikeData();
                                                    result = "[" + fd.Member + "]" + " like " + parameterName; break;                   
        case FilterOperator.Contains:
            filterValue = "%" + fd.Value.ToString().ToSqlSafeLikeData() + "%";
                                                    result= "[" + fd.Member + "]" + " like " + parameterName; break;
        case FilterOperator.IsContainedIn:
            throw new Exception("There is no translator for [" + fd.Member + "]" + " " + fd.Operator + " " + fd.Value);
        case FilterOperator.DoesNotContain:
            filterValue = "%" + fd.Value.ToString().ToSqlSafeLikeData();
                                                    result = "[" + fd.Member + "]" + " not like " + parameterName; break;
        case FilterOperator.IsNull:     result = "[" + fd.Member + "]" + " IS NULL"; break;
        case FilterOperator.IsNotNull:  result = "[" + fd.Member + "]" + " IS NOT NULL"; break;
        case FilterOperator.IsEmpty:    result = "[" + fd.Member + "]" + " = ''"; break;
        case FilterOperator.IsNotEmpty: result = "[" + fd.Member + "]" + " <> ''"; break;
        default:
            throw new Exception("There is no translator for [" + fd.Member + "]" + " " + fd.Operator + " " + fd.Value);
    }
 
    command.Parameters.Add(new SqlParameter(parameterName, filterValue));
 
    return result;
}

Gambrell