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

问题描述

我在调试数据库操作代码，我发现正确的UPDATE从来没有发生，虽然代码从来没有失败。这是代码：

  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）; 
  

但是经过一些调整，这个工作起来了：

  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. 为什么这么重要，WHERE子句中的参数在OleDb连接的情况下必须给最后一个？以前使用MySQL，我可以（通常做）写WHERE子句的参数，因为这对我更合乎逻辑。




2. 在查询数据库时，参数顺序是否重要？某些性能问题或某些问题？




3. 是否有其他数据库（如DB2，Sqlite等）需要维护的特定订单？

更新：我摆脱了？具有和不具有 @ 的名称。顺序真的很重要。在这两种情况下，只有最后提到WHERE子句参数，实际更新发生。更糟糕的是，在复杂的查询中，很难知道哪个顺序是Access期望的，并且在所有情况下，顺序被改变，查询不执行它的预期义务，没有警告/错误!!

$ b $在Access中， ADODB.Command 对象忽略参数名称。 事实上，我可以使用一个假的名称（甚至不存在于SQL语句中）引用一个参数，ADO不在乎。所有它似乎关心的是，您提供参数值在完全相同的顺序作为这些参数出现在SQL语句。 BTW，这也是如果我建立与？占位符而不是命名参数的SQL语句会发生什么。

虽然我意识到你的问题是关于c＃和 OleDbCommand ，它看起来像我一样Dot.Net的 OleDbCommand 可能操作与Access' ADODB.Command 相同。不幸的是，我不知道Dot.Net ...但这是我的预感。 ： - ）

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. 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.

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

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

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!!

解决方案

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.

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. :-)

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