0

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?

Improve this question
1
  • Pass the Parameters correctly in query and its done. Commented Feb 21, 2017 at 7:07

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This is fantastic, thanks! Just had to change string.Join(separator, parameterNames) to string.Join(separator, parameterNames.ToArray()).
Portions of this answer have been copied from this one without attribution. Please review your posts and attribute as required.
@spender thanks for pointing out my mistake, I have updated my answer.
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

Improve this answer

Comments

0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.