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

What you are trying to do is possible but not using your current approach. This is a very common problem with all possible solutions prior to SQL Server 2008 having trade offs related to performance, security and memory usage.

This link shows some approaches for SQL Server 2000/2005

SQL Server 2008 supports passing a table value parameter.

I hope this helps.

Answer 2

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 )

Answer 3

If I understand right, you're trying to pass a list as a SQL parameter.

Some folks have attempted this before with limited success:

Passing Arrays to Stored Procedures

Arrays and Lists in SQL 2005

Passing Array of Values to SQL Server without String Manipulation

Using MS SQL 2005's XML capabilities to pass a list of values to a command

Answer 4

• Am I trying something impossible?

No, it isn't impossible.

• Did I took the wrong approach?

Your approach is not working (at least in .net 2)

• Do I have mistakes in this approach?

I would try "Joel Coehoorn" solution (2nd answers) if it is possible. Otherwise, another option is to send a "string" parameter with all values delimited by an separator. Write a dynamic query (build it based on values from string) and execute it using "exec".

Another solution will be o build the query directly from code. Somthing like this:

StringBuilder sb = new StringBuilder();
for (int i=0; i< listOfWords.Count; i++)
{
    sb.AppendFormat("p{0},",i);
    comm.Parameters.AddWithValue("p"+i.ToString(), listOfWords[i]);
}

comm.CommandText = string.Format(""SELECT blahblahblah WHERE blahblahblah IN ({0})", 
sb.ToString().TrimEnd(','));

The command should look like:

SELECT blah WHERE blah IN (p0,p1,p2,p3...)...p0='aaa',p1='bbb'

In MsSql2005, "IN" is working only with 256 values.