Retrieve all records passing a list of ints in where clause

Question

Please help me fix the syntax for this command text "query" below. I want to pass a list of integers "ints" to retrieve all rows that match them.

private void(List<int>ints)
{     
     // some variables  
        string query =
                    string.Format(
                       "SELECT * FROM " +  table +
                       " WHERE SubId={0} AND RouteID IN({1})",
                       SubId,
                       ints
                       );

       // more stuff
 }

score 2 · Answer 1 · edited May 23 '17 at 11:43

2

If you would like to format a list of ints like that, you need to add commas in between:

string.Format(
    "SELECT * FROM " +  table +
    " WHERE SubId={0} AND RouteID IN({1})",
    SubId,
    string.Join(",", ints)
);

Assuming that everything is an int, nothing comes from user input, and the list is not too long, this should work fine. However, you should strongly consider moving to parameterized SQL. Specifically, in this case you could improve the query a great deal by using a table-valued parameter.

edited May 23 '17 at 11:43

Community

1
1

answered Jul 17 '15 at 18:08

Sergey Kalinichenko

714,442
84
1,110
1,523

"ints" is a List of integers. I incorporated your code, string.join works only for Join(String, String[], Int32, Int32) – Asha Jul 17 '15 at 18:18
And Join(String, Object[]) – Asha Jul 17 '15 at 18:18
1

@Asha It looks like you have C# 3.5, not 4.0. In 4.0 you could use a list of `int`s directly. 3.5 requires a `ToArray()` call: `string.Join(",", ints.ToArray())` – Sergey Kalinichenko Jul 17 '15 at 18:21

Retrieve all records passing a list of ints in where clause

1 Answers1