Visual Studio C#: How to generate combo box dropdown list based on Access table value.

Question

For example, I have two Access tables(attached screenshot): Place and Product. How should I write code for below code blocks.

Is there any way to put OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString)/Cn Open()/Cn Close() in somewhere else? So that I don't need to repeat it for each code block.

Thanks.

How can I replace below comment portion with real code?

        private void comboBoxProduct_Click(object sender, EventArgs e)  
        {  
            comboBoxProduct.Items.Clear();  
  
  
            OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString);  
  
            Cn.Open();  
            //There is Access table Product, which has only one field, and each record is already unique  
  
 //SELECT * From Product, display them in ComboBoxProduct  
  
            Cn.Close();  
  
        }  
  
  
  
        private void comboBoxCountry_Click(object sender, EventArgs e)  
        {  
            comboBoxCountry.Items.Clear();  
  
  
            OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString);  
  
            Cn.Open();  
            //There is Access table Place, which has three fields  
  
 //SELECT DISTINCT Country From Place, display them in ComboBoxCountry  
  
            Cn.Close();  
  
        }  
  
  
 private void comboBoxCounty_SelectedIndexChanged(object sender, EventArgs e)  
        {  
            comboBoxState.Items.Clear();  
  
  
            OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString);  
  
            Cn.Open();  
            //There is Access table Place, which has three fields  
  
 //SELECT DISTINCT State From Place WHERE County=ComboBoxCounty.Value, then display them in ComboBoxState  
 //User is allowed to enter his/her own value for Country. E.g. user is allowed to enter 'Mexico' as Country, while 'Mexico' is not in database table Place, so for this case, no items should be generated for comboBoxState. As long as the SQL statement returns nothing, then no item needs to be added to comboBoxState.  
  
            Cn.Close();  
        }  
  
  
 private void comboBoxState_SelectedIndexChanged(object sender, EventArgs e)  
        {  
            comboBoxCity.Items.Clear();  
  
  
            OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString);  
  
            Cn.Open();  
            //There is Access table Place, which has three fields  
  
 //SELECT DISTINCT City From Place WHERE County=ComboBoxCounty.Value AND State=ComboBoxState.Value, then add those items in ComboBoxCity  
 //User is allowed to enter his/her own value for Country/State. E.g. user is allowed to enter 'Mexico' in Country, while 'Mexico' is not in database table Place; or user may enter FL as US state, while FL is not in the table. So for these examples, no items should be generated for comboBoxCity. As long as the SQL statement returns nothing, then no item needs to be added to comboBoxCity.  
  
            Cn.Close();  
        }  

Answer 1

You can use a singleton as I suggested in a reply to another question you asked about a database path. Originally done for SQL-Server (original source) but by changing the provider works on any data connection.

Usage

DataConnections.Instance.Connection()

Singleton for above

using System;
using System.Data;
using System.Data.Odbc;

namespace SingletonExample1.Classes
{
    public sealed class DataConnections
    {
        private static readonly Lazy<DataConnections> 
            Lazy = new Lazy<DataConnections>(() => new DataConnections());

        public static DataConnections Instance => Lazy.Value;

        public OdbcConnection Connection()
        {
            OdbcConnection connection = null;

            try
            {
                connection = connection = new OdbcConnection(RuntimeSettings.Instance.ConnectionString);
            }
            catch (Exception)
            {
                // ignored
            }

            if (connection == null || connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed)
            {
                try
                {
                    connection?.Dispose();
                }
                catch (Exception)
                {
                    // ignored
                }

                connection = new OdbcConnection();
            }

            if (connection.State != ConnectionState.Closed) return connection;

            connection.ConnectionString = RuntimeSettings.Instance.ConnectionString;

            connection.Open();

            return connection;
        }

    }
}

Class to read app.config

using System.Configuration;
using System.IO;
using System.Reflection;

namespace SingletonExample1.Classes
{
    public class ApplicationSettings
    {
        public static string GetDatabasePath() => GetSettingAsString("DatabasePath");
        public static void SetDatabasePath(string value) => SetValue("DatabasePath", value);
        public static string DatabaseConnectionString() => GetSettingAsString("ConnectionString");
        public static string GetSettingAsString(string configKey) => ConfigurationManager.AppSettings[configKey];
        public static void SetValue(string key, string value)
        {
            var applicationDirectoryName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            var configFile = Path.Combine(applicationDirectoryName, 
                $"{Assembly.GetExecutingAssembly().GetName().Name}.exe.config");
            var configFileMap = new ExeConfigurationFileMap { ExeConfigFilename = configFile };
            var config = ConfigurationManager.OpenMappedExeConfiguration(configFileMap, 
                ConfigurationUserLevel.None);

            config.AppSettings.Settings[key].Value = value;
            config.Save();

            Reload();

        }
        public static void Reload()
        {
            ConfigurationManager.RefreshSection("appSettings");
        }
    }
}

Class to get setting including a live data connection

using System;

namespace SingletonExample1.Classes
{

    public sealed class RuntimeSettings
    {
        private static readonly Lazy<RuntimeSettings> Lazy =
            new Lazy<RuntimeSettings>(() => new RuntimeSettings());


        public static RuntimeSettings Instance => Lazy.Value;

        public string DatabasePath
        {
            get => ApplicationSettings.GetDatabasePath();
            set
            {
                ApplicationSettings.SetDatabasePath(value);
                OnDatabasePathChangedEvent?.Invoke();
            }
        }

        public string ConnectionString => ApplicationSettings.DatabaseConnectionString();

        public delegate void OnDataPathChanged();
        public static event OnDataPathChanged OnDatabasePathChangedEvent;

    }
}

configuration file

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
    </startup>
  <appSettings>
    <add key="DatabasePath" value="C:\Users\QR\Documents\UserDatabase.accdb" />
    <add key="ConnectionString" value="TODO" />
  </appSettings>
</configuration>

Answer 2

Use global variables, like :

string sConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\Database1.accdb;";
OdbcConnection conn = null;

and put the connection only once in Form1_Load :

private void Form1_Load(object sender, EventArgs e)
{
    conn = new OdbcConnection(sConnectionString);
    try
    {
        conn.Open();
    }
    catch (Exception ex)
    {
        System.Windows.Forms.MessageBox.Show(ex.Message);
    }
}

and disconnection for example in FormClosed

private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
    if (conn != null)
        conn.Close();
}

then you can use conn in your Combo Boxes clicks.