Handle filter generated parameters on server side for a grid serverFiltering=true

11 posts, 0 answers
  1. Bilal
    Bilal avatar
    27 posts
    Member since:
    Sep 2011

    Posted 28 Aug 2012 Link to this post

    Hi,

    I'm using Kendo with ASP .NET Web Forms.
    I can see that handling the filter generated parameters of a grid with serverFiltering=true is so complex.
    The autogenerated params are something like that:

    http://127.0.0.1:81/Service.svc/GetAssetsJSONP?take=50&skip=0&page=1&pageSize=50&filter[filters][0][field]=ModelsModelCodeAS&filter[filters][0][operator]=eq&filter[filters][0][value]=kjkjkjjk&filter[filters][1][field]=ModelsModelCodeAS&filter[filters][1][operator]=eq&filter[filters][1][value]=dfsdfsd&filter[filters][2][logic]=and&filter[filters][2][filters][0][field]=ModelsModelNameAS&filter[filters][2][filters][0][operator]=eq&filter[filters][2][filters][0][value]=fdfsdfsdf&filter[filters][2][filters][1][field]=ModelsModelNameAS&filter[filters][2][filters][1][operator]=eq&filter[filters][2][filters][1][value]=fdsfdsfsdfsd&filter[logic]=and

    How am I supposed to deal with this on server side? Please Kendo clarify this.

    Thanks,
    Bilal
  2. Atanas Korchev
    Admin
    Atanas Korchev avatar
    8462 posts

    Posted 31 Aug 2012 Link to this post

    Hello Bilal,

     When serverFiltering is enabled the developer is responsible for performing it on the server side. You can use the parameterMap function to translate the filter expression to something which you can parse easily on the server side.

    Regards,
    Atanas Korchev
    the Telerik team
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
  3. Kendo UI is VS 2017 Ready
  4. pucsoftware
    pucsoftware avatar
    24 posts
    Member since:
    Sep 2006

    Posted 09 Nov 2012 Link to this post

    "When serverFiltering is enabled the developer is responsible for performing it on the server side. You can use the parameterMap function to translate the filter expression to something which you can parse easily on the server side."

    This sounds great. How about an example? You build this component that submits parameters in a manner which I've never seen yet provide no example on how to use it? The page you reference appears to allow the developer to set pre-defined sorts, filters, and grouping based on pre-defined fields and values. What if I don't want the records to initially be filtered or sorted in any way. From the documentation is looks like all I'm left with is attempting to parse out the users selections on the server, which is what I'm trying to do. So how can this be done? None of the posts that ask this specific question has an answer from Telerik. Does Telerik even know?
  5. Sunil
    Sunil avatar
    230 posts
    Member since:
    Jan 2004

    Posted 17 Nov 2012 Link to this post

    I fully agree with you.  Its impossible to know how to handle filters when using web form, and Telerik seems to be  reluctant to tell how to do this for reasons best known to them. Else you would have received your reply by now.
    Wish you good luck with Kendo UI. 
  6. Dan
    Dan avatar
    9 posts
    Member since:
    Aug 2010

    Posted 17 Nov 2012 Link to this post

    I ended up writing the following for my needs a while back. It should be easy enough to translate into whatever you need. Also you will want to do some optimizations before implementing directly as I've limited the sorting and filters to 20 etc. This has never been used in production by me so take it for what it is. Also I have a string extension for Parse going so you will need to do your own default values and casting.

    public class GridRequestParameters
        {
            public int Page { get; set; }
            public int PageSize { get; set; }
            public int Skip { get; set; }
            public int Take { get; set; }
            public string FilterLogic { get; set; }
     
            public IEnumerable<SortingInfo> Sortings { get; set; }
            public IEnumerable<FilterInfo> Filters { get; set; }
     
            public static GridRequestParameters Current
            {
                get
                {
                    var p = new GridRequestParameters();
                    p.Populate();
                    return p;
                }
            }
     
            //TODO: pull default values from config
            internal void Populate()
            {
                if (HttpContext.Current != null)
                {
                    HttpRequest curRequest = HttpContext.Current.Request;
                    this.Page = curRequest["page"].Parse<int>();
                    this.PageSize = curRequest["pageSize"].Parse(Configuration.Settings.GridDefaults.PageSize);
                    this.Skip = curRequest["skip"].Parse(0);
                    this.Take = curRequest["take"].Parse(Configuration.Settings.GridDefaults.QuerySize);
                    this.FilterLogic = curRequest["filter[logic]"].EmptyDefault("AND").ToUpper();
     
                    //build sorting objects
                    var sorts = new List<SortingInfo>();
                    var x = 0;
                    while (x < 20)
                    {
                        var sortDirection = curRequest["sort[" + x + "][dir]"];
                        if (sortDirection == null)
                        {
                            break;
                        }
                        var sortOn = curRequest["sort[" + x + "][field]"];
                        if (sortOn != null)
                        {
                            sorts.Add(new SortingInfo {SortOn = sortOn, SortOrder = sortDirection});
                        }
                        x++;
                    }
                    Sortings = sorts;
     
                    //build filter objects
                    var filters = new List<FilterInfo>();
                    x = 0;
                    while (x < 20)
                    {
                        var field = curRequest["filter[filters][" + x + "][field]"];
                        if (field == null)
                        {
                            break;
                        }
     
                        var val = curRequest["filter[filters][" + x + "][value]"] ?? string.Empty;
     
                        var strop = curRequest["filter[filters][" + x + "][operator]"];
                        if (strop != null)
                        {
                            filters.Add(new FilterInfo
                                            {
                                                Operator = FilterInfo.ParseOperator(strop),
                                                Field = field,
                                                Value = val
                                            });
                        }
                        x++;
                    }
                    Filters = filters;
                }
            }
        }
    public class FilterInfo
        {
            public string Field { get; set; }
            public FilterOperations Operator { get; set; }
            public string Value { get; set; }
     
            public static FilterOperations ParseOperator(string theOperator)
            {
                switch (theOperator)
                {
                        //equal ==
                    case "eq":
                    case "==":
                    case "isequalto":
                    case "equals":
                    case "equalto":
                    case "equal":
                        return FilterOperations.Equals;
                        //not equal !=
                    case "neq":
                    case "!=":
                    case "isnotequalto":
                    case "notequals":
                    case "notequalto":
                    case "notequal":
                    case "ne":
                        return FilterOperations.NotEquals;
                        // Greater
                    case "gt":
                    case ">":
                    case "isgreaterthan":
                    case "greaterthan":
                    case "greater":
                        return FilterOperations.Greater;
                        // Greater or equal
                    case "gte":
                    case ">=":
                    case "isgreaterthanorequalto":
                    case "greaterthanequal":
                    case "ge":
                        return FilterOperations.GreaterOrEquals;
                        // Less
                    case "lt":
                    case "<":
                    case "islessthan":
                    case "lessthan":
                    case "less":
                        return FilterOperations.LessThan;
                        // Less or equal
                    case "lte":
                    case "<=":
                    case "islessthanorequalto":
                    case "lessthanequal":
                    case "le":
                        return FilterOperations.LessThanOrEquals;
                    case "startswith":
                        return FilterOperations.StartsWith;
     
                    case "endswith":
                        return FilterOperations.EndsWith;
                        //string.Contains()
                    case "contains":
                        return FilterOperations.Contains;
                    case "doesnotcontain":
                        return FilterOperations.NotContains;
                    default:
                        return FilterOperations.Contains;
                }
            }
        }
    public class SortingInfo
    {
        public string SortOrder { get; set; }
        public string SortOn { get; set; }
    }
    public enum FilterOperations
       {
           Equals,
           NotEquals,
           Greater,
           GreaterOrEquals,
           LessThan,
           LessThanOrEquals,
           StartsWith,
           EndsWith,
           Contains,
           NotContains,
       }

  7. Sunil
    Sunil avatar
    230 posts
    Member since:
    Jan 2004

    Posted 17 Nov 2012 Link to this post

    Hi Dan,

    Thanks for your reply. I did some coding on my end to translate filters to a SQL clause when server-side filtering is enabled in Kendo UI Grid and came up with following approach, which I have tried on my end and it seems to work so far. Of course, there is room for further improvement.
    1. You need to define 4 methods in your aspx code-behind, 2 of which are custom in the sense that you need to change them for every databinding scenario, while the other 2 are generic methods that can be used across all databinding scenarios without any changes. The generic methods can be expanded to include more filter operators and more column types. At the moment, I have included only string, int and bool  column types in these generic methods, and most probably not all filter operators, but its easy to add to these generic methods once you understand how these 4 methods interact with each other.
    • GetColumnType(string columnName)   ( custom method)
    • GetDatabaseColumnName(string filterColumnName)   ( custom method)
    • string GetSqlFilterClauseForAllColumns() ( generic method)
    • string GetSqlFilterClauseForColumn(string filterColumnName, string filterOperator, string filterValue) ( generic method).
    The complete code for these methods is as given in code block below. NOTE: I have not included any sorting in this, but only filters.

    UPDATE: I have not tried my methods when calling a static PageMethod in a aspx Webform, so use this code only if you are not calling a static PageMethod in your code-behind. I guess when using static PageMethod, we might have to change how we get the 'Request' object in these methods so that its obtained from 'System.Web.HttpContext.Current.Request'.
    Thanks
    Sunil

    /// <summary>
     /// Gets column's data type only for database columns.
     /// For non-database columns YOU MUST return an empty string so it gets ignored when creating filter strings.
     /// *****CUSTOM METHOD****** THAT YOU MUST CHANGE FOR EACH KENDO UI GRID
     /// DATABINDING SCENARIO
     /// </summary>
     /// <param name="columnName"></param>
     /// <returns></returns>
     ///
     public string GetColumnType(string columnName)
     {
         if (columnName == "ProductId")
         {
             return "int";
         }
         else if (columnName == "ProductName")
         {
             return "string";
         }
         else if (columnName == "Name")
         {
             return "string";
         }
           
         return string.Empty;
     }
     
     /// <summary>
     /// Maps the column name in Kendo Ui Grid to actual database columns
     /// *****CUSTOM METHOD****** THAT YOU MUST CHANGE FOR EACH KENDO UI GRID
     /// DATABINDING SCENARIO    /// </summary>
     /// <param name="filterColumnName"></param>
     /// <returns></returns>
     public string GetDatabaseColumnName(string filterColumnName)
     {
         if (filterColumnName == "CategoryName")
         {
             return "Name";
         }
          else if (filterColumnName == "ProductId")
         {
             return "ProductId";
         }
      else if (filterColumnName == "ProductName")
         {
             return "ProductName";
         }
         else //this else is there so SQL INJECTION is completely avoided
         {
             return string.Empty;
         }
     }
     
     /// <summary>
     /// Gets the filters translated to SQL for database columns only.
     /// Filters on non-database columns like template columns are
     /// ignored by this method.
     /// ******THIS IS A GENERIC METHOD****** AND WILL BE THE SAME FOR ALL
     /// SCENARIOS OF DATABINDING OF KENDO UI GRID
     /// </summary>
     /// <returns></returns>
     public  string GetSqlFilterClauseForAllColumns()
     {
         int i = 0;
         string a = "filter[filters][{0}]";
         string simpleFilterField = "filter[filters][{0}][field]";
         string simpleFilterOperator = "filter[filters][{0}][operator]";
         string simpleFilterValue = "filter[filters][{0}][value]";
         string complexFilterOperator = "filter[filters][{0}][logic]";
         string complexFilterField1 = "filter[filters][{0}][filters][0][field]";
         string complexFilterOperator1 = "filter[filters][{0}][filters][0][operator]";
         string complexFilterValue1 = "filter[filters][{0}][filters][0][value]";
         string complexFilterField2 = "filter[filters][{0}][filters][1][field]";
         string complexFilterOperator2 = "filter[filters][{0}][filters][1][operator]";
         string complexFilterValue2 = "filter[filters][{0}][filters][1][value]";
         string filterOperator = "filter[logic]";
     
         string filterString = string.Empty;
     
          
     
         while (true)
         {
             if (Request.Params[string.Format(simpleFilterField, i.ToString())] != null)
             {
                 if (!string.IsNullOrEmpty(filterString))
                 {
                     filterString = filterString + " " + Request.Params[filterOperator] + " ";
                 }
                  
                 if (Request.Params[string.Format(complexFilterOperator, i.ToString())] != null)
                 {
                     filterString = filterString + " (" + GetSqlFilterClauseForColumn(Request.Params[string.Format(complexFilterField1, i.ToString())], Request.Params[string.Format(complexFilterOperator1, i.ToString())], Request.Params[string.Format(complexFilterValue1, i.ToString())]) + " " + Request[string.Format(complexFilterOperator, i.ToString())] +
                                    " " + GetSqlFilterClauseForColumn(Request.Params[string.Format(complexFilterField2, i.ToString())], Request.Params[string.Format(complexFilterOperator2, i.ToString())], Request.Params[string.Format(complexFilterValue2, i.ToString())]) + " ) ";
                 }
                 else
                 {
                     filterString = filterString + " " + GetSqlFilterClauseForColumn(Request.Params[string.Format(simpleFilterField, i.ToString())], Request.Params[string.Format(simpleFilterOperator, i.ToString())], Request.Params[string.Format(simpleFilterValue, i.ToString())]);
                 }
             }
             else
             {
                 break;
             }
             i++;
         }
         return filterString;
     }
     
     
     /// <summary>
     /// Gets the filter clause in SQL language for a database column.
     /// Non-database columns are ignored by this method.
     /// ******THIS IS A GENERIC METHOD****** AND WILL BE THE SAME FOR ALL
     /// SCENARIOS OF DATABINDING OF KENDO UI GRID    /// </summary>
     /// <param name="filterColumnName"></param>
     /// <param name="filterOperator"></param>
     /// <param name="filterValue"></param>
     /// <returns></returns>
     public string GetSqlFilterClauseForColumn(string filterColumnName, string filterOperator, string filterValue)
     {
        //get the actual database column name
         filterColumnName = GetDatabaseColumnName(filterColumnName);

         if (filterColumnName == string.Empty) //do not create a SQL clause for a non-database columns Or if user specified a column name different from what we are expecting. THIS WILL PREVENT SQL INJECTION.
         {
             return string.Empty;
         }
     
         //replace all single quote/s or double quotes with 2 single quotes to prevent any chance of SQL Injection     
        
    filterValue = filterValue.Replace("'","''");

         filterValue = filterValue.Replace(""","''");
     
         string sqlClause = string.Empty;
         switch(GetColumnType(filterColumnName))     
         {
                  
             case "string":  
                 if (filterOperator == "contains"
                 {
                     sqlClause =   filterColumnName + " like '%" + filterValue + "%' ";
                 }
                 else if (filterOperator == "doesnotcontain")
                 {
                     sqlClause = filterColumnName + " not like '%" + filterValue + "%' ";
                 }
                 else if (filterOperator == "startswith")
                 {
                     sqlClause = filterColumnName + " like '" + filterValue + "%' ";
                 }
                 else if (filterOperator == "endswith")
                 {
                     sqlClause = filterColumnName + " like '" + filterValue + "%' ";
                 }
                 else if (filterOperator == "eq")
                 {
                     sqlClause = filterColumnName + " = '" + filterValue + "' ";
                 }
                 else if (filterOperator == "neq")
                 {
                     sqlClause = filterColumnName + " <> '" + filterValue + "' ";
                 }
                 break;                 
             case "int":           
                 if (filterOperator == "eq"
                 {
                     sqlClause = filterColumnName + " = " + filterValue + " ";
                 }
                 else if (filterOperator == "neq")
                 {
                     sqlClause = filterColumnName + " <> " + filterValue + " ";
                 }
                 else if (filterOperator == "lt")
                 {
                     sqlClause = filterColumnName + " < " + filterValue + "  ";
                 }
                 else if (filterOperator == "lte")
                 {
                     sqlClause = filterColumnName + " <= " + filterValue + " ";
                 }
                 else if (filterOperator == "gt")
                 {
                     sqlClause = filterColumnName + " > " + filterValue + "  ";
                 }
                 else if (filterOperator == "gte")
                 {
                     sqlClause = filterColumnName + " >= " + filterValue + " ";
                 }
                 break;
             case "bool":
                 if (filterOperator == "eq" && filterValue == "true")
                 {
                     sqlClause = filterColumnName + " = cast( 1 as bit) ";
                 }
                 else if (filterOperator == "eq" && filterValue == "false")
                 {
                     sqlClause = filterColumnName + " = cast( 0 as bit) ";
                 }
                  
                 break;
             default: //column is not a database column but a template column, so we ignore it
                 break;     
         }
         return sqlClause;
     }
  8. Dan
    Dan avatar
    9 posts
    Member since:
    Aug 2010

    Posted 17 Nov 2012 Link to this post

    You should be very careful when writing your sql statements like that as you leave yourself open to sql injection attacks if you are not sanitizing the data. 

  9. Sunil
    Sunil avatar
    230 posts
    Member since:
    Jan 2004

    Posted 17 Nov 2012 Link to this post

    That's a good point. So how would you create SQL that is based on dynamically generated filters? Use stored procedure with parameters?

    I don't think any SQL Injection would be possible, if the dynamic SQL is created inside a stored procedure, and this stored procedure has a parameter for each column value, each column's filter operator and each column's filter value. Or you think even then there may be some scope of SQL injection?

    UPDATE:
    I think when using the approach I mentioned in last post, SQL Injection would not happen, since the column name is being decided by the method GetDatabaseColumnName. If this method is implemented so that a column name other than any one from a fixed  set of column names would result in an empty string being returned as the database column name, which then causes the method I have GetSqlFilterClauseForColumn to return an empty string for filter even though you could have specified a dangerous filter value of 'delete from products'.
    On the other hand, if you do specify a correct column name with a dangerous filter value of 'delete from products', then it will have NO effect, since we would end up with a query  as below using the methods I have in place, which does NO HARM at all. So it appears that my approach is 100% SQL INJECTION proof if developer handles the GetDatabaseColumnName method to return an empty string when the column name does not match any one of the list of column names.

    SELECT * FROM LargeProducts lp WHERE ProductName = 'delete  from Products'

    Also, I am replacing all single quotes and double quotes passed within the filter value by two single quotes in GetFilterClauseForColumn, which will make sure that any attempt to attack through a drop or delete query would not happen.
         filterValue = filterValue.Replace("'","''");
         filterValue = filterValue.Replace(@"""","''");

    UPDATE: I think to be 100% injection proof, we have to use ADO.Net parameters in our dynamic SQL. Even after including some checks in my methods, there is still a chance of SQL Injection.
  10. Sunil
    Sunil avatar
    230 posts
    Member since:
    Jan 2004

    Posted 18 Nov 2012 Link to this post

    Here is another solution after Dan pointed out that my original approach was prone to SQL Injection. This new approach uses parameterized dynamic SQL . To use this simply use sample code like below code below.

    SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["northwndConnectionString"].ConnectionString);
    string sql = @"with x as (SELECT  distinct   row_number() over (  order by ProductId asc ) AS RowNumber, ProductID, ProductName,  name as CategoryName FROM LargeProducts p INNER JOIN Category c ON c.ID = p.CategoryID  {0} )  " +
          @"SELECT   * FROM x    where RowNumber > @rowNum1 and RowNumber < =@rowNum2   ORDER BY RowNumber asc;";
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@rowNum1", skip);
    cmd.Parameters.AddWithValue("@rowNum2", (skip + take));
     
    string filterOperator = Request.Params["filter[logic]"];
    sqlWhereClauseParameterized = CreateParameterizedDynamicSQL(filters, filterOperator, cmd);
     
      
    cmd.CommandText = string.Format(sql, string.IsNullOrEmpty(sqlWhereClauseParameterized) ? string.Empty : " where " + sqlWhereClauseParameterized);

    You should include following 4 methods in your server-side code. Two of these are custom methods which you need to implement appropriately. the other 2 are generic methods and you don;t need to do anything to them except improve them with better or more functionality.

    /// <summary>
    /// Gets column's data type only for database columns.
    /// For non-database columns YOU MUST return an empty string so it gets ignored when creating filter strings.
    /// *****CUSTOM METHOD****** THAT YOU MUST CHANGE FOR EACH KENDO UI GRID
    /// DATABINDING SCENARIO
    /// </summary>
    /// <param name="columnName"></param>
    /// <returns></returns>
    ///
    public static string GetColumnType(string columnName)
    {
        if (columnName == "ProductId")
        {
            return "int";
        }
        else if (columnName == "ProductName")
        {
            return "string";
        }
        else if (columnName == "Name")
        {
            return "string";
        }
     
        return string.Empty;
    }
     
    /// <summary>
    /// Maps the column name in Kendo Ui Grid to actual database columns
    /// *****CUSTOM METHOD****** THAT YOU MUST CHANGE FOR EACH KENDO UI GRID
    /// DATABINDING SCENARIO    /// </summary>
    /// <param name="filterColumnName"></param>
    /// <returns></returns>
    public static string GetDatabaseColumnName(string filterColumnName)
    {
        if (filterColumnName == "CategoryName")
        {
            return "Name";
        }
        else if (filterColumnName == "ProductName")
        {
            return "ProductName";
        }
        else if (filterColumnName == "ProductId")
        {
            return "ProductId";
        }
        return string.Empty;
    }
     
    /// <summary>
    /// Gets the filters for all columns, including multiple filters for a column.
    /// Filters are retuned as a collection. Each List within the outer List
    /// represents multiple filters for the same column.
    /// ******THIS IS A GENERIC METHOD****** AND WILL BE THE SAME FOR ALL
    /// SCENARIOS OF DATABINDING OF KENDO UI GRID   
    /// </summary>
    /// <returns></returns>
    public static List<List<Filter>> GetSqlFiltersForAllColumns()
    {
        int i = 0;
        string a = "filter[filters][{0}]";
        string simpleFilterField = "filter[filters][{0}][field]";
        string simpleFilterOperator = "filter[filters][{0}][operator]";
        string simpleFilterValue = "filter[filters][{0}][value]";
        string complexFilterOperator = "filter[filters][{0}][logic]";
        string complexFilterField1 = "filter[filters][{0}][filters][0][field]";
        string complexFilterOperator1 = "filter[filters][{0}][filters][0][operator]";
        string complexFilterValue1 = "filter[filters][{0}][filters][0][value]";
        string complexFilterField2 = "filter[filters][{0}][filters][1][field]";
        string complexFilterOperator2 = "filter[filters][{0}][filters][1][operator]";
        string complexFilterValue2 = "filter[filters][{0}][filters][1][value]";
        string filterOperator = "filter[logic]";
     
        string filterString = string.Empty;
        List<Filter> filter = null;
        List<List<Filter>> filters = new List<List<Filter>>();
     
        while (true)
        {
            if (System.Web.HttpContext.Current.Request.Params[string.Format(simpleFilterField, i.ToString())] != null)
            {
                if (System.Web.HttpContext.Current.Request.Params[string.Format(complexFilterOperator, i.ToString())] != null)
                {
                    List<Filter> complexFilters = new List<Filter>();
                    complexFilters.Add(GetSqlFilterForColumn(System.Web.HttpContext.Current.Request.Params[string.Format(complexFilterField1, i.ToString())], System.Web.HttpContext.Current.Request.Params[string.Format(complexFilterOperator1, i.ToString())], System.Web.HttpContext.Current.Request.Params[string.Format(complexFilterValue1, i.ToString())]));//+ " " +
                    complexFilters[0].ComplexFilterOperator = System.Web.HttpContext.Current.Request[string.Format(complexFilterOperator, i.ToString())];
                    complexFilters.Add(GetSqlFilterForColumn(System.Web.HttpContext.Current.Request.Params[string.Format(complexFilterField2, i.ToString())], System.Web.HttpContext.Current.Request.Params[string.Format(complexFilterOperator2, i.ToString())], System.Web.HttpContext.Current.Request.Params[string.Format(complexFilterValue2, i.ToString())])) ;//+ " " + System.Web.HttpContext.Current.Request[string.Format(complexFilterOperator, i.ToString())]
                    filters.Add(complexFilters);
                }
                else
                {
                    filter = new List<Filter>();
                    filter.Add(GetSqlFilterForColumn(System.Web.HttpContext.Current.Request.Params[string.Format(simpleFilterField, i.ToString())], System.Web.HttpContext.Current.Request.Params[string.Format(simpleFilterOperator, i.ToString())], System.Web.HttpContext.Current.Request.Params[string.Format(simpleFilterValue, i.ToString())]));
                    filters.Add(filter);
                }
            }
            else
            {
                break;
            }
            i++;
        }
        return filters;
    }
     
    /// <summary>
    /// Gets the filter for a database column as Filter object.
    /// For non-database columns, no filter is determined.
    /// ******THIS IS A GENERIC METHOD****** AND WILL BE THE SAME FOR ALL
    /// SCENARIOS OF DATABINDING OF KENDO UI GRID
    /// </summary>
    /// <param name="filterColumnName"></param>
    /// <param name="filterOperator"></param>
    /// <param name="filterValue"></param>
    /// <returns></returns>
    public static Filter GetSqlFilterForColumn(string filterColumnName, string filterOperator, string filterValue)
    {
        //get the actual database column name
        filterColumnName = GetDatabaseColumnName(filterColumnName);
     
        if (filterColumnName == string.Empty) //do not create a SQL clause for a non-database columns
        {
            return null;
        }
     
        //replace single quotes and double quotes with 2 single quotes. This also helps prevent SQL INJECTION
        filterValue = filterValue.Replace("'", "''").Replace(@"""", "''");
     
        Filter filter = null;
        switch (GetColumnType(filterColumnName))
        {
            case "string":
                if (filterOperator == "contains")
                {
                    filter = new Filter(filterColumnName, "like", "%" + filterValue + "%", "string");
                }
                else if (filterOperator == "doesnotcontain")
                {
                    filter = new Filter(filterColumnName, "not like", "%" + filterValue + "%", "string");
                }
                else if (filterOperator == "startswith")
                {
                    filter = new Filter(filterColumnName, "like", "%" + filterValue , "string");
                }
                else if (filterOperator == "endswith")
                {
                    filter = new Filter(filterColumnName, "like", filterValue + "%", "string");
                }
                else if (filterOperator == "eq")
                {
                    filter = new Filter(filterColumnName, "=", filterValue, "string");
                }
                else if (filterOperator == "neq")
                {
                    filter = new Filter(filterColumnName, "<>", filterValue, "string");
                }
                break;
            case "int":
                if (filterOperator == "eq")
                {
                    filter = new Filter(filterColumnName, "=", filterValue, "int");
                }
                else if (filterOperator == "neq")
                {
                    filter = new Filter(filterColumnName, "<>", filterValue, "int");
                }
                else if (filterOperator == "lt")
                {
                    filter = new Filter(filterColumnName, "<", filterValue, "int");
                }
                else if (filterOperator == "lte")
                {
                    filter = new Filter(filterColumnName, "<=", filterValue, "int");
                }
                else if (filterOperator == "gt")
                {
                    filter = new Filter(filterColumnName, ">", filterValue, "int");
                }
                else if (filterOperator == "gte")
                {
                    filter = new Filter(filterColumnName, ">=", filterValue, "int");
                }
                break;
            case "bool":
                if (filterOperator == "eq" && filterValue == "true")
                {
                    filter = new Filter(filterColumnName, "=", "true", "bool");
                }
                else if (filterOperator == "eq" && filterValue == "false")
                {
                    filter = new Filter(filterColumnName, "=", "false", "bool");
                }
     
                break;
            default: //column is not a database column but a template column, so we ignore it
                break;
        }
        return filter;
    }
     
    /// <summary>
    /// Creates parameterized dynamic SQL and adds all necessary parameters to a command object.
    /// ******THIS IS A GENERIC METHOD****** AND WILL BE THE SAME FOR ALL
    /// SCENARIOS OF DATABINDING OF KENDO UI GRID
    /// </summary>
    /// <param name="allFilters"></param>
    /// <param name="filterOperator"></param>
    /// <param name="cmd"></param>
    /// <returns></returns>
    public static string CreateParameterizedDynamicSQL(List<List<Filter>> allFilters, string filterOperator, SqlCommand cmd)
    {
        string s = string.Empty;
        foreach (var filter in allFilters)
        {
            if (!string.IsNullOrEmpty(s))
            {
                s = s + " " + filterOperator + " ";
            }
     
            if (filter.Count == 1) //we have simple filter
            {
                if (filter[0].ColumnType == "string")
                {
                    cmd.Parameters.AddWithValue("@" + filter[0].FilterColumn, filter[0].FilterValue);
                    s = s + filter[0].FilterColumn + " " + filter[0].FilterOperator + " @" + filter[0].FilterColumn ;
                }
                else if (filter[0].ColumnType == "int")
                {
                    cmd.Parameters.AddWithValue("@" + filter[0].FilterColumn, int.Parse(filter[0].FilterValue));
                    s = s + filter[0].FilterColumn + " " + filter[0].FilterOperator + " @" + filter[0].FilterColumn ;
                }
            }
            else if (filter.Count > 1) //we habve a complex filter
            {
                foreach (var f in filter)
                {
                    if (filter.IndexOf(f) == 0)
                    {
                        if (f.ColumnType == "string")
                        {
                            cmd.Parameters.AddWithValue("@" + f.FilterColumn, f.FilterValue);
                            s = s + " ( " + f.FilterColumn + " " + f.FilterOperator + " @" + f.FilterColumn + " " + f.ComplexFilterOperator;
                        }
                        else if (f.ColumnType == "int")
                        {
                            cmd.Parameters.AddWithValue("@" + f.FilterColumn, int.Parse(f.FilterValue));
                            s = s + " ( " + f.FilterColumn + " " + f.FilterOperator + " @" + f.FilterColumn + " " + f.ComplexFilterOperator;
                        }
                    }
                    else if (filter.IndexOf(f) == (filter.Count - 1))
                    {
                        if (f.ColumnType == "string")
                        {
                            cmd.Parameters.AddWithValue("@" + f.FilterColumn, f.FilterValue);
                            s = s + f.FilterColumn + " " + f.FilterOperator + " @" + f.FilterColumn + " )" ;
                        }
                        else if (f.ColumnType == "int")
                        {
                            cmd.Parameters.AddWithValue("@" + filter[0].FilterColumn, int.Parse(filter[0].FilterValue));
                            s = s + f.FilterColumn + " " + f.FilterOperator + " @" + f.FilterColumn + " )" ;
                        }
                    }
                    else
                    {
                        if (f.ColumnType == "string")
                        {
                            cmd.Parameters.AddWithValue("@" + f.FilterColumn, f.FilterValue);
                            s = s + f.FilterColumn + " " + f.FilterOperator + " @" + f.FilterColumn + " " + f.ComplexFilterOperator;
                        }
                        else if (f.ColumnType == "int")
                        {
                            cmd.Parameters.AddWithValue("@" + f.FilterColumn, int.Parse(f.FilterValue));
                            s = s + f.FilterColumn + " " + f.FilterOperator + " @" + f.FilterColumn + " " + f.ComplexFilterOperator;
                        }
                    }
                }
            }
        }
     
        return s;
    }
     
    public class Filter
    {
        public string FilterColumn { get; set; }
     
        public string FilterOperator { get; set; }
     
        public string FilterValue { get; set; }
     
        public string ColumnType { get; set; }
     
        public string ComplexFilterOperator { get; set; }
     
        public string SimpleFilterOperator { get; set; }
     
        public Filter(string filterColumn, string filterOperator, string filterValue, string columnType)
        {
            this.FilterColumn = filterColumn;
            this.FilterOperator = filterOperator;
            this.FilterValue = filterValue;
            this.ColumnType = columnType;
            this.ComplexFilterOperator = string.Empty;
            this.SimpleFilterOperator = "and";
        }
     
        public Filter(string filterColumn, string filterOperator, string filterValue, string columnType, string complexFilterOperator)
        {
            this.FilterColumn = filterColumn;
            this.FilterOperator = filterOperator;
            this.FilterValue = filterValue;
            this.ColumnType = columnType;
            this.ComplexFilterOperator = complexFilterOperator;
            this.SimpleFilterOperator = "and";
        }
     
        public Filter(string filterColumn, string filterOperator, string filterValue, string columnType, string complexFilterOperator, string simpleFilterOperator)
        {
            this.FilterColumn = filterColumn;
            this.FilterOperator = filterOperator;
            this.FilterValue = filterValue;
            this.ColumnType = columnType;
            this.ComplexFilterOperator = complexFilterOperator;
            this.SimpleFilterOperator = simpleFilterOperator;
        }
    }
  11. Tim
    Tim avatar
    6 posts
    Member since:
    Dec 2012

    Posted 13 Dec 2012 Link to this post

    It would nice if someone from Telerik could provide and example of how they intend parameterMap to be used.

  12. Marcin Butlak
    Marcin Butlak avatar
    26 posts
    Member since:
    Dec 2012

    Posted 16 Dec 2012 Link to this post

    Hi Tim,

    The parameterMap is only to change the structure of parameters sent to the server-side. This will only come handy if you are using some framework that have a ready component or external service able to paginate, sort and filter based on parameters you send. Other use case beside changing the value before sending is irrelevant as the base parameters structure send to the server-side is very easy to use. As for the example there is a simple example in the API reference already.
Back to Top
Kendo UI is VS 2017 Ready