[ACCEPTED]-Parameterized Queries with LIKE and IN conditions-parameterized

Let's say that you have your category ids 26 in an integer array and Name is a string. The 25 trick is to create the command text to allow 24 you to enter all of your category ids as 23 individual parameters and construct the 22 fuzzy match for name. To do the former, we 21 use a loop to construct a sequence of parameter 20 names @p0 through @pN-1 where N is the number 19 of category ids in the array. Then we construct 18 a parameter and add it to the command with 17 the associated category id as the value 16 for each named parameter. Then we use concatenation 15 on the name in the query itself to allow 14 the fuzzy search on name.

string Name = "someone";
int[] categoryIDs = new int[] { 238, 1138, 1615, 1616, 1617,
                                1618, 1619, 1620, 1951, 1952,
                                1953, 1954, 1955, 1972, 2022 };

SqlCommand comm = conn.CreateCommand();

string[] parameters = new string[categoryIDs.Length];
for(int i=0;i<categoryIDs.Length;i++)
{
   parameters[i] = "@p"+i;
   comm.Parameters.AddWithValue(parameters[i], categoryIDs[i]);
}
comm.Parameters.AddWithValue("@name",$"%{Name}%");
comm.CommandText = "SELECT * FROM Products WHERE Category_ID IN (";
comm.CommandText += string.Join(",", parameters) + ")";
comm.CommandText += " OR name LIKE @name";

This is a fully 13 parameterized query that should make your 12 DBA happy. I suspect that since these 11 are integers, though it would not be much 10 of a security risk just to construct the 9 command text directly with the values, while 8 still parameterizing the name. If your 7 category ids are in a string array, just 6 split the array on commas, convert each 5 to an integer, and store it in the integer 4 array.

Note: I say array and use it in the example, but 3 it should work for any collection, although 2 your iteration will probably differ.

Original 1 idea from http://www.tek-tips.com/viewthread.cfm?qid=1502614&page=9

You need "%" in value of sql parameter.

SqlCommand comm = new SqlCommand("SELECT * FROM Products WHERE Category_ID IN (@categoryid1, @categoryid2) OR name LIKE @name", conn);
comm.Parameters.Add("@categoryid1", SqlDbType.Int);
comm.Parameters["@categoryid1"].Value = CategoryID[0];
comm.Parameters.Add("@categoryid2", SqlDbType.Int);
comm.Parameters["@categoryid2"].Value = CategoryID[1];
comm.Parameters.Add("@name", SqlDbType.NVarChar);
comm.Parameters["@name"].Value = "%" + Name + "%";

0

This approach will not work. Period.

The 7 IN clause expects a list of parameters itself, so 6 when you bind one parameter to it, you have 5 the chance to pass in one value.

Build your 4 statement string dynamically, with the exact 3 amount of individual IN clause placeholders 2 you intend to pass in, and then add parameters 1 and bind values to them in a loop.

not sure if this is the right way but it 3 is a way I have done it in the Before

list 2 templist = new list

comm.Parameters.Add("@categoryids", SqlDbType.varchar); comm.Parameters["@categoryids"].value 1 = string.join(",",templist.toarray())