Limit GridAutoCompleteColumn size and server side filtering

5 posts, 0 answers
  1. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 10 Jul 2013 Link to this post

    Which could be a simple way to limit the AutoCompleteColumn suggestion to say 10 or 50 or whatever on a large dataset and how to apply this filtering for the AutoCompleteColumn server side so that the "TOP n" clause is injected in the SELECT retrieving data for the autocomplete column? (with an objectdatasource for example or other method)
    I think this is quite a common scenario together with the minimum character required to trigger suggestion.

    Another question is if it is possible to retrieve an bind an IQueryable(of String) to the AutoCompleteColumn thus avoid specifying data text and data value. Let's say I'm filtering a user name, I would just retrieve the UserName field in my datasource and passa back an array/iqueryable/list of string and not a complex object.

    Thanks in advance.
  2. Vasil
    Admin
    Vasil avatar
    1547 posts

    Posted 15 Jul 2013 Link to this post

    Hi Massimiliano,

    The RadAutoCompleteBox in the column will show all the data that is selected from it's datasource. It will not select a "top N" records. If you need to show less records, then your datasource should return less records.

    The RadAutoCompleteBox needs the DataTextField and DataValueField to show the records. So you need to define them into the column.

    Regards,
    Vasil
    Telerik
    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 the blog feed now.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 15 Jul 2013 Link to this post

    Thank you Vasil for your answer here as well.

    "If you need to show less records, then your datasource should return less records."
    The problem is that with a filter involved like the one in GridAutoCompleteColumn you have to limit the source size applying the filter and not the way back. 
    If I just return 50 records from the datasource the AutoCompleteBox will then filter those first 50 records.. and that's not the intended behaviour of course. The intended behaviour is to return the first filtered 50 records.
    This is the problem. 
    I'm trying to solve with Bozhidar suggestion on the other topic:
    http://www.telerik.com/community/forums/aspnet-ajax/autocompletebox/radautocompletebox-and-very-large-sql-table.aspx
    But I'm missing the "You can handle the Grid's ItemCreated event, where you can find the AutoCompleteBox and attach the OnDataSourceSelect event to it..." part since I'm really new to oop and radgrid.

  5. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 18 Jul 2013 Link to this post

    Ok I tryed with Bozhidar solution suggested here http://www.telerik.com/community/forums/aspnet-ajax/autocompletebox/radautocompletebox-and-very-large-sql-table.aspx but my inexpertise doesn't allow me to reach a working solution.
    Here is what I came up so far.
    In aspx page:

    <telerik:GridAutoCompleteColumn UniqueName="TestBox" AllowCustomEntry="true"
    AllowSorting="true" ConvertEmptyStringToNull="true" DataTextField="UserName"
    DataValueField="UserName" DataSourceID="ObjectDataSource1"
    AllowTokenEditing="false" DataType="System.String" Filter="StartsWith"
    FilterControlWidth="99%" FilterDelay="300" InputType="Text"
    SelectionMode="Single" AllowFiltering="true" SortExpression="UserName"
    DataField="UserName" HeaderText="Nome utente"
    AutoPostBackOnFilter="true" CurrentFilterFunction="StartsWith"></telerik:GridAutoCompleteColumn>

    The object data source:

    <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="Eva.Bll.Core.EvaUsersManager" SelectMethod="GetTop">
            <SelectParameters>
                <asp:Parameter Name="rowsNumber" Type="Int32" />
                <asp:Parameter Name="filterSql" Type="String" />
            </SelectParameters>
    </asp:ObjectDataSource>

    In the ItemCreated event of radgrid I SUCCESSFULLY attach to the DataSourceSelect event of AutoCompleteBox


    If TypeOf e.Item Is GridFilteringItem Then
        Dim fltItem As GridFilteringItem = DirectCast(e.Item, GridFilteringItem)
     
        ' Autocomplete filter column fix to select only N records in the DB
        Dim autoComplete As RadAutoCompleteBox = DirectCast(fltItem("TestBox").Controls(0), RadAutoCompleteBox)
     
        AddHandler autoComplete.DataSourceSelect, New Telerik.Web.UI.AutoCompleteBoxDataSourceSelectEventHandler(AddressOf RadAutoCompleteBox1_DataSourceSelect)
    End If

    And then in the RadAutoCompleteBox1_DataSourceSelect sub:

    Protected Sub RadAutoCompleteBox1_DataSourceSelect(sender As Object, e As Telerik.Web.UI.AutoCompleteBoxDataSourceSelectEventArgs)
        Dim dataSource As ObjectDataSource = DirectCast(e.DataSource, ObjectDataSource)
     
        dataSource.SelectParameters("rowsNumber").DefaultValue = "10"
        dataSource.SelectParameters("filterSql").DefaultValue = e.FilterString
    End Sub

    The problem at this point is that e.DataSource seems to be of type System.Web.UI.WebControls.ReadOnlyDatasource
    So I cannoct cast it to objectDataSource.
    I solved with this solution is this correct?

    Protected Sub RadAutoCompleteBox1_DataSourceSelect(sender As Object, e As Telerik.Web.UI.AutoCompleteBoxDataSourceSelectEventArgs)
        'Dim dataSource As ObjectDataSource = DirectCast(e.DataSource, ObjectDataSource)
     
        ObjectDataSource1.SelectParameters("rowsNumber").DefaultValue = "10"
        ObjectDataSource1.SelectParameters("filterSql").DefaultValue = e.FilterString
        'dataSource.SelectParameters("rowsNumber").DefaultValue = "10"
        'dataSource.SelectParameters("filterSql").DefaultValue = e.FilterString
    End Sub

    If this works tomorrow I will do a complete recap for other user wich I think will be much useful to many. Just have to solve second issue before:

    ----------------------------------- ISSUE 2

    Showing list only after n characters

    Also how could I use your (Vasil) suggestion that you gave me in another post to avoid SQL triggering when characters number in the RadAutoCompleteBox are < n? OnClientRequesting It's a javascript event but it's not exposed in AutoCompleteColumn:

    // You said:
    // "You can handle the OnClientRequesting event of the RadAutoCompleteBox to cancel the filtering if less than specified values chars are entered."
     
    function OnClientRequesting(sender, args) {
        if (args.get_text().length < 4) {
            args.set_cancel(true); 
        }
    }

    Thanks for your kind support as always
  6. Massimiliano
    Massimiliano avatar
    184 posts
    Member since:
    Oct 2012

    Posted 19 Jul 2013 Link to this post

    As promised here is a complete working recap for those interested in the filtering functionalities of the new GridAutoCompleteColumn with the added benefit of server side filtering and limiting the results to max n records. Also the query and suggestion list is triggered only after "n" minimum characters have been inserted in the filter box and this check happens both client side and as a fallback server side.
    I guess all those options are invaluable in this scenario and maybe GridAutoCompleteColumn should provide those by default.
    Anyway in the meanwhile here is a solution (suggestions for improvement are always welcome). If Telerik thinks this can be useful I will be glad to post it in the code library as well as soon as it will be complete with the JS functionality to trigger the filter after n characters.

    In the aspx declaration of the grid this is how I set my GridAutoCompleteColumns:

    <telerik:GridAutoCompleteColumn UniqueName="UserName" DataSourceID="ObjectDataSource1"
    DataTextField="UserName" DataField="UserName" DataType="System.String" HeaderText="Nome utente"
    ColumnGroupName="Anagrafica" ShowFilterIcon="true" FilterImageToolTip="" AutoPostBackOnFilter="true"
    CurrentFilterFunction="StartsWith" ConvertEmptyStringToNull="true" FilterControlWidth="80%"
    AllowCustomEntry="true" AllowTokenEditing="false" Filter="StartsWith" InputType="Text"
    SelectionMode="Single" ItemStyle-CssClass="RadGridBatchEditInput"></telerik:GridAutoCompleteColumn>
     
    <telerik:GridAutoCompleteColumn UniqueName="CommunityRole" DataSourceID="ObjectDataSource2"
    DataTextField="CommunityRole" DataField="CommunityRole" DataType="System.String" HeaderText="Ruolo"
    ColumnGroupName="Operatività" ShowFilterIcon="true" FilterImageToolTip="" AutoPostBackOnFilter="true"
    CurrentFilterFunction="StartsWith" ConvertEmptyStringToNull="true" FilterControlWidth="80%"
    AllowCustomEntry="true" AllowTokenEditing="false" Filter="StartsWith" InputType="Text"
    SelectionMode="Single" ItemStyle-CssClass="RadGridBatchEditInput"></telerik:GridAutoCompleteColumn>

    Of course this is a complete implementation so you can remove ItemStyle-CssClass or ColumnGroupName or whatever you don't use.
    One thing to note here is that AutoPostBackOnFilter="true" doesn't work with GridAutoCompleteColumn, infact pressing "enter" or tabbing out or pressing the filter icon has no auto postback effect at all.

    To prevent triggering when characters < n you have to add this JS function in the aspx page (more on this in the code behind section)

    <script type="text/javascript">
        function AutoCompleteOnClientRequesting(sender, args) {
            if (args.get_text().length < 3) {
                args.set_cancel(true);
            }
        }
    </script>

    The ObjectDataSources (you could use other data sources as well) are declared that way:

    <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="Eva.Bll.Core.EvaUsersManager" SelectMethod="GetAutoCompleteUserName">
            <SelectParameters>
                <asp:Parameter Name="rowsNumber" Type="Int32" />
                <asp:Parameter Name="filterSql" Type="String" />
                <asp:Parameter Name="contains" Type="Boolean" />
            </SelectParameters>
    </asp:ObjectDataSource>
    <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" TypeName="Eva.Bll.Core.EvaUsersManager" SelectMethod="GetAutoCompleteCommunityRoles">
            <SelectParameters>
                <asp:Parameter Name="rowsNumber" Type="Int32" />
                <asp:Parameter Name="filterSql" Type="String" />
                <asp:Parameter Name="contains" Type="Boolean" />
            </SelectParameters>
    </asp:ObjectDataSource>

    This is my business object, whose method accepts the listed parameter to specify max number of rows returned, the filter string and if the suggestion list should be filtered with "StartsWith" (default) or "Contains".

    In the code behind is where all the magic happens:

    Protected Sub RadGrid1_ItemCreated(sender As Object, e As GridItemEventArgs) Handles RadGrid1.ItemCreated
        ' Tweak filter items
        If TypeOf e.Item Is GridFilteringItem Then
            Dim fltItem As GridFilteringItem = DirectCast(e.Item, GridFilteringItem)
     
            ' Autocomplete filter column fix to select only N records in the DB
            Dim autoCompleteUserName As RadAutoCompleteBox = DirectCast(fltItem("UserName").Controls(0), RadAutoCompleteBox)
            Dim autoCompleteCommunityRole As RadAutoCompleteBox = DirectCast(fltItem("CommunityRole").Controls(0), RadAutoCompleteBox)
            autoCompleteUserName.OnClientRequesting = "AutoCompleteOnClientRequesting"
            autoCompleteCommunityRole.OnClientRequesting = "AutoCompleteOnClientRequesting"
     
            AddHandler autoCompleteUserName.DataSourceSelect, New Telerik.Web.UI.AutoCompleteBoxDataSourceSelectEventHandler(AddressOf RadAutoCompleteBox1_DataSourceSelect)
            AddHandler autoCompleteCommunityRole.DataSourceSelect, New Telerik.Web.UI.AutoCompleteBoxDataSourceSelectEventHandler(AddressOf RadAutoCompleteBox2_DataSourceSelect)
        End If
    End Sub



     Protected Sub RadAutoCompleteBox1_DataSourceSelect(sender As Object, e As Telerik.Web.UI.AutoCompleteBoxDataSourceSelectEventArgs)
         ' Exit with no filter text or text length < 3
         If Not String.IsNullOrWhiteSpace(e.FilterString) AndAlso Len(e.FilterString) > 2 Then
             Dim autoCompleteBox As RadAutoCompleteBox = DirectCast(sender, RadAutoCompleteBox)
     
             ObjectDataSource1.SelectParameters("rowsNumber").DefaultValue = AutoCompleteMaxResults
             ObjectDataSource1.SelectParameters("filterSql").DefaultValue = e.FilterString
             ObjectDataSource1.SelectParameters("contains").DefaultValue = (autoCompleteBox.Filter = RadAutoCompleteFilter.Contains).ToString
         End If
     End Sub
     
     Protected Sub RadAutoCompleteBox2_DataSourceSelect(sender As Object, e As Telerik.Web.UI.AutoCompleteBoxDataSourceSelectEventArgs)
         ' Exit with no filter text or text length < 3
         If Not String.IsNullOrWhiteSpace(e.FilterString) AndAlso Len(e.FilterString) > 2 Then
             Dim autoCompleteBox As RadAutoCompleteBox = DirectCast(sender, RadAutoCompleteBox)
     
             ObjectDataSource2.SelectParameters("rowsNumber").DefaultValue = AutoCompleteMaxResults
             ObjectDataSource2.SelectParameters("filterSql").DefaultValue = e.FilterString
             ObjectDataSource2.SelectParameters("contains").DefaultValue = (autoCompleteBox.Filter = RadAutoCompleteFilter.Contains).ToString
         End If
     End Sub

    Note that AutoCompleteMaxResults is a costant in the page. You could easily implement a similar behaviour with a costant for minimum characters required to trigger the suggestion.

            Private Const AutoCompleteMaxResults As Integer = 10
            Private Const AutoCompleteMinTrigger As Integer = 3

    Just add a literal before the javascript event in your aspx page that you can render from code behind using the costant and declare a "var autoCompleteMinTrigger = ..."
    I'm sure there is a way to optimize this passing the ObjectDataSource as a reference and handling a single event and such. But I'm a newbie with OOP so suggestions are welcome.

    One last thing about the business object and DTO used. I use PetaPoco as a micro ORM but you could exploit Sql DataReaders or whatever method you like. My business object and DTO look like this:

        Public Function GetAutoCompleteUserName(rowsNumber As Integer, filterSql As String, contains As Boolean) As List(Of AutoCompleteUserName)
            ' Exit with no filter text or text length < 3
            If String.IsNullOrWhiteSpace(filterSql) OrElse Len(filterSql) < 3 Then
                Return New List(Of AutoCompleteUserName)
            End If
     
            Dim db As Eva.Dal.Core.EvaDb = DalHelpers.GetEvaDbIstance()
            Dim containsChar As String = String.Empty
     
            ' Default minimum rows
            If rowsNumber = 0 Then rowsNumber = 10
     
            ' Adjust Contains or StartsWith
            If contains Then containsChar = "%"
     
            Dim query As PetaPoco.Sql = New PetaPoco.Sql(String.Format("SELECT DISTINCT UserName FROM EVA_Users WHERE UserName LIKE '{0}@0%' ORDER BY UserName", containsChar), filterSql)
            Dim result As PetaPoco.Page(Of AutoCompleteUserName) = db.Page(Of AutoCompleteUserName)(1, rowsNumber, query)
     
            Return result.Items
        End Function
     
     
    <TableName("EVA_Users")> _
    <PrimaryKey("", autoIncrement:=False)> _
    <ExplicitColumns> _
    <Serializable()> _
    Partial Public Class AutoCompleteUserName
        Private mUserName As String
        <Column> _
        Public Property UserName As String
            Get
                Return mUserName
            End Get
            Set(value As String)
                mUserName = value
            End Set
        End Property
    End Class

    I hope this can be useful to someone.
     
    As a side note I'd like to point out a graphic inconsistency (at least with default skin, didn't try the others) between the AutoCompleteBox textbox and the other columns textboxes, as you can see from the screenshot that follows (mostly is about rounded corner with default skin): http://www.hakkar.it/columngfx.jpg
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017