SQL find rows with key specified in list, C#

Question

I want to make a sql query in C# that finds all rows with a key that is specified in a list. Can I do this with one query? I suppose that is much more efficent than my solution which finds one item at the time inside a for loop, se below:

foreach (int i in list)
{
    string Q = "... where pk = " + i.ToString();
    using (SqlCommand CM = new SqlCommand(Q, C))
    {
         using (SqlDataReader R = CM.ExecuteReader())
         {
              while (R.Read())
              {
                  ...
              }
          }
     }
}

list contains different in values.

Thanks in advance!

Take a look at the `IN` SQL keyword. [Building SQL “where in” statement from list of strings in one line?](http://stackoverflow.com/questions/6272214/building-sql-where-in-statement-from-list-of-strings-in-one-line) for example. Obligatory "use prepared statements instead of manually crafting SQL strings". — CodeCaster, May 28 '15 at 08:08
Can't u send the list as a parameter using Table valued parameter ? — Dreamweaver, May 28 '15 at 08:08

score 3 · Accepted Answer · answered May 28 '15 at 08:10

3

Replace

string Q = "... where pk = " + i.ToString();

with

string Q = "... where pk IN ('" + string.Join("','", list)+"')";

then you can remove the loop. The result should look like ... where pk IN ('1','2','3')

answered May 28 '15 at 08:10

fubo

44,811
17
103
137

score 0 · Answer 2 · answered May 28 '15 at 08:08

0

You can use the IN keyword and pass your list by converting it to a comma seperated string in your query.

Something like

string Q = "select * from tablename where pk IN " + (comma seperated list here);

answered May 28 '15 at 08:08

Rahul Tripathi

168,305
31
280
331

SQL find rows with key specified in list, C#

2 Answers2