String list in SqlCommand through Parameters in C#

Question

Hi folks,

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 )

Answer 2

I would recommend setting the parameter as a comma delimited string of values and use a Split function in SQL to turn that into a single column table of values and then you can use the IN feature.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648 - Split Functions

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

If you want to pass the list as a string in a parameter, you could just build the query dynamically.

DECLARE @query varchar(500) SET @query = 'SELECT blah blah WHERE blahblah in (' + @list + ')' EXECUTE(@query)

Answer 5

I used to have the same problem, I think there is now way to do this directly over the ADO.NET API.

You might consider inserting the words into a temptable (plus a queryid or something) and then refering to that temptable from the query. Or dynamically creating the query string and avoid sql injection by other measures (e.g. regex checks).

Answer 6

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 7

• 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.