title: How can I run a SQL Query with a list of String values using the "WHERE [columnname] IN [values]" format in ASP.NET?
body: <p>I have a SQL query I'm running in an ASP.NET page. The final parsed SQL needs to contain a list of string values in the <code>WHERE [columnname] IN [values]</code> format. For example, the final query might look something like this:</p>
  
  <pre><code>SELECT PRODUCTNAME FROM PRODUCT WHERE PRODUCTCODE IN ('ABC','DEF','GHI','JKL', /* etc */);
  </code></pre>
  
  <p>However, the string values in the WHERE clause need to be dynamic. Normally I use parametrized queries to make my code convenient and safe, so conceptually I'd like to do something like this:</p>
  
  <pre><code>String[] productCodes = { "ABC", "DEF", "GHI", "JKL" };
  SqlCommand cmd = "SELECT PRODUCTNAME FROM PRODUCT WHERE PRODUCTCODE IN (@ProductCodes)";
  cmd.Parameters.Add("@ProductCodes", productCodes);
  </code></pre>
  
  <p>However, this sort of functionality doesn't appear to exist in .NET. How should I go about implementing this? I could use a foreach loop on the array and run a query with a single value as a parameter for each value, but there could potentially be a hundred or so different values in the array and it seems like querying them separately would be very inefficient.</p>
  
  <p>I've read <a href="https://stackoverflow.com/questions/182060/sql-where-in-array-of-ids">another question where someone suggested a solution for strongly-typed int parameters</a>, but that method would make me nervous about SQL injection when used with String values, especially since the client may very well be able to influence the input values.</p>
  
  <p>How would you implement this query functionality?</p>
  
  <p><b>EDIT with DB Details</b>:</p>
  
  <p>The database is SQL Server 2005. Sorry for forgetting to mention that.</p>