SqlParameter conversion

Question

I try to call the SQL statement below, but I get the following error:

System.Data.SqlClient.SqlException: during conversion, it was not possible to convert the varchar value '+ @buildingIDs +' for the int data type.

@"SELECT id, startDateTime, endDateTime FROM tb_bookings WHERE buildingID IN (' +@buildingIDs +') AND startDateTime <= @fromDate"; 

buildingID - column of type int in db. Do I need to pass identifiers as an ints array?

Answer 1

Bravax mode is a bit dangerous. I would go with the following so that you are not attacked with SQL Injections:

 int[] buildingIDs = new int[] { 1, 2, 3 }; /***/ @"SELECT id, startDateTime, endDateTime From tb_bookings WHERE buildingID IN (" + string.Join(", ", buildingIDs.Select(id => id.ToString()).ToArray()) + ") AND startDateTime <= @fromDate"; 

Answer 2

Note that LINQ can do this through Contains (which matches IN). With regular TSQL, another option is to pass the list as a CSV (etc.) varchar and use a table-based UDF to split the varchar into parts. This allows you to use one TSQL query (by making an INNER JOIN for a UDF result).

Answer 3

I would prefer to go to the stored procedure, if possible, and pass the identifier lists as xml. You can get more information about this approach from here .

Answer 4

It tries to compare int with the string value '+ @buildingsIDs +'
Therefore, it tries to convert the string to convert it to int and failed.

So do the following:
buildingsIDs = "1, 5, 6";
@"SELECT id, startDateTime, endDateTime From tb_bookings WHERE buildingID IN (" + buildingIDs + ") AND startDateTime <= @fromDate";