/// <summary> /// </summary> /// <param name="schedule"></param> /// <param name="trx"></param> /// <returns></returns> private void LoadScheduleProperties(Schedule schedule, IDataAccessTransaction trx) { string sql = "SELECT * FROM SCHEDULEPROPERTY WHERE SCHEDULE_ID = @SCHEDULE_ID ORDER BY ATTRIBUTE, SEQUENCE"; try { List <ScheduleProperty> list = new List <ScheduleProperty>(); using (IDbCommand cmd = GetCommand(sql, trx)) { cmd.Parameters.Add(GetDataParameter("@SCHEDULE_ID", schedule.Id)); using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { list.Add(CreateSchedulePropertiesFromReader(schedule, reader, ordinals)); } } } schedule.ScheduleProperties = list; } catch (Exception ex) { throw new DataAccessException(sql, ex); } }
/// <summary> /// Returns all global schedules (those not associated with either any equipment serial numbers /// nor any component codes). True global schedules also have a null equipment code. /// </summary> /// <param name="trx"></param> /// <returns></returns> public virtual IList <Schedule> FindGlobalSchedules(IDataAccessTransaction trx) { List <Schedule> list = new List <Schedule>(); StringBuilder sqlBuilder = new StringBuilder(string.Format("SELECT SCHEDULE.*, {0}.* FROM SCHEDULE, {1}", TableName, TableName), 500); sqlBuilder.Append(" WHERE SCHEDULE.EQUIPMENTCODE IS NULL"); sqlBuilder.Append(" AND NOT EXISTS ( SELECT SCHEDULE_ID FROM SCHEDULEDEQUIPMENT WHERE SCHEDULE_ID = SCHEDULE.ID )"); sqlBuilder.Append(" AND NOT EXISTS ( SELECT SCHEDULE_ID FROM SCHEDULEDCOMPONENTCODE WHERE SCHEDULE_ID = SCHEDULE.ID )"); sqlBuilder.AppendFormat(" AND {0}.SCHEDULE_ID = SCHEDULE.ID", TableName); string sql = sqlBuilder.ToString(); try { using (IDbCommand cmd = GetCommand(string.Format(sql, TableName, TableName), trx)) { using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { Schedule sched = CreateFromReader(reader, ordinals); list.Add(sched); LoadScheduleProperties(sched, trx); } } } } catch (Exception ex) { throw new DataAccessException(sql, ex); } return(list); }
//TODO:Ajay Duplicate this mehtod with IDataAccessTransaction method //Other method that is accepting DataAccessTransaction can be removed. static internal IDbCommand GetCommand(string sql, int commandTimeout, IDataAccessTransaction trx) { DataAccessTransaction transaction = (DataAccessTransaction)trx; IDbCommand cmd = GetCommand(sql, transaction.Connection); cmd.Transaction = transaction.Transaction; if (commandTimeout != NULL_INT_FLAG) { cmd.CommandTimeout = commandTimeout; } return(cmd); }
/// <summary> /// Finds all EventJournals for the specified equipment/component serial numbers. /// </summary> /// <param name="serialNumbers"></param> /// <param name="trx"></param> /// <returns>An empty array is returned if no match is found.</returns> public virtual List <EventJournal> FindBySerialNumbers(string[] serialNumbers, IDataAccessTransaction trx) { List <EventJournal> journalsList = new List <EventJournal>(); string sql = string.Format("SELECT * FROM EVENTJOURNAL WHERE SN IN ( {0} )", MakeCommaDelimitedParamNames(serialNumbers.Length, "@SN")); using (IDbCommand cmd = GetCommand(sql, trx)) { for (int i = 0; i < serialNumbers.Length; i++) { cmd.Parameters.Add(GetDataParameter("@SN" + (i + 1).ToString(), serialNumbers[i])); } using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { journalsList.Add(CreateFromReader(reader, ordinals)); } } } return(journalsList); }
/// <summary> /// Finds all EventJournals for the last event of the specified type for the specified instrument serial number and its components. /// </summary> /// <param name="instrumentSerialNumber"></param> /// <param name="eventCode"></param> /// <param name="trx"></param> /// <returns>An empty array is returned if no match is found.</returns> public virtual List <EventJournal> FindLastEventByInstrumentSerialNumber(string instrumentSerialNumber, string eventCode, IDataAccessTransaction trx) { List <EventJournal> journalsList = new List <EventJournal>(); //string sql = "SELECT * FROM EVENTJOURNAL WHERE (SN = @SN OR INSTRUMENTSN = @SN) AND EVENTCODE = @EVENTCODE"; string sql = "SELECT * FROM EVENTJOURNAL WHERE (SN = @SN OR INSTRUMENTSN = @SN) AND EVENTCODE = @EVENTCODE AND EVENTTIMEUTC = (SELECT MAX(EVENTTIMEUTC) FROM EVENTJOURNAL WHERE (SN = @SN OR INSTRUMENTSN = @SN) AND EVENTCODE = @EVENTCODE)"; using (IDbCommand cmd = GetCommand(sql, trx)) { cmd.Parameters.Add(GetDataParameter("@SN", instrumentSerialNumber)); cmd.Parameters.Add(GetDataParameter("@EVENTCODE", eventCode)); using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { journalsList.Add(CreateFromReader(reader, ordinals)); } } } return(journalsList); }
//TODO:Ajay Duplicate this mehtod with IDataAccessTransaction method //Other method that is accepting DataAccessTransaction can be removed. static internal IDbCommand GetCommand(string sql, IDataAccessTransaction trx) { return(GetCommand(sql, NULL_INT_FLAG, trx)); }
/// <summary> /// Returns all global type-specific schedules (those not associated with either any equipment serial numbers /// nor any component codes). Global type-specific schedules do not have a null equipment code and can be assumed /// to only be for instrument events. /// </summary> /// <param name="equipmentCode">The device types</param> /// <param name="trx"></param> /// <returns>A list of global type-specific schedules for the equipment</returns> public virtual IList <Schedule> FindGlobalTypeSpecificSchedules(string[] equipmentCodes, IDataAccessTransaction trx) { List <Schedule> list = new List <Schedule>(); StringBuilder sqlBuilder = new StringBuilder(string.Format("SELECT SCHEDULE.*, {0}.* FROM SCHEDULE, {1}", TableName, TableName), 500); sqlBuilder.Append(string.Format(" WHERE SCHEDULE.EQUIPMENTCODE IN ( {0} ) ", MakeCommaDelimitedParamNames(equipmentCodes.Length, "@EQUIPMENTCODE"))); sqlBuilder.Append(" AND NOT EXISTS ( SELECT SCHEDULE_ID FROM SCHEDULEDEQUIPMENT WHERE SCHEDULE_ID = SCHEDULE.ID )"); sqlBuilder.Append(" AND NOT EXISTS ( SELECT SCHEDULE_ID FROM SCHEDULEDCOMPONENTCODE WHERE SCHEDULE_ID = SCHEDULE.ID )"); sqlBuilder.AppendFormat(" AND {0}.SCHEDULE_ID = SCHEDULE.ID", TableName); string sql = sqlBuilder.ToString(); try { using (IDbCommand cmd = GetCommand(sql, trx)) { for (int i = 0; i < equipmentCodes.Length; i++) { cmd.Parameters.Add(GetDataParameter("@EQUIPMENTCODE" + (i + 1).ToString(), equipmentCodes[i])); } using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { Schedule sched = CreateFromReader(reader, ordinals); list.Add(sched); LoadScheduleProperties(sched, trx); } } } } catch (Exception ex) { throw new DataAccessException(sql, ex); } return(list); }
/// <summary> /// </summary> /// <param name="id"></param> /// <param name="tableName">Must be either SCHEDULEDCOMPONENTCODE or SCHEDULEDEQUIPMENT </param> /// <param name="stringFieldName">COMPONENTCODE or SN</param> /// <param name="trx"></param> /// <returns></returns> private string[] FindScheduledForItems(long id, string tableName, string stringFieldName, IDataAccessTransaction trx) { List <string> strList = new List <string>(); string sql = string.Format(string.Format("SELECT {0} FROM {1} WHERE SCHEDULE_ID = @SCHEDULE_ID", stringFieldName, tableName)); try { using (IDbCommand cmd = GetCommand(sql, trx)) { cmd.Parameters.Add(GetDataParameter("@SCHEDULE_ID", id)); using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { string str = SqlSafeGetString(reader, ordinals[stringFieldName]); if (str != null && str.Length > 0) { strList.Add(str); } } } } } catch (Exception ex) { throw new DataAccessException(sql, ex); } return(strList.ToArray());; }
public virtual IList <Schedule> FindByComponentCodes(string[] componentCodes, IDataAccessTransaction trx) { return(FindByScheduledItems(componentCodes, "SCHEDULEDCOMPONENTCODE", "COMPONENTCODE", trx)); }
/// <summary> /// </summary> /// <remarks> /// "Items" is either one or more pieces of equipment (specified by serial number) /// or one or more component (sensor) types. /// </remarks> /// <param name="items">serial numbers or component type codes</param> /// <param name="itemTableName">SCHEDULEDCOMPONENTCODE or SCHEDULEDEQUIPMENT</param> /// <param name="stringFieldName">COMPONENTCODE or SN</param> /// <param name="trx"></param> /// <returns></returns> private IList <Schedule> FindByScheduledItems(string[] items, string itemTableName, string itemFieldName, IDataAccessTransaction trx) { List <Schedule> list = new List <Schedule>(); StringBuilder sqlBuilder = new StringBuilder(string.Format("SELECT SCHEDULE.*, {0}.* FROM SCHEDULE, {1}, {2}", TableName, TableName, itemTableName), 500); sqlBuilder.AppendFormat(" WHERE {0}.{1} IN ( {2} )", itemTableName, itemFieldName, MakeCommaDelimitedParamNames(items.Length, "@PARAM")); // SGF 05-Nov-2010 Single Sensor Cal and Bump -- corrected bug in statement sqlBuilder.AppendFormat(" AND {0}.SCHEDULE_ID = SCHEDULE.ID", itemTableName); sqlBuilder.AppendFormat(" AND {0}.SCHEDULE_ID = SCHEDULE.ID", TableName); string sql = sqlBuilder.ToString(); try { using (IDbCommand cmd = GetCommand(string.Format(sql, TableName, TableName), trx)) { for (int i = 0; i < items.Length; i++) { cmd.Parameters.Add(GetDataParameter("@PARAM" + (i + 1).ToString(), items[i])); } using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { Schedule sched = CreateFromReader(reader, ordinals); sched.ComponentCodes = new List <string>(FindScheduledForItems(sched.Id, "SCHEDULEDCOMPONENTCODE", "COMPONENTCODE", trx)); sched.SerialNumbers = new List <string>(FindScheduledForItems(sched.Id, "SCHEDULEDEQUIPMENT", "SN", trx)); LoadScheduleProperties(sched, trx); list.Add(sched); } } } } catch (Exception ex) { throw new DataAccessException(sql, ex); } return(list); }
public virtual IList <Schedule> FindBySerialNumbers(string[] serialNumbers, IDataAccessTransaction trx) { return(FindByScheduledItems(serialNumbers, "SCHEDULEDEQUIPMENT", "SN", trx)); }
/// <summary> /// Initializes a new instance of the <see cref="DataAccessCommandHandler{TCommand}" /> class. /// </summary> /// <param name="mediator"> /// A processing intermediary that is used to process sub-commands. /// </param> /// <param name="repositoryFactory"> /// The factory that produces data access repositories for the handler. /// </param> /// <param name="transaction"> /// A transaction that is used to process the command. /// </param> /// <exception cref="ArgumentException"> /// <paramref name="transaction" /> is in an invalid state. /// </exception> /// <exception cref="ArgumentNullException"> /// <paramref name="mediator" /> is <see langword="null" /> -or- <paramref name="repositoryFactory" /> is /// <see langword="null" /> -or- <paramref name="transaction" /> is <see langword="null" />. /// </exception> protected DataAccessCommandHandler(ICommandMediator mediator, IDataAccessRepositoryFactory repositoryFactory, IDataAccessTransaction transaction) : base(mediator) { Repositories = new FactoryProducedInstanceGroup(repositoryFactory); Transaction = transaction.RejectIf().IsNull(nameof(transaction)).OrIf((argument) => argument.State != DataAccessTransactionState.Ready, nameof(transaction)).TargetArgument; }
/// <summary> /// Processes the specified command. /// </summary> /// <param name="command"> /// The command to process. /// </param> /// <param name="repositories"> /// An object that provides access to data access repositories. /// </param> /// <param name="transaction"> /// A transaction that is used to process the command. /// </param> /// <param name="controlToken"> /// A token that ensures thread safety for the operation. /// </param> protected abstract void Process(TCommand command, IFactoryProducedInstanceGroup repositories, IDataAccessTransaction transaction, ConcurrencyControlToken controlToken);
/// <summary> /// Processes the specified command. /// </summary> /// <param name="command"> /// The command to process. /// </param> /// <param name="repositories"> /// An object that provides access to data access repositories. /// </param> /// <param name="transaction"> /// A transaction that is used to process the command. /// </param> /// <param name="controlToken"> /// A token that ensures thread safety for the operation. /// </param> /// <returns> /// The result that is emitted when processing the command. /// </returns> protected sealed override IEnumerable <Int64> Process(GetFibonacciNumberValuesCommand command, IFactoryProducedInstanceGroup repositories, IDataAccessTransaction transaction, ConcurrencyControlToken controlToken) { var numberSeriesNumberRepository = repositories.Get <NumberSeriesNumberRepository>(); return(numberSeriesNumberRepository.FindWhere(entity => entity.NumberSeries.Identifier == NumberSeries.Named.Fibonacci.Identifier).Select(entity => entity.Number.Value).ToList()); }
/// <summary> /// Processes the specified command. /// </summary> /// <param name="command"> /// The command to process. /// </param> /// <param name="repositories"> /// An object that provides access to data access repositories. /// </param> /// <param name="transaction"> /// A transaction that is used to process the command. /// </param> /// <param name="controlToken"> /// A token that ensures thread safety for the operation. /// </param> protected override void Process(AddFibonacciNumberCommand command, IFactoryProducedInstanceGroup repositories, IDataAccessTransaction transaction, ConcurrencyControlToken controlToken) { var fibonacciNumberSeries = NumberSeries.Named.Fibonacci; var numberRepository = repositories.Get <NumberRepository>(); var number = numberRepository.FindByValue(command.NumberValue); if (number is null) { number = new Number() { Identifier = Guid.NewGuid(), Value = command.NumberValue }; numberRepository.Add(number); } var numberSeriesNumberRespository = repositories.Get <NumberSeriesNumberRepository>(); var numberSeriesNumber = numberSeriesNumberRespository.FindWhere(entity => entity.Number.Value == number.Value && entity.NumberSeriesIdentifier == fibonacciNumberSeries.Identifier).SingleOrDefault(); if (numberSeriesNumber is null) { numberSeriesNumber = new NumberSeriesNumber() { Identifier = Guid.NewGuid(), Number = number, NumberIdentifier = number.Identifier, NumberSeriesIdentifier = fibonacciNumberSeries.Identifier }; numberSeriesNumberRespository.Add(numberSeriesNumber); } }