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

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

Solution 2

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

Comments

[Steven Ryssaert]

this will generate an error: your array variable should be numbers in the parameter value

[Mr Shoubs]

if you can use an array in the way Quassnoi suggest, I'd got with that.

[Quassnoi]

@UwConcept: honestly, it is a long time since I last used NPgSQL and don't even have a VS installed to check :)

[Quassnoi]

@Adrian: is the syntax OK or you had to correct it? I cannot check.

[Rios]

Use NpgsqlDbType | NpgsqlDbType.Numeric if you pass an int[]. Also I think its $numbers not :numbers? Other than that the syntax worked for me.