How to Pass Array Parameters to a SqlCommand in C#?

Passing array parameters to SqlCommand in C#

In C#, when trying to pass array parameters to a SQL command, there may be an issue that does not work as expected. The root cause is that arrays cannot be added directly as parameters to SqlCommand. To solve this problem, each value in the array must be added individually.

Solution:

1. Define array parameters:

   Create an array of parameters with the required names and data types.

2. Add parameters to SqlCommand:

   For each value in the array, add a new SqlParameter object and specify the corresponding name and value. If necessary, set the appropriate SqlDbType and size.

3. Replace IN clause:

   Replace the "{parameterNameRoot}" placeholder in CommandText with a comma-separated list of parameter names.

Example:

<code class="language-csharp">var parameters = new string[items.Length]; // 此处items应替换为实际的数组变量
var cmd = new SqlCommand();
for (int i = 0; i < items.Length; i++)
{
    cmd.Parameters.AddWithValue("@param" + i, items[i]);
}
cmd.CommandText = cmd.CommandText.Replace("{param}", string.Join(",", Enumerable.Range(0, items.Length).Select(i => "@param" + i)));</code>

Improved solution:

An improved, reusable solution can be implemented as an extension method:

<code class="language-csharp">public static class SqlCommandExt
{
    public static SqlParameter[] AddArrayParameters<T>(this SqlCommand cmd, string paramNameRoot, IEnumerable<T> values, SqlDbType? dbType = null, int? size = null)
    {
        var parameters = new List<SqlParameter>();
        var parameterNames = new List<string>();
        var paramNbr = 1;

        foreach (var value in values)
        {
            var paramName = string.Format("@{0}{1}", paramNameRoot, paramNbr++);
            parameterNames.Add(paramName);
            var p = new SqlParameter(paramName, value);
            if (dbType.HasValue)
                p.SqlDbType = dbType.Value;
            if (size.HasValue)
                p.Size = size.Value;

            cmd.Parameters.Add(p);
            parameters.Add(p);
        }

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

        return parameters.ToArray();
    }
}</code>

Usage:

<code class="language-csharp">var cmd = new SqlCommand("SELECT * FROM TableA WHERE Age IN ({Age})");
cmd.AddArrayParameters("Age", new int[] { 1, 2, 3 });</code>

This improved example uses generics, making it more flexible and allowing SqlDbType and Size to be specified. It also avoids the potential security risk of splicing parameter names directly in CommandText. Remember, in actual use, replace TableA and Age with your actual table and column names.

The above is the detailed content of How to Pass Array Parameters to a SqlCommand in C#?. For more information, please follow other related articles on the PHP Chinese website!