Programmatically alter generated query generated by RadFilter

2 posts, 0 answers
  1. Steve LaForge
    Steve LaForge avatar
    61 posts
    Member since:
    Feb 2005

    Posted 12 Jun 2012 Link to this post

    I have a SQL database that users should be able to query.  However, the database has some fields that are similar and when they choose to build a filter on one of those columns, I want to also include the same function for the other column(s) as well.  I am not trying to bind the filter directly to a data source, but will programmatically bind it in the code.

    For example, if a user selects to filter on Manufacturer starts with 'somecompany', I want to build the SQL query to look like:
    ManfName like 'somecompany%' or AltManf like 'somecompany%' or GenericManf like 'somecompany%'

    I have been able to create the code to loop the generated expressions, but I am really hoping that there is a much easier way to accomplish my objective.  As it is, I have to use a recursive subroutine to process each of the groups, and manually build the SQL code.  For example, I can use

    Dim rfProvider As New RadFilterSqlQueryProvider

    To build the SQL string, but then there is a LOT of coding that would be required to parse through the string and inject the additional SQL code.

    What I want to be able to do is use the recursive call to get the current SQL code, then use a string replacement function to add the check for the additional fields to the query.  The snippet below would be an example of what I am trying to accomplish, but don't know how.

    dim queryString as string
    Protected Sub RadFilter1_ApplyExpressions(sender As Object, e As Telerik.Web.UI.RadFilterApplyExpressionsEventArgs) Handles RadFilter1.ApplyExpressions
        queryString = String.Empty
        Dim objExpression As RadFilterGroupExpression = e.ExpressionRoot
        Dim objCollection As RadFilterExpressionsCollection = objExpression.Expressions
    End Sub
    Private Sub processGroup(ByVal objCollection As RadFilterExpressionsCollection)
      For Each objExp As RadFilterExpression In objCollection
        Dim objFunction As RadFilterFunction = objExp.FilterFunction
        Dim rfProvider As New RadFilterSqlQueryProvider
      ' I don't know how to produce the functionality of the next statement!
        Dim strFilter as new string = rfProvider.ProcessFilter(RadFilterExpression)  ' This would return "Manufacturer like 'somecompany%'"
        if strFilter.StartsWith("Manufacturer") then
          queryString &= " (" & strFilter.Replace("Manufacturer","ManfName") & _
            " or " & strFilter.Replace("Manufacturer", "AltManf") & _
            " or " & strFilter.Replace("Manufacturer", "GenericManf") & ") "
        end if
    End Sub

    Can you please provide any suggestions?  Thank you!!!

  2. Andrey
    Andrey avatar
    836 posts

    Posted 15 Jun 2012 Link to this post


    My colleague have answered your question in the support thread you have supplied, however, I will post his answer here as well, so the community could use it if needed.

    The idea is to loop through all expression items and build each group recursively. Before appending the current group expression result you could implement your replacing and processing logic.

    Protected Sub RadFilter1_ApplyExpressions(sender As Object, e As Telerik.Web.UI.RadFilterApplyExpressionsEventArgs)
        Dim bulder As New StringBuilder()
        Dim expressionResult As New StringBuilder()
        ProcessExpressions(e.ExpressionRoot, expressionResult)
    End Sub
    Protected Sub ProcessExpressions(groupExpression As RadFilterGroupExpression, resultExpression As StringBuilder)
        Dim sqlQueryProvider As New RadFilterSqlQueryProvider()
        sqlQueryProvider.ProcessGroup(TryCast(groupExpression, RadFilterGroupExpression))
        Dim result As String = sqlQueryProvider.Result
        'your processing
        For Each expression As RadFilterExpression In groupExpression.Expressions
            If TypeOf expression Is RadFilterGroupExpression Then
                ProcessExpressions(TryCast(expression, RadFilterGroupExpression), resultExpression)
            End If
    End Sub

    Additionally, here is a project that demonstrates the approach.

    the Telerik team
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
Back to Top