public int GetVariableID(int seriesID) { var result = DbOperations.ExecuteSingleOutput("SELECT VariableID FROM DataSeries WHERE SeriesID = " + seriesID); return(Convert.ToInt32(result)); }
public double CalculatePercAvailable(long seriesID, DateTime minDate, DateTime maxDate) { var query = string.Format( "select count(DataValue) from {0} main " + "LEFT JOIN DataSeries ds ON ds.SeriesID = main.SeriesID " + "LEFT JOIN Variables v ON v.VariableID = ds.VariableID " + "WHERE DateTimeUTC >= '{1}' and DateTimeUTC <= '{2}' and main.SeriesID = {3} and DataValue <> v.NoDataValue ", TableName, minDate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), maxDate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), seriesID); var hasValues = Convert.ToDouble(DbOperations.ExecuteSingleOutput(query), CultureInfo.InvariantCulture); query = string.Format( "select count(DataValue) from {0} main " + "WHERE DateTimeUTC >= '{1}' and DateTimeUTC <= '{2}' and main.SeriesID = {3} ", TableName, minDate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), maxDate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), seriesID); var totalValues = Convert.ToDouble(DbOperations.ExecuteSingleOutput(query), CultureInfo.InvariantCulture); var value = hasValues * 100.0 / totalValues; return(value); }
public Tuple <DateTime, DateTime> GetDateTimes(long seriesID) { var begin = Convert.ToDateTime(DbOperations.ExecuteSingleOutput("Select BeginDateTime FROM DataSeries WHERE SeriesID = " + seriesID)); var end = Convert.ToDateTime(DbOperations.ExecuteSingleOutput("Select EndDateTime FROM DataSeries WHERE SeriesID = " + seriesID)); return(new Tuple <DateTime, DateTime>(begin, end)); }
public double GetMinValue(long seriesID) { var query = "SELECT MIN(DataValue) FROM DataValues WHERE SeriesID = " + seriesID; var res = DbOperations.ExecuteSingleOutput(query); return(Convert.ToDouble(res)); }
public void UpdateDataSeriesFromDataValues(long seriesID) { var SQLstring = "SELECT LocalDateTime FROM DataValues WHERE SeriesID = " + seriesID + " ORDER BY LocalDateTime ASC"; var BeginDateTime = Convert.ToDateTime(DbOperations.ExecuteSingleOutput(SQLstring), CultureInfo.InvariantCulture); SQLstring = "SELECT LocalDateTime FROM DataValues WHERE SeriesID = " + seriesID + " ORDER BY LocalDateTime DESC"; var EndDateTime = Convert.ToDateTime(DbOperations.ExecuteSingleOutput(SQLstring), CultureInfo.InvariantCulture); SQLstring = "SELECT DateTimeUTC FROM DataValues WHERE SeriesID = " + seriesID + " ORDER BY LocalDateTime ASC"; var BeginDateTimeUTC = Convert.ToDateTime(DbOperations.ExecuteSingleOutput(SQLstring), CultureInfo.InvariantCulture); SQLstring = "SELECT DateTimeUTC FROM DataValues WHERE SeriesID = " + seriesID + " ORDER BY LocalDateTime DESC"; var EndDateTimeUTC = Convert.ToDateTime(DbOperations.ExecuteSingleOutput(SQLstring), CultureInfo.InvariantCulture); SQLstring = "SELECT COUNT(*) FROM DataValues WHERE SeriesID = " + seriesID; var ValueCount = DbOperations.ExecuteSingleOutput(SQLstring); SQLstring = "UPDATE DataSeries SET BeginDateTime = '" + BeginDateTime.ToString("yyyy-MM-dd HH:mm:ss") + "', "; SQLstring += "EndDateTime = '" + EndDateTime.ToString("yyyy-MM-dd HH:mm:ss") + "', "; SQLstring += "BeginDateTimeUTC = '" + BeginDateTimeUTC.ToString("yyyy-MM-dd HH:mm:ss") + "', "; SQLstring += "EndDateTimeUTC = '" + EndDateTimeUTC.ToString("yyyy-MM-dd HH:mm:ss") + "', "; SQLstring += "ValueCount = " + ValueCount + " WHERE SeriesID = " + seriesID; DbOperations.ExecuteNonQuery(SQLstring); }
public long GetQualityControlLevelID(long seriesID) { var res = DbOperations.ExecuteSingleOutput("SELECT QualityControlLevelID FROM DataSeries WHERE SeriesID = " + seriesID); return(Convert.ToInt64(res)); }
public bool ExistsSeries(Site site, Variable variable) { var query = string.Format("select count(*) from DataSeries where SiteID={0} and VariableID={1}", site.Id, variable.Id); var res = DbOperations.ExecuteSingleOutput(query); return(Convert.ToInt64(res) > 0); }
public bool Exists(object key) { var res = DbOperations.ExecuteSingleOutput( string.Format("select count(*) from {0} where {1}=?", TableName, PrimaryKeyName), key); return(Convert.ToInt64(res) > 0); }
public long GetCountForAllFieldsInSequence(IList <int> seriesIDs) { var whereClause = GetWhereClauseForIds(seriesIDs); var countQuery = "select count(ValueID) from DataValues WHERE " + whereClause; var res = DbOperations.ExecuteSingleOutput(countQuery); return(Convert.ToInt64(res)); }
public double GetNoDataValueForSeriesVariable(long seriesID) { var query = "SELECT NoDataValue FROM DataSeries LEFT JOIN Variables ON DataSeries.VariableID = Variables.VariableID WHERE SeriesID = " + seriesID; var result = DbOperations.ExecuteSingleOutput(query); return(Convert.ToDouble(result)); }
public string GetQualityControlLevelCode(long seriesID) { var query = "SELECT QualityControlLevelCode FROM DataSeries AS d LEFT JOIN QualityControlLevels AS q ON (d.QualityControlLevelID = q.QualityControlLevelID) WHERE SeriesID = " + seriesID; var res = DbOperations.ExecuteSingleOutput(query); return(Convert.ToString(res)); }
public bool ExistsConversion(Unit unitA, Unit unitB) { var res = DbOperations.ExecuteSingleOutput ( "select count(*) from UnitConversions where FromUnitsID = ? and ToUnitsID = ?", new object[] { unitA.Id, unitB.Id } ); return(Convert.ToInt64(res) > 0); }
public int?GetMethodID(string methodDescription) { var res = DbOperations.ExecuteSingleOutput(string.Format("select MethodID from Methods where MethodDescription='{0}'", methodDescription)); if (res == null || res == DBNull.Value) { return(null); } return(Convert.ToInt32(res)); }
public long GetCountForJustValuesInParallel(IList <int> seriesIDs) { var whereClause = GetWhereClauseForIds(seriesIDs); var countQuery = string.Format("select count(*) from (select distinct LocalDateTime from DataValues where {0}) A", whereClause); var res = DbOperations.ExecuteSingleOutput(countQuery); return(Convert.ToInt64(res)); }
public void InsertNewTheme(long seriesID, long newSeriesID) { var SQLstring = "SELECT ThemeID FROM DataThemes WHERE SeriesID = " + seriesID; var ThemeID = Convert.ToInt64(DbOperations.ExecuteSingleOutput(SQLstring)); SQLstring = "INSERT INTO DataThemes(ThemeID, SeriesID) VALUES ("; SQLstring += ThemeID + "," + newSeriesID + ")"; DbOperations.ExecuteNonQuery(SQLstring); }
public int?GetID(string themeName) { const string sql = "SELECT ThemeID from DataThemeDescriptions WHERE ThemeName =?"; var objThemeId = DbOperations.ExecuteSingleOutput(sql, themeName); if (objThemeId == null || objThemeId == DBNull.Value) { return(null); } return(Convert.ToInt32(objThemeId)); }
public void AddQualifier(Qualifier entity) { var query = "INSERT INTO Qualifiers(QualifierCode, QualifierDescription) VALUES (?, ?)" + LastRowIDSelect; var id = DbOperations.ExecuteSingleOutput(query, new object[] { entity.Code, entity.Description }); entity.Id = Convert.ToInt64(id); }
public bool Exists(Site site) { if (site == null) { return(false); } const string query = "select count(*) from {0} where SiteID = {1} and SiteCode = '{2}'"; var result = DbOperations.ExecuteSingleOutput(string.Format(query, TableName, site.Id, site.Code)); return(Convert.ToInt32(result) > 0); }
public DataTable GetThemesForAllSeries() { var dtThemes = DbOperations.LoadTable(TableName, "SELECT ThemeID, ThemeName from DataThemeDescriptions"); if (Int32.Parse(DbOperations.ExecuteSingleOutput("Select count(*) from DataSeries " + "Where SeriesID not in (Select SeriesID from DataThemes)"). ToString()) > 0) { dtThemes.Rows.Add(DBNull.Value, OTHER_DATA_SERIES); } return(dtThemes); }
public bool Exists(Variable entity) { if (entity == null) { return(false); } const string query = "select count(*) from {0} where VariableID = {1} and VariableCode = '{2}'"; var result = DbOperations.ExecuteSingleOutput(string.Format(query, TableName, entity.Id, entity.Code)); return(Convert.ToInt32(result) > 0); }
public void AddNew(QualityControlLevel entity) { var query = "INSERT INTO QualityControlLevels(QualityControlLevelCode, Definition, Explanation) VALUES (?, ?, ?)" + LastRowIDSelect; var id = DbOperations.ExecuteSingleOutput(query, new object[] { entity.Code, entity.Definition, entity.Explanation }); entity.Id = Convert.ToInt64(id); }
public void AddUnit(Unit unit) { var query = "INSERT INTO Units(UnitsAbbreviation, UnitsName, UnitsType)" + "VALUES (?, ?, ?)" + LastRowIDSelect; var id = DbOperations.ExecuteSingleOutput(query, new object[] { unit.Abbreviation, unit.Name, unit.UnitsType, }); unit.Id = Convert.ToInt64(id); }
public double?AggregateValues(long seriesID, string aggregateFunction, DateTime minDate, DateTime maxDate) { var query = string.Format( "select {0}(DataValue) from {1} main " + "LEFT JOIN DataSeries ds ON ds.SeriesID = main.SeriesID " + "LEFT JOIN Variables v ON v.VariableID = ds.VariableID " + "WHERE DateTimeUTC >= '{2}' and DateTimeUTC <= '{3}' and main.SeriesID = {4} and DataValue <> v.NoDataValue ", aggregateFunction, TableName, minDate.ToString("yyyy-MM-dd HH:mm:ss"), maxDate.ToString("yyyy-MM-dd HH:mm:ss"), seriesID); var value = DbOperations.ExecuteSingleOutput(query); return(value != DBNull.Value ? (double?)Convert.ToDouble(value, CultureInfo.InvariantCulture) : null); }
public void AddSite(Site site) { var query = "INSERT INTO Sites(SiteCode, SiteName, Latitude, Longitude, Elevation_m, Comments, County, State, PosAccuracy_m, LocalX, LocalY, VerticalDatum, LatLongDatumID, LocalProjectionID)" + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + LastRowIDSelect; var id = DbOperations.ExecuteSingleOutput(query, new object[] { site.Code, site.Name, site.Latitude, site.Longitude, site.Elevation_m, site.Comments, site.County, site.State, site.PosAccuracy_m, site.LocalX, site.LocalX, site.VerticalDatum, site.SpatialReference == null? 0 : site.SpatialReference.Id, site.LocalProjection == null? 0 : site.LocalProjection.Id }); site.Id = Convert.ToInt64(id); }
public void AddVariable(Variable variable) { var query = string.Format( @"INSERT INTO {0}(VariableCode, VariableName, Speciation, SampleMedium, ValueType, IsRegular, IsCategorical, TimeSupport, DataType, GeneralCategory, NoDataValue, TimeUnitsID, VariableUnitsID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", TableName) + LastRowIDSelect; var res = DbOperations.ExecuteSingleOutput(query, new object[] { variable.Code, variable.Name, variable.Speciation, variable.SampleMedium, variable.ValueType, Convert.ToInt32(variable.IsRegular), Convert.ToInt32(variable.IsCategorical), variable.TimeSupport, variable.DataType, variable.GeneralCategory, variable.NoDataValue, variable.TimeUnit != null ? variable.TimeUnit.Id : 0, variable.VariableUnit != null ? variable.VariableUnit.Id : 0 }); variable.Id = Convert.ToInt64(res); }
public void UpdateValuesForEditView(DataTable table) { const string updateFormatString = "UPDATE DataValues SET DataValue = {0}, QualifierID = {1} WHERE ValueID = {2}; "; const string insertFormatString = "INSERT INTO DataValues (ValueID,SeriesID,DataValue,ValueAccuracy,LocalDateTime,UTCOffset,DateTimeUTC, " + "OffsetValue, OffsetTypeID, CensorCode, QualifierID, SampleID, FileID) VALUES (" + "{0},{1},{2},'{3}','{4}',{5},'{6}',{7},{8},'{9}',{10},{11},{12}) ;"; var sqLstring2 = new StringBuilder(); sqLstring2.Append("BEGIN TRANSACTION; "); var qualifierRepo = RepositoryFactory.Instance.Get <IQualifiersRepository>(); for (int i = 0; i < table.Rows.Count; i++) { var row = table.Rows[i]; var valueID = Convert.ToInt64(row["ValueID"]); var other = Convert.ToInt64(row["Other"]); if (other != 0) { //Deleteing point if (other == -1) { sqLstring2.AppendFormat("DELETE FROM DataValues WHERE ValueID = {0}; ", valueID); } else if (other == 1) { if ( String.IsNullOrEmpty( DbOperations.ExecuteSingleOutput( "Select ValueID FROM DataValues WHERE ValueID = " + valueID).ToString())) { sqLstring2.AppendFormat(insertFormatString, row[0], row[1], Convert.ToString(row[2], CultureInfo.InvariantCulture), row[3] == DBNull.Value ? "NULL" : row[3], Convert.ToDateTime(row[4]).ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), row[5], Convert.ToDateTime(row[6]).ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), row[8] == DBNull.Value ? "NULL" : Convert.ToString(row[8], CultureInfo.InvariantCulture), row[9] == DBNull.Value ? "NULL" : row[9], row[10] == DBNull.Value ? "NULL" : row[10], row[7] == DBNull.Value ? "NULL" : qualifierRepo.FindByCodeOrCreate(row[7].ToString()).Id. ToString(CultureInfo.InvariantCulture), row[11] == DBNull.Value ? "NULL" : row[11], row[12] == DBNull.Value ? "NULL" : row[12]); } } else if (other == 2) { sqLstring2.AppendFormat(updateFormatString, Convert.ToString(row["DataValue"], CultureInfo.InvariantCulture), qualifierRepo.FindByCodeOrCreate(row["QualifierCode"].ToString()).Id, valueID); } } } if (!sqLstring2.ToString().TrimEnd().EndsWith(";")) { sqLstring2.Append(";"); } sqLstring2.Append("COMMIT;"); DbOperations.ExecuteNonQuery(sqLstring2.ToString()); }
public bool Exists(string name) { var res = DbOperations.ExecuteSingleOutput("Select count(*) FROM Units where UnitsName=?", new object[] { name }); return(Convert.ToInt64(res) > 0); }