Kendo Server Filtering

If you want to enable Server Filtering on Kendo Grid, first step is to tell Kendo that you want to enable that functionality because, by default, the data source performs filtering on client side.

var dataSource = new kendo.data.DataSource({
	transport: {
		read: function (options) {
			var filter = options.data.filter;
			/* send the filter to the server*/
			options.success(receivedDataFromServer);	
		}
	},
	serverFiltering: true
});

On the server side you need to map Kendo Filter to a C# Filter class.

public class Filter
{
	public string Field { get; set; }
	public string Operator { get; set; }
	public object Value { get; set; }
	public string Logic { get; set; }
	public IEnumerable<Filter> Filters { get; set; }
}

If the filter is a compound one, then Filters property is set (it means that the filter has some other filters as childs) and the Field, Operator and Value properties will be set to null.

In order to Obtain SQL expression, you have to extract smaller expressions from each child filter and concatenate them.

It is important to know that supported operators in Kendo are: “eq” (equal to), “neq” (not equal to), “lt” (less than), “lte” (less than or equal to), “gt” (greater than), “gte” (greater than or equal to), “startswith”, “endswith”, “contains”, “doesnotcontain”.

public static string GetSqlPredicate(Filter filter, List<KeyValuePair<string, object>> values, int index = 0)
{
	if (filter.Filters != null && filter.Filters.Count > 0)
	{
        List<string> expressions = new List<string>();
        foreach (var item in filter.Filters)
        {
            expressions.Add(ToSqlExpression(item, values, index++));
        }

        return "(" + string.Join(" " + filter.Logic + " ", expressions.ToArray()) + ")";
    }

    switch (filter.Operator)
    {
        case "eq": filter.Operator = "="; break;
        case "neq": filter.Operator = "<>"; break;
        case "lt": filter.Operator = "<"; break;
        case "lte": filter.Operator = "<="; break;
        case "gt": filter.Operator = ">"; break;
        case "gte": filter.Operator = ">="; break;
        default: break;
    }

    switch (filter.Operator)
    {
        case "startswith": return string.Format("[{0}] LIKE '{1}%'", filter.Field, filter.Value);
        case "endswith": return string.Format("[{0}] LIKE '%{1}'", filter.Field, filter.Value);
        case "contains": return string.Format("[{0}] LIKE '%{1}%'", filter.Field, filter.Value);
        case "doesnotcontain": return string.Format("[{0}] NOT LIKE '%{1}%'", filter.Field, filter.Value);
        case "=":
        case "!=":
        case "<=":
        case "<":
        case ">=":
        case ">":
            values.Add(new KeyValuePair<string, object>("attr" + index, filter.Value));
            return string.Format("[{0}] {1} @attr{2}", filter.Field, filter.Operator, index);
        default: return "";
    }
}

 

Example:

Kendo Filter:

{
    "logic": "and",
    "filters": [
        {
            "field": "LastName",
            "operator": "eq",
            "value": "White"
        },
        {
            "field": "Age",
            "operator": "gte",
            "value": 20
        },
        {
            "logic": "or",
            "filters": [
                {
                    "field": "FirstName",
                    "operator": "startswith",
                    "value": "Mary"
                },
                {
                    "field": "City",
                    "operator": "eq",
                    "value": "London"
                }
            ]
        }
    ]
}

SqlPredicate:

([LastName] = @attr0 and [Age] >= @attr1 and ([FirstName] LIKE 'Mary%' or [City] = @attr3))

 

How to use this predicate with ADO.NET

List<KeyValuePair<string, object>> predicateValues = new List<KeyValuePair<string, object>>();
string predicate = GetSqlPredicate (…, predicateValues); 
/* ... */
using (SqlCommand command = new SqlCommand(…))
{
	if (predicateValues!=null)
	{
		foreach (var val in predicateValues)
        {
            command.Parameters.AddWithValue(val.Key, val.Value);
      	}
	}
}
/* ... */

Finally, there’s another very important peculiarity of what does Cialis that brings it so high above its alternatives. It is the only med that is available in two versions – one intended for use on as-needed basis and one intended for daily use. As you might know, Viagra and Levitra only come in the latter of these two forms and should be consumed shortly before expected sexual activity to ensure best effect. Daily Cialis, in its turn, contains low doses of Tadalafil, which allows to build its concentration up in your system gradually over time and maintain it on acceptable levels, which, consequently, makes it possible for you to enjoy sex at any moment without having to time it.