How to pass parameter to sql 'in' statement?

Question

I want to create this query:

select * from products where number in ('123', '234', '456');

but I can't find any example of achiving this with Npgsql and NpgsqlParameter. I tried like this:

string[] numbers = new string[] { "123", "234" };

NpgsqlCommands cmd = new NpgsqlCommands("select * from products where number in (:numbers)");
NpgsqlParameter p = new NpgsqlParameter("numbers", numbers);
command.Parameters.Add(p);

but it didn't work ;)

Answer 1

Pass it as an array:

string[] numbers = new string[] { "123", "234" };

NpgsqlCommands cmd = new NpgsqlCommands("select * from products where number = ANY(:numbers)");
NpgsqlParameter p = new NpgsqlParameter("numbers", NpgsqlDbType.Array | NpgsqlDbType.Text);
p.value = numbers;
command.Parameters.Add(p);

Answer 2

In addition to @Quassnoi answer I'll add this one to show, how we done it in real code.

Warning! This working code is from real project and can damage your beautiful approaches!

string commstr = "SELECT product_name, price, product_url, image_url FROM products WHERE id  = ANY(@arr);";
NpgsqlCommand cm = new NpgsqlCommand(commstr, cn);
NpgsqlParameter arpar = new NpgsqlParameter();
arpar.ParameterName = "arr";
arpar.NpgsqlDbType = NpgsqlDbType.Array | NpgsqlDbType.Bigint;
arpar.Value = PerformQuerySphinx(query, limit);
cm.Parameters.Add(arpar);

Answer 3

You need to dynamically create your command string - loop with your first parameter as :num0, second as :num1 etc. When you've added all of them, remove the last character "," and replace it with ")".

Answer 4

use "delete from test where id IN (select unnest(@ids))"

Answer 5

Updated answer for ~2021:

You can use ANY like this:

WHERE id = ANY (@ids)

Then when you add your params, do this:

cmd.Parameters.AddWithValue("ids", ids.ToArray());