在SqlCommand中传递数组参数

  • Z时代
  • 2024-01-10
  • 分类：问答

我试图将数组参数传递给C＃中的SQL commnd，如下所示，但它不起作用。有人见过吗？

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(',');

回答：

您将需要一次将一个值添加到数组中。

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

这是一个扩展且可重用的解决方案，该解决方案使用了Adam的答案及其建议的编辑方式。我对其进行了一些改进，并使其成为一种扩展方法，以使其更易于调用。

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="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="values">The array of strings that need to be added as parameters.</param>
    /// <param name="dbType">One of the System.Data.SqlDbType values. If null, determines type based on T.</param>
    /// <param name="size">The maximum size, in bytes, of the data within the column. The default value is inferred from the parameter value.</param>
    public static SqlParameter[] AddArrayParameters<T>(this SqlCommand cmd, string paramNameRoot, IEnumerable<T> values, SqlDbType? dbType = null, int? size = null)
    {
        /* 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 = 1;
        foreach (var value in values)
        {
            var paramName = string.Format("@{0}{1}", paramNameRoot, paramNbr++);
            parameterNames.Add(paramName);
            SqlParameter 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();
    }
}

像这样

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

请注意，sql语句中的“

{Age}”与我们要发送给AddArrayParameters的参数名称相同。AddArrayParameters将用正确的参数替换该值。

以上是 在SqlCommand中传递数组参数 的全部内容， 来源链接： utcz.com/qa/418872.html

回到顶部