Autosuggest TextBox From Database Column in Windows Forms

Introduction

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

Step 1:  Create a new Windows Forms Application and add a TextBox and a Label on the Form1 as in the above figure.

Step 2:   Set the properties of the TextBox.

Property Name	Value
(Name)	txtFirstName
AutoCompleteSource	CustomSource
AutoCompleteMode	SuggestAppend

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

The 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 the 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 the first value is appended in the TextBox.

Step 3:   Write the 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 a connection string from the App.Config file in the ConString variable. Then using SqlDataReader add FirstNames to a AutoCompleteStringCollection object MyCollection. AutoCompleteCustomSource accepts AutoCompleteStringCollection object.