WHERE IN（ID 数组）

Question

我有一个网络服务，它传递一个整数数组。 我想按如下方式执行 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);
}

Answer 1

你不能（不幸的是）这样做。 Sql 参数只能是单个值，因此您必须这样做：

WHERE buildingID IN (@buildingID1, @buildingID2, @buildingID3...)

当然，这需要您知道有多少个构建 ID，或者动态构建查询。

作为解决方法*，我做了以下操作：

WHERE buildingID IN (@buildingID)

command.CommandText = command.CommandText.Replace(
  "@buildingID", 
  string.Join(buildingIDs.Select(b => b.ToString()), ",")
);

它将用数字替换语句的文本，最终结果如下：

WHERE buildingID IN (1,2,3,4)

• 请注意，这接近于 Sql 注入漏洞，但由于它是 int 数组，因此是安全的。 任意字符串不安全，但无法将 Sql 语句嵌入到整数（或日期时间、布尔值等）中。

Answer 2

首先，您需要一个函数和一个存储过程。 该函数将分割您的数据并返回一个表：

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

接下来您需要一个存储过程来使用它：

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

最后，您的代码：

[WebMethod]
        public MiniEvent[] getAdminEvents(int[] buildingIDs, DateTime startDate)
        command.CommandText = @"exec GetAdminEvents";
 SqlParameter buildID= new SqlParameter("@buildingIDs", buildingIDs);

这远远超出了您的问题所要求的范围，但它将满足您的需要。

注意：如果您传入任何不是 int 的值，整个数据库函数将会失败。 我将错误处理留给最终用户作为练习。

Answer 3

注意：我一般不赞成使用非参数化查询。 然而，在这种情况下，考虑到我们正在处理一个整数数组，您可以这样做，而且效率会更高。 然而，考虑到每个人似乎都想降级答案，因为它不符合他们的有效建议标准，我将提交另一个执行得很糟糕但可能会在 LINK2SQL 中运行的答案。

假设，正如您的问题所述，您有一个整数数组，您可以使用以下代码返回一个字符串，该字符串将包含 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);
}

然后，我建议您跳过尝试参数化命令 < em>假设这是一个整数数组并且只需使用：

command.CommandText = @"SELECT id,
            startDateTime, endDateTime From
            tb_bookings WHERE buildingID IN
            (" + SQLArrayToInString(buildingIDs) + ") AND startDateTime <=
            @fromDate";

Answer 4

超快的 XML 方法，不需要不安全的代码或用户定义的函数：

您可以使用存储过程并传递以逗号分隔的建筑物 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))

所有功劳都归于 Guru 布拉德·舒尔茨的博客

Answer 5

访问接受多个 Id 值的 T-SQL 存储过程 了解如何做到这一点的想法。

Answer 6

我使用这种方法并且对我有用。

我的变量 act = 我的 ID 列表，字符串。

行为=“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);

Answer 7

[Web方法]

public MiniEvent[] getAdminEvents(int buildingID, DateTime startDate)

...

SqlParameter buildID= new SqlParameter("@buildingIDs", buildingIDs);

也许我说得太详细了，但这个方法接受单个 int，而不是 int 数组。 如果您希望传入数组，则需要更新方法定义以拥有 int 数组。 获得该数组后，如果您计划在 SQL 查询中使用它，则需要将该数组转换为字符串。

Answer 8

你可以用这个。 在 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

创建函数后，您必须在代码中调用此函数：

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

此函数获取“array”上的文本内部逗号，并使用该值作为 int 创建一个表，称为ID。 当您的数据库中有此功能时，您可以在任何项目中使用。

---

感谢微软 MSDN。

Igo S Ventura

Microsoft MVA

Sistema Ari de Sá

[电子邮件受保护]

PS：我我来自巴西。 抱歉我的英语...XD

Answer 9

这是我想到的一个 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...
}