/// <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); }
private bool Update(EventJournal journal, DataAccessTransaction trx) { // If the journal's LastDockedTime is set to a valid date, then update it. // If set to 'null', then leave whatever is stored in the database alone. // Note also that we only do the update if the runTime is newer than the currently stored runTime. string sql = "UPDATE EVENTJOURNAL SET INSTRUMENTSN = @INSTRUMENTSN, RECUPDATETIMEUTC = @RECUPDATETIMEUTC, EVENTTIMEUTC = @EVENTTIMEUTC, RUNTIMEUTC = @RUNTIMEUTC, PASSED = @PASSED, POSITION = @POSITION, SOFTWAREVERSION = @SOFTWAREVERSION WHERE SN = @SN AND EVENTCODE = @EVENTCODE AND @RUNTIMEUTC >= RUNTIMEUTC"; try { using (IDbCommand cmd = GetCommand(sql, trx)) { cmd.Parameters.Add(GetDataParameter("@SN", journal.SerialNumber)); cmd.Parameters.Add(GetDataParameter("@RECUPDATETIMEUTC", trx.TimestampUtc)); cmd.Parameters.Add(GetDataParameter("@INSTRUMENTSN", journal.InstrumentSerialNumber)); cmd.Parameters.Add(GetDataParameter("@RUNTIMEUTC", journal.RunTime)); cmd.Parameters.Add(GetDataParameter("@EVENTTIMEUTC", journal.EventTime)); cmd.Parameters.Add(GetDataParameter("@EVENTCODE", journal.EventCode.Code)); cmd.Parameters.Add(GetDataParameter("@PASSED", (journal.Passed == true) ? (short)1 : (short)0)); cmd.Parameters.Add(GetDataParameter("@POSITION", journal.Position)); cmd.Parameters.Add(GetDataParameter("@SOFTWAREVERSION", journal.SoftwareVersion)); int numRows = cmd.ExecuteNonQuery(); return(numRows > 0); } } catch (Exception ex) { throw new DataAccessException(string.Format("Journal: \"{0}\", SQL: {1}", journal, sql), ex); } }
/// <summary> /// Finds all EventJournals for the specified equipment/component serial numbers. /// </summary> /// <param name="serialNumbers"></param> /// <returns>An empty array is returned if no match is found.</returns> public List <EventJournal> FindBySerialNumbers(string[] serialNumbers) { using (DataAccessTransaction trx = new DataAccessTransaction(true)) { return(FindBySerialNumbers(serialNumbers, trx)); } }
private void InsertScheduledEquipment(Schedule schedule, DataAccessTransaction trx) { if (schedule.SerialNumbers.Count == 0) { return; } string sql = "INSERT INTO SCHEDULEDEQUIPMENT ( SN, SCHEDULE_ID ) VALUES ( @SN, @SCHEDULE_ID )"; try { using (IDbCommand cmd = GetCommand(sql, trx)) { foreach (string sn in schedule.SerialNumbers) { cmd.Parameters.Clear(); cmd.Parameters.Add(GetDataParameter("@SN", sn)); cmd.Parameters.Add(GetDataParameter("@SCHEDULE_ID", schedule.Id)); cmd.ExecuteNonQuery(); } } } catch (Exception e) { Log.Debug(e.ToString()); throw new DataAccessException(string.Format("ID:{0}, SQL:{1}", schedule.Id, sql), e); } }
public bool Save(EventJournal journal, DataAccessTransaction trx) { #if DEBUG if (journal.EventCode.Code == EventCode.Calibration || journal.EventCode.Code == EventCode.BumpTest) { Log.Assert(journal.InstrumentSerialNumber != string.Empty, string.Format("Found unexpected empty InstrumentSerialNumber for \"{0}\"", journal.EventCode)); } else { Log.Assert(journal.InstrumentSerialNumber == string.Empty, string.Format("Found unexpected non-empty InstrumentSerialNumber (\"{0}\") for \"{1}\"", journal.InstrumentSerialNumber, journal.EventCode)); } Log.Assert(journal.InstrumentSerialNumber != journal.SerialNumber, string.Format("SerialNumber and InstrumentSerialNumber are equal (\"{0}\") for \"{1}\"", journal.SerialNumber, journal.EventCode)); #endif // If an Insert hint is passed in, they we first try and insert // and then do an update if the insert fails. It's intended that // the caller would pass in an Insert hint when it thinks the data // being inserted is new (such as the very first time a docking station // is saving its event journals). if (trx.Hint == DataAccessHint.Insert) { // If event already exists, then update it, otherwise insert as new. if (Insert(journal, trx)) { return(true); // must have already existed. No need to attempt an insert. } return(Update(journal, trx)); } // Default behavior: If event already exists, then update it, otherwise insert as new. if (Update(journal, trx)) { return(true); // must have already existed. No need to attempt an insert. } return(Insert(journal, trx)); }
/// <summary> /// Returns the ID of the oldest item on the queue /// </summary> /// <returns>DomainModelConstant.NullId is returned if the queue is empty.</returns> public long FindOldestId() { using (DataAccessTransaction trx = new DataAccessTransaction(this._dataSourceId, true)) { return(FindOldestId(trx)); } }
/// <summary> /// Deletes all scheduling data related to the specified serial numbers. /// </summary> /// <param name="snList"></param> /// <param name="trx">True if there was a SCHEDULE to delete; else false.</param> public bool DeleteBySerialNumbers(IList <string> snList, DataAccessTransaction trx) { const string paramName = "@SN"; string paramsString = MakeCommaDelimitedParamNames(snList.Count, paramName); string sql = string.Empty; try { sql = "DELETE FROM SCHEDULE WHERE ID IN ( SELECT DISTINCT SCHEDULE_ID FROM SCHEDULEDEQUIPMENT WHERE SN IN ( {0} ) )"; using (IDbCommand cmd = GetCommand(string.Format(sql, paramsString), trx)) { for (int i = 0; i < snList.Count; i++) { cmd.Parameters.Add(GetDataParameter(paramName + (i + 1).ToString(), snList[i])); } int delCount = cmd.ExecuteNonQuery(); Log.Debug(string.Format("Deleted {0} SCHEDULEs", delCount)); return(delCount > 0); } } catch (Exception ex) { Log.Debug(ex.ToString()); throw new DataAccessException(string.Format("SQL:{0}", sql), ex); } }
private void SaveGasEndPoints(IEnumerable <GasEndPoint> gasEndPoints, DataAccessTransaction trx) { foreach (GasEndPoint gep in gasEndPoints) { if (gep.GasChangeType == GasEndPoint.ChangeType.Uninstalled) { Log.Debug(string.Format("Deleting uninstalled {0} cylinder on port {1} ({2},{3})", gep.InstallationType, gep.Position, gep.Cylinder.FactoryId, gep.Cylinder.PartNumber)); Delete(gep, trx); } if (gep.GasChangeType == GasEndPoint.ChangeType.Installed) { Log.Debug(string.Format("Saving new/modified {0} cylinder on port {1} ({2},{3},{4})", gep.InstallationType, gep.Position, gep.Cylinder.FactoryId, gep.Cylinder.PartNumber, gep.Cylinder.Pressure)); Save(gep, trx); } if (gep.GasChangeType == GasEndPoint.ChangeType.PressureChanged) { Log.Debug(string.Format("Saving PressureLevel.{0} for {1} cylinder on port {2} ({3},{4})", gep.Cylinder.Pressure, gep.InstallationType, gep.Position, gep.Cylinder.FactoryId, gep.Cylinder.PartNumber)); UpdatePressureLevel(gep, trx); } } }
/// <summary> /// Returns the cylinder currently installed at the specified position. /// </summary> /// <param name="position"></param> /// <returns>null is returned if no cylinder is known to be installed at the specified position.</returns> public GasEndPoint FindByPosition(int position) { using (DataAccessTransaction trx = new DataAccessTransaction(true)) { return(FindByPosition(position, trx)); } }
/// <summary> /// Returns all of the currently known installed cylinders. /// </summary> /// <returns>The list of InstalledCylinders will be sorted by Position</returns> public List <GasEndPoint> FindAll() { using (DataAccessTransaction trx = new DataAccessTransaction(true)) { return(FindAll(trx)); } }
/// <summary> /// /// </summary> /// <param name="gep"></param> /// <param name="trx"></param> /// <returns></returns> public bool Save(GasEndPoint gep, DataAccessTransaction trx) { string sql = string.Empty; try { Delete(gep, trx); // delete any old cylinder at this position. sql = "INSERT INTO GASENDPOINT ( POSITION, RECUPDATETIMEUTC, FACTORYID, PARTNUMBER, PRESSURE, REFILLDATE, EXPIRATIONDATE, INSTALLATIONTYPE ) VALUES ( @POSITION, @RECUPDATETIMEUTC, @FACTORYID, @PARTNUMBER, @PRESSURE, @REFILLDATE, @EXPIRATIONDATE, @INSTALLATIONTYPE )"; using (IDbCommand cmd = GetCommand(sql, trx)) { cmd.Parameters.Add(GetDataParameter("@POSITION", gep.Position)); cmd.Parameters.Add(GetDataParameter("@RECUPDATETIMEUTC", trx.TimestampUtc)); cmd.Parameters.Add(GetDataParameter("@INSTALLATIONTYPE", gep.InstallationType.ToString())); cmd.Parameters.Add(GetDataParameter("@FACTORYID", gep.Cylinder.FactoryId)); cmd.Parameters.Add(GetDataParameter("@PARTNUMBER", gep.Cylinder.PartNumber)); cmd.Parameters.Add(GetDataParameter("@PRESSURE", gep.Cylinder.Pressure.ToString())); cmd.Parameters.Add(GetDataParameter("@REFILLDATE", gep.Cylinder.RefillDate)); cmd.Parameters.Add(GetDataParameter("@EXPIRATIONDATE", gep.Cylinder.ExpirationDate)); return(cmd.ExecuteNonQuery() > 0); } } catch (DataAccessException) { throw; } catch (Exception ex) { throw new DataAccessException(sql, ex); } }
/// <summary> /// Save the passed in critical errors. /// </summary> /// <param name="criticalErrors"></param> public void Save(List <CriticalError> criticalErrors) { using (DataAccessTransaction trx = new DataAccessTransaction()) { Save(criticalErrors, trx); } }
/// <summary> /// Finds and returns all Critical Instrument Errors in the database. /// </summary> /// <returns></returns> public virtual List <CriticalError> FindAll() { using (DataAccessTransaction trx = new DataAccessTransaction(true)) { return(FindAll(trx)); } }
/// <summary> /// Finds and returns a specific FactoryCylinder by its part number. /// </summary> /// <param name="partNumber"></param> /// <returns></returns> public FactoryCylinder FindByPartNumber(string partNumber) { using (DataAccessTransaction trx = new DataAccessTransaction(true)) { return(FindByPartNumber(partNumber, trx)); } }
/// <summary> /// Returns the oldest item on the queue. /// </summary> /// <returns>null is returned if the queue is empty.</returns> public PersistedQueueData FindOldest() { using (DataAccessTransaction trx = new DataAccessTransaction(this._dataSourceId, true)) { return(FindOldest(trx)); } }
/// <summary> /// Finds and returns all Firmware Upgrade Settings in the database. /// </summary> /// <returns></returns> public List <FirmwareUpgradeSetting> FindAll() { using (DataAccessTransaction trx = new DataAccessTransaction(true)) { return(FindAll(trx)); } }
/// <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); } } }
/// <summary> /// Finds and returns all Sensor Calibration Limits in the database. /// </summary> /// <returns></returns> public List <SensorCalibrationLimits> FindAll() { using (DataAccessTransaction trx = new DataAccessTransaction(true)) { return(FindAll(trx)); } }
/// <summary> /// /// </summary> /// <param name="id"></param> /// <param name="trx"></param> /// <returns>True if there was a SCHEDULE to delete; else false.</returns> public bool DeleteById(long id, DataAccessTransaction trx) { if (id == DomainModelConstant.NullId) { return(false); } string sql = string.Empty; bool deleted = false; try { sql = "DELETE FROM SCHEDULE WHERE ID = @ID"; using (IDbCommand cmd = GetCommand(sql, trx)) { cmd.Parameters.Add(GetDataParameter("@ID", id)); deleted = cmd.ExecuteNonQuery() > 0; } } catch (Exception ex) { throw new DataAccessException(string.Format("ID:{0}, SQL:{1}", id, sql), ex); } return(deleted); }
/// <summary> /// Return the GasConcentrations of the cylinder's part number. /// </summary> /// <param name="factoryCylinder"></param> /// <returns></returns> internal IList <GasConcentration> FindByFactoryCylinder(FactoryCylinder factoryCylinder) { using (DataAccessTransaction trx = new DataAccessTransaction(true)) { return(FindByFactoryCylinder(factoryCylinder, trx)); } }
private void InsertScheduledComponentCodes(Schedule schedule, DataAccessTransaction trx) { if (schedule.ComponentCodes.Count == 0 || (schedule.ComponentCodes.Count == 1 && schedule.ComponentCodes[0].Length == 0)) { return; } string sql = "INSERT INTO SCHEDULEDCOMPONENTCODE ( COMPONENTCODE, SCHEDULE_ID ) VALUES ( @COMPONENTCODE, @SCHEDULE_ID )"; try { using (IDbCommand cmd = GetCommand(sql, trx)) { foreach (string code in schedule.ComponentCodes) { cmd.Parameters.Clear(); cmd.Parameters.Add(GetDataParameter("@COMPONENTCODE", code)); cmd.Parameters.Add(GetDataParameter("@SCHEDULE_ID", schedule.Id)); cmd.ExecuteNonQuery(); } } } catch (Exception e) { Log.Debug(e.ToString()); throw new DataAccessException(string.Format("ID:{0}, SQL:{1}", schedule.Id, sql), e); } }
public override bool Insert(Schedule schedule, DataAccessTransaction trx) { if (!InsertSchedule(schedule, trx)) { return(false); } string sql = "INSERT INTO {0} ( SCHEDULE_ID ) VALUES ( @SCHEDULE_ID )"; using (IDbCommand cmd = GetCommand(string.Format(sql, TableName), trx)) { cmd.Parameters.Add(GetDataParameter("@SCHEDULE_ID", schedule.Id)); try { cmd.ExecuteNonQuery(); } catch (SQLiteException e) { Log.Debug(string.Format("Insert {0}, ID={1} - {2}", TableName, schedule.Id, e)); if (e.ErrorCode == SQLiteErrorCode.Constraint) { return(false); // assume we have a 'duplicate' error. } throw new DataAccessException(string.Format("ID:{0}, SQL:{1}", schedule.Id, sql), e); } } return(true); }
/// <summary> /// Replaces entire contents of the table with the passed-in serial numbers. /// </summary> /// <param name="serialNumbers"></param> /// <param name="trx"></param> /// <returns>Number of serial numbers saved.</returns> public int SaveAll(IEnumerable <string> serialNumbers, DataAccessTransaction trx) { // First, we always delete the current contents of the table. DeleteAll(trx); int insertedCount = 0; using (IDbCommand cmd = GetCommand(string.Format("INSERT INTO {0} ( SN, RECUPDATETIMEUTC ) VALUES ( @SN, @RECUPDATETIMEUTC )", TableName), trx)) { foreach (string sn in serialNumbers) { cmd.Parameters.Clear(); cmd.Parameters.Add(GetDataParameter("@SN", sn)); cmd.Parameters.Add(GetDataParameter("@RECUPDATETIMEUTC", trx.TimestampUtc)); try { insertedCount += cmd.ExecuteNonQuery(); } catch (Exception ex) { throw new DataAccessException(string.Format("Failure saving \"{0}\" to {1}", sn, TableName), ex); } } } return(insertedCount); }
public DockingStation Find() { using (DataAccessTransaction trx = new DataAccessTransaction(true)) { return(Find(trx)); } }
private bool Insert(EventJournal journal, DataAccessTransaction trx) { string sql = "INSERT INTO EVENTJOURNAL ( EVENTCODE, SN, INSTRUMENTSN, RECUPDATETIMEUTC, RUNTIMEUTC, EVENTTIMEUTC, PASSED, POSITION, SOFTWAREVERSION ) VALUES ( @EVENTCODE, @SN, @INSTRUMENTSN, @RECUPDATETIMEUTC, @RUNTIMEUTC, @EVENTTIMEUTC, @PASSED, @POSITION, @SOFTWAREVERSION )"; IDbCommand cmd = null; try { cmd = GetCommand(sql, trx); cmd.Parameters.Add(GetDataParameter("@EVENTCODE", journal.EventCode.Code)); cmd.Parameters.Add(GetDataParameter("@SN", journal.SerialNumber)); cmd.Parameters.Add(GetDataParameter("@RECUPDATETIMEUTC", trx.TimestampUtc)); cmd.Parameters.Add(GetDataParameter("@INSTRUMENTSN", journal.InstrumentSerialNumber)); cmd.Parameters.Add(GetDataParameter("@EVENTTIMEUTC", journal.EventTime)); cmd.Parameters.Add(GetDataParameter("@RUNTIMEUTC", journal.RunTime)); cmd.Parameters.Add(GetDataParameter("@PASSED", (journal.Passed == true) ? (short)1 : (short)0)); cmd.Parameters.Add(GetDataParameter("@POSITION", journal.Position)); cmd.Parameters.Add(GetDataParameter("@SOFTWAREVERSION", journal.SoftwareVersion)); int inserted = cmd.ExecuteNonQuery(); return(inserted > 0); } catch (Exception ex) { if ((ex is SQLiteException) && (((SQLiteException)ex).ErrorCode == SQLiteErrorCode.Constraint)) { return(false); // assume we have a 'duplicate' error. } throw new DataAccessException(string.Format("Journal: \"{0}\", SQL: {1}", journal, sql), ex); } }
private void SaveProperties(PersistedQueueData persistedQueueData, DataAccessTransaction trx) { if (persistedQueueData.Properties.Count == 0) { return; } string sql = string.Format("INSERT INTO {0} ( QUEUE_ID, ATTRIBUTE, VALUE ) VALUES ( @QUEUE_ID, @ATTRIBUTE, @VALUE )", TableName + "PROPERTY"); try { IDbCommand cmd = GetCommand(sql, trx); foreach (string attribute in persistedQueueData.Properties.Keys) { string value = persistedQueueData.Properties[attribute]; cmd.Parameters.Clear(); cmd.Parameters.Add(GetDataParameter("@QUEUE_ID", persistedQueueData.Id)); cmd.Parameters.Add(GetDataParameter("@ATTRIBUTE", attribute)); cmd.Parameters.Add(GetDataParameter("@VALUE", value)); int inserted = cmd.ExecuteNonQuery(); } } catch (Exception ex) { throw new DataAccessException("SQL: " + sql, ex); } }
/// <summary> /// Deletes all EventJournal records for the specified serial numbers. /// </summary> /// <param name="serialNumbers"></param> /// <param name="trx"></param> /// <returns></returns> public int DeleteBySerialNumbers(ICollection <string> serialNumbers, DataAccessTransaction trx) { string sql = string.Format("DELETE FROM EVENTJOURNAL WHERE SN IN ( {0} )", MakeCommaDelimitedParamNames(serialNumbers.Count, "@SN")); try { IDbCommand cmd = GetCommand(sql, trx); int i = 0; foreach (string sn in serialNumbers) { cmd.Parameters.Add(GetDataParameter("@SN" + (++i).ToString(), sn)); } int delCount = cmd.ExecuteNonQuery(); Log.Debug(string.Format("Deleted {0} EVENTJOURNALs", delCount)); return(delCount); } catch (Exception ex) { throw new DataAccessException(string.Format("SQL:{0}", sql), ex); } }
/// <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); } }
/// <summary> /// Deletes all EventJournal records for events older than the specified time. /// </summary> /// <param name="journalAge"></param> /// <param name="trx"></param> /// <returns></returns> public int DeleteByTime(TimeSpan journalAge, DataAccessTransaction trx) { if (journalAge < TimeSpan.Zero) { return(-1); // The time span provided is negative; we consider a negative span to be invalid. } DateTime deleteTimeStamp = DateTime.UtcNow - journalAge; string sql = string.Format("DELETE FROM EVENTJOURNAL WHERE EVENTTIMEUTC < @DELETETIMESTAMP"); try { IDbCommand cmd = GetCommand(sql, trx); cmd.Parameters.Add(GetDataParameter("@DELETETIMESTAMP", deleteTimeStamp)); int delCount = cmd.ExecuteNonQuery(); Log.Debug(string.Format("Deleted {0} EVENTJOURNALs older than {1} days, {2} hours, {3} minutes, and {4} seconds", delCount, journalAge.Days, journalAge.Hours, journalAge.Minutes, journalAge.Seconds)); return(delCount); } catch (Exception ex) { throw new DataAccessException(string.Format("SQL:{0}", sql), ex); } }
/// <summary> /// Finds and returns all FactoryCylinders in the database. /// </summary> /// <returns></returns> public IList <FactoryCylinder> FindAll() { using (DataAccessTransaction trx = new DataAccessTransaction(true)) { return(FindAll(trx)); } }