/// <summary> /// Finds and returns all FactoryCylinders in the database. /// </summary> public IList <FactoryCylinder> FindAll(DataAccessTransaction trx) { IList <FactoryCylinder> list = new List <FactoryCylinder>(); using (IDbCommand cmd = GetCommand("SELECT * FROM FACTORYCYLINDER", trx)) { using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { list.Add(CreateFromReader(reader, ordinals)); } } } // For each cylinder, load its gases FactoryCylinderGasDataAccess factoryCylinderGasDataAccess = new FactoryCylinderGasDataAccess(); foreach (FactoryCylinder cylinder in list) { LoadFactoryCylinderGases(cylinder, factoryCylinderGasDataAccess, trx); } return(list); }
/// <summary> /// Returns the 'version' of this database's schema. /// </summary> /// <remarks> /// During startup, the application needs to obtain this version and compare it /// to what version it thinks it's compatible with. /// </remarks> /// <returns></returns> public int GetSchemaVersion() { try { // THIS TRANSACTION MUST REMAIN WRITABLE. SINCE IT IS CALLED FIRST IT WILL ENSURE ALL // PENDING DATABASE TRANSACTIONS HAVE BEEN ROLLED BACK. using (DataAccessTransaction trx = new DataAccessTransaction(this._dataSourceId, false)) { using (IDbCommand cmd = GetCommand("SELECT VERSION FROM SCHEMA", trx)) { using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); // There should never be more than one row. Just take the first. if (reader.Read()) { int result = SqlSafeGetInt(reader, ordinals["VERSION"]); trx.Commit(); return(result); } trx.Commit(); return(0); // return a default value of 0 for version } } } } catch (Exception ex) { throw new DataAccessException("Could not get Version from Queue Schema. See inner exception for details.", ex); } }
protected internal override Schedule CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals) { short dayOfMonth = SqlSafeGetShort(reader, ordinals["DAYOFMONTH"]); short week = SqlSafeGetShort(reader, ordinals["WEEK"]); DayOfWeek?dayOfWeek = null; short rawDayOfWeek = SqlSafeGetShort(reader, ordinals["DAYOFWEEK"]); if (rawDayOfWeek != DomainModelConstant.NullShort) { dayOfWeek = (DayOfWeek)rawDayOfWeek; } return(new ScheduledMonthly( GetId(reader, ordinals), GetRefId(reader, ordinals), GetName(reader, ordinals), GetEventCode(reader, ordinals), GetEquipmentCode(reader, ordinals), GetEquipmentSubTypeCode(reader, ordinals), GetEnabled(reader, ordinals), GetOnDocked(reader, ordinals), GetInterval(reader, ordinals), GetStartDate(reader, ordinals), GetRunAtTime(reader, ordinals), week, dayOfWeek, dayOfMonth)); }
/// <summary> /// Return the GasConcentrations of the cylinder's part number. /// </summary> /// <param name="factoryCylinder"></param> /// <param name="trx"></param> /// <returns>Returned list is empty if part number can't be found.</returns> internal IList <GasConcentration> FindByFactoryCylinder(FactoryCylinder factoryCylinder, DataAccessTransaction trx) { List <GasConcentration> list = new List <GasConcentration>(); using (IDbCommand cmd = GetCommand("SELECT * FROM FACTORYCYLINDERGAS WHERE PARTNUMBER = @PARTNUMBER", trx)) { cmd.Parameters.Add(GetDataParameter("@PARTNUMBER", factoryCylinder.PartNumber)); using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { string gasCode = SqlSafeGetString(reader, ordinals["GASCODE"]); double concentration = SqlSafeGetDouble(reader, ordinals["CONCENTRATION"], 0.0f); GasConcentration gasConcentration = new GasConcentration(gasCode, concentration); list.Add(gasConcentration); } } } 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());; }
/// <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); }
private SensorCalibrationLimits CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals) { string sensorCode = SqlSafeGetString(reader, ordinals["SENSORCODE"]); int age = SqlSafeGetInt(reader, ordinals["AGE"]); return(new SensorCalibrationLimits(sensorCode, age)); }
/// <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 the oldest item on the queue /// </summary> /// <param name="trx"></param> /// <returns>null is returned if the queue is empty.</returns> public PersistedQueueData FindOldest(DataAccessTransaction trx) { using (IDbCommand cmd = GetCommand(string.Format("SELECT * FROM {0} WHERE ID = ( SELECT MIN(ID) FROM {1} )", TableName, TableName), trx)) { using (IDataReader reader = cmd.ExecuteReader()) { if (!reader.Read()) { return(null); } DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); long id = SqlSafeGetLong(reader, ordinals["ID"]); string accountNum = SqlSafeGetString(reader, ordinals["ACCOUNTNUM"]); DateTime timeStamp = SqlSafeGetDateTime(reader, ordinals["TIMESTAMPUTC"], DateTimeKind.Utc); string label = SqlSafeGetString(reader, ordinals["LABEL"]); string type = SqlSafeGetString(reader, ordinals["TYPE"]); string body = SqlSafeGetString(reader, ordinals["BODY"]); PersistedQueueData persistedQueueData = new PersistedQueueData(accountNum, label, type, body); persistedQueueData.Id = id; persistedQueueData.Timestamp = timeStamp; FindProperties(persistedQueueData, trx); return(persistedQueueData); } } }
protected TimeSpan GetRunAtTime(IDataReader reader, DataAccessOrdinals ordinals) { string runAtTime = SqlSafeGetString(reader, ordinals["RUNATTIME"]); TimeSpan runAtTimeSpan = (runAtTime == null) ? TimeSpan.Zero : TimeSpan.Parse(runAtTime); return(runAtTimeSpan); }
/// <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); }
private FactoryCylinder CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals) { string partNumber = SqlSafeGetString(reader, ordinals["PARTNUMBER"]); string manufacturerCode = SqlSafeGetString(reader, ordinals["MANUFACTURERCODE"]); return(new FactoryCylinder(partNumber, manufacturerCode)); }
private CriticalError CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals) { int code = SqlSafeGetInt(reader, ordinals["CODE"]); string description = SqlSafeGetString(reader, ordinals["DESCRIPTION"]); return(new CriticalError(code, description)); }
/// <summary> /// /// </summary> /// <param name="readOnly">We allow performing the 'find' using a writable transaction. /// We do allow this because when we're starting up the device, this is the very first /// query on the database. If there's a sqlite "hot journal" file in existence, when /// the first query is issued, then sqlite will want to roll back the unclosed transaction. /// But it's unable to do so if the transaction is read-only.</param> /// <returns></returns> public Schema Find(bool readOnly) { Schema schema = null; using (DataAccessTransaction trx = new DataAccessTransaction(readOnly)) { using (IDbCommand cmd = GetCommand("SELECT * FROM " + TableName, trx)) { using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); // There should never be more than one row. Just take the first. if (reader.Read()) { schema = new Schema(); schema.Version = SqlSafeGetInt(reader, ordinals["VERSION"]); schema.AccountNum = SqlSafeGetString(reader, ordinals["ACCOUNTNUM"]); schema.Activated = (SqlSafeGetShort(reader, ordinals["ACTIVE"]) == 1) ? true : false; if (schema.Version >= INET_VERSION_INS3017) // SGF 08-Jun-2012 Changed constant to reflect which version this data was added { schema.IsManufacturing = (SqlSafeGetShort(reader, ordinals["ISMANUFACTURING"]) == 1) ? true : false; } // It's assumed the dates are all stored in UTC. So pass in DateTimeKind.Utc // to ensure they're not converted to local time when retrieved schema.CylindersVersion = SqlSafeGetNullableDateTime(reader, ordinals["FACTORYCYLINDERSUTCVERSION"], DateTimeKind.Utc); schema.SchedulesVersion = SqlSafeGetNullableDateTime(reader, ordinals["SCHEDULESUTCVERSION"], DateTimeKind.Utc); schema.EventJournalsVersion = SqlSafeGetNullableDateTime(reader, ordinals["EVENTJOURNALSUTCVERSION"], DateTimeKind.Utc); schema.SettingsVersion = SqlSafeGetNullableDateTime(reader, ordinals["SETTINGSUTCVERSION"], DateTimeKind.Utc); schema.EquipmentVersion = SqlSafeGetNullableDateTime(reader, ordinals["EQUIPMENTUTCVERSION"], DateTimeKind.Utc); //Suresh 06-FEB-2012 INS-2622 //Column 'CRITICALTERRORSUTCVERSION' is newly added to database so it won't be available in older version of database. if (schema.Version >= INET_VERSION_INS2622) // SGF 08-Jun-2012 Changed constant to reflect which version this data was added { schema.CriticalErrorsVersion = SqlSafeGetNullableDateTime(reader, ordinals["CRITICALINSTRUMENTERRORSUTCVERSION"], DateTimeKind.Utc); } //INS-7715/7282 - To determine the account's Service type, to do checks based on Service accounts if (schema.Version >= INET_VERSION_INS7715) //The version in which SERVICECODE is added { schema.ServiceCode = SqlSafeGetString(reader, ordinals["SERVICECODE"]); } if (!trx.ReadOnly) { trx.Commit(); } } } } } return(schema); }
protected internal override Schedule CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals) { return(new ScheduledUponDocking( GetId(reader, ordinals), GetRefId(reader, ordinals), GetName(reader, ordinals), GetEventCode(reader, ordinals), GetEquipmentCode(reader, ordinals), GetEquipmentSubTypeCode(reader, ordinals), GetEnabled(reader, ordinals))); }
private FirmwareUpgradeSetting CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals) { string equipmentCode = SqlSafeGetString(reader, ordinals["EQUIPMENTCODE"]); string equipmentSubTypeCode = SqlSafeGetString(reader, ordinals["EQUIPMENTSUBTYPECODE"]); string equipmentFullCode = SqlSafeGetString(reader, ordinals["EQUIPMENTFULLCODE"]); string version = SqlSafeGetString(reader, ordinals["VERSION"]); byte[] checksum = SqlSafeGetBLOB(reader, ordinals["CHECKSUM"]); string fileName = SqlSafeGetString(reader, ordinals["FILENAME"]); return(new FirmwareUpgradeSetting(equipmentCode, equipmentSubTypeCode, equipmentFullCode, version, checksum, fileName)); }
private EventJournal CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals) { string eventCode = SqlSafeGetString(reader, ordinals["EVENTCODE"]); string sn = SqlSafeGetString(reader, ordinals["SN"]); string instSn = SqlSafeGetString(reader, ordinals["INSTRUMENTSN"]); DateTime runTime = SqlSafeGetDateTime(reader, ordinals["RUNTIMEUTC"], DateTimeKind.Utc); DateTime eventTime = SqlSafeGetDateTime(reader, ordinals["EVENTTIMEUTC"], DateTimeKind.Utc); bool passed = SqlSafeGetShort(reader, ordinals["PASSED"]) == 0 ? false : true; int position = SqlSafeGetInt(reader, ordinals["POSITION"]); string softwareVersion = SqlSafeGetString(reader, ordinals["SOFTWAREVERSION"]); return(new EventJournal(eventCode, sn, instSn, runTime, eventTime, passed, position, softwareVersion)); }
protected bool[] GetDaysFromReader(IDataReader reader, DataAccessOrdinals ordinals) { bool[] days = new bool[7]; days[(int)DayOfWeek.Sunday] = (SqlSafeGetShort(reader, ordinals["SUNDAY"]) > 0) ? true : false; days[(int)DayOfWeek.Monday] = (SqlSafeGetShort(reader, ordinals["MONDAY"]) > 0) ? true : false; days[(int)DayOfWeek.Tuesday] = (SqlSafeGetShort(reader, ordinals["TUESDAY"]) > 0) ? true : false; days[(int)DayOfWeek.Wednesday] = (SqlSafeGetShort(reader, ordinals["WEDNESDAY"]) > 0) ? true : false; days[(int)DayOfWeek.Thursday] = (SqlSafeGetShort(reader, ordinals["THURSDAY"]) > 0) ? true : false; days[(int)DayOfWeek.Friday] = (SqlSafeGetShort(reader, ordinals["FRIDAY"]) > 0) ? true : false; days[(int)DayOfWeek.Saturday] = (SqlSafeGetShort(reader, ordinals["SATURDAY"]) > 0) ? true : false; return(days); }
protected internal override Schedule CreateFromReader(IDataReader reader, DataAccessOrdinals ordinals) { return(new ScheduledDaily( GetId(reader, ordinals), GetRefId(reader, ordinals), GetName(reader, ordinals), GetEventCode(reader, ordinals), GetEquipmentCode(reader, ordinals), GetEquipmentSubTypeCode(reader, ordinals), GetEnabled(reader, ordinals), GetOnDocked(reader, ordinals), GetInterval(reader, ordinals), GetStartDate(reader, ordinals), GetRunAtTime(reader, ordinals))); }
/// <summary> /// Finds and returns all Sensor Calibration Limits in the database. /// </summary> public List <SensorCalibrationLimits> FindAll(DataAccessTransaction trx) { List <SensorCalibrationLimits> list = new List <SensorCalibrationLimits>(); using (IDbCommand cmd = GetCommand("SELECT * FROM " + TableName, trx)) { using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { list.Add(CreateFromReader(reader, ordinals)); } } } return(list); }
/// <summary> /// Return all the serial numbers in the table. /// </summary> /// <param name="trx"></param> /// <returns> /// The order of the returned strings in undefined (they may be in any order). /// </returns> public List <string> FindAll(DataAccessTransaction trx) { List <string> snList = new List <string>(); using (IDbCommand cmd = GetCommand(string.Format("SELECT * FROM {0}", TableName), trx)) { using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { snList.Add(SqlSafeGetString(reader, ordinals["SN"])); } } } return(snList); }
/// <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); }
private long FindIdByRefId(long refId, DataAccessTransaction trx) { using (IDbCommand cmd = GetCommand("SELECT ID FROM SCHEDULE WHERE REFID = @REFID", trx)) { cmd.Parameters.Add(GetDataParameter("@REFID", refId)); using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); // There should never be more than one row. Just take the first. if (reader.Read()) { return(SqlSafeGetLong(reader, ordinals["ID"])); } } } return(DomainModelConstant.NullId); }
private GasEndPoint MakeInstalledCylinder(IDataReader reader, DataAccessOrdinals ordinals, DataAccessTransaction trx) { short position = SqlSafeGetShort(reader, ordinals["POSITION"]); string partNumber = SqlSafeGetString(reader, ordinals["PARTNUMBER"]); // Try and get the Factory Cylinder information for the part number. // Note that there may not be any factory cylinder info available if the // part number is for a new cylinder type that's unknown to to iNet. FactoryCylinder factoryCylinder = null; if (partNumber != string.Empty) { factoryCylinder = new FactoryCylinderDataAccess().FindByPartNumber(partNumber, trx); } Cylinder cylinder; if (factoryCylinder != null) { cylinder = new Cylinder(factoryCylinder); } else { cylinder = new Cylinder(); cylinder.PartNumber = partNumber; } string installationTypeString = SqlSafeGetString(reader, ordinals["INSTALLATIONTYPE"]); GasEndPoint.Type installationType = (GasEndPoint.Type)Enum.Parse(typeof(GasEndPoint.Type), installationTypeString, true); GasEndPoint gep = new GasEndPoint(cylinder, position, installationType); gep.Cylinder.FactoryId = SqlSafeGetString(reader, ordinals["FACTORYID"]); gep.Cylinder.ExpirationDate = SqlSafeGetDate(reader, ordinals["EXPIRATIONDATE"]); gep.Cylinder.RefillDate = SqlSafeGetDate(reader, ordinals["REFILLDATE"]); string pressure = SqlSafeGetString(reader, ordinals["PRESSURE"]); gep.Cylinder.Pressure = (PressureLevel)Enum.Parse(typeof(PressureLevel), pressure, true); return(gep); }
/// <summary> /// Returns all of the currently known installed cylinders. /// </summary> /// <param name="trx"></param> /// <returns>The list of InstalledCylinders will be sorted by Position</returns> public List <GasEndPoint> FindAll(DataAccessTransaction trx) { List <GasEndPoint> list = new List <GasEndPoint>(); string sql = "SELECT * FROM GASENDPOINT ORDER BY POSITION"; using (IDbCommand cmd = GetCommand(sql, trx)) { using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { GasEndPoint gep = MakeInstalledCylinder(reader, ordinals, trx); list.Add(gep); } } } return(list); }
/// <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> /// Returns all data on the queue (except for the Body fields). /// </summary> /// <param name="count">Ignored if zero or less.</param> /// <param name="trx"></param> /// <returns></returns> public List <PersistedQueueData> FindAll(int count) { using (DataAccessTransaction trx = new DataAccessTransaction(this._dataSourceId, true)) { List <PersistedQueueData> list = new List <PersistedQueueData>(); string sql = string.Format("SELECT ID, ACCOUNTNUM, TIMESTAMPUTC, LABEL, TYPE FROM {0} ORDER BY ID DESC", TableName); if (count > 0) { sql += string.Format(" LIMIT {0}", count); } using (IDbCommand cmd = GetCommand(sql, trx)) { using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { long id = SqlSafeGetLong(reader, ordinals["ID"]); string accountNum = SqlSafeGetString(reader, ordinals["ACCOUNTNUM"]); DateTime timeStamp = SqlSafeGetDateTime(reader, ordinals["TIMESTAMPUTC"], DateTimeKind.Utc); string label = SqlSafeGetString(reader, ordinals["LABEL"]); string type = SqlSafeGetString(reader, ordinals["TYPE"]); PersistedQueueData persistedQueueData = new PersistedQueueData(accountNum, label, type, string.Empty); persistedQueueData.Id = id; persistedQueueData.Timestamp = timeStamp; FindProperties(persistedQueueData, trx); list.Add(persistedQueueData); } } } return(list); } }
/// <summary> /// Queries the schema table for just the database version. /// </summary> /// <returns>0 is returned if table is empty (which should probably never happen).</returns> public int FindVersion() { int version = 0; using (DataAccessTransaction trx = new DataAccessTransaction(true)) { using (IDbCommand cmd = GetCommand("SELECT VERSION FROM " + TableName, trx)) { using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); // There should never be more than one row. Just take the first. if (reader.Read()) { version = SqlSafeGetInt(reader, ordinals["VERSION"]); } } } } return(version); }
/// <summary> /// Finds all EventJournals 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 List <EventJournal> FindByInstrumentSerialNumber(string instrumentSerialNumber, string eventCode, DataAccessTransaction trx) { List <EventJournal> journalsList = new List <EventJournal>(); string sql = "SELECT * 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); }
private void FindProperties(PersistedQueueData persistedQueueData, DataAccessTransaction trx) { persistedQueueData.Properties.Clear(); string sql = string.Format("SELECT ATTRIBUTE, VALUE FROM {0} WHERE QUEUE_ID = {1}", TableName + "PROPERTY", persistedQueueData.Id); using (IDbCommand cmd = GetCommand(sql, trx)) { using (IDataReader reader = cmd.ExecuteReader()) { DataAccessOrdinals ordinals = new DataAccessOrdinals(reader); while (reader.Read()) { string attribute = SqlSafeGetString(reader, ordinals["ATTRIBUTE"]); string value = SqlSafeGetString(reader, ordinals["VALUE"]); persistedQueueData.Properties[attribute] = value; } } } }