This is a migrated thread and some comments may be shown as answers.

Limit GridAutoCompleteColumn size and server side filtering

4 Answers 185 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Massimiliano
Top achievements
Rank 1
Massimiliano asked on 10 Jul 2013, 08:04 PM
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.

4 Answers, 1 is accepted

Sort by
0
Vasil
Telerik team
answered on 15 Jul 2013, 10:58 AM
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.
0
Massimiliano
Top achievements
Rank 1
answered on 15 Jul 2013, 12:00 PM
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.

0
Massimiliano
Top achievements
Rank 1
answered on 18 Jul 2013, 01:28 PM
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
0
Massimiliano
Top achievements
Rank 1
answered on 19 Jul 2013, 03:30 PM
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
Tags
Grid
Asked by
Massimiliano
Top achievements
Rank 1
Answers by
Vasil
Telerik team
Massimiliano
Top achievements
Rank 1
Share this question
or