C# Multiple comboBoxes used in MySQL Where

Question

Im trying to learn using MySQl with C# so im creating app that allow sorting and adding data to my tables.

That is code that i use to show items selected only by my comboBox_1 value. But now i want to add 2nd comboBox and show data that meets both conditions. The problem is i have no idea how to get there. I thought using multiple ifs that check if combobox have any items selected but my code is looking monstrous then.

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if(comboBox1.SelectedIndex >= 0)
            {
                MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM narzedzia.narzedzia where status='" + comboBox1.Text + "'", Program.connection);
                if (Program.connection.State == ConnectionState.Closed) { Program.connection.Open(); }
                

                DataSet ds = new DataSet();
                adapter.Fill(ds, "narzedzia");
                dataGridView1.DataSource = ds.Tables["narzedzia"];
                Program.connection.Close();
            }
            
        }```

[Why should you use parameters](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) — HoneyBadger, Jun 22 '22 at 18:14
Also don't try to use the same connection throughout your app. It breaks ADO.Net connection pooling, makes things slower and use more memory, and can create bottlenecks or force things to be serial that could have been parallel or async. Instead, it really is more efficient to allocate a new connection object for most queries and only reuse the same connection string. — Joel Coehoorn, Jun 22 '22 at 18:46

Bryan Dellinger · Accepted Answer · 2022-06-22T18:50:15.090

I usually start with a basic select statement ( I use where 1 = 1 so I can just start adding and statements when my conditions are met)

so maybe something like this

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM narzedzia.narzedzia where 1 = 1 ");
    
    if(comboBox1.SelectedIndex >= 0){
        adapter.SelectCommand = adapter.SelectCommand + " and status = @status ";
        adapter.SelectCommand.Parameters.AddWithValue("@status", comboBox1.Text );
    }
    
    if(comboBox2.SelectedIndex >= 0){
        adapter.SelectCommand = adapter.SelectCommand + " and color = @color ";
        adapter.SelectCommand.Parameters.AddWithValue("@color", comboBox2.Text );
    }
      
    DataSet ds = new DataSet();
    adapter.Fill(ds, "narzedzia");
    dataGridView1.DataSource = ds.Tables["narzedzia"];     
}

Oh i didnt know adapter.SelectComand. Ty :D – Arui Jun 22 '22 at 21:22 — Arui, Jun 22 '22 at 21:22

C# Multiple comboBoxes used in MySQL Where

1 Answers1