在 C# 中多次使用单个参数的更好方法

发布于 2024-12-12 00:18:40 字数 1309 浏览 0 评论 0原文

我刚开始使用准备好的语句从数据库查询数据,并且在实现 C# 参数(特别是 OracleParameters)时遇到问题。

假设我有以下 SQL:

string sql = "select * 
              from table1 t1, table2 t2 
              where t1.columnX = @parm and t2.columnY = @parm"

代码是这样的:

OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter("@parm", strParm));

问题是当 cmd 执行时 t1.columnX 获取 strParm 的值,但是当 t2.columnY 即将获取 strParm 的值时,它会抛出一个“ ORA-01008: 并非所有变量都绑定”异常。

在我看来,即使该参数在 sql 中的其他地方看到,该参数也只能被替换一次。

我尝试过并且对我有用的一个解决方案是这样的:

OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter("@parm", strParm));
cmd.Parameters.Add(new OracleParameter("@parm", strParm));

另一种解决方案是这样的:

OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter("@parm1", strParm));
cmd.Parameters.Add(new OracleParameter("@parm2", strParm));

修改后的sql是这样的:

string sql = "select * 
             from table1 t1, table2 t2 
             where t1.columnX = @parm1 and t2.columnY = @parm2" 

问题是,是否有更好的方法来做到这一点,这样我就不必添加另一个具有相同值的参数。

注意:我刚刚简化了上面的查询,以显示 @parm 在查询的多个部分中使用。在现实生活中,此查询多次使用相同的参数,并且必须添加多个具有相同名称和值的参数是一件痛苦的事情。

I'm new in using prepared statements for querying data from the database and I'm experiencing problems implementing parameters for c# specifically OracleParameters.

So let's say I have the following SQL:

string sql = "select * 
              from table1 t1, table2 t2 
              where t1.columnX = @parm and t2.columnY = @parm"

And the code is this:

OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter("@parm", strParm));

The problem is when the cmd gets executed t1.columnX gets the value of strParm but when t2.columnY is just about to get the value of strParm, it throws an "ORA-01008: not all variables bound" exception.

It seems to me that the parameter gets to be substituted only once even though that parameter is seen somewhere else in the sql.

One solution I tried and works for me is this:

OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter("@parm", strParm));
cmd.Parameters.Add(new OracleParameter("@parm", strParm));

Another solution is this:

OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter("@parm1", strParm));
cmd.Parameters.Add(new OracleParameter("@parm2", strParm));

and the modified sql is this:

string sql = "select * 
             from table1 t1, table2 t2 
             where t1.columnX = @parm1 and t2.columnY = @parm2" 

The question is, is there a better way of doing this so that I don't have to add another parameter with the same value.

Note: I've just simplified the query above to show that @parm is used in several parts of the query. In real life this query has used the same parameter several times and its a pain to have to add more than one parameter of the same name and value.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

春风十里 2024-12-19 00:18:40

我之前遇到过同样的问题,IIRC 解决了它:

cmd.BindByName = true;

编辑:我刚刚重新检查过,这确实允许您设置参数值一次,即使该参数可能在查询中指定多次。我所做的唯一与您不同的事情是我使用前导 : 指定参数名称,例如 :param

I've had the same issue before and IIRC this solved it:

cmd.BindByName = true;

EDIT: I've just re-checked and this does allow you to set the parameter value once even though the parameter may be specified multiple times in the query. The only other thing that I'm doing differently to you is that I specify the parameter names with a leading :, for example :param.

满身野味 2024-12-19 00:18:40

在您的情况下,似乎不需要实际使用两个参数。如何将 SQL 更改为:

select * from table1 t1, table2 t2 where t1.columnX = @parm and t2.columnY = t1.columnX

甚至

select * from table1 t1 join table2 t2 on t2.columnY = t1.columnX where t1.columnX = @parm

In your case, there doesn't seem to be a need to actually use two parameters. How about changing your SQL to:

select * from table1 t1, table2 t2 where t1.columnX = @parm and t2.columnY = t1.columnX

or even

select * from table1 t1 join table2 t2 on t2.columnY = t1.columnX where t1.columnX = @parm
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文