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?

Pass the Parameters correctly in query and its done. – Masoud Andalibi Feb 21 '17 at 07:07 — Masoud Andalibi, Feb 21 '17 at 07:07

score 1 · Accepted Answer · edited May 23 '17 at 11:53

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.

This is fantastic, thanks! Just had to change string.Join(separator, parameterNames) to string.Join(separator, parameterNames.ToArray()). — user2181948, Feb 21 '17 at 21:00
Portions of this answer have been copied from [this one](http://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand) without attribution. Please review your posts and attribute as required. — spender, Feb 27 '17 at 20:51
@spender thanks for pointing out my mistake, I have updated my answer. — MANISH KUMAR CHOUDHARY, Feb 28 '17 at 03:40

score 0 · Answer 2 · edited May 23 '17 at 12:16

0

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

edited May 23 '17 at 12:16

Community

1
1

answered Feb 21 '17 at 07:07

fofik

998
11
17

score 0 · Answer 3 · edited May 23 '17 at 12:00

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

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

3 Answers3