在 SqlCommand 中传递数组参数

使用 .AddWithValue()，因此:

sqlComm.Parameters.AddWithValue("@Age", sb.ToString().TrimEnd(','));

或者，你也可以这样做:

sqlComm.Parameters.Add(
new SqlParameter("@Age", sb.ToString().TrimEnd(',')) { SqlDbType = SqlDbType. NVarChar }
);

您的总代码示例将看到以下内容:

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;


StringBuilder sb = new StringBuilder();
foreach (ListItem item in ddlAge.Items)
{
if (item.Selected)
{
sb.Append(item.Text + ",");
}
}


sqlComm.Parameters.AddWithValue("@Age", sb.ToString().TrimEnd(','));


// OR


// sqlComm.Parameters.Add(new SqlParameter("@Age", sb.ToString().TrimEnd(',')) { SqlDbType = SqlDbType. NVarChar });

试试这样

StringBuilder sb = new StringBuilder();
foreach (ListItem item in ddlAge.Items)
{
if (item.Selected)
{
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);
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 }”与我们发送给 AddArrayParameter 的参数名称相同。AddArrayParameter 将使用正确的参数替换该值。

我想进一步解释一下，布莱恩的贡献使得这个在其他地方也很容易使用。

/// <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 (returnValue))
/// </summary>
/// <param name="sqlCommand">The SqlCommand object to add parameters to.</param>
/// <param name="array">The array of strings that need to be added as parameters.</param>
/// <param name="paramName">What the parameter should be named.</param>
protected string AddArrayParameters(SqlCommand sqlCommand, string[] array, string paramName)
{
/* 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 string[array.Length];
for (int i = 0; i < array.Length; i++)
{
parameters[i] = string.Format("@{0}{1}", paramName, i);
sqlCommand.Parameters.AddWithValue(parameters[i], array[i]);
}


return string.Join(", ", parameters);
}

您可以按以下方式使用这个新功能:

SqlCommand cmd = new SqlCommand();


string ageParameters = AddArrayParameters(cmd, agesArray, "Age");
sql = string.Format("SELECT * FROM TableA WHERE Age IN ({0})", ageParameters);


cmd.CommandText = sql;

public static class Extensions
{
public static void AddArrayParameters<T>(this SqlCommand cmd, string name, IEnumerable<T> values)
{
name = name.StartsWith("@") ? name : "@" + name;
var names = string.Join(", ", values.Select((value, i) => {
var paramName = name + i;
cmd.Parameters.AddWithValue(paramName, value);
return paramName;
}));
cmd.CommandText = cmd.CommandText.Replace(name, names);
}
}

然后，您可以按照以下方式使用这种扩展方法:

var ageList = new List<int> { 1, 3, 5, 7, 9, 11 };
var cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM MyTable WHERE Age IN (@Age)";
cmd.AddArrayParameters("Age", ageList);

在调用 AddArrayParameter 之前，请确保设置了 CommandText。

还要确保参数名称不会与语句中的其他内容部分匹配(例如@AgeOfChild)

因为有一个方法

SqlCommand.Parameters.AddWithValue(parameterName, value)

创建一个接受要替换的参数(名称)和值列表的方法可能更方便。它不是在 参数级别(像 AddWithValue) ，而是在命令本身上，所以最好称它为 值，而不仅仅是 价值观:

查询:

SELECT * from TableA WHERE Age IN (@age)

用途:

sqlCommand.AddParametersWithValues("@age", 1, 2, 3);

扩展方法:

public static class SqlCommandExtensions
{
public static void AddParametersWithValues<T>(this SqlCommand cmd,  string parameterName, params T[] values)
{
var parameterNames = new List<string>();
for(int i = 0; i < values.Count(); i++)
{
var paramName = @"@param" + i;
cmd.Parameters.AddWithValue(paramName, values.ElementAt(i));
parameterNames.Add(paramName);
}


cmd.CommandText = cmd.CommandText.Replace(parameterName, string.Join(",", parameterNames));
}
}

如果你可以使用像“ dapper”这样的工具，那么它可以很简单:

int[] ages = { 20, 21, 22 }; // could be any common list-like type
var rows = connection.Query<YourType>("SELECT * from TableA WHERE Age IN @ages",
new { ages }).ToList();

Dapper 将处理对单个参数 为了你的展开。

如果您正在使用 MSSQLServer2008及以上版本，您可以使用如下所述的表值参数 Http://www.sommarskog.se/arrays-in-sql-2008.html.

create type int32_id_list as table (id int not null primary key)

public static SqlCommand AddParameter<T>(this SqlCommand command, string name, IEnumerable<T> ids)
{
var parameter = command.CreateParameter();


parameter.ParameterName = name;
parameter.TypeName = typeof(T).Name.ToLowerInvariant() + "_id_list";
parameter.SqlDbType = SqlDbType.Structured;
parameter.Direction = ParameterDirection.Input;


parameter.Value = CreateIdList(ids);


command.Parameters.Add(parameter);
return command;
}


private static DataTable CreateIdList<T>(IEnumerable<T> ids)
{
var table = new DataTable();
table.Columns.Add("id", typeof (T));


foreach (var id in ids)
{
table.Rows.Add(id);
}


return table;
}

cmd.CommandText = "select * from TableA where Age in (select id from @age)";
cmd.AddParameter("@age", new [] {1,2,3,4,5});

将项数组作为折叠参数传递到 WHERE。.IN 子句将失败，因为查询将采用 WHERE Age IN ("11, 13, 14, 16")的形式。

但是您可以将参数作为序列化为 XML 或 JSON 的数组传递:

使用 nodes()方法:

StringBuilder sb = new StringBuilder();


foreach (ListItem item in ddlAge.Items)
if (item.Selected)
sb.Append("<age>" + item.Text + "</age>"); // actually it's xml-ish


sqlComm.CommandText = @"SELECT * from TableA WHERE Age IN (
SELECT Tab.col.value('.', 'int') as Age from @Ages.nodes('/age') as Tab(col))";
sqlComm.Parameters.Add("@Ages", SqlDbType.NVarChar);
sqlComm.Parameters["@Ages"].Value = sb.ToString();

使用 OPENXML方法:

using System.Xml.Linq;
...
XElement xml = new XElement("Ages");


foreach (ListItem item in ddlAge.Items)
if (item.Selected)
xml.Add(new XElement("age", item.Text);


sqlComm.CommandText = @"DECLARE @idoc int;
EXEC sp_xml_preparedocument @idoc OUTPUT, @Ages;
SELECT * from TableA WHERE Age IN (
SELECT Age from OPENXML(@idoc, '/Ages/age') with (Age int 'text()')
EXEC sp_xml_removedocument @idoc";
sqlComm.Parameters.Add("@Ages", SqlDbType.Xml);
sqlComm.Parameters["@Ages"].Value = xml.ToString();

这在 SQL 方面有点多，您需要一个合适的 XML (带有 root)。

使用 OPENJSON方法(SQLServer2016 +) :

using Newtonsoft.Json;
...
List<string> ages = new List<string>();


foreach (ListItem item in ddlAge.Items)
if (item.Selected)
ages.Add(item.Text);


sqlComm.CommandText = @"SELECT * from TableA WHERE Age IN (
select value from OPENJSON(@Ages))";
sqlComm.Parameters.Add("@Ages", SqlDbType.NVarChar);
sqlComm.Parameters["@Ages"].Value = JsonConvert.SerializeObject(ages);

注意，对于最后一个方法，还需要兼容级别为130 + 。

我想提出另一种方法，如何解决与 IN 运算符的限制。

例如，我们有以下查询

select *
from Users U
WHERE U.ID in (@ids)

我们需要传递几个 ID 来过滤用户。不幸的是，用简单的方法来处理 C # 是不可能的。但是我已经使用“ string _ split”函数找到了解决办法。我们需要重写一点我们的查询以下。

declare @ids nvarchar(max) = '1,2,3'


SELECT *
FROM Users as U
CROSS APPLY string_split(@ids, ',') as UIDS
WHERE U.ID = UIDS.value

现在我们可以很容易地传递一个参数枚举，这个枚举的值用逗号分隔。

概述: 使用 DbType 设置参数类型。

var parameter = new SqlParameter();
parameter.ParameterName = "@UserID";
parameter.DbType = DbType.Int32;
parameter.Value = userID.ToString();


var command = conn.CreateCommand()
command.Parameters.Add(parameter);
var reader = await command.ExecuteReaderAsync()

仅仅改变 DbType 就足够了:

string sqlCommand = "SELECT * from TableA WHERE Age IN (@Age)";
SqlConnection sqlCon = new SqlConnection(connectString);
SqlCommand sqlComm = new SqlCommand
{
Connection = sqlCon,
CommandType = CommandType.Text,
CommandText = sqlCommand,
CommandTimeout = 300
};


var itens = string.Join(',', ddlAge.Items);
sqlComm.Parameters.Add(
new SqlParameter("@Age", itens)
{
DbType = DbType.String
});