Solve the problem of C # using parameter execution failures in Oracle

Whether the other Oracle version has this problem is not clear, at least for the Oracle 10g, Oracle 12c I tested at the moment, after the execution of either inexplicable error, or after the execution of the return of 0 rows affected.

My situation is this: using C # to call Oracle.ManagedDataAccess.dll to access the database, the implementation of the UPDATE statement does not have a reaction. Returns the number of affected rows by 0.

Investigation

The statement has no errors, where conditions are not wrong.
Execution without parameters is completely fine.
Finally checked for a few hours, the Internet incredibly found the same situation as I met people ... Troubleshooting Oracle's statement and parameter order must be consistent, feel Oracle is too smart.

The case is as follows

The code is as follows

Copy Code

StringBuilder strSQL = new StringBuilder (); Strsql.append ("Update macinfo set"); Strsql.append ("mac_address=:mac_address,"); Strsql.append ("Mac_status=:mac_status"); Strsql.append ("where macinfo_id=:macinfo_id"); oracleparameter[] Parameters = { New OracleParameter (": macinfo_id", oracledbtype.varchar2,17), New OracleParameter (": mac_address", oracledbtype.varchar2,17), New OracleParameter (": Mac_status", oracledbtype.int32,4)}; Parameters[0]. Value = model. macinfo_id; PARAMETERS[1]. Value = model. mac_address; PARAMETERS[2]. Value = model. Mac_status; int result = Dbhelper.executecommand (Strsql.tostring (), commandtype.text,parameters); return result;

The most important thing is the order of the SQL statement and the OracleParameter parameter, the order must be consistent! So you need to write this:

The code is as follows	Copy Code
oracleparameter[] Parameters = { New OracleParameter (": mac_address", oracledbtype.varchar2,17), New OracleParameter (": Mac_status", oracledbtype.int32,4), New OracleParameter (": macinfo_id", oracledbtype.varchar2,17)};

Naturally, the assignment is needless to say, follow parameters go.
That's OK.