How to make parameterized query involving multiple tables with ADO.NET / C#?

I would like to make parameterized query for SQLquery that can be something like this:

SELECT * FROM Table1 WHERE Col1 IN (SELECT Col2 FROM Table2 WHERE Col3 IN (1, 2, 3));

The values come from WS interface and although I can trust the consumers I want to play it safe and use parameterized query involving DbParameters to prevent SQL injection. How to do it?

Thanks!

Solution

The key point, as you note, is to use parameters. IN clauses are notoriously problematic for that, annoyingly. Now, if you know the values are all integers (for example you are taking an int[] parameter to your C# method), then you can just about get away with things like:

cmd.CommandText = "SELECT * FROM Table1 WHERE Col1 IN (SELECT Col2 FROM Table2 WHERE Col3 IN ("
+ string.Join(",", values) + "))"; // please don't!!!

It is horrible, suggests a very bad practice (if somebody copies it for strings, you are in a world of pain), and can't use query plan caching. You could do something like:

var sb = new StringBuilder("SELECT * FROM Table1 WHERE Col1 IN (SELECT Col2 FROM Table2 WHERE Col3 IN (");
int idx = 0;
foreach(var val in values) {
    if(idx != 0) sb.Append(',');
    sb.Append("@p").Append(idx);
    cmd.Parameters.AddWithValue("@p" + idx, val);
    idx++
}
sb.Append("))");
cmd.CommandText = sb.ToString();

Which is preferable but awkward.

Or simplest: with a tool like dapper, let the library worry about it:

var data = conn.Query<YourType>(
    "SELECT * FROM Table1 WHERE Col1 IN (SELECT Col2 FROM Table2 WHERE Col3 IN @values)",
    new { values });

Here dapper spots the usage and "does the right thing". It also handles the "0 values" case for you.