/// <summary>
/// Executes a simple SQL statement against the db for one or more values.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="elementCount">The number of elements in the batch.</param>
/// <returns>The number of affected records</returns>
private int ExecuteBatchSql(string sql, OracleParameter[] parameters, int elementCount)
{
using (var cn = AISConnection.GetConnection())
{
var sw = new Stopwatch();
sw.Start();
try
{
using (var cmd = new OracleCommand(sql, cn))
{
if (parameters != null)
{
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
return(cmd.ExecuteArray(elementCount));
}
}
catch (Exception ex)
{
Log.Error(ex, ex.Message);
throw;
}
finally
{
cn.Close();
LogExecutionTimes(sql, sw);
}
}
}
public int BulkUpdateMutationStatus(List <MutationStatusInfo> infos)
{
int affectedRecords;
// Target status must be the same for all elements
var statusGroup = infos.GroupBy(g => g.NewStatus).ToList();
if (statusGroup.Count > 1)
{
throw new ArgumentException("All elements in the list must have the same NewStatus value.");
}
using (var cn = AISConnection.GetConnection())
{
var sw = new Stopwatch();
var sql = new StringBuilder();
try
{
sql.Append("Update tbk_viaduc_mttn set aktn_stts = :stts");
// If we have a completed or failed attempt, update the counter
if (statusGroup.First().Key == ActionStatus.SyncCompleted || statusGroup.First().Key == ActionStatus.SyncFailed)
{
sql.Append(", SYNC_ANZ_VRSCH = nvl(SYNC_ANZ_VRSCH, 0) + 1");
}
sql.Append(" where mttn_id = :mttn_id ");
// If the status udpate is only allowed from a specific existing status,
// add the required where clause.
if (statusGroup.First().Count(s => s.ChangeFromStatus.HasValue) > 0)
{
sql.Append(" and aktn_stts = :sttsFrom ");
}
using (var cmd = new OracleCommand(sql.ToString(), cn))
{
cmd.Parameters.Add("mttn_id", OracleDbType.Long);
cmd.Parameters["mttn_id"].Value = statusGroup.First().Select(s => s.MutationId).ToArray();
cmd.Parameters.Add("stts", OracleDbType.Integer);
cmd.Parameters["stts"].Value = statusGroup.First().Select(s => (int)s.NewStatus).ToArray();
if (statusGroup.First().Count(s => s.ChangeFromStatus.HasValue) > 0)
{
cmd.Parameters.AddWithValue("sttsFrom", OracleDbType.Integer);
cmd.Parameters["sttsFrom"].Value = statusGroup.First()
.Select(s => s.ChangeFromStatus.HasValue ? (int)s.ChangeFromStatus.Value : -1).ToArray();
}
affectedRecords = cmd.ExecuteArray(infos.Count);
}
LogExecutionTimes(sql.ToString(), sw);
}
catch (Exception ex)
{
Log.Error(ex, ex.Message);
throw;
}
finally
{
cn.Close();
}
// Insert Log record into the log table
if (affectedRecords > 0)
{
InsertMutationStatusLog(infos);
}
}
return(affectedRecords);
}