Add List<int> to a mysql parameter

Question

I have this question about the MySqlParameter from the .NET connector.

I have this query:

SELECT * FROM table WHERE id IN (@parameter) 

And the MySqlParameter is:

intArray = new List<int>(){1,2,3,4};  ...connection.Command.Parameters.AddWithValue("parameter", intArray); 

This is possible? Is possible to pass an array of int to a single MySqlParameter? The other solution will be convert the array of int to a string such like "1,2,3,4", but this, when i pass it to the MySqlParameter and this is recognized as a string, it puts in the sql query like "1\,2\,3\,4" and this do not return the expected values.

@ UPDATE: Seems like the mysql connector team should work a little bit harder.

Answer 1

when i pass it to the MySqlParameter and this is recognized as a string, it puts in the sql query like "1\,2\,3\,4" and this do not return the expected values.

I ran into this last night. I found that FIND_IN_SET works here:

SELECT * FROM table WHERE FIND_IN_SET(id, @parameter) != 0 ... intArray = new List<int>(){1,2,3,4}; conn.Command.Parameters.AddWithValue("parameter", string.Join(",", intArray)); 

Apparently this has some length limitations (I found your post looking for an alternate solution), but this may work for you.

Answer 2

Parameters don't work with IN. I have always embedded such things as a string in the query itself. While that is generally considered bad form because SQL injection, if you are constructing the query from a strongly typed numeric list, then there should be no possibility of any external input corrupting it in a meaningful way.