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

Issue

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?

Solution

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
                    // ...
                }
            }
        }

Answered By – fofik

Answer Checked By – Terry (BugsFixing Volunteer)