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