/// <summary> /// Updates the mutation status of a mutation record in the AIS. /// </summary> /// <param name="info">Info object with detailed information about the new status</param> /// <returns>The number of affected records.</returns> public int UpdateMutationStatus(MutationStatusInfo info) { int affectedRecords; 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 (info.NewStatus == ActionStatus.SyncCompleted || info.NewStatus == 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 (info.ChangeFromStatus.HasValue) { sql.Append(" and aktn_stts = :sttsFrom "); } using (var cmd = new OracleCommand(sql.ToString(), cn)) { cmd.Parameters.AddWithValue("mttn_id", info.MutationId); cmd.Parameters.AddWithValue("stts", info.NewStatus); if (info.ChangeFromStatus.HasValue) { cmd.Parameters.AddWithValue("sttsFrom", info.ChangeFromStatus); } affectedRecords = cmd.ExecuteNonQuery(); } 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(info); } } return(affectedRecords); }
/// <summary> /// Executes a simple SQL statement against the db. /// </summary> /// <param name="sql">The SQL.</param> /// <param name="parameters">The parameters.</param> /// <returns>The number of affected records</returns> private T ExecuteSqlScalar <T>(string sql, OracleParameter[] parameters = null) { 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); } } var obj = cmd.ExecuteScalar(); return((T)obj); } } catch (Exception ex) { Log.Error(ex, ex.Message); throw; } finally { cn.Close(); LogExecutionTimes(sql, sw); } } }
/// <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 HarvestLogInfoResult GetHarvestLogInfo(HarvestLogInfoRequest request) { var retVal = new HarvestLogInfoResult(); var sql = SqlStatements.HarvestLogInfo; sql = string.Format(sql, string.IsNullOrEmpty(request.ArchiveRecordIdFilter) ? "" : " AND m.gsft_obj_id = :gsft_obj_id ", request.ActionStatusFilterList.Any() ? $" AND aktn_stts in ({string.Join(",", request.ActionStatusFilterList.Select(a => ((int) a).ToString()))}) " : "", "" /* no sort order yet */); using (var cn = AISConnection.GetConnection()) { var dateRange = new QueryDateRange(request.DateRangeFilter); var cmd = new OracleCommand(sql, cn); cmd.Parameters.Add("cur1", OracleDbType.Cursor, ParameterDirection.Output); cmd.Parameters.Add("cur2", OracleDbType.Cursor, ParameterDirection.Output); cmd.Parameters.AddWithValue("dateFrom", dateRange.From); cmd.Parameters.AddWithValue("dateTo", dateRange.To); cmd.Parameters.AddWithValue("recordFrom", request.PageSize * (request.Page - 1)); cmd.Parameters.AddWithValue("recordTo", request.PageSize * request.Page); if (!string.IsNullOrEmpty(request.ArchiveRecordIdFilter)) { cmd.Parameters.AddWithValue("gsft_obj_id", request.ArchiveRecordIdFilter); } var ds = new DataSet(); var da = new OracleDataAdapter(cmd); da.TableMappings.Add("Table", "ResultCount"); da.TableMappings.Add("Table1", "LogInfo"); da.Fill(ds); var dsDetail = new DataSet(); if (ds.Tables["LogInfo"].Rows.Count > 0) { var ids = string.Join(",", ds.Tables["LogInfo"].AsEnumerable().Select(t => (int)t.Field <double>("mttn_id"))); dsDetail = GetDataSetFromSql <DataSet>(string.Format(SqlStatements.HarvestLogInfoDetail, ids)); } retVal.TotalResultSetSize = (int)(decimal)ds.Tables["ResultCount"].Rows[0][0]; retVal.ResultSet = (from r in ds.Tables["LogInfo"].AsEnumerable() select new HarvestLogInfo { MutationId = (int)r.Field <double>("mttn_id"), ArchiveRecordId = r.Field <double>("gsft_obj_id").ToString("F0"), ArchiveRecordIdName = r.Field <string>("gsft_obj_kurz_nm"), ActionName = r.Field <string>("aktn"), CurrentStatus = (ActionStatus)r.Field <int>("aktn_stts"), CreationDate = r.Field <DateTime>("erfsg_dt"), LastChangeDate = r.Field <DateTime>("mttn_dt"), NumberOfSyncRetries = r.Field <int>("sync_anz_vrsch"), Details = GetLogDetails(dsDetail, (int)r.Field <double>("mttn_id")) } ).ToList(); } return(retVal); }
/// <summary> /// Gets a generic DataSet given an SQL statement and optional parameters. /// </summary> /// <typeparam name="T">A DataSet or a typed DataSet</typeparam> /// <param name="sql">The sql statement to execute</param> /// <param name="parameters">An array or OracleParameters if needed.</param> /// <returns>The populated DataSet</returns> private T GetDataSetFromSql <T>(string sql, OracleParameter[] parameters = null) where T : DataSet, new() { var ds = new T(); 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); } } var da = new OracleDataAdapter(cmd); if (ds.Tables.Count > 0) { da.Fill(ds.Tables[0]); } else { da.Fill(ds); } } } catch (Exception ex) { Log.Error(ex, ex.Message); throw; } finally { cn.Close(); LogExecutionTimes(sql, sw); } } return(ds); }
/// <summary> /// Loads the node context of a specific record /// </summary> /// <param name="recordId"></param> /// <returns></returns> public NodeContext LoadNodeContext(long recordId) { var retVal = new NodeContext(); using (var cn = AISConnection.GetConnection()) { var sw = new Stopwatch(); try { using (var cmd = new OracleCommand(SqlStatements.SqlNodeContext, cn)) { // Command is Stored Procedure cmd.CommandType = CommandType.StoredProcedure; // Parameter setzen cmd.Parameters.Add(new OracleParameter("pi_gsft_obj_bzhng_typ_id", OracleDbType.Integer) { Direction = ParameterDirection.Input, Value = vrzngEnhtHierarchieBzhngTypId }); cmd.Parameters.Add(new OracleParameter("pi_item_id", OracleDbType.Integer) { Direction = ParameterDirection.Input, Value = recordId }); cmd.Parameters.Add(new OracleParameter("pio_pred_id", OracleDbType.Integer) { Direction = ParameterDirection.Output, Value = -1 }); cmd.Parameters.Add(new OracleParameter("pio_succ_id", OracleDbType.Integer) { Direction = ParameterDirection.Output, Value = -1 }); cmd.Parameters.Add(new OracleParameter("pio_parent_id", OracleDbType.Integer) { Direction = ParameterDirection.Output, Value = -1 }); cmd.Parameters.Add(new OracleParameter("pio_first_child_id", OracleDbType.Integer) { Direction = ParameterDirection.Output, Value = -1 }); cmd.ExecuteNonQuery(); // Set the return values retVal.ParentArchiveRecordId = Convert.ToInt32(cmd.Parameters["pio_parent_id"].Value).ToString(); retVal.FirstChildArchiveRecordId = Convert.ToInt32(cmd.Parameters["pio_first_child_id"].Value).ToString(); retVal.NextArchiveRecordId = Convert.ToInt32(cmd.Parameters["pio_succ_id"].Value).ToString(); retVal.PreviousArchiveRecordId = Convert.ToInt32(cmd.Parameters["pio_pred_id"].Value).ToString(); retVal.ArchiveRecordId = recordId.ToString(); } } catch (Exception ex) { Log.Error(ex, ex.Message); throw; } finally { cn.Close(); LogExecutionTimes(SqlStatements.SqlNodeContext, sw); } } return(retVal); }
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); }