public async Task <int?> saveLocationToDB(MetadataInput newMetadata, NpgsqlConnection connection)
{
string newLocationQuery = MetadataQueryBuilder.InsertLocationString(newMetadata);
int? locationID = null;
using var cmdLocation = new NpgsqlCommand(newLocationQuery);
cmdLocation.Connection = connection;
var dataReaderLocation = await cmdLocation.ExecuteReaderAsync();
var fieldsCountLocation = dataReaderLocation.GetColumnSchema().Count();
while (dataReaderLocation.Read())
{
locationID = dataReaderLocation["id"] == DBNull.Value ? (int?)null : dataReaderLocation.GetFieldValue <int>(dataReaderLocation.GetOrdinal("id"));
}
await dataReaderLocation.CloseAsync();
return(locationID);
}
public async Task <MetadataType> saveMetadataToDB(int?locationID, int sensorID, MetadataInput newMetadata, NpgsqlConnection connection)
{
String newMetadataQuery = MetadataQueryBuilder.CreateInsertMetadataString(newMetadata, locationID, sensorID);
using var cmd = new NpgsqlCommand(newMetadataQuery);
cmd.Connection = connection;
var dataReader = await cmd.ExecuteReaderAsync();
MetadataType addedMetadata = null;
while (dataReader.Read())
{
addedMetadata = BuildMetadataObject(dataReader);
}
;
await dataReader.CloseAsync();
return(addedMetadata);
}
public async Task <MetadataType> addMetadataToDatabase(MetadataInput newMetadata)
{
NpgsqlConnection _npgsqlConnection = new NpgsqlConnection(_databaseSettings.DatabaseConnectionString);
await _npgsqlConnection.OpenAsync();
//Retrieve the last metadata from database if exists
String lastMetadataQuery = MetadataQueryBuilder.CreateMetadataString(null, newMetadata.Number, true);
int? locationID = null;
MetadataType lastMetadata = null;
var queryResult = GetMetadata(lastMetadataQuery).Result;
bool addNewLocation = true;
// If the sensor has previous metadata
if (queryResult.Count > 0)
{
lastMetadata = queryResult.ElementAt(0);
locationID = lastMetadata.LocationID;
addNewLocation = isLocationNew(newMetadata, lastMetadata);
//If location is new for that metadata, registrer it.
if (isLocationNew(newMetadata, lastMetadata))
{
if (isLocationNotEmpty(newMetadata))
{
//Add the new location to the database and update the locationID with the returned ID from the Database
locationID = await saveLocationToDB(newMetadata, _npgsqlConnection);
}
else
{
// if all of location fields are empty then set locationID to null
locationID = null;
}
}
//save the new metadata to DB
MetadataType addedMetadata = await saveMetadataToDB(locationID, lastMetadata.SensorID, newMetadata, _npgsqlConnection);
//Update outdated timestamp on last metadata
String updateQuery = MetadataQueryBuilder.UpdateOldMetadataString(addedMetadata.CreatedAt, lastMetadata.MetadataID);
using var cmdUpdate = new NpgsqlCommand(updateQuery);
cmdUpdate.Connection = _npgsqlConnection;
await cmdUpdate.ExecuteNonQueryAsync();
await _npgsqlConnection.CloseAsync();
return(addedMetadata);
}
else
{
//save location to DB if not empty
if (isLocationNotEmpty(newMetadata))
{
//Add the new location to the database and update the locationID with the returned ID from the Database
locationID = await saveLocationToDB(newMetadata, _npgsqlConnection);
}
//add new sensor in "sensor" table
using var cmdNewSensor = new NpgsqlCommand(MetadataQueryBuilder.InsertNewEmptySensor());
cmdNewSensor.Connection = _npgsqlConnection;
int sensorID = Convert.ToInt32(await cmdNewSensor.ExecuteScalarAsync());
//save the new metadata to DB, connected to the new sensor
var addedMetadata = await saveMetadataToDB(locationID, sensorID, newMetadata, _npgsqlConnection);
_npgsqlConnection.CloseAsync();
return(addedMetadata);
}
}
