WHERE IN (array of IDs)

Solution 1:

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

Solution 2:

First you're going to need a function and a sproc. The function will split your data and return a table:

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:

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.

Solution 3:

NOTE: I am not generally for using unparameterized queries. IN THIS INSTANCE, however, given that we are dealing with an integer array, you could do 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.

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:

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 ints and just use:

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

Solution 4:

A superfast XML Method which requires no unsafe code or user defined functions :

You can use a stored procedure and pass the comma separated list of Building IDs :

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

Solution 5:

Visit T-SQL stored procedure that accepts multiple Id values for ideas on how to do this.