Autocomplete Combo Box in C# with SQL values

Posted by Kyprulez in Programming | 13 comments

In the following article I’m going to show you how to make an autocompleting Combo Box in C#, that uses a SQL database as its custom source of values.First, I’m going to display the whole implementation and afterwards explain the code in detail.

Database Structure

Table name: keywordsList

Column Name	Data Type
item	nchar(30)

Implementation

public partial class Autocomplete : Form
    {
        SqlConnection connection;
        SqlConnectionStringBuilder builder;
        SqlCommand command;
        SqlParameter param;
        SqlDataReader reader;

        string[] suggestionsList;
        int dbRecords;

        public Autocomplete()
        {
            InitializeComponent();

            string keyword = comboBox1.text;
            connection = new SqlConnection();

            try
            {
                builder = new SqlConnectionStringBuilder();
                builder.DataSource = @".SQLEXPRESS";
                builder.AttachDBFilename = @"Your path goes here ...";
                builder.IntegratedSecurity = true;
                builder.UserInstance = true;

                connection.ConnectionString = builder.ConnectionString;

                connection.Open();

                SqlCommand command = new SqlCommand();
                command.CommandText =
                    "SELECT *
                     FROM keywordsList
                     WHERE item LIKE '%' + @keywordParam + '%'";
                command.Connection = connection;

                param = new SqlParameter("@keywordParam", keyword);
                command.Parameters.Add(param);

                reader = command.ExecuteReader();

                while (reader.Read()) dbRecords++;
                suggestionsList = new string[dbRecords];
                reader.Close();

                reader = command.ExecuteReader();

                int index = 0;

                while (reader.Read())
                {
                    string result = reader.GetString(0);
                    suggestionsList[index] = result;
                    index++;
                }

                reader.Close();
            }
            catch (SqlException e)
            {
                errorLabel.Text = e.Message;
            }
            finally
            {
                connection.Close();
            }

            comboBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            comboBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
            comboBox1.AutoCompleteCustomSource.AddRange(suggestionsList);
        }
    }

Code Explanation:

        SqlConnection connection;
        SqlConnectionStringBuilder builder;
        SqlCommand command;
        SqlParameter param;
        SqlDataReader reader;

        string[] suggestionsList;
        int dbRecords;

        public Autocomplete()
        {
            InitializeComponent();

            string keyword = comboBox1.text;
            connection = new SqlConnection();

            try
            {
                builder = new SqlConnectionStringBuilder();
                builder.DataSource = @".SQLEXPRESS";
                builder.AttachDBFilename = @"Your path goes here ...";
                builder.IntegratedSecurity = true;
                builder.UserInstance = true;

                connection.ConnectionString = builder.ConnectionString;

                connection.Open();

Ok, so we begin by declaring the SQL variables that we are going to use.Next, suggestionsList is the array that will hold the values from the database and dbRecords is going to represent the length of the array.
Initialize a new SQL connection and using the SqlConnectionStringBuilder object, create the connection string.Don’t forget to provide the path to your database file for the AttachDBFilename property.Finally, assign the connection string to the SQL connection object and open the connection.

                SqlCommand command = new SqlCommand();
                command.CommandText =
                    "SELECT *
                     FROM keywordsList
                     WHERE item LIKE '%' + @keywordParam + '%'";
                command.Connection = connection;

                param = new SqlParameter("@keywordParam", keyword);
                command.Parameters.Add(param);

                reader = command.ExecuteReader();

                while (reader.Read()) dbRecords++;
                suggestionsList = new string[dbRecords];
                reader.Close();

                reader = command.ExecuteReader();

                int index = 0;

                while (reader.Read())
                {
                    string result = reader.GetString(0);
                    suggestionsList[index] = result;
                    index++;
                }

                reader.Close();
            }
            catch (SqlException e)
            {
                errorLabel.Text = e.Message;
            }
            finally
            {
                connection.Close();
            }

Now create a new SqlCommand object and provide it with the CommandText and Connection properties.Remember, I’ve named my table “keywordsList”.You might want to change it if yours differs.
In order to fetch the rows identified by the SQL statement use the ExecuteReader method of the SqlCommand object to create the SqlDataReader object.First, use the Read() method to get the counter of the fetched rows.Now that you have this, use it to specify the length of your newly-created suggestionsList array.
With that set, re-create the SqlDataReader and this time, using the Read() method, extract the values from the database and place them into the suggestionsList array.The parameter of the GetString() method specifies which column to be read.Database work finished, you can now close the resources.

            comboBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            comboBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
            comboBox1.AutoCompleteCustomSource.AddRange(suggestionsList);

Let’s set the AutoCompleteMode to SuggestAppend.If you want you can also set it to either Suggest or Append, but SuggestAppend combines both of them.Next, choose CustomSource as your AutoCompleteSource.And because we use a CustomSource, we have to fill the AutoCompleteCustomSource property.Use the AddRange() method, that takes a string array as a parameter, to populate the autocomplete suggestions list with the values retrieved from the database(e.g. suggestionsList array).
Tip: The Text Box control also supports autocompletion using the same properties described above.

I hope you enjoyed the article.If you have any questions or feedback, I’m looking forward to hearing from you.Thanks for reading.

Related posts:

1. Sql Update if exists else Insert
2. Extracting a random row from sql database