Autosuggest TextBox from database column in Windows Forms

• Download source

Introduction

In this article we will create a TextBox that will suggest names from a SQL Server database column FirstName. Typing first few characters will show matching names from the FirstName column as dropdown.

Using the code

Create a new Windows Forms Application and add a TextBox and a Label on Form1.

Set the following properties of the TextBox.

Property Name	Value
(Name)	txtFirstName
AutoCompleteSource	CustomSource
AutoCompleteMode	SuggestAppend

AutoCompleteSource property sets the source for auto complete data. It can be set to a AutoCompleteSource enumeration, FileSystem, HistoryList, RecentlyUsedList, AllUrl, AllSystemSources, FileSystemDirectories, CustomSource or None. As we are getting our own data we set it to CustomSource.

AutoCompleteMode property defines how text is suggested in the TextBox. It can be set to a AutoCompleteMode enumeration, Append, Suggest, SuggestAppend, None. Suggest displays all the suggestions as dropdown. Append displays first value of the suggestion appended or selected in the TextBox, other values can be navigated using arrow keys. SuggestAppend displays suggested values as dropdown and first value appended in the TextBox.

Write following code in the Load event of Form1

private void Form1_Load(object sender, EventArgs e)
{
    string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(ConString))
    {
        SqlCommand cmd = new SqlCommand("SELECT FirstName FROM Employees", con);
        con.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        AutoCompleteStringCollection MyCollection = new AutoCompleteStringCollection();
        while (reader.Read())
        {
            MyCollection.Add(reader.GetString(0));
        }
        txtFirstName.AutoCompleteCustomSource = MyCollection;
        con.Close();
    }
}

Here, first we get we get connection string from App.Config file in ConString variable. Then using SqlDataReader add FirstName values to a AutoCompleteStringCollection object MyCollection. AutoCompleteCustomSource accepts AutoCompleteStringCollection object.

Now when you run the application and type some text in the TextBox, you will get output as in the above figure.

History

• 4th April, 2012: First version.