C# 在 SqlCommand 中传递数组参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2377506/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pass Array Parameter in SqlCommand
提问by Yongwei Xing
I am trying to pass array parameter to SQL commnd in C# like below, but it does not work. Does anyone meet it before?
我正在尝试将数组参数传递给 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(',');
采纳答案by Brian
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.
更新：这是一个扩展且可重用的解决方案，它使用了 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();
}
}
It is called like this...
是这样叫的...
var cmd = new SqlCommand("SELECT * FROM TableA WHERE Age IN ({Age})");
cmd.AddArrayParameters("Age", new int[] { 1, 2, 3 });
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.
注意 sql 语句中的“{Age}”与我们发送给 AddArrayParameters 的参数名称相同。AddArrayParameters 将用正确的参数替换该值。
回答by Kyle Rozendo
Use .AddWithValue(), So:
使用.AddWithValue()，所以：
sqlComm.Parameters.AddWithValue("@Age", sb.ToString().TrimEnd(','));
sqlComm.Parameters.AddWithValue("@Age", sb.ToString().TrimEnd(','));
Alternatively, you could use this:
或者，你可以使用这个：
sqlComm.Parameters.Add(
new SqlParameter("@Age", sb.ToString().TrimEnd(',')) { SqlDbType = SqlDbType. NVarChar }
);
Your total code sample will look at follows then:
您的总代码示例将如下所示：
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 });
回答by Ballin
try it like this
像这样试试
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(',');
}
}
回答by Ballin
try
尝试
sqlComm.Parameters["@Age"].Value = sb.ToString().Replace(","," ");
回答by J Adam Rogers
I wanted to expand on the answer that Brian contributed to make this easily usable in other places.
我想扩展 Brian 为使其在其他地方易于使用而做出的贡献的答案。
/// <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);
}
You can use this new function as follows:
您可以按如下方式使用此新功能：
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);
}
}
You can then use this extension method as follows:
然后，您可以按如下方式使用此扩展方法：
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);
Make sure you set the CommandText before calling AddArrayParameters.
确保在调用 AddArrayParameters 之前设置 CommandText。
Also make sure your parameter name won't partially match anything else in your statement (i.e. @AgeOfChild)
还要确保您的参数名称不会部分匹配您语句中的任何其他内容（即@AgeOfChild）
回答by tridy
Since there is a method on
由于有一种方法
SqlCommand.Parameters.AddWithValue(parameterName, value)
it might be more convenient to create a method accepting a parameter (name) to replace and a list of values. It is not on the Parameterslevel (like AddWithValue) but on command itself so it's better to call it AddParametersWithValuesand not just AddWithValues:
创建一个接受要替换的参数（名称）和值列表的方法可能更方便。它不在参数级别（如AddWithValue），而是在命令本身，因此最好将其称为AddParametersWithValues而不仅仅是AddWithValues：
query:
询问：
SELECT * from TableA WHERE Age IN (@age)
usage:
用法：
sqlCommand.AddParametersWithValues("@age", 1, 2, 3);
the extension method:
扩展方法：
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));
}
}
回答by Marc Gravell
If you can use a tool like "dapper", this can be simply:
如果你可以使用像“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 will handle unwrapping this to individual parameters for you.
小巧玲珑将处理展开这对各个参数给你。
回答by Gregor Slavec
If you are using MS SQL Server 2008 and above you can use table-valued parameters like described here http://www.sommarskog.se/arrays-in-sql-2008.html.
如果您使用的是 MS SQL Server 2008 及更高版本，您可以使用表值参数，如 http://www.sommarskog.se/arrays-in-sql-2008.html 中所述。
1. Create a table type for each parameter type you will be using
1. 为您将使用的每个参数类型创建一个表类型
The following command creates a table type for integers:
以下命令为整数创建表类型：
create type int32_id_list as table (id int not null primary key)
2. Implement helper methods
2. 实现辅助方法
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;
}
3. Use it like this
3. 像这样使用
cmd.CommandText = "select * from TableA where Age in (select id from @age)";
cmd.AddParameter("@age", new [] {1,2,3,4,5});
回答by Lukasz Matysiak
Passing an array of items as a collapsed parameter to the WHERE..IN clause will fail since query will take form of WHERE Age IN ("11, 13, 14, 16").
将一组项目作为折叠参数传递给 WHERE..IN 子句将失败，因为查询将采用WHERE Age IN ("11, 13, 14, 16").
But you can pass your parameter as an array serialized to XML or JSON:
但是您可以将参数作为序列化为 XML 或 JSON 的数组传递：
Using nodes()method:
使用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();
Using OPENXMLmethod:
使用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();
That's a bit more on the SQL side and you need a proper XML (with root).
这在 SQL 方面有点多，您需要一个适当的 XML（使用 root）。
Using OPENJSONmethod (SQL Server 2016+):
使用OPENJSON方法（SQL Server 2016+）：
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);
Note that for the last method you also need to have Compatibility Level at 130+.
请注意，对于最后一种方法，您还需要具有 130+ 的兼容性级别。
回答by user2399170
I want to propose another way, how to solve limitation with IN operator.
我想提出另一种方法，如何解决IN运算符的限制。
For example we have following query
例如我们有以下查询
select *
from Users U
WHERE U.ID in (@ids)
We want to pass several IDs to filter users. Unfortunately it is not possible to do with C# in easy way. But I have fount workaround for this by using "string_split" function. We need to rewrite a bit our query to following.
我们想传递几个 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
Now we can easily pass one parameter enumeration of values separated by comma.
现在我们可以轻松地传递一个参数枚举值，以逗号分隔。
