How to pass unknown number of parameters to sql query from C#?

Question

I have a simple query where in FROM I've got something like:

WHERE ([Comment]  LIKE '%something%'
      OR [Comment] LIKE '%aaaa%'
      OR [Commnet] LIKE '%ccc') 
AND Replace([Number], ' ', '') = @number

Unfortunetly this is now hardcoded in code so if anything changes (more OR's on the [Comment] field I have to go and change it in the code. Is it possible to pass [Comment] like a parameter with multiple values (unknown to me) so I would create some SQL Table with every comment I want to use in that query (and users would be able to add new ones from within program so it would be taken care of automatically?

  using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails)) 
        using (var sqlQuery = new SqlCommand(preparedCommand, varConnection)) {
            sqlQuery.Parameters.AddWithValue("@number", varNumber);
            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                while (sqlQueryResult.Read()) {
                    string varID = sqlQueryResult["ID"].ToString();

                }
        }

Answer 1

You can use table value parameters in SQL Server 2008.

For earlier versions, you could pass in an XML parameter and parse the data with XPath.

For good in depth analysis of the different options, read this article by Erland Sommarskog (SQL Server 2005) and the additional article about SQL Server 2008.

Answer 2

Even if the solution of Oded seems to be the best way to go (which i also didn't know till now), i currently built up the command and parameters in some way automatically like this (which doesn't currently match your problem, cause you're using like):

// Some sql statement with a placeholder for all parameters (@ID0, @ID1, @ID2, ...)
string sql = "select * from table where ID in ( {0} )";

// Create a list of items of @ID0, 3; @ID1, 8; ...
var parameters = myList.Where(item => item.MatchesSomeCondition())
                       .Select((item, index) => new
                               {
                                   Name = "@ID" + index,
                                   Value = item.ID
                               });

// Add all parameters to the sqlCmd
foreach(parameter in parameters)
{
    sqlCmd.Parameters.AddWithValue(parameter.Name, parameter.Value);
}

// Insert all @IDx into the sql statement
// Result: "select * from table where ID in ( @ID0, @ID1, @ID2, ... )"
sqlCmd.CommandText = String.Format(sql, String.Join(", ", parameters.Select(parameter => parameter.Name).ToArray()