WHERE IN (array of IDs)

by

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

More Related Contents:

  1. Use of SqlParameter in SQL LIKE clause not working
  2. SQL: Update a row and returning a column value with 1 query
  3. Why does the SqlParameter name/value constructor treat 0 as null?
  4. Passing array to a SQL Server Stored Procedure
  5. Procedure or function xxxxx has too many arguments specified [duplicate]
  6. An unhandled exception of type ‘System.InvalidOperationException’ occurred in System.Data.dll?
  7. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  8. How to pass an array into a SQL Server stored procedure
  9. Remove weird characters ( A with hat) from SQL Server varchar column
  10. How do I return multiple result sets with SqlCommand?
  11. SQL error: Incorrect syntax near the keyword ‘User’
  12. What represents a double in sql server?
  13. Passing List to SQL Stored Procedure
  14. SqlConnection SqlCommand SqlDataReader IDisposable
  15. SqlBulkCopy from a List
  16. ado.net Closing Connection when using “using” statement
  17. SqlParameter does not allows Table name – other options without sql injection attack?
  18. How to run multiple SQL commands in a single SQL connection?
  19. How can I retrieve a list of parameters from a stored procedure in SQL Server
  20. Creating a SQL Server table from a C# datatable
  21. How to keep single SQL Server connection instance open for multiple request in C#?
  22. How to pass XML from C# to a stored procedure in SQL Server 2008?
  23. How to use SqlCommand to CREATE DATABASE with parameterized db name?
  24. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  25. Using SqlDataAdapter to insert a row
  26. What size do you use for varchar(MAX) in your parameter declaration?
  27. Add WHERE clauses to SQL dynamically / programmatically
  28. Unable to connect to localDB in VS2012 – “A network-related or instance-specific error occurred while establishing a connection to SQL Server…”
  29. How do I connect to a SQL database from C#?
  30. How to give ADO.NET Parameters

Leave a Comment