Update using IDataParameter not working in Oracle

Question

I have a C# abstract class for implementing database transactions that has both a SQL (System.Data.SqlClient) and Oracle (Oracle.ManagedDataAccess.Client) implementation.

public int ExecuteDML<T>(string sql, List<T> objects)
{
    int cnt = 0;
    using (IDbConnection conn = GetConnection())
    {
        using (IDbTransaction txn = conn.BeginTransaction())
        {
            using (IDbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = PrepSQL(sql);                        
                cmd.Transaction = txn;
                try
                {
                    foreach (T obj in objects)
                    {
                        cmd.Parameters.Clear();
                        foreach (var kvp in GetDbParameters<T>(obj))
                        {
                            IDataParameter param = new DbParameter
                            {
                                ParameterName = kvp.Key,
                                Value = kvp.Value ?? DBNull.Value
                            };
                            cmd.Parameters.Add(param);
                        }

                        cnt += cmd.ExecuteNonQuery();                                
                    }
                    txn.Commit();
                }
                catch (Exception)
                {
                    txn.Rollback();
                    throw;
                }
            }
        }
    }
    return cnt;
}

I am able to execute INSERT, UPDATE and DELETE statements in both implementations. But when I run an UPDATE in the Oracle implementation, the record does not get updated in the database; ExecuteNonQuery returns 0. However, the same data/command in the SQL implementation works fine.

Why would the parameterized query not work for UPDATE, while INSERT and DELETE are fine?

Query

UPDATE CONFIG_PARAMS SET PARAM_VALUE = :ParamValue, LOAD_DATE = :LoadDate, UPDATED_BY = :UpdatedBy WHERE ACTION_NAME = :ActionName AND PARAM_NAME = :ParamName

Added the query to the post. The parameters is an OracleParameterCollection with a parameter matching each named parameter in the query. — Paul, Jan 07 '20 at 17:18
If I hardcode the :ActionName and :ParamName values in the WHERE clause, the values in the SET clause work. So the issue seems to be somewhere in there. — Paul, Jan 07 '20 at 17:31

score 0 · Accepted Answer · answered Jan 07 '20 at 17:53

Found solution in this post. BindByName setting needs to be set explicitly for Oracle, since the parameters were out of order.

I added this code after creating the IDbCommand

if (cmd is Oracle.ManagedDataAccess.Client.OracleCommand)
{
    ((Oracle.ManagedDataAccess.Client.OracleCommand)cmd).BindByName = true;
}

Update using IDataParameter not working in Oracle

1 Answers1