凡（ID的数组） [英] WHERE IN (array of IDs)

问题描述

我有一个传递int数组的web服务。 我想，如下所示做SELECT语句，但不断收到错误。我需要的数组更改为一个字符串？

  [WebMethod的]
公共MiniEvent [] getAdminEvents（INT buildingID，日期时间的startDate）
{
    command.CommandText = @SELECT ID，
                            startDateTime，endDateTime从
                            tb_bookings WHERE buildingID IN
                            （@buildingIDs）和startDateTime＆LT; =
                            @从日期;

    的SqlParameter buildID =新的SqlParameter（@ buildingIDs，buildingIDs）;
}
 

解决方案

您不能（不幸）做到这一点。一个SQL参数只能是一个值，所以你要做的：

 其中buildingID IN（@ buildingID1，@ buildingID2，@ buildingID3 ...）
 

其中，当然，需要你知道有多少建筑IDS存在，或者动态地构造查询。

作为一种解决方法*，我已经做了以下内容：

 其中buildingID IN（@buildingID）

command.CommandText = command.CommandText.Replace（
  @buildingID
  的string.join（buildingIDs.Select（二=＆GT; b.T​​oString（）），，）
）;
 

将取代同号声明的文本，结束了，就像这样：

 其中buildingID IN（1,2,3,4）
 

• 请注意，这是越来越接近SQL注入漏洞，但因为它是一个int数组是安全的。任意字符串的没有的安全，但是没有办法嵌入到一个整数的SQL语句（或日期时间，布尔值，等等）。

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

解决方案

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...)

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)

• 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).

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