用于 Oracle 的 C# 参数化查询

本文介绍了用于 Oracle 的 C# 参数化查询 - 认真&危险的错误！的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这绝对是一个咆哮者.我不敢相信自己的眼睛，而且我无法相信在我之前没有人会发现它，如果它是 C# 中的真正错误，所以我将它发布给其他开发人员社区，告诉我我做错了什么.我敢肯定这个问题会让我说DOH！"并用我的手掌狠狠地拍打我的头 - 但无论如何……

This is an absolute howler. I cannot believe my own eyes, and I cannot believe nobody before me would have discovered this if it was a genuine bug in C#, so I'm putting it out for the rest of the developer community to tell me what I am doing wrong. I'm sure this question is going to involve me saying "DOH!" and smacking my head very hard with the palm of my hand - but here goes, anyway...

为了测试，我创建了一个表Test_1，脚本如下:

For the sake of testing, I have created a table Test_1, with script as follows:

CREATE TABLE TEST_1 ( COLUMN1 NUMBER(12) NOT NULL, COLUMN2 VARCHAR2(20), COLUMN3 NUMBER(12)) TABLESPACE USERS STORAGE ( INITIAL 64K MAXEXTENTS UNLIMITED ) LOGGING;

现在我执行以下代码:

var conn = new OracleConnection("connectionblahblah"); conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandText = "insert into Test_1(Column1, Column2, Column3) " + "values(:Column1, :Column2, :Column3)"; var p = cmd.Parameters; p.Add("Column1", 1); p.Add("Column3", null); p.Add("Column2", "record 1"); cmd.ExecuteNonQuery();

哇！我收到一个 ORA-01722 错误 - 无效号码"！不过怎么了?Column1 是数字，值为 1，所以没问题；Column2 是一个字符串，而 Column3 是一个可以为空的列，所以应该不会造成任何麻烦...

Whoa! I get an ORA-01722 error - "invalid number"! What's wrong, though? Column1 is numeric, and has a value of 1, so that's fine; Column2 is a string, and Column3 is a nullable column, so that shouldn't cause any trouble...

现在坐下来看看这个……这里的问题是 Column3 和 Column2 按照它们添加到 OracleParameterCollection 的顺序转置.切换它们，然后快速！它有效！

Now sit down for this one... the problem here is that Column3 and Column2 are transposed in the order in which they are added to the OracleParameterCollection. Switch them around, and presto! It works!

当然，这将引导我进行下一个明显的实验...让我们更改用于添加参数的代码块，如下所示:

This, of course, leads me to the next obvious experiment... let's change that block of code for adding parameters like so:

p.Add("Foo", 1); p.Add("Bar", "record 1"); p.Add("hahahahahahaha", null);

你认为这行得通吗?猜猜看 - 它可以！

You think that'll work? Well guess what - it does!

我坐在这里完全惊呆了.我不敢相信我所看到的，我也不敢相信在我之前没有人发现过这种行为(除非我不知道如何正确使用 Google).

I am sitting here absolutely stunned. I cannot believe what I am seeing, and I likewise cannot believe that nobody before me has discovered this behavior (unless I don't know how to use Google properly).

这不仅仅是一种烦恼 - 这是非常危险的.如果我调换了相同数据类型的两列会发生什么?我什至不会出错 - 我会简单地将错误的数据插入到错误的列中，而这并不明智.

This is not just an annoyance - it is seriously dangerous. What would have happened if I'd transposed two columns of the same data type? I wouldn't have even got an error - I would have simply inserted the wrong data into the wrong columns, and been none the wiser.

除了注意不要以错误的顺序添加参数之外，有没有人有任何解决方法的想法?

Does anyone have any ideas for a workaround - other than just being careful not to add parameters in the wrong order?

推荐答案

这不是错误，而是在 Oracle ODP.Net 文档中明确提及.在 OracleCommand 类中，参数默认按位置绑定.如果要按名称绑定，请显式设置属性 cmd.BindByName = true;.

This is not a bug but explicitly mentioned in Oracle ODP.Net documentation. In a OracleCommand class the parameters are bound by position as default. If you want to bind by name then set the property cmd.BindByName = true; explicitly.

参考 Oracle 文档.download.oracle/docs/cd/E11882_01/win.112/e12249/OracleCommandClass.htm#i997666

Reference to Oracle documentation. download.oracle/docs/cd/E11882_01/win.112/e12249/OracleCommandClass.htm#i997666