postgresql 如何将参数传递给 sql 'in' 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5701482/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pass parameter to sql 'in' statement?
提问by Adrian Serafin
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:
但我找不到任何使用 Npgsql 和 NpgsqlParameter 实现此目标的示例。我试过这样：
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 ;)
但它没有用；）
回答by Quassnoi
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);
回答by Mr Shoubs
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 ")".
您需要动态创建您的命令字符串 - 使用您的第一个参数作为 :num0 循环，第二个作为 :num1 等。当您添加所有这些后，删除最后一个字符“,”并将其替换为“)”。
回答by Arman Hayots
In addition to @Quassnoi answer I'll add this one to show, how we done it in real code.
除了@Quassnoi 的回答之外，我还会添加这个来展示我们是如何在实际代码中完成的。
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);
回答by srini
use "delete from test where id IN (select unnest(@ids))"
使用“从测试中删除 id IN（选择 unnest（@ids））”
