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? for example. Obligatory "use prepared statements instead of manually crafting SQL strings". — CodeCaster
– CodeCaster, Commented May 28, 2015 at 8:08
Can't u send the list as a parameter using Table valued parameter ? — Dreamweaver
– Dreamweaver, Commented May 28, 2015 at 8:08

fubo · Accepted Answer · 2015-05-28 08:10:10Z

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, 2015 at 8:10

fubo

46.2k20 gold badges109 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rahul Tripathi · Accepted Answer · 2015-05-28 08:08:59Z

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, 2015 at 8:08

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Collectives™ on Stack Overflow

SQL find rows with key specified in list, C#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related