C# WinForms: create automatically if statement connected to access database

Question

I am new to C# and need some guidance!

short summary:

I have an application with few forms that is connected to the Access database. Everything is working fine! The user can select the needed item from the combo box that is shown in the label on the next form. Additionally the elements of the table are shown in the datagridview2 on the same form. Every item from the combo box is connected to a different table in the Access database:

private void frmData_Load(object sender, EventArgs e)
{
    lblItem.Text = Item;

    string connectionString = null;
    OleDbConnection con;
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\".\\Database_Example.accdb\"";
    con = new OleDbConnection(connectionString);

    if (lblItem.Text == "X")
    {
        OleDbCommand cmd2 = new OleDbCommand("SELECT ID, Column1, Column2 FROM X", con);
        OleDbDataAdapter sda = new OleDbDataAdapter(cmd2);
        DataTable td = new DataTable();
        sda.Fill(td);
        dataGridView2.DataSource = td;
    }

    if (lblItem.Text == "Y")
    {
        OleDbCommand cmd = new OleDbCommand("SELECT ID, Column1, Column2 FROM Y", con);
        OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
        DataTable td = new DataTable();
        sda.Fill(td);
        dataGridView2.DataSource = td;
    }
}

Now I want to use my application to add new tables to the database. Therefore there are bunch of different approaches on the web.

Problem: I´m looking for an option to automatically create new if-statements once I´ve added new tables in the database. For example for items "Z"; "A"; "B"; ...

Is there a way to do it? Or do I need a different approach?

Greetings :)

Answer 1

We get this question a few times a month and the answer is always the same:

You really need ONE table, with an additional column for your Z, A, B, etc. Make this the first field of the primary key.

Now this:

if (lblItem.Text == "X")
{
    OleDbCommand cmd2 = new OleDbCommand("SELECT ID, Column1, Column2 FROM X", con);
    OleDbDataAdapter sda = new OleDbDataAdapter(cmd2);
    DataTable td = new DataTable();
    sda.Fill(td);
    dataGridView2.DataSource = td;
}
// etc

Becomes this:

var td = new DataTable();
using (var con = new OleDbConnection("connection string here"))
using (var cmd = new OleDbCommand("SELECT ID, Column1, Column2 FROM MyTable WHERE Key = ? ", con))
using (var sda = new OleDbDataAdapter(cmd2))
{
    cmd.Parameters.Add("?", OleDbType.NChar, 3).Value = lblItem.Text;
    sda.Fill(td);
}
 

No if statement required.

Additionally, do NOT try to re-use your connection object throughout a form instance like that. It really is more efficient to create a new connection for each call to the database.