I need to provide an auto-complete feature that suggests/appends based on 150k records. The winform that will provide this feature will be opened around 5k times per day by 30+ users in the office. The data is fairly volatile meaning new records get added fairly often throughout the day so I can't cache the dataset for too long without running the risk of someone not seeing recently added records, which can actually cause some legal issues in the industry we're in.
I'm wondering what's the best way to implement this feature. One idea is to handle the TextChanged event and whenever the event is triggered a DB call would be made to bring back a subset of data that matches what the user has typed upto that point and then bind that subset to the UltraComboEditor control.
Can anyone think of a more efficient way to accommodate this scenario?
I can see how that would be a problem. But I'm not sure I see any simple solution.
If you are going to try loading the data as the user types, instead of loading it all at once, then I think you might be better off creating your own combo instead of using the UltraCombo with the built-in dropdown. That way you will have more control over the auto-complete functionality.
This isn't as much work as it may, at first, seem. You can use UltraComboEditor, hide the default dropdown button and use the EditorButtonsRight collection to add a DropDownEditorButton. then you can put any control you want (like an UltraGrid or UltraListView) on the dropdown. You will then have to handle what happens when the combo drops down or when the user makes a selection on the dropdown control. But it gives you a lot more control about what happens to the selection and the auto-complete.
There's a sample of this technique here:
UltraComboEditor - Can I Use a Custom Control for the DropDown? - Infragistics Community
Thanks for your reply. My primary concern is that the data is volatile and therefore the entire dataset would have to be refetched from db every time the UI form is opened (total approx 5000 times per day). Let's assume it takes 1 sec for the db to fetch all 150k records each time, and also assume 1 sec for the data to cross the wire, and another sec to load in memory. That's 3 secs each time someone opens the UI form. Multiplying it by 5,000 times per day would mean that total 250 min/day would be spent on just populating the UltraComboEditor. And this is assuming each step would only take 1 sec -- sometimes it will take less, sometimes longer depending on how busy the db server is and also how busy our network pipe is.
The AutoCompleteMode functionality wasn't really designed to handle a case like there where the list data is continuously changing.
What is your concern about populating the list with all of the data up-front? Is it a question of memory usage? Or performance?
If memory usage is a problem, then I can understand that. You might have more luck creating your own combo in that case. Not from scratch, of course. But you could use UltraComboEditor, hide the dropdown button, and then add a DropDownEditorButton to the ButtonsRight collection and put a WinGrid or ListView or something on the dropdown. There's some work involved, as you would have to handle what happens when the user types, drops down the list, or selects a value. But it would give you a lot more control over the behavior. There's a more detailed description of this technique here: UltraComboEditor - Can I Use a Custom Control for the DropDown? - Infragistics Community
If performance is a concern, then I recommend using the UltraCombo instead of UltraComboEditor. As long as your DisplayMember field is IComparable, the UltraCombo will cache the data and do a binary search. So you can load the control with the entire set of data and the auto-complete functionality should be pretty fast. There is s small performance hit the first time the list is accessed, though, for when the combo builds the sorted list.
I did some prototyping and quickly ran into a series of challenges.
First of all I set AutoCompleteMode to SuggestAppend and set AlwaysInEditMode to true. Then in my TextChanged event handler, I added logic to make a db call that takes as input the text entered by the user. And then I set UltraComboEditor.DataSource to my data collection object Lastly I called UltraComboEditor.DropDown() to force open the dropdown list. So far so good.
I ran my app and entered one character and the dropdown list opened and showed matching results. It seems to have worked! Then when I entered my second character it replaced the first character because apparently the combobox had automatically highlighted the first character. After searching the forum I found a workaround and it also worked as expected. After that I was able to enter more characters without any issue and the suggest/append worked as expected. I thought I could move on but ran into yet another challenge. When I used the up/down arrow keys to select one of the visible records that's when everything went haywire. I realized that was because as soon as I hit the up/down key to select a record the TextChanged() event fired and now the combobox would try to make suggestions based on the selected record itself.... I think I can figure out a way around this too but can anyone think of a better way?