How can I run a SQL Query with a list of String values using the "WHERE [columnname] IN [values]" format in ASP.NET?

Question

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 WHERE [columnname] IN [values] format. For example, the final query might look something like this:

SELECT PRODUCTNAME FROM PRODUCT WHERE PRODUCTCODE IN ('ABC','DEF','GHI','JKL', /* etc */);

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:

String[] productCodes = { "ABC", "DEF", "GHI", "JKL" };
SqlCommand cmd = "SELECT PRODUCTNAME FROM PRODUCT WHERE PRODUCTCODE IN (@ProductCodes)";
cmd.Parameters.Add("@ProductCodes", productCodes);

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.

I've read another question where someone suggested a solution for strongly-typed int parameters, 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.

How would you implement this query functionality?

EDIT with DB Details:

The database is SQL Server 2005. Sorry for forgetting to mention that.

Answer 1

Create your base sql statement as a format, and add the parameters dynamically, and then set the values in a loop.

String[] productCodes = { "ABC", "DEF", "GHI", "JKL" };
string sqlFormat = "SELECT PRODUCTNAME FROM PRODUCT WHERE PRODUCTCODE IN ({0})";
var @params = productCodes.Select((id, index) => String.Format("@id{0}", index)).ToArray();
var sql = String.Format(sqlFormat, string.Join(",", @params));

using(var cmd = new DbCommand(sql))
{
    for (int i = 0; i < productCodes.Length; i++)
        cmd.Parameters.Add(new Parameter(@params[i], DbType.String, productCodes[i]));
    // execute query
}

Answer 2

Just an idea:

String[] productCodes = { "ABC", "DEF", "GHI", "JKL" };
SqlCommand cmd = "SELECT PRODUCTNAME FROM PRODUCT WHERE PRODUCTCODE IN ("; 

for (int i=0;i<productCodes.Length;i++) {
  cmd.CommandText += "@" + productCodes[i] + ",";
  cmd.Parameters.Add("@" + productCodes[i], productCodes[i]);
}
cmd.CommandText = cmd.CommandText.SubString(0, cmd.CommandText.Length-1);
cmd.CommandText += ");"

Probably not the best way, but I guess this is how I would try it.

Answer 3

You'rel looking for table-valued parameters. However, these weren't available for sql server until well after asp.net was widely adopted, and so the support for them in asp.net is limited.

What I recommend instead is to think of it like building a shopping cart. Users add items to the cart, and at some point you want to display all the items in the cart. In this case, the natural solution is that the shopping cart itself is in a database table rather. Rather than pulling down the items for the card to include as part of an "IN (?)" directive, you build this as a subquery for your directive instead: "WHERE X IN (SELECT X FROM ShoppingCart WHERE UserID= @UserID AND SessionKey= @SessionKey)". Even if you have hundreds of items added, the user is only so going to be so fast and the load per-insert is spread fairly evenly.

Of course, you're probably building something other than a shopping cart, but nevertheless your query will almost always fall into one of three categories:

1. The items for your list are selected by the user by hand, in which case you can still have each selection result in a new database record that can in turn be used in a subquery
2. It's data that's already available in your database, in which case you should still be able to use a sub query (and if you can't, it might be time to add a "category" column somewhere so that you can).
3. It's data that's hard coded into your app, in which case you can also code it into your query

The rare exception is when the query is triggered by another machine source, or you may also have a lot of code that you are reluctant to re-work to make this possible.
So if, for whatever reason, this approach doesn't cut it for you, the standard article on the subject for sql server (including a few alternatives) can be found here:
http://www.sommarskog.se/arrays-in-sql.html

This article really is the standard work on the subject, and is well worth your time.

Answer 4

For the record, querying the hundred or so values in the array "not separately" could also be inefficient (though not as inefficient as a hundred or so round trips to the SQL Server). However, parameterizing the query helps a bit because you can prepare the same query and execute it several times. A stored procedure could be even better.

Any chance you can use Linq, or are you pre-.NET-3.5?

If you can't use Linq, and you absolutely must go this route, try the following:

SqlCommand cmd = "SELECT PRODUCTNAME FROM PRODUCT WHERE PRODUCTCODE = @ProductCode";
cmd.Prepare();
List<string> results;
foreach (string code in productCodes)
{
    cmd.Parameters.Clear();
    cmd.Parameters.Add("@ProductCodes", DbType.VarChar).Value = code;
    cmd.ExecuteQuery();
    // Add code here to add the returned values to the results list.  It's been
    // a while since I've used ADO.NET, and I don't have time to look it up
    // at the moment...
}

And then, you have your list of results.