/// /// <param name="property">Module2Property to be written</param> /// <param name="timestamp">Time when data arrived to module</param> public void WriteProperty(IModule2Property property) { logger.LogNewInfo("Writing new property to database."); Dataset set = DatasetRepository.GetDataset(property.Code); string tableName = DatabaseTableNamesRepository.GetTableNameByDataset(set); string signalCode = property.Code.ToString(); double value = property.Value; string query = @"INSERT INTO " + tableName + "(signalCode, signalValue) VALUES (@code, @value)"; using (SQLiteCommand command = new SQLiteCommand(query, databaseConnection)) { command.Parameters.AddWithValue("@code", signalCode); command.Parameters.AddWithValue("@value", value); if (command.ExecuteNonQuery() == 0) { logger.LogNewWarning("Could not write to database."); } else { logger.LogNewInfo("Property written successfully."); } } }
/// /// <param name="property">Module 1 property</param> public void WriteProperty(IModule1Property property) { logger.LogNewInfo(string.Format("Trying to write property with signal code {0} and value {1} to database", property.Code, property.Module1Value)); Dataset set = DatasetRepository.GetDataset(property.Code); string tableName = DatabaseTableNamesRepository.GetTableNameByDataset(set); string signalCode = property.Code.ToString(); double value = property.Module1Value; string query = @"DELETE FROM " + tableName + " WHERE signalCode=@codeToDelete; INSERT INTO " + tableName + " (signalCode, signalValue) VALUES(@codeToInsert, @value)"; using (SQLiteCommand command = new SQLiteCommand(query, databaseConnection)) { command.Parameters.AddWithValue("@codeToDelete", signalCode); command.Parameters.AddWithValue("@codeToInsert", signalCode); command.Parameters.AddWithValue("@value", value); if (command.ExecuteNonQuery() == 0) { logger.LogNewWarning("Could not write to database."); } else { logger.LogNewInfo("Property written successfully."); } } }
/// /// <param name="periodStart">Beginning of the search period</param> /// <param name="periodEnd">End of the search period</param> public List<IModule2Property> ReadPropertiesByTimeframe(DateTime periodStart, DateTime periodEnd, SignalCode code) { List<IModule2Property> returnList = new List<IModule2Property>(); logger.LogNewInfo(string.Format("Reading properties for code {0} with starting date {1}, end date {2}", code, periodEnd, periodEnd)); Dataset set = DatasetRepository.GetDataset(code); string tableName = DatabaseTableNamesRepository.GetTableNameByDataset(set); string signalCode = code.ToString(); string dateStart= periodStart.ToUniversalTime().ToString("yyyy'-'MM'-'dd' 'HH':'mm':'ss"); string dateEnd = periodEnd.ToUniversalTime().ToString("yyyy'-'MM'-'dd' 'HH':'mm':'ss"); string query = @"SELECT signalCode, signalValue FROM " + tableName + " WHERE signalCode=@code AND " + "strftime('%s', timestamp) BETWEEN strftime('%s', @startDate) AND strftime('%s', @endDate) " + "ORDER BY timestamp DESC "; using (SQLiteCommand command = new SQLiteCommand(query, databaseConnection)) { command.Parameters.AddWithValue("@code", signalCode); command.Parameters.AddWithValue("@startDate", dateStart); command.Parameters.AddWithValue("@endDate", dateEnd); try { SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { string retrievedSignal = reader["signalCode"].ToString(); string value = reader["signalValue"].ToString(); SignalCode retrievedCode = (SignalCode)Enum.Parse(typeof(SignalCode), retrievedSignal); double valueRetrieved = double.Parse(value); Module2Property property = new Module2Property(retrievedCode, valueRetrieved); returnList.Add(property); } } catch (Exception ex) { logger.LogNewWarning(string.Format("ERROR occured reading database. MESSAGE: {0}", ex.Message)); return returnList; } } logger.LogNewInfo(string.Format("Found {0} properties within the requested timeframe", returnList.Count)); return returnList; }
/// /// <param name="code">Signal code</param> public IModule2Property ReadLastByCode(SignalCode code) { Dataset set = DatasetRepository.GetDataset(code); string tableName = DatabaseTableNamesRepository.GetTableNameByDataset(set); string signalCode = code.ToString(); string query = "SELECT ID, signalCode, signalValue FROM " + tableName + " WHERE(signalCode=@code) " + "ORDER BY timestamp DESC LIMIT 1"; using (SQLiteCommand command = new SQLiteCommand(query, databaseConnection)) { command.Parameters.AddWithValue("@code", signalCode); try { using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { string retrievedSignal = reader["signalCode"].ToString(); string value = reader["signalValue"].ToString(); SignalCode retrievedCode = (SignalCode)Enum.Parse(typeof(SignalCode), retrievedSignal); double valueRetrieved = double.Parse(value); Module2Property property = new Module2Property(retrievedCode, valueRetrieved); return property; } return null; } }catch(Exception ex) { logger.LogNewWarning(string.Format("ERROR occured reading database. MESSAGE: {0}", ex.Message)); return null; } } }
public string GetTableNameByDataset_AllDatasets_AssertName(Dataset set) { return(DatabaseTableNamesRepository.GetTableNameByDataset(set)); }