Using items from List<string> in SQL query

Question

Ok, I have a list that consists of a bunch of values from a sql query, that part works fine. What I want to do is use the items in that list to tell another query what to look for. So, what it is saying is that, it should return all columns from CMMReports where PartNumber is like %listItem1..2...3%, Any advice?

List<string> ImportedParts = GetImportedPartNumbers();

string query = "SELECT * FROM CMMReports WHERE (RacfId IS NULL OR RacfId = '') AND (FilePath NOT LIKE '%js91162%') AND PartNumber LIKE %" + ImportedParts + "% ORDER BY CreatedOn DESC;";

Answer 1

Not that I condone this as you should be using parameterized queries. However, this should work:

StringBuilder partNumbers = new StringBuilder();
foreach (string queryValue in ImportedParts)
{
    string q = "PartNumber LIKE '%" + queryValue + "%'";
    if (string.IsNullOrEmpty(partNumbers.ToString())
    {
       partNumbers.Append(q);
    }
    else
    {
       partNumbers.Append(" OR " + q);
    }
}

string query = string.Format("SELECT * FROM CMMReports WHERE (RacfId IS NULL OR RacfId = '') " +
           "AND (FilePath NOT LIKE '%js91162%') AND ({0}) " +
           "ORDER BY CreatedOn DESC;", partNumbers.ToString());

Answer 2

Untested, but you should get the idea:

List<string> ImportedParts = GetImportedPartNumbers(); 

SqlCommand cmd = myConnection.CreateCommand();
cmd.CommandText = "SELECT * FROM CMMReports WHERE (RacfId IS NULL OR RacfId = '') AND (FilePath NOT LIKE '%js91162%') AND (";

int i = 0;
foreach (string part in ImportedParts) {
    cmd.AddParameterWithValue("@param" + i.ToString(), "%" + part + "%");
    if (i != 0) cmd.CommandText += " OR"
    cmd.CommandText += " PartNumber LIKE @param" + i.ToString();
    i++;
}

cmd.CommandText += ") ORDER BY CreatedOn DESC;";

This solution uses a parameterized query instead of just appending strings in the SQL, which is considered a potential security risk.

Answer 3

You might look up the IN clouse for SQL that way you get the answer for the parts that SQL Server can find in the database. Using WHERE x = y for all the items means that if one item can't be found the whole query returns nothing.

Answer 4

I would consider doing this in a stored procedure and passing in your list as an Xml parameter.

See the following article for more info on using Xml parameters in a stored proc:

Passing lists to SQL Server 2005 with XML Parameters - By Jon Galloway

Form there you can easily use your list data inside your stored proc using the Xml syntax and treat it almost as another table of data.