Yongwei Xing Yongwei Xing - 1 month ago 13
C# Question

Pass Array Parameter in SqlCommand

I am trying to pass array parameter to SQL commnd in C# like below, but it does not work. Does anyone meet it before?

string sqlCommand = "SELECT * from TableA WHERE Age IN (@Age)";
SqlConnection sqlCon = new SqlConnection(connectString);
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlCon;
sqlComm.CommandType = System.Data.CommandType.Text;
sqlComm.CommandText = sqlCommand;
sqlComm.CommandTimeout = 300;
sqlComm.Parameters.Add("@Age", SqlDbType.NVarChar);
StringBuilder sb = new StringBuilder();
foreach (ListItem item in ddlAge.Items)
{
if (item.Selected)
{
sb.Append(item.Text + ",");
}
}

sqlComm.Parameters["@Age"].Value = sb.ToString().TrimEnd(',');

Answer

You will need to add the values in the array one at a time.

var parameters = new string[items.Length];
var cmd = new SqlCommand();
for (int i = 0; i < items.Length; i++)
{
    parameters[i] = string.Format("@Age{0}", i);
    cmd.Parameters.AddWithValue(parameters[i], items[i]);
}

cmd.CommandText = string.Format("SELECT * from TableA WHERE Age IN ({0})", string.Join(", ", parameters));
cmd.Connection = new SqlConnection(connStr);

UPDATE: Here is an extended and reusable solution that uses Adam's answer along with his suggested edit. I improved it a bit and made it an extension method to make it even easier to call.

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));

        return parameters.ToArray();
    }
}

It is called like this...

var cmd = new SqlCommand("SELECT * FROM TableA WHERE Age IN ({Age})");
cmd.AddArrayParameters(new int[] { 1, 2, 3 }, "Age");

Notice the "{Age}" 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.

Comments