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.
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
0
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
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.
"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:
The object data source:
In the ItemCreated event of radgrid I SUCCESSFULLY attach to the DataSourceSelect event of AutoCompleteBox
And then in the RadAutoCompleteBox1_DataSourceSelect 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?
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:
Thanks for your kind support as always
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:
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)
The ObjectDataSources (you could use other data sources as well) are declared that way:
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:
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:
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
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