List<string> 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
– Tetsuya Yamamoto, Commented Sep 13, 2017 at 3:54

Tetsuya Yamamoto · Accepted Answer · 2017-09-13 04:42:55Z

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 · Accepted Answer · 2017-09-13 14:22:18Z

0

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);
    }
}

edited Sep 13, 2017 at 14:22

answered Sep 13, 2017 at 3:59

Akash KC

16.4k6 gold badges41 silver badges59 bronze badges

3 Comments

Joel Coehoorn Over a year ago

But this will be crazy vulnerable to sql injection attacks.

Akash KC Over a year ago

@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 .

Keith Nicholas Over a year ago

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

Collectives™ on Stack Overflow

List<string> in query Mysql in C#

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related