Пример #1
0
        /// <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);
        }
Пример #2
0
        /// <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);
                }
            }
        }
Пример #3
0
        /// <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);
        }
Пример #5
0
        /// <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);
        }
Пример #6
0
        /// <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);
        }
Пример #7
0
        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);
        }