C# MySQL adding multiple of the same parameter with a loop

Question

Update: as the original question was essentially answered, I've marked this as complete.

I have a C# project in which I'd like to query a database. The SQL query will SELECT from a table, and in the WHERE clause I want to filter the results from a pre-defined list of values specified in C#.

List<string> productNames = new List<string >() { "A", "B", "C" };

foreach (name in productNames)
{
    string query = @"
        SELECT *
        FROM products
        WHERE name IN (@name);";

    // Execute query
    MySqlCommand cmd = new MySqlCommand(query, dbConn);
    cmd.Parameters.AddWithValue("name", name);

    MySqlDataReader row = cmd.ExecuteReader();
    while (row.Read())
    {
        // Process result
        // ...
    }
}

However I'm getting an error:

There is already an open DataReader associated with this Connection which must be closed first

Is it not possible then to use a for loop to add parameters this way to a SELECT statement?

sujith karivelil · Answer 1 · 2017-02-21T06:13:10.317

3

You are doing few things wrong. Let me point out them:

Everything is fine in the first iteration of the loop, when you are in second iteration the First Reader associated with the command remains opened and that result in current error.
You are using Where IN(..) you you can specify the values within IN as comma separated so there is no need to iterate through parameters.
You can use String.Join method to construct this values with a comma separator.

Now take a look into the following code:

List<int> productsIds = new List<int>() { 23, 46, 76, 88 };

string idInParameter = String.Join(",", productsIds);

// Create id as comma separated string
string query = "SELECT * FROM products WHERE id IN (@productId);";

MySqlCommand cmd = new MySqlCommand(query, dbConn);
cmd.Parameters.AddWithValue("@productId", idInParameter);

MySqlDataReader row = cmd.ExecuteReader();
while (row.Read())
{
    // Process result
    // ...
}

Please note if the id field in the table is not integers then you have to modify the construction of idInParameter as like the following:

idInParameter = String.Join(",", productsIds.Select(x => "'" + x.ToString() + "'").ToArray());

edited Feb 21 '17 at 06:13

answered Feb 21 '17 at 04:54

sujith karivelil

28,671
6
55
88

Thanks, this works. What if I wanted to use a List of strings instead, is it just the same? Would I have to enclose the parameter in quotes, eg.: ...WHERE id IN ('@myString'); ? – user2181948 Feb 21 '17 at 05:07
@user2181948: I have updated the answer, Please take a look – sujith karivelil Feb 21 '17 at 05:09
No results are found when strings are used... however when I copy and paste the value of idInParameter directly into the SQL query, and comment out 'cmd.Parameters.AddWithValue("productId", idInParameter)', then results are found... – user2181948 Feb 21 '17 at 05:33
Did you missed the `@` in `cmd.Parameters.AddWithValue("`? – sujith karivelil Feb 21 '17 at 05:35
Zero results are found when I try both with @ and no @. What is the purpose of this symbol? I've only seen cmd.Parameters.AddWithValue() with no @ sign before – user2181948 Feb 21 '17 at 06:09
Looks like @ sign is equivalent to no @ sign: http://stackoverflow.com/questions/2313540/should-i-include-the-when-using-sqlcommand-parameters-addwithvalue – user2181948 Feb 21 '17 at 06:28

score 3 · Accepted Answer · answered Feb 21 '17 at 06:21

You need to dispose your object to not get the exception. However you don't need to iterate over values and run a query for every value in the list. Try the following code. It makes a parameter for every value and adds it to command to use in "IN (...)" clause.

Also "using" keywords handles disposing objects.

List<string> productsIds = new List<string>() { "23", "46", "76", "88" };
        string query = @"
            SELECT *
            FROM products
            WHERE id IN ({0});";

        // Execute query
        using (MySqlCommand cmd = new MySqlCommand(query, dbConn))
        {
            int index = 0;
            string sqlWhere = string.Empty;
            foreach (string id in productsIds)
            {
                string parameterName = "@productId" + index++;
                sqlWhere += string.IsNullOrWhiteSpace(sqlWhere) ? parameterName : ", " + parameterName;
                cmd.Parameters.AddWithValue(parameterName, id);
            }

            query = string.Format(query, sqlWhere);
            cmd.CommandText = query;
            using (MySqlDataReader row = cmd.ExecuteReader())
            {
                while (row.Read())
                {
                    // Process result
                    // ...
                }
            }
        }

Adil · Answer 3 · 2017-02-21T05:02:09.903

Pass the comma separated productIds string instead of looping. Read more about IN here.

 string productIdsCommaSeparated = string.Join(",", productsIds.Select(n => n.ToString()).ToArray())
   string query = @"
        SELECT *
        FROM products
        WHERE id IN (@productId);";

    // Execute query
    MySqlCommand cmd = new MySqlCommand(query, dbConn);
    cmd.Parameters.AddWithValue("productId", productIdsCommaSeparated );

    MySqlDataReader row = cmd.ExecuteReader();
    while (row.Read())
    {
        // Process result
        // ...
    }

The error you are getting is because you do not close the MySqlDataReader. You must close it to get rid of error before you call ExecuteReader in next iteration but this is not proper way in this case.

score 0 · Answer 4 · answered Mar 23 '22 at 11:07

From what I tried and tested seems best solution (especially for text column types) is to create a list of individual parameters and add it as a range the to the query and parameters.

e.g:

List<MySqlParameter> listParams = new List<MySqlParameter>();
for (int i = 0; i < listOfValues.Length; i++) 
{
    listParams.Add(new MySqlParameter(string.Format("@values{0}", i), 
        MySqlDbType.VarChar) { Value = listOfValues[i] });
}

string sqlCommand = string.Format("SELECT data FROM table WHERE column IN ({0})", 
    string.Join(",", listParams.Select(x => x.ParameterName)));

......

using (MySqlCommand command = new MySqlCommand(sqlCommand, connection)
{
    ............
    command.Parameters.AddRange(listParams.ToArray());
    ............
}

C# MySQL adding multiple of the same parameter with a loop

4 Answers4

Linked