New to Telerik UI for ASP.NET AJAXStart a free 30-day trial

Get SQL compliant FilterExpressions

DESCRIPTION

In some scenarios the developer would need to use the FilterExpressions generated by RadGrid to query an MSSQL database. Most of the time, the FilterExpressions are working, however, there are cases when they are not. The following two cases have been reported by users to be invalid.

  1. Boolean filter expression should contain apostrophes for valid SQL syntax when EnableLinqExpressions=false
  2. String representation of dates in GridDateTimeColumn is wrong in the FilterExpression when using Between filter.

For more information, you can check out the following Forum thread: GridDateTimeColumn filtering localization issues

SOLUTION

Access the Grid's FilterExpressions and correct them with a valid Syntax that will be accepted by the SQL Database.

RadGrid SQL Compliant FilterExpressions

Download the Sample from: grid-get-sql-compliant-filterexpressions.zip (Note: You will need to add the Telerik.Web.UI.dll to the Bin folder to be able to run the sample)

Below you can find a Property and a Function version of the logic.

The logic as a Property

C#
public string SqlFilterExpression
{
    get
    {
        StringBuilder filterExpression = new StringBuilder();
        foreach (GridColumn column in RadGrid1.MasterTableView.RenderColumns)
        {
            if (!column.SupportsFiltering())
            {
                continue;
            }
 
            string filterText = column.EvaluateFilterExpression();
            if (String.IsNullOrEmpty(filterText))
            {
                continue;
            }
 
            GridDateTimeColumn dateTimeColumn = column as GridDateTimeColumn;
            if (dateTimeColumn != null && dateTimeColumn.EnableRangeFiltering)
            {
                filterText = filterText.Replace(",", " ");
            }
 
            filterText = filterText.Replace(" True", " 'True'").Replace(" False", " 'False'");
 
            if (filterExpression.Length > 0)
            {
                filterExpression.Append(" AND ");
            }
 
            filterExpression.AppendFormat("({0})", filterText);
        }
 
        return filterExpression.ToString();
    }
}

The logic as a Function that takes a RadGrid Object as a parameter

C#
private string GetSqlFilterExpression(RadGrid grid)
{
    StringBuilder filterExpression = new StringBuilder();
    foreach (GridColumn column in grid.MasterTableView.RenderColumns)
    {
        if (!column.SupportsFiltering())
        {
            continue;
        }
 
        string filterText = column.EvaluateFilterExpression();
        if (String.IsNullOrEmpty(filterText))
        {
            continue;
        }
 
        GridDateTimeColumn dateTimeColumn = column as GridDateTimeColumn;
        if (dateTimeColumn != null && dateTimeColumn.EnableRangeFiltering)
        {
            filterText = filterText.Replace(",", " ");
        }
 
        filterText = filterText.Replace(" True", " 'True'").Replace(" False", " 'False'");
 
        if (filterExpression.Length > 0)
        {
            filterExpression.Append(" AND ");
        }
 
        filterExpression.AppendFormat("({0})", filterText);
    }
 
    return filterExpression.ToString();
}
In this article
DESCRIPTIONSOLUTION
Not finding the help you need?
Contact Support