C#: complete ComboBox

Article
1/17/2024

Introduction

This article will explain how to properly setup a Windows forms ComboBox that implements auto completely functionality. Many developers will implement auto complete functionality which provides a list of items for a user to select from by clicking into a ComboBox and begin typing to quickly narrow down a selection.

Background

A typical example, a ComboBox is setup for a list of states in the USA. For this requirement providing a list of states as strings is perfectly acceptable as state names have not changed.

Another example might be for a patient intake at a hospital where the items are department names. In this case a department name may change. If a developer provides a list of department names that are inserted into a database then later on one or more department names changes someone must change the names in fields within one or more tables or when reports are required alter the reports to satisfy names of departments that have changed.

A better solution would be to create a table in the database which is a reference table which contains a primary key field and a department field at the very least. In the table(s) which use departments to complete a record the department primary key is used rather than the department name. This means that there is no issues with modifying existing records to change department names in patient records.

Another consideration is, how many items will be needed to populate a ComboBox? The answer for common reference tables is more than two and less than several hundred. When dealing with over one hundred records a different solution may be needed. As the amount of items in a ComboBox grow (and this is with implementing auto complete) its critical to have lean code to populate the ComboBox which means tossing the idea of a DataSet or DataTable as they have baggage which is not required for auto complete functionality.

Source code updated 01-18-2019

Implementation

The following provided simple steps to setup a ComboBox with auto complete functionality working with several SQL-Server database tables. What is not covered is permitting users to add item(s) to the list populated from the database. If you need to provide the ability to handle adding items to the current list then handle the KeyDown event, check for the "Enter" key, search the backend table, if the item does not exists then add the item to the database table. Or before your form closes pass the list back to the database and check for new items. If new items are found add them to the database table.

For this exercise a patient is being admitted to a hospital, the first items needed are the person's first and last name, gender and which department they will be sent too.

In the first image above the ComboBox displays all department names. In the second image "p" has been entered which narrows down the search to all departments that start with "p". The third image shown the result of adding a new patient. In the DataGridView primary keys are displayed for the new patient record, the gender primary key for "female" and the department primary key for "pain management clinics". The DataGridView is only for ensuring the proper data has been entered.

Backend operations

All data operations are in a class project. There are classes for both gender and departments.

namespace BackEndLibrary
{
  public class  Gender
  {
    public int  Identifier { get; set; }
    public string  Value { get; set; }
    public override  string ToString()
    {
      return Value;
    }
  }
}

namespace BackEndLibrary
{
  public class  Patient
  {
    public int  Id { get; set; }
    public string  FirstName { get; set; }
    public string  LastName { get; set; }
    public int  GenderIdentifier { get; set; }
    public int  DepartmentIdentifier { get; set; }
  }
}

Outline for the data class which follows:

The function Departments is responsible for reading the primary key and department name for each record in the department reference table into a Dictionary of string/int where the (key) is the department name and the (value) is the primary key for a department. These departments will be assigned to a ComboBox on a form.

The function Genders reads all gender records into a List of Gender which will populate the gender ComboBox on the form.

The function AddPatient accepts a patient instance which will be inserted into the backend database. On success the patient instance passed in will have the new primary key.

Patient class

namespace BackEndLibrary
{
  public class  Patient
  {
    public int  Id { get; set; }
    public string  FirstName { get; set; }
    public string  LastName { get; set; }
    public int  GenderIdentifier { get; set; }
    public int  DepartmentIdentifier { get; set; }
  }
}

Backend data class

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
 
namespace BackEndLibrary
{
  /// <summary>
  /// Responsible for reading departments into a Dictionary suitable
  /// for populating a ComboBox to be used with AutoComplete functionality.
  /// 
  /// Reads Gender types for use in a ComboBox w/o AutoComplete functionality.
  /// 
  /// Add a new patient record with information collected by a form that
  /// in the case for AutoComplete functionality gets the primary key for the
  /// selected value in the department ComboBox.
  /// 
  /// Each method is rigged with try/catch along with setting properties of
  /// the base class BaseExceptionHandler where there are properties exposed
  /// to check for issues.
  /// 
  /// </summary>
  public class  AutoCompleteOperations : BaseSqlServerConnections
  {
    public AutoCompleteOperations()
    {
      DefaultCatalog = "PatientTrackerDatabase";
    }
 
    /// <summary>
    /// Read in all departments from database
    /// </summary>
    /// <returns>Dictionary of deparment names and primary key</returns>
    public Dictionary<string,int> Deparments()
    {
       
      var results = new  Dictionary<string,int>();
      var selectStatement = "SELECT DepartmentName, DepartmentIdentifier  FROM dbo.Department";
 
      try
      {
        using (var cn = new SqlConnection() {ConnectionString = ConnectionString})
        {
          using (var cmd = new SqlCommand() {Connection = cn, CommandText = selectStatement})
          {
 
            cn.Open();
 
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
              results.Add(reader.GetString(0),reader.GetInt32(1));
            }
          }
        }
      }
      catch (Exception e)
      {
        mHasException = true;
        mLastException = e;
      }
 
      return results;
    }
    /// <summary>
    /// Return all gender types from database
    /// </summary>
    /// <returns>all gender names and primary keys</returns>
    public List<Gender> Genders()
    {
      var results = new  List<Gender>();
 
      var selectStatement = "SELECT GenderIdentifier,Gender FROM dbo.GenderTypes";
 
      try
      {
        using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
        {
          using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement })
          {
            cn.Open();
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
              results.Add(new Gender() { Identifier = reader.GetInt32(0), Value = reader.GetString(1)});
            }
          }
        }
      }
      catch (Exception e)
      {
        mHasException = true;
        mLastException = e;
      }
      return results;
    }
    /// <summary>
    /// Add new patient and return new primary key if no errors
    /// </summary>
    /// <param name="pPatient"></param>
    /// <returns>success of adding a patient</returns>
    public bool  AddPatient(Patient pPatient)
    {
      var result = false;
 
      var insertStatement = "INSERT INTO dbo.Patient "  +
                 "(FirstName,LastName,GenderIdentifier,DepartmentIdentifier) " +
                 "VALUES (@FirstName,@LastName,@GenderIdentifier,@DepartmentIdentifier); " + 
                 "SELECT CAST(scope_identity() AS int);";
 
      try
      {
        using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
        {
          using (var cmd = new SqlCommand() { Connection = cn, CommandText = insertStatement })
          {
            cmd.Parameters.AddWithValue("@FirstName", pPatient.FirstName);
            cmd.Parameters.AddWithValue("@LastName", pPatient.LastName);
            cmd.Parameters.AddWithValue("@GenderIdentifier", pPatient.GenderIdentifier);
            cmd.Parameters.AddWithValue("@DepartmentIdentifier", pPatient.DepartmentIdentifier);
 
            cn.Open();
 
            pPatient.Id = Convert.ToInt32(cmd.ExecuteScalar());
 
            result = true;
          }
        }
      }
      catch (Exception e)
      {
        mHasException = true;
        mLastException = e;
      }
 
      return result;
    }
  }
}

Outline for the form load/shown operations.

In form shown event the ComboBox is setup to perform auto complete operations by setting AutoCompleteMode to Suggest and AutoCompleteSource to the list items setup in the DataSource of the ComboBox which are populated by the backend class method Departments.

A BindingSource, private to the form has it's DataSource set to the Dictionary returned from the Departments method.

DisplayMember of the ComboBox is set to "key" and the ValueMember is set to "value" which are two properties of a KeyValuePair within the Dictionary setup to the BindingSource which becomes the data source of the ComboBox.

Adding a new patient record

In the sole button which is responsible for adding a new record to the backend patient database table the first step is to validate all fields have values and for the Department Combobox the selected value is valid e.g. is the user attempts to add an item that is not present in the list the selected item will be null so this needs to be checked. If all values to be sent to the database table are valid they are passed to the backend data class and the record is inserted.

Summary

With the information and code samples provides along with advice to keep items in a ComboBox (or TextBox) list to a minimum and using light weight transports, a class instance rather than a heavy weight container such as a DataSet or DataTable you have the tools to provide users with auto complete functionality.

Source code

The code samples are provided in Microsoft Visual Studio 2017 using Microsoft SQL-Server located on GitHub.

Steps to build the solution

Edit script.sql located in the BackendLibrary project for FILENAME. FILENAME points to where the database will be created were the current location may be different from the location current in the script.
Create PatientTrackerDatabase database using SQL script within Visual Studio or SQL-Server Management Studio.
Build the solution.
Run the project FrontEnd.

Share via