C# SqlDataAdapter - Select Command Concern

Question

I'm having trouble with my select command. The scenario is when i'm putting many value in my variable I got nothing but if i put only one value in variable it is working.

All I want is to run this script:

SELECT * 
FROM gsm00 a 
INNER JOIN gsd00 b ON a.idnum=b.idnum WHERE b.idnum IN ('0001','0002')

Here's my sample code:

string val= "0001" - this is working

//string val= "0001,0002" - this is not working

query = "SELECT * FROM gsm00 a INNER JOIN gsd00 b ON a.idnum=b.idnum WHERE b.idnum IN (@searchKey)";

MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, dbconfig.conn);
dataAdapter.SelectCommand.Parameters.AddWithValue("@searchKey", val);
dataAdapter.Fill(dataTable);
dataAdapter.Dispose();

Answer 1

In the first case, your @searchKey Value ist "0001" and this means your statement looks like

SELECT * FROM gsm00 a INNER JOIN gsd00 b ON a.idnum=b.idnum WHERE b.idnum IN ('0001')

this works because it is 1 parameter and the internal paramter handling is putting single-quotes around it.

The seconds case looks like this:

SELECT * FROM gsm00 a INNER JOIN gsd00 b ON a.idnum=b.idnum WHERE b.idnum IN ('0001,0002')

but it has to look like

SELECT * FROM gsm00 a INNER JOIN gsd00 b ON a.idnum=b.idnum WHERE b.idnum IN ('0001','0002')

Its not implicitly possible to check from your statement if its 1 paremeter or multiple parameters because the "," can be a part of your IN-Clause value.

This is a similar question with solutions, maybe you have also a look at this:

How to pass sqlparameter to IN()?

Answer 2

try:

query = "SELECT * FROM gsm00 a INNER JOIN gsd00 b ON a.idnum=b.idnum WHERE b.idnum IN ({@searchKey})";
...
var a = new string [] {"0001", "0002" } ;
dataAdapter.SelectCommand.AddArrayParameters("@searchKey", a );