CODEWITHSUNDEEP
c#postgresqlado.netnpgsql
Adrian Serafin
Adrian Serafin

Reputation: 7715

How to pass parameter to sql 'in' statement?

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 ;)

Upvotes: 20

Views: 21641

Answers (5)

JacobIRR
JacobIRR

Reputation: 8966

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());

Upvotes: 2

srini
srini

Reputation: 21

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

Upvotes: 1

Arman Hayots
Arman Hayots

Reputation: 2508

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);

Upvotes: 1

Mr Shoubs
Mr Shoubs

Reputation: 15419

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 ")".

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425683

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);

Upvotes: 39

Related Questions