Monday, 11 July 2011

Parameterize a query containing an IN clause

Parameterize a query containing an IN clause

For example we have query like this and you wanted to parameterize it in your C# code for obvious security reasons:
select * from Tags
where Name in ('var','cars','mud','cure')
order by Count desc

To achieve it in your C# code:
Create an array that consists of all the values of IN clause
        string[] tags = new string[] { "var", "cars", "mud", "cure" };
Create the Query like this:
        const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

Now in your command object add the array using string.Join() function.

        using (SqlCommand cmd = new SqlCommand(cmdText))
        {
            cmd.Parameters.AddWithValue("@tags", string.Join("|", tags));
        }

And you are done!!