C# 如何向 SQL 参数提供 List<int> ？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11508240/
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
How can I supply a List<int> to a SQL parameter?
提问by B. Clay Shannon
I have a SQL statement like the following:
我有一个像下面这样的 SQL 语句：
...
const string sql = @"UPDATE PLATYPUS
SET DUCKBILLID = :NEWDUCKBILLID
WHERE PLATYPUSID IN (:ListOfInts)";
...
ocmd.Parameters.Add("ListOfInts", ??WhatNow??);
How can I provide the comma separated list of ints, which could be any (reasonable*) number of values
我如何提供逗号分隔的整数列表，它可以是任何（合理*）数量的值
- By "reasonable" in this case I mean between one and a couple dozen.
- 在这种情况下，“合理”是指一到几十个。
采纳答案by Abe Miessler
UPDATE: As Lasse points out you can't achieve what Clay is trying to do using the method below. While this does pass in a coma separated list of numbers it isn't going to execute as expected. Unfortunately I can't delete this since it's the accepted answer.
更新：正如 Lasse 指出的那样，您无法使用以下方法实现 Clay 尝试做的事情。虽然这确实传递了一个以昏迷分隔的数字列表，但它不会按预期执行。不幸的是，我无法删除它，因为它是公认的答案。
Try something like this:
尝试这样的事情：
ocmd.Parameters.Add("ListOfInts", String.Join(",",myList.ToArray());
The String.Joinmethod takes all elements in an array and places the specified character between them.
所述的string.join方法需要在阵列中的所有元素，并把它们之间的指定的字符。
回答by Shyju
Use the Joinmethod of stringon the Array of Ints. You can mention commaas the seperator
在整数数组上使用 的Join方法string。您可以提及comma为分隔符
List<int> ints = new List<int>();
ints.Add(4);
ints.Add(6);
ints.Add(2);
string concatenatedIds= string.Join(",", ints.ToArray());
The output (value in concatenatedIds) will be 4,6,2. You can use that string as the parameter value for your INclause
输出（中的值concatenatedIds）将为4,6,2。您可以将该字符串用作IN子句的参数值
ocmd.Parameters.Add("ListOfInts",concatenatedIds);
回答by Umar Jamil
I think the only possible solution is to pass comma-separated values, which you can convert to a table in SQL using a function. Here is the function I am using
我认为唯一可能的解决方案是传递逗号分隔值，您可以使用函数将其转换为 SQL 中的表。这是我正在使用的功能
CREATE FUNCTION dbo.CSVToList (@CSV varchar(3000))
RETURNS @Result TABLE (Value varchar(30))
AS
BEGIN
DECLARE @List TABLE
(
Value varchar(30)
)
DECLARE
@Value varchar(30),
@Pos int
SET @CSV = LTRIM(RTRIM(@CSV))+ ','
SET @Pos = CHARINDEX(',', @CSV, 1)
IF REPLACE(@CSV, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Value = LTRIM(RTRIM(LEFT(@CSV, @Pos - 1)))
IF @Value <> ''
INSERT INTO @List (Value) VALUES (@Value)
SET @CSV = RIGHT(@CSV, LEN(@CSV) - @Pos)
SET @Pos = CHARINDEX(',', @CSV, 1)
END
END
INSERT @Result
SELECT
Value
FROM
@List
RETURN
END
and you can use the following code (for example) to perform operations:
并且您可以使用以下代码（例如）来执行操作：
DECLARE @CSV varchar(100)
SET @CSV = '30,32,34,36,40'
SELECT
ProductID,
ProductName,
UnitPrice
FROM
Products
WHERE
ProductID IN (SELECT * FROM dbo.CSVToLIst(@CSV))
I took the code from here: http://www.geekzilla.co.uk/view5C09B52C-4600-4B66-9DD7-DCE840D64CBD.htm
我从这里拿了代码：http: //www.geekzilla.co.uk/view5C09B52C-4600-4B66-9DD7-DCE840D64CBD.htm
Hope it helps.
希望能帮助到你。
回答by Felice Pollano
You can't, you have to create some helper function that replaces :ListOfIntswith ( for example ) :I0,:I1,:I2...and pass the parameter by adding one by one in code. The function you want is gracefully emulated by Dapper.Netin its list support.
你不能，你必须创建一些辅助函数来替换:ListOfInts（例如）:I0,:I1,:I2...并通过在代码中一一添加来传递参数。Dapper.Net在其列表支持中优雅地模拟了您想要的功能。
回答by Joel Coehoorn
The best way to handle this is to keep the list in the database, such that you could write a select query to return the values in your list. The question is, how can you do this when the data originates at the client? Most of the time, this data is hand-picked by the user, one record at a time. In that case, you can use a table (think: "shopping cart") that adds records for the values one at a time, as the user selects them. If it's more of a batch job, you may want to use a BulkInsert process to create the records.
处理此问题的最佳方法是将列表保留在数据库中，这样您就可以编写一个选择查询来返回列表中的值。问题是，当数据来自客户端时，您如何做到这一点？大多数情况下，这些数据是由用户手工挑选的，一次一条记录。在这种情况下，您可以使用一个表（想想：“购物车”），在用户选择它们时一次添加一个值的记录。如果它更像是一个批处理作业，您可能需要使用 BulkInsert 过程来创建记录。
回答by James Simpson
You don't state which database you are using, but if it is SQL Server 2008+ you may also want to consider Table Valued Parameters.
您没有说明您使用的是哪个数据库，但如果它是 SQL Server 2008+，您可能还需要考虑Table Valued Parameters。
For your example the added complexity may not be worthwhile, but they can be useful in other scenarios.
对于您的示例，增加的复杂性可能不值得，但它们在其他场景中可能很有用。
