MySQL query does not work in C#, but works when executed directly on database

Question

In my C# project, I'm trying to query a database table by dynamically populating the WHERE clause with a comma-separated list of strings:

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

// Construct SQL query
string names = String.Join(",", productNames.Select(n => "'" + n.ToLower() + "'").ToArray());
// names = 'a', 'b', 'c'

string query = "SELECT * FROM products WHERE LOWER(name) IN (@names);";

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

MySqlDataReader row = cmd.ExecuteReader();
while (row.Read())
{
    // Zero rows returned
}

When I run the above, no rows are returned. However when I run the SQL query directly on the database via MySQL Workbench, the rows are found:

SELECT * FROM products WHERE LOWER(name) IN ('a', 'b', 'c');
// 3 rows found

Why does this does not work in my C code?

Answer 1

This has been well explained by Brain in his blog post here. The answer below is an extract from this post: You will need to add the values in the array one at a time.

List<string> productNames = new List<string>() { "A", "B", "C" };
var parameters = new string[productNames.Count];
var cmd = new SqlCommand();
for (int i = 0; i < productNames.Count; i++)
{
    parameters[i] = string.Format("@name{0}", i);
    cmd.Parameters.AddWithValue(parameters[i], productNames[i]);
}

cmd.CommandText = string.Format("SELECT * FROM products WHERE LOWER(name) IN ({0})", string.Join(", ", parameters));
cmd.Connection = new SqlConnection(connStr);

Here is an extended and reusable solution

public static class SqlCommandExt
{
    /// <summary>
    /// This will add an array of parameters to a SqlCommand. This is used for an IN statement.
    /// Use the returned value for the IN part of your SQL call. (i.e. SELECT * FROM table WHERE field IN ({paramNameRoot}))
    /// </summary>
    /// <param name="cmd">The SqlCommand object to add parameters to.</param>
    /// <param name="values">The array of strings that need to be added as parameters.</param>
    /// <param name="paramNameRoot">What the parameter should be named followed by a unique value for each value. This value surrounded by {} in the CommandText will be replaced.</param>
    /// <param name="start">The beginning number to append to the end of paramNameRoot for each value.</param>
    /// <param name="separator">The string that separates the parameter names in the sql command.</param>
    public static SqlParameter[] AddArrayParameters<T>(this SqlCommand cmd, IEnumerable<T> values, string paramNameRoot, int start = 1, string separator = ", ")
    {
        /* An array cannot be simply added as a parameter to a SqlCommand so we need to loop through things and add it manually. 
         * Each item in the array will end up being it's own SqlParameter so the return value for this must be used as part of the
         * IN statement in the CommandText.
         */
        var parameters = new List<SqlParameter>();
        var parameterNames = new List<string>();
        var paramNbr = start;
        foreach(var value in values)
        {
            var paramName = string.Format("@{0}{1}", paramNameRoot, paramNbr++);
            parameterNames.Add(paramName);
            parameters.Add(cmd.Parameters.AddWithValue(paramName, value));
        }

        cmd.CommandText = cmd.CommandText.Replace("{" + paramNameRoot + "}", string.Join(separator, parameterNames.ToArray()));

        return parameters.ToArray();
    }
}

you can call this inside your method like

var cmd = new SqlCommand("SELECT * FROM products WHERE LOWER(name) IN ({name})");
cmd.AddArrayParameters(new int[] {"A", "B", "C" }, "name");

Notice the "{name}" in the sql statement is the same as the parameter name we are sending to AddArrayParameters. AddArrayParameters will replace the value with the correct parameters.

Answer 2

Your C# code didn't work because here

cmd.Parameters.AddWithValue("@names", names);

you are passing a single value names. It is not taken as three parameters. So your equivalent sql query will be like

SELECT * FROM products WHERE LOWER(name) IN ("'a', 'b', 'c'");

and no match will be found. To know more about IN clause and its misconceptions, please have a look at this nice article Arrays and Lists in SQL Server

Ideally parameter must be a single value. You can do like that

SELECT * FROM products WHERE LOWER(name) IN (@name1, @name2, @name3);

It is simple and clean. But you can also try passing multiple values in single parameter like done here Parameterize an SQL IN clause and Having multiple values assigned to a single ADO.Net SqlClient parameter

Answer 3

When you use parameterized query with IN you should use one parameter for every value. I mean you should not use a single parameter with value of "A, B, C". Instead of this you should use a query like this.

"SELECT * FROM products WHERE LOWER(name) IN (@name1, @name2, @name3);"

On your another question today, I wrote an example for this. Please check this answer