Use SQL Table Data As AutoCompleteCustomSource

Introduction

I wanted to create a simple AutoComplete source which pulled data from a SQL table and displayed the choices in a text box so when the user types, it brings up the possible choices. I got it to work but it was very slow so I loaded up JetBrains dot Trace and found a much faster way of getting this done.

The data I was trying to get was a list of IDs and store names from my Store table. The clients had entered the 4-digit store IDs with leading zeros (0004) but wanted to search by entering the store ID without the leading zeros (4).

Background

JetBrains dotTrace is a great tool for profiling resource hogging functions or variables. It can monitor CPU or memory usage and can give you resource statistics down to a single function in your code or variable in memory. I love it!

Using the Code

I will show you three methods I tried and show you the performance impact of doing this in different ways.

The first thing you will have to do is create your TextBox control and set the AutoCompleteMode property. You can select from:

• Suggest - Shows available choices in a drop-down interface
• Append - Doesn't show drop-down, instead automatically appends the rest of the first available choice to the text in the box
• SuggestAppend - Obviously, it does both...I like this one

Next, set the AutoCompleteSource property. It supports these sources out of the box:

• FileSystem
• HistoryList
• RecentlyUsedList
• AllUrl
• AllSystemSources
• FileSystemDirectories
• CustomSource
• ListItems

If you want to know more about these, here is a good article on them. For the purposes of this article, we are going to use CustomSource.

Now, in your code behind for the form's Load event, let's create some code to get the list of choices and bind it to the TextBox AutoCompleteCustomSources. The first method I tried looks like this:

For Each store As StoreDataSet.StoreRow In StoreDataSet.Store
    If store.StoreID.StartsWith("0") Then
        StoreIDTextBox.AutoCompleteCustomSource.Add(store.StoreID.TrimStart("0"))
    End If
    StoreIDTextBox.AutoCompleteCustomSource.Add(store.StoreID)
    StoreNameTextBox.AutoCompleteCustomSource.Add(store.StoreName)
Next store

So I've got a DataSet and I loop through each row in the Store table. For each row, I add the store ID and name to their respective TextBox AutoCompleteCustomSources. Also, if the StoreID starts with a "0", I trim the zeros from it and add that as well (so users gets the auto completion whether they type the leading zeros or not). Pretty simple, except it was SLOW!

So I loaded up the JetBrainz dotTrace to see what was causing the problem. I found that the AutoCompleteStringCollection.Add method had fired 1080 times taking 20 seconds to complete and 65.34%of the form load time! For some reason, the .NET folks made this method quite expensive.

Next I found that it was MUCH faster to create new AutoCompleteStringCollections, add the store IDs and names to those, and then assign them as the AutoCompleteCustomSources for the textboxes instead of adding them directly:

Dim storeIDList As New AutoCompleteStringCollection()
Dim storeNameList As New AutoCompleteStringCollection()
For Each store As StoreDataSet.StoreRow In StoreDataSet.Store
    If store.StoreID.StartsWith("0") Then
        storeIDList.Add(store.StoreID.TrimStart("0"))
    End If
    storeIDList.Add(store.StoreID)
    storeNameList.Add(store.StoreName)
Next store

StoreIDTextBox.AutoCompleteCustomSource = storeIDList
StoreNameTextBox.AutoCompleteCustomSource = storeNameList

I noticed right away that the form loaded very quickly now. I did another trace and found that this code is a 65.08% improvement!

I did a bit more tinkering with using LINQ instead of writing the loop code myself and was able to do this with 1 line of code (per textbox):

StoreIDTextBox.AutoCompleteCustomSource.AddRange_
	((From row In StoreDataSet.Store Select row.StoreID).ToArray)
StoreIDTextBox.AutoCompleteCustomSource.AddRange_
	((From row In StoreDataSet.Store Where row.StoreID.StartsWith("0") _
	Select row.StoreID.TrimStart("0"c)).ToArray)
StoreNameTextBox.AutoCompleteCustomSource.AddRange_
	((From row In StoreDataSet.Store Select row.StoreName).ToArray) 

The LINQ query gets all of the StoreIDs, converts them to an array, and uses the AddRange method to add them all to the AutoCompleteCustomSource in one call. The second line just adds a Where clause to get only the StoreIDs with zeros, trim the zeros, and add those as well. The third line does the same thing for store names.

I suspected that the LINQ query would be less efficient since it is basically doing the same thing I did but wrapped inside of a LINQ query. I ran another trace and found it was only .27% worse performing than the second example. I can live with this since I think it's more readable (and elegant).

Points of Interest

I think JetBrainz has a really GREAT product in dotTrace and it really helped me out in this case. LINQ is pretty dang sweet too.

History

• 12-21-09 - First revision