List in query Mysql in C#

Question

I want to use my list in my query,so far I have this:

public List<string> penalties = new List<string>();
while (myReader.Read())
{
    penalties.Add((myReader["Area"].ToString()));
}

And this is to populate my datagridview. But it doesn't work.

foreach(string area in penalties)
{
   string query = "SELECT * FROM tblpenalty WHERE Area='" + area + "';";
   using (connection)
   {
      using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection))
      {
         DataSet ds = new DataSet();
         adapter.Fill(ds);
         dataGridView1.DataSource = ds.Tables[0];
         dataGridView1.AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders);
      }
   }
}

Seems your query doesn't return proper results. Again avoid query string concatenation, use parameters instead. — Tetsuya Yamamoto, Sep 13 '17 at 03:54
Possible duplicate of [How do you use the MySql IN clause](https://stackoverflow.com/questions/6804657/how-do-you-use-the-mysql-in-clause) — mjwills, Sep 13 '17 at 04:09

score 1 · Accepted Answer · answered Sep 13 '17 at 04:42

This is what I tried from this solution & this solution to build parameterized query for a List<string> using IN clause, with help of MySqlDataParameter.SelectCommand usage:

string query = "SELECT * FROM tblpenalty WHERE Area IN ({0})";
string[] penaltiesArray = penalties.ToArray(); // list converted to array as in /a/6804883/
string[] parameters = penalties.Select((x, n) => "@area" + n.ToString()).ToArray();
query = string.Format(query, string.Join(",", parameters));
using (connection)
{
    using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection))
    {
        // iterate through the list & set parameters to data adapter
        for (int i = 0; i < parameters.Length; i++)
        {
            // use MySqlDataParameter.SelectCommand directly without additional MySqlCommand
            // and use MySqlDbType depending on data type used in target column
            adapter.SelectCommand.Parameters.Add(parameters[i], MySqlDbType.VarChar).Value = penaltiesArray[i];
        }
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        dataGridView1.DataSource = ds.Tables[0];
        dataGridView1.AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders);
    }
}

Note that your example query will executed & the results inserted to DataGridView repeatedly due to foreach loop, so that you didn't get proper results. Using IN clause to hold multiple values is much more preferred solution.

Akash KC · Answer 2 · 2017-09-13T14:22:18.377

Looking your code, I strongly believe you would like to populate penalty in datagrid which area belongs from given list of penalties. If that is requirement, you can have IN query.

Following code will populate all penalty which belongs from given list of penalty :

List<string> penalties = new List<string>();
string joinedPenalty = String.Join(",", penalties);
string query = "SELECT * FROM tblpenalty WHERE Area IN (" + joinedPenalty + ");";
using (connection)
{
    using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection))
    {
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        dataGridView1.DataSource = ds.Tables[0];
        dataGridView1.AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders);
        }
    }
}

When passing raw query with value, you need to pass value with parameterized your query as it prevents sql injection. To have parameterized IN query, you can take reference from following example.

Parameterized query approach to prevent SQL injection :

var penalties = new List<string>();
var parameters = new string[penalties.Count];
var cmd = new SqlCommand();
for (int i = 0; i < penalties.Count; i++)
{
    parameters[i] = $"@Area{i}";
    cmd.Parameters.AddWithValue(parameters[i], penalties[i]);
}
string query = $"SELECT * FROM tblpenalty WHERE Area IN ({string.Join(", ", parameters)});";
using (connection)
{
    using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection))
    {
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        dataGridView1.DataSource = ds.Tables[0];
        dataGridView1.AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders);
    }
}

@JoelCoehoorn : Thanks for commenting about sql injection. I've updated my answer. I just posted my answer to give idea about approaching OP requirement in simpler way so missed sql injection stuff . — Akash KC, Sep 13 '17 at 04:10
downvoted till you fix YOUR code not to be vulnerable, not good enough to say, well mine is insecure, here's a link to the way to do it properly — Keith Nicholas, Sep 13 '17 at 04:17

List in query Mysql in C#

2 Answers2