C# WHERE IN（ID 数组）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/182060/
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
WHERE IN (array of IDs)
提问by user17510
I have webservice which is passed an array of ints. I'd like to do the select statement as follows but keep getting errors. Do I need to change the array to a string?
我有传递一个整数数组的网络服务。我想按如下方式执行 select 语句，但不断出现错误。我需要将数组更改为字符串吗？
[WebMethod]
public MiniEvent[] getAdminEvents(int buildingID, DateTime startDate)
{
command.CommandText = @"SELECT id,
startDateTime, endDateTime From
tb_bookings WHERE buildingID IN
(@buildingIDs) AND startDateTime <=
@fromDate";
SqlParameter buildID = new SqlParameter("@buildingIDs", buildingIDs);
}
回答by jop
Visit T-SQL stored procedure that accepts multiple Id valuesfor ideas on how to do this.
访问接受多个 Id 值的 T-SQL 存储过程，了解如何执行此操作的想法。
回答by Mark Brackett
You can't (unfortunately) do that. A Sql Parameter can only be a single value, so you'd have to do:
你不能（不幸地）那样做。Sql 参数只能是单个值，因此您必须执行以下操作：
WHERE buildingID IN (@buildingID1, @buildingID2, @buildingID3...)
Which, of course, requires you to know how many building ids there are, or to dynamically construct the query.
当然，这需要您知道有多少个建筑 ID，或者动态构建查询。
As a workaround*, I've done the following:
作为一种解决方法*，我已完成以下操作：
WHERE buildingID IN (@buildingID)
command.CommandText = command.CommandText.Replace(
"@buildingID",
string.Join(buildingIDs.Select(b => b.ToString()), ",")
);
which will replace the text of the statement with the numbers, ending up as something like:
它将用数字替换语句的文本，最终类似于：
WHERE buildingID IN (1,2,3,4)
- Note that this is getting close to a Sql injection vulnerability, but since it's an int array is safe. Arbitrary strings are notsafe, but there's no way to embed Sql statements in an integer (or datetime, boolean, etc).
- 请注意，这接近于 Sql 注入漏洞，但由于它是一个 int 数组是安全的。任意字符串并不安全，但无法将 Sql 语句嵌入整数（或日期时间、布尔值等）中。
回答by Josef
NOTE:I am not generally for using unparameterized queries. IN THIS INSTANCE, however, given that we are dealing with an integer array, you coulddo such a thing and it would be more efficient. However, given that everyone seems to want to downgrade the answer because it doesn't meet their criteria of valid advice, I will submit another answer that performs horribly but would probably run in LINK2SQL.
注意：我通常不使用非参数化查询。然而，在这个例子中，鉴于我们正在处理一个整数数组，你可以做这样的事情，而且效率会更高。但是，鉴于每个人似乎都希望将答案降级，因为它不符合他们的有效建议标准，我将提交另一个表现糟糕但可能会在 LINK2SQL 中运行的答案。
Assuming, as your question states, that you have an array of ints, you can use the following code to return a string that would contain a comma delimited list that SQL would accept:
假设，如您的问题所述，您有一个整数数组，您可以使用以下代码返回一个字符串，该字符串将包含 SQL 可接受的逗号分隔列表：
private string SQLArrayToInString(Array a)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < a.GetUpperBound(0); i++)
sb.AppendFormat("{0},", a.GetValue(i));
string retVal = sb.ToString();
return retVal.Substring(0, retVal.Length - 1);
}
Then, I would recommend you skip trying to parameterize the command given that this is an array of intsand just use:
然后，我建议您跳过尝试参数化命令，因为这是一个整数数组，只需使用：
command.CommandText = @"SELECT id,
startDateTime, endDateTime From
tb_bookings WHERE buildingID IN
(" + SQLArrayToInString(buildingIDs) + ") AND startDateTime <=
@fromDate";
回答by Chris Porter
[WebMethod]
public MiniEvent[] getAdminEvents(int buildingID, DateTime startDate)
...
SqlParameter buildID= new SqlParameter("@buildingIDs", buildingIDs);
[网络方法]
public MiniEvent[] getAdminEvents(int buildingID, DateTime startDate)
...
SqlParameter buildID= new SqlParameter("@buildingIDs", buildingIDs);
Perhaps I'm being over detailed, but this method accepts a single int, not an array of ints. If you expect to pass in an array, you will need to update your method definition to have an int array. Once you get that array, you will need to convert the array to a string if you plan to use it in a SQL query.
也许我说得太详细了，但是这个方法接受一个整数，而不是一个整数数组。如果您希望传入一个数组，则需要更新您的方法定义以拥有一个 int 数组。获得该数组后，如果您打算在 SQL 查询中使用它，则需要将该数组转换为字符串。
回答by Josef
First you're going to need a function and a sproc. The function will split your data and return a table:
首先，您将需要一个函数和一个 sproc。该函数将拆分您的数据并返回一个表：
CREATE function IntegerCommaSplit(@ListofIds nvarchar(1000))
returns @rtn table (IntegerValue int)
AS
begin
While (Charindex(',',@ListofIds)>0)
Begin
Insert Into @Rtn
Select ltrim(rtrim(Substring(@ListofIds,1,Charindex(',',@ListofIds)-1)))
Set @ListofIds = Substring(@ListofIds,Charindex(',',@ListofIds)+len(','),len(@ListofIds))
end
Insert Into @Rtn
Select ltrim(rtrim(@ListofIds))
return
end
Next you need a sproc to use that:
接下来你需要一个 sproc 来使用它：
create procedure GetAdminEvents
@buildingids nvarchar(1000),
@startdate datetime
as
SELECT id,startDateTime, endDateTime From
tb_bookings t INNER JOIN
dbo.IntegerCommaSplit(@buildingids) i
on i.IntegerValue = t.id
WHERE startDateTime <= @fromDate
Finally, your code:
最后，您的代码：
[WebMethod]
public MiniEvent[] getAdminEvents(int[] buildingIDs, DateTime startDate)
command.CommandText = @"exec GetAdminEvents";
SqlParameter buildID= new SqlParameter("@buildingIDs", buildingIDs);
That goes way beyond what your question asked but it will do what you need.
这远远超出了您的问题，但它会满足您的需求。
Note:should you pass in anything that's not an int, the whole database function will fail. I leave the error handling for that as an exercise for the end user.
注意：如果您传入任何不是 int 的内容，整个数据库功能将失败。我将错误处理留给最终用户作为练习。
回答by Nishant
A superfast XML Method which requires no unsafe code or user defined functions :
不需要不安全代码或用户定义函数的超快 XML 方法：
You can use a stored procedure and pass the comma separated list of Building IDs :
您可以使用存储过程并传递以逗号分隔的 Building ID 列表：
Declare @XMLList xml
SET @XMLList=cast('<i>'+replace(@buildingIDs,',','</i><i>')+'</i>' as xml)
SELECT x.i.value('.','varchar(5)') from @XMLList.nodes('i') x(i))
All credit goes to Guru Brad Schulz's Blog
所有功劳归功于大师布拉德舒尔茨的博客
回答by Igo Soares
You can use this. Execute in SQLServer to create a function on your DB (Only once):
你可以用这个。在 SQLServer 中执行以在您的数据库上创建一个函数（仅一次）：
IF EXISTS(
SELECT *
FROM sysobjects
WHERE name = 'FN_RETORNA_ID_FROM_VARCHAR_TO_TABLE_INT')
BEGIN
DROP FUNCTION FN_RETORNA_ID_FROM_VARCHAR_TO_TABLE_INT
END
GO
CREATE FUNCTION [dbo].FN_RETORNA_ID_FROM_VARCHAR_TO_TABLE_INT (@IDList VARCHAR(8000))
RETURNS
@IDListTable TABLE (ID INT)
AS
BEGIN
DECLARE
--@IDList VARCHAR(100),
@LastCommaPosition INT,
@NextCommaPosition INT,
@EndOfStringPosition INT,
@StartOfStringPosition INT,
@LengthOfString INT,
@IDString VARCHAR(100),
@IDValue INT
--SET @IDList = '11,12,113'
SET @LastCommaPosition = 0
SET @NextCommaPosition = -1
IF LTRIM(RTRIM(@IDList)) <> ''
BEGIN
WHILE(@NextCommaPosition <> 0)
BEGIN
SET @NextCommaPosition = CHARINDEX(',',@IDList,@LastCommaPosition + 1)
IF @NextCommaPosition = 0
SET @EndOfStringPosition = LEN(@IDList)
ELSE
SET @EndOfStringPosition = @NextCommaPosition - 1
SET @StartOfStringPosition = @LastCommaPosition + 1
SET @LengthOfString = (@EndOfStringPosition + 1) - @StartOfStringPosition
SET @IDString = SUBSTRING(@IDList,@StartOfStringPosition,@LengthOfString)
IF @IDString <> ''
INSERT @IDListTable VALUES(@IDString)
SET @LastCommaPosition = @NextCommaPosition
END --WHILE(@NextCommaPosition <> 0)
END --IF LTRIM(RTRIM(@IDList)) <> ''
RETURN
ErrorBlock:
RETURN
END --FUNCTION
After create the function you have to call this on your code:
创建函数后，您必须在代码中调用它：
command.CommandText = @"SELECT id,
startDateTime, endDateTime From
tb_bookings WHERE buildingID IN
(SELECT ID FROM FN_RETORNA_ID_FROM_VARCHAR_TO_TABLE_INT(@buildingIDs))) AND startDateTime <=
@fromDate";
command.Parameters.Add(new SqlParameter(){
DbType = DbType.String,
ParameterName = "@buildingIDs",
Value = "1,2,3,4,5" //Enter the parameters here separated with commas
});
This function get the text inner commas on "array" and make an table with this values as int, called ID. When this function is on you DB you can use in any project.
此函数获取“array”上的文本内部逗号，并使用此值为 int 的表创建一个表，称为 ID。当此功能在您的数据库上时，您可以在任何项目中使用。
Thanks to Microsoft MSDN.
感谢微软 MSDN。
Igo S Ventura
伊戈·文图拉
Microsoft MVA
微软MVA
Sistema Ari de Sá
阿里德萨系统
P.S.: I'm from Brazil. Apologize my english... XD
PS：我来自巴西。向我的英语道歉...XD
回答by Gon?alo Dinis
I use that approach and works for me.
我使用这种方法并为我工作。
My variable act = my list of ID's at string.
我的变量 act = 我在字符串中的 ID 列表。
act = "1, 2, 3, 4"
动作 = "1, 2, 3, 4"
command = new SqlCommand("SELECT x FROM y WHERE x.id IN (@actions)", conn);
command.Parameters.AddWithValue("@actions", act);
command.CommandText = command.CommandText.Replace("@actions", act);
回答by Nyerguds
Here's a Linq solution I thought up. It'll automatically insert all items in the list as parameters @item0, @item1, @item2, @item3, etc.
这是我想到的 Linq 解决方案。它将自动插入列表中的所有项目作为参数@item0、@item1、@item2、@item3 等。
[WebMethod]
public MiniEvent[] getAdminEvents(Int32[] buildingIDs, DateTime startDate)
{
// Gets a list with numbers from 0 to the max index in buildingIDs,
// then transforms it into a list of strings using those numbers.
String idParamString = String.Join(", ", (Enumerable.Range(0, buildingIDs.Length).Select(i => "@item" + i)).ToArray());
command.CommandText = @"SELECT id,
startDateTime, endDateTime From
tb_bookings WHERE buildingID IN
(" + idParamString + @") AND startDateTime <=
@fromDate";
// Reproduce the same parameters in idParamString
for (Int32 i = 0; i < buildingIDs.Length; i++)
command.Parameters.Add(new SqlParameter ("@item" + i, buildingIDs[i]));
command.Parameters.Add(new SqlParameter("@fromDate", startDate);
// the rest of your code...
}
