Using named parameters with MySQL .NET Provider

Question

In the MySQL .NET provider, you can use named parameters in the syntax:

?parametername

Now, I'm trying to create a named parameter for a parameter that will be used in an 'IN' list, e.g. :

select * from mytable where id in (?ids)

How can I use a named parameter with this, if I use varchar, it will add quotes before and after the list, ie.:

If I pass the value of the parameter using varchar:

cmd.Parameters.Add("?ids", MySqlDbType.Varchar).Value = ids; // ids is a string which contains the ids separated by commas, e.g. 1, 2, 3 .. etc

the query will be executed like this:

select * from mytable where id in ('1, 2 ,3')

Of course this will throw an error, how can I pass a named parameter without getting the quotes, this is the way it should be executed:

select * from mytable where id in (1, 2 , 3)

Is there any workaround for this? I'm currently using String.Format() but would like to use a named parameter, is this possible some how?

P.S. I'm only using plain text statements, no sproc, so none of this is going to be passed to an sproc (just in case you think it's not possible because sprocs don't accept arrays)

Answer 1

This has been asked here so many times that I've stopped to count.

It's always the same answer, regardless of technology. You simply must add as may parameters to your query as you plan to have IN "arguments".

If you want to query WHERE id IN (1, 2 ,3), your prepared statement must look like this:

SELECT * FROM mytable WHERE id IN (?, ?, ?)

Use whatever string building facility you see fit to make such an SQL string. After that, add the three parameter values to it.

It's the whole point of prepared statements to separate SQL code from data. The commas are SQL code, you will never get them into the statement with a single parameter, they must be in before.

Okay, there is one alternative. Make a separate/temporary table, store your IDs in it and query something like this:

SELECT
  * 
FROM 
  mytable m
  INNER JOIN searchtable s ON m.id = s.id

Answer 2

The problem is that you're not passing just one parameter. You want to pass a collection of paramaters. For as far as I know there's no support for this yet (no DbType for arrays or collections). So you can't add multiple values as one parameter.

Since (I assume) the number elements in 'ids' can vary you'd need to change the command string to have the right amount of parameters. It would be possible to use some loops ot generate both the command string and fill the parameters with their respective values. This basicly means you're making a new command for every query.

Another solution would be to use an stored procedure that takes your comma-seperated list and uses it to build the query.