Autosuggest TextBox from database column in Windows Forms

In this article we will see how to create an autosuggest TextBox that will suggest data from a SQL Server database column

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. Final output will be like following

Step 1:

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

Step 2:

Set 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, AlSystemSources, 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.

Step 3:

Write code in 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 FirstNames to a AutoCompleteStringCollection object MyCollection. AutoCompleteCustomSource accepts AutoCompleteStringCollection object.

Thanks. Happy Programming!

One thought on “Autosuggest TextBox from database column in Windows Forms

Leave a Reply

Your email address will not be published. Required fields are marked *