在MySQL .NET Provider中使用命名参数 [英] Using named parameters with MySQL .NET Provider

问题描述

在MySQL .NET提供程序中，可以在语法中使用命名参数:

In the MySQL .NET provider, you can use named parameters in the syntax:

?parametername

现在，我正在尝试为将在"IN"列表中使用的参数创建命名参数，例如:

Now, I'm trying to create a named parameter for a parameter that will be used in an 'IN' list, e.g. :

select * from mytable where id in (?ids)

如何与此一起使用命名参数，如果使用varchar，它将在列表之前和之后添加引号，即:

How can I use a named parameter with this, if I use varchar, it will add quotes before and after the list, ie.:

如果我使用varchar传递参数的值:

If I pass the value of the parameter using varchar:

cmd.Parameters.Add("?ids", MySqlDbType.Varchar).Value = ids; // ids is a string which contains the ids separated by commas, e.g. 1, 2, 3 .. etc

查询将像这样执行:

select * from mytable where id in ('1, 2 ,3')

这当然会引发错误，我该如何在不获取引号的情况下传递命名参数，这是应该执行的方式:

Of course this will throw an error, how can I pass a named parameter without getting the quotes, this is the way it should be executed:

select * from mytable where id in (1, 2 , 3)

有什么解决方法吗?我目前正在使用String.Format()，但想使用一个命名参数，这可能有一些方法吗?

Is there any workaround for this? I'm currently using String.Format() but would like to use a named parameter, is this possible some how?

P.S.我只使用纯文本语句，不使用存储过程，因此这些都不会传递给存储过程(以防万一，因为存储过程不接受数组，所以您认为这不可能)

P.S. I'm only using plain text statements, no sproc, so none of this is going to be passed to an sproc (just in case you think it's not possible because sprocs don't accept arrays)

推荐答案

在这里已经被问了很多遍了，以至于我停止计数.

This has been asked here so many times that I've stopped to count.

无论技术如何，答案始终相同.您只需在计划具有IN参数"时向查询中添加尽可能多的参数即可.

It's always the same answer, regardless of technology. You simply must add as may parameters to your query as you plan to have IN "arguments".

如果要查询WHERE id IN (1, 2 ,3)，则准备好的语句必须如下所示:

If you want to query WHERE id IN (1, 2 ,3), your prepared statement must look like this:

SELECT * FROM mytable WHERE id IN (?, ?, ?)

使用您认为合适的任何字符串构建工具来制作这样的SQL字符串.之后，将三个参数值添加到其中.

Use whatever string building facility you see fit to make such an SQL string. After that, add the three parameter values to it.

准备好的语句的全部要点是将SQL代码与数据分开.逗号是SQL代码，您永远不会使用单个参数将它们放入语句中，它们必须在之前中.

It's the whole point of prepared statements to separate SQL code from data. The commas are SQL code, you will never get them into the statement with a single parameter, they must be in before.

好的，有 一种选择.制作一个单独的/临时表，在其中存储您的ID并查询类似以下内容的内容:

Okay, there is one alternative. Make a separate/temporary table, store your IDs in it and query something like this:

SELECT
  * 
FROM 
  mytable m
  INNER JOIN searchtable s ON m.id = s.id

这篇关于在MySQL .NET Provider中使用命名参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！