sql - String list in SqlCommand through Parameters in C#

Question

Working with a SqlCommand in C# I've created a query that contains a IN (list...) part in the where clause. Instead of looping through my string list generating the list I need for the query (dangerous if you think in sqlInjection). I thought that I could create a parameter like:

SELECT blahblahblah WHERE blahblahblah IN @LISTOFWORDS

Then in the code I try to add a parameter like this:

DataTable dt = new DataTable();
dt.Columns.Add("word", typeof(string));
foreach (String word in listOfWords)
{
    dt.Rows.Add(word);
}
comm.Parameters.Add("LISTOFWORDS", System.Data.SqlDbType.Structured).Value = dt;

But this doesn't work.

Questions:

• Am I trying something impossible?
• Did I took the wrong approach?
• Do I have mistakes in this approach?

Thanks for your time :)

Answer 1

You want to think about where that list comes from. Generally that information is in the database somewhere. For example, instead of this:

SELECT * FROM [Table] WHERE ID IN (1,2,3)

You could use a subquery like this:

SELECT * FROM [Table] WHERE ID IN ( SELECT TableID FROM [OtherTable] WHERE OtherTableID= @OtherTableID )