数据库 Command 对象的参数顺序真的很重要吗? [英] Is order of parameters for database Command object really important?

问题描述

我正在调试一个数据库操作代码，我发现正确的 UPDATE 从未发生，尽管代码从来没有失败过.这是代码:

I was debugging a database operation code and I found that proper UPDATE was never happening though the code never failed as such. This is the code:

        condb.Open();
        OleDbCommand dbcom = new OleDbCommand("UPDATE Word SET word=?,sentence=?,mp3=? WHERE id=? AND exercise_id=?", condb);
        dbcom.Parameters.AddWithValue("id", wd.ID);
        dbcom.Parameters.AddWithValue("exercise_id", wd.ExID);
        dbcom.Parameters.AddWithValue("word", wd.Name);
        dbcom.Parameters.AddWithValue("sentence", wd.Sentence);
        dbcom.Parameters.AddWithValue("mp3", wd.Mp3);

但经过一些调整后，这奏效了:

But after some tweaking this worked:

        condb.Open();
        OleDbCommand dbcom = new OleDbCommand("UPDATE Word SET word=?,sentence=?,mp3=? WHERE id=? AND exercise_id=?", condb);
        dbcom.Parameters.AddWithValue("word", wd.Name);
        dbcom.Parameters.AddWithValue("sentence", wd.Sentence);
        dbcom.Parameters.AddWithValue("mp3", wd.Mp3);                         
        dbcom.Parameters.AddWithValue("id", wd.ID);
        dbcom.Parameters.AddWithValue("exercise_id", wd.ExID);

1. 在 OleDb 连接的情况下，为什么 WHERE 子句中的参数必须放在最后一个如此重要?以前使用过 MySQL，我可以(并且通常会)首先编写 WHERE 子句的参数，因为这对我来说更合乎逻辑.

1. Why is it so important that the parameters in WHERE clause has to be given the last in case of OleDb connection? Having worked with MySQL previously, I could (and usually do) write parameters of WHERE clause first because that's more logical to me.

一般查询数据库时参数顺序重要吗?一些性能问题还是什么?

Is parameter order important when querying database in general? Some performance concern or something?

在其他数据库(如 DB2、Sqlite 等)的情况下，是否有特定的顺序需要维护?

Is there a specific order to be maintained in case of other databases like DB2, Sqlite etc?

更新:我去掉了 ? 并包含有和没有 @ 的专有名称.顺序真的很重要.在这两种情况下，只有最后提到 WHERE 子句参数时，才会发生实际更新.更糟糕的是，在复杂的查询中，我们很难知道 Access 期望的是哪个订单，并且在订单更改的所有情况下，查询都不会在没有警告/错误的情况下完成预期的任务！！

Update: I got rid of ? and included proper names with and without @. The order is really important. In both cases only when WHERE clause parameters was mentioned last, actual update happened. To make matter worse, in complex queries, its hard to know ourselves which order is Access expecting, and in all situations where order is changed, the query doesnt do its intended duty with no warning/error!!

推荐答案

在 Access 中，ADODB.Command 对象会忽略参数名称.事实上，我可以使用虚假名称(它甚至不存在于 SQL 语句中)来引用参数，而 ADO 并不关心.它似乎只关心您以完全相同的顺序提供参数值，因为这些参数出现在 SQL 语句中.顺便说一句，如果我使用 ? 占位符而不是命名参数构建 SQL 语句，也会发生这种情况.

Within Access, an ADODB.Command object ignores parameter names. In fact I can refer to a parameter using a bogus name (which doesn't even exist in the SQL statement) and ADO doesn't care. All it seems to care about is that you supply parameter values in the exact same order as those parameters appear in the SQL statement. BTW, that is also what happens if I build the SQL statement with ? place-holders instead of named parameters.

虽然我意识到您的问题是关于 c# 和 OleDbCommand，但在我看来，Dot.Net 的 OleDbCommand 可能与 Access 的 ADODB 操作相同.命令.不幸的是，我不知道 Dot.Net ……但这是我的直觉.:-)

While I realize that your question is about c# and OleDbCommand, it looks to me like Dot.Net's OleDbCommand may be operating the same as Access' ADODB.Command. Unfortunately, I don't know Dot.Net ... but that is my hunch. :-)

这篇关于数据库 Command 对象的参数顺序真的很重要吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！