OracleCommand Update works with interpolated SQL, but not with Parameterized

Question

I'm doing some maintenance on a legacy app uses OracleConnection and OracleCommand to manage our data. I'm Having an issue where a specific update isn't working when I use parameters, but if I convert the same statement to an interpolated string, it's working fine. I'm not getting any exceptions, the update just doesn't happen, and returns a 0 for rows updated. I'm doing other updates with parameters, so I'm curious if anyone sees anything that I might have missed with this one. I've tried with/without the transaction as well as explicitly creating OracleParameter objects to no effect.

The method is below. I've left the parameterized version and the parameter setting commented out for reference.

    public int UpdateBusinessEntitlement(int appId, int businessId, int entitlementTypeId, string sso)
    {

        // Non-Working Parameterized Version
        //var sql = "UPDATE APD.APD_BUS_TO_APP_MAP " +
        //                   "SET ENTITLEMENT_TYPE_SEQ_ID = :entitlementTypeId, " +
        //                   "LAST_UPDATE_DATE = SYSDATE, " +
        //                   "LAST_UPDATED_BY = :lastUpdatedBy " +
        //                   "WHERE APP_SEQ_ID = :appId AND BUSINESS_SEQ_ID = :businessId";

        var sql = "UPDATE APD.APD_BUS_TO_APP_MAP " +
               $"SET ENTITLEMENT_TYPE_SEQ_ID = {entitlementTypeId}, " +
               "LAST_UPDATE_DATE = SYSDATE, " +
               $"LAST_UPDATED_BY = {sso} " +
               $"WHERE APP_SEQ_ID = {appId} AND BUSINESS_SEQ_ID = {businessId}";


        using (var cn = _connectionBuilder.GetUpdaterConnection())
        {
            using (var cmd = _connectionBuilder.GetCommand(sql, cn))
            {
                cn.Open();
                var transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = transaction;

                //cmd.Parameters.Add("appId", appId);
                //cmd.Parameters.Add("businessId", businessId);
                //cmd.Parameters.Add("entitlementTypeId", entitlementTypeId);
                //cmd.Parameters.Add("lastUpdatedBy", sso);


                var rows = cmd.ExecuteNonQuery();
                transaction.Commit();

                return rows;
            }
        }
    }

Answer 1

I suspect you are binding parameters by position rather than name.

By position, you'd be putting appId first into entitlement_type_seq_id. Then BusinessId into last_Updated_By, entitlementTypeId into app_seq_id and lastUpdatedBy into business_seq_id.

https://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleCommandClass.htm#i997666

Answer 2

Either you have to set

cmd.BindByName = true;

because default value for BindByName property is false, which means the parameters are bound by position.

Or you have to use the same order of parameters as they appear in your statement, i.e.

cmd.Parameters.Add("entitlementTypeId", entitlementTypeId);
cmd.Parameters.Add("lastUpdatedBy", sso);
cmd.Parameters.Add("appId", appId);
cmd.Parameters.Add("businessId", businessId);

btw, usually OracleParameter are added like this:

cmd.Parameters.Add("entitlementTypeId", OracleDbType.Int32, ParameterDirection.Input).Value = entitlementTypeId;
cmd.Parameters.Add("lastUpdatedBy", OracleDbType.Varchar2, ParameterDirection.Input).Value = entitlementTypeId;
cmd.Parameters.Add("appId", OracleDbType.Int32, ParameterDirection.Input).Value = appId;
cmd.Parameters.Add("businessId", OracleDbType.Int32, ParameterDirection.Input).Value = businessId;

I assume for simple data types like numbers of string the syntax does not matter, however other data type (e.g. Date) may fail if you simply use cmd.Parameters.Add(string name, object val).