Get SQL compliant FilterExpressions from RadGrid

RELATED TO:

AUTHOR: Attila Antal

DATE POSTED: November 08, 2018

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

As a last resort workaround, the FilterExpressions can be fixed by replacing the invalid date and boolean values and be used as an SQL query.

RadGrid SQL Compliant FilterExpressions

C# property

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();
    }
}

C# method that takes RadGrid object as parameter

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();
}

VB property

Public ReadOnly Property SqlFilterExpression As String
    Get
        Dim filterExpression As StringBuilder = New StringBuilder()
 
        For Each column As GridColumn In RadGrid1.MasterTableView.RenderColumns
 
            If Not column.SupportsFiltering() Then
                Continue For
            End If
 
            Dim filterText As String = column.EvaluateFilterExpression()
 
            If String.IsNullOrEmpty(filterText) Then
                Continue For
            End If
 
            Dim dateTimeColumn As GridDateTimeColumn = TryCast(column, GridDateTimeColumn)
 
            If dateTimeColumn IsNot Nothing AndAlso dateTimeColumn.EnableRangeFiltering Then
                filterText = filterText.Replace(",", " ")
            End If
 
            filterText = filterText.Replace(" True", " 'True'").Replace(" False", " 'False'")
 
            If filterExpression.Length > 0 Then
                filterExpression.Append(" AND ")
            End If
 
            filterExpression.AppendFormat("({0})", filterText)
        Next
 
        Return filterExpression.ToString()
    End Get
End Property

VB method that takes RadGrid object as parameter

Public Function GetSqlFilterExpressions(ByVal grid As RadGrid) As String
    Dim filterExpression As StringBuilder = New StringBuilder()
 
    For Each column As GridColumn In grid.MasterTableView.RenderColumns
 
        If Not column.SupportsFiltering() Then
            Continue For
        End If
 
        Dim filterText As String = column.EvaluateFilterExpression()
 
        If String.IsNullOrEmpty(filterText) Then
            Continue For
        End If
 
        Dim dateTimeColumn As GridDateTimeColumn = TryCast(column, GridDateTimeColumn)
 
        If dateTimeColumn IsNot Nothing AndAlso dateTimeColumn.EnableRangeFiltering Then
            filterText = filterText.Replace(",", " ")
        End If
 
        filterText = filterText.Replace(" True", " 'True'").Replace(" False", " 'False'")
 
        If filterExpression.Length > 0 Then
            filterExpression.Append(" AND ")
        End If
 
        filterExpression.AppendFormat("({0})", filterText)
    Next
 
    Return filterExpression.ToString()
End Function

Files

More Resources

  • Forums

    Find answers to thousands of questions or reach out to our active community.

  • Demos

    Browse 1,200+ demos with source code covering basic and advanced scenarios.

  • Sample Applications

    Learn how to integrate the controls in real-world examples.

  • Feedback Portal

    Vote and follow the features you want to see added to Telerik UI for ASP.NET AJAX.

  • User Voice

    Get answers directly from the people who build the product.

  • Documentation

    Benefit from detailed help articles and API references for all controls.

  • Knowledge Base

    Learn how to achieve common scenarios from a multitude of tutorials and working examples.

  • Code Library

    Browse sample projects submitted by Telerik staff and community members.

  • Blog

    Stay up-to-date with the latest functionality, how-to articles and industry trends.

  • Release History

    Up-to-date release notes for all new features and improvements.