WHERE IN(ID 数组) [英] WHERE IN (array of IDs)

问题描述

我有传递整数数组的网络服务.我想按如下方式执行 select 语句，但不断出现错误.我需要将数组更改为字符串吗?

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?

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

推荐答案

你不能(很遗憾)那样做.Sql 参数只能是单个值，因此您必须这样做:

You can't (unfortunately) do that. A Sql Parameter can only be a single value, so you'd have to do:

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

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

Which, of course, requires you to know how many building ids there are, or to dynamically construct the query.

作为一种解决方法*，我已完成以下操作:

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)

• 请注意，这接近于 Sql 注入漏洞，但由于它是一个 int 数组是安全的.任意字符串不安全，但无法在整数(或日期时间、布尔值等)中嵌入 Sql 语句.
• Note that this is getting close to a Sql injection vulnerability, but since it's an int array is safe. Arbitrary strings are not safe, but there's no way to embed Sql statements in an integer (or datetime, boolean, etc).

这篇关于WHERE IN(ID 数组)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！