public void DeriveInsertAggregateDataValues(DataTable dt, long newSeriesID, DateTime currentdate, DateTime lastdate, DeriveAggregateMode mode, DeriveComputeMode computeMode, double nodatavalue, IProgressHandler progressHandler) { const string insertQuery = "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});"; const int chunkLength = 400; var index = 0; while (currentdate <= lastdate) { // Save values by chunks var newValueID = DbOperations.GetNextID("DataValues", "ValueID"); var query = new StringBuilder("BEGIN TRANSACTION; "); for (int i = 0; i <= chunkLength - 1; i++) { double newvalue = 0.0; string sqlString = string.Empty; double UTC = 0.0; switch (mode) { case DeriveAggregateMode.Daily: sqlString = "LocalDateTime >= '" + currentdate.ToString(CultureInfo.InvariantCulture) + "' AND LocalDateTime <= '" + currentdate.AddDays(1).AddMilliseconds(-1).ToString(CultureInfo.InvariantCulture) + "' AND DataValue <> " + nodatavalue.ToString(CultureInfo.InvariantCulture); break; case DeriveAggregateMode.Monthly: sqlString = "LocalDateTime >= '" + currentdate.ToString(CultureInfo.InvariantCulture) + "' AND LocalDateTime <= '" + currentdate.AddMonths(1).AddMilliseconds(-1).ToString(CultureInfo.InvariantCulture) + "' AND DataValue <> " + nodatavalue.ToString(CultureInfo.InvariantCulture); break; case DeriveAggregateMode.Quarterly: sqlString = "LocalDateTime >= '" + currentdate.ToString(CultureInfo.InvariantCulture) + "' AND LocalDateTime <= '" + currentdate.AddMonths(3).AddMilliseconds(-1).ToString( CultureInfo.InvariantCulture) + "' AND DataValue <> " + nodatavalue.ToString(CultureInfo.InvariantCulture); break; } try { switch (computeMode) { case DeriveComputeMode.Maximum: newvalue = Convert.ToDouble(dt.Compute("Max(DataValue)", sqlString)); break; case DeriveComputeMode.Minimum: newvalue = Convert.ToDouble(dt.Compute("MIN(DataValue)", sqlString)); break; case DeriveComputeMode.Average: newvalue = Convert.ToDouble(dt.Compute("AVG(DataValue)", sqlString)); break; case DeriveComputeMode.Sum: newvalue = Convert.ToDouble(dt.Compute("Sum(DataValue)", sqlString)); break; } UTC = Convert.ToDouble(dt.Compute("AVG(UTCOffset)", sqlString)); } catch (Exception) { newvalue = nodatavalue; } query.AppendFormat(insertQuery, newValueID + i, newSeriesID, newvalue, 0, Convert.ToDateTime(dt.Rows[index]["LocalDateTime"]).ToString("yyyy-MM-dd HH:mm:ss"), UTC.ToString(CultureInfo.InvariantCulture), currentdate.AddHours(UTC).ToString("yyyy-MM-dd HH:mm:ss"), "NULL", "NULL", "nc", "NULL", "NULL", "NULL"); query.AppendLine(); switch (mode) { case DeriveAggregateMode.Daily: currentdate = currentdate.AddDays(1); break; case DeriveAggregateMode.Monthly: currentdate = currentdate.AddMonths(1); break; case DeriveAggregateMode.Quarterly: currentdate = currentdate.AddMonths(3); break; } if (currentdate > lastdate) { break; } index = index + 1; //Report progress progressHandler.ReportProgress(index - 1, null); } query.AppendLine("COMMIT;"); DbOperations.ExecuteNonQuery(query.ToString()); progressHandler.ReportProgress(index - 1, null); } }
public void DeriveInsertDataValues(double A, double B, double C, double D, double E, double F, DataTable dt, long newSeriesID, long sourceSeriesID, bool isAlgebraic, IProgressHandler progressHandler) { const int chunkLength = 400; var nodatavalue = GetNoDataValueForSeriesVariable(newSeriesID); const string insertQuery = "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 index = 0; while (index != dt.Rows.Count - 1) { //Save values by chunks var newValueID = DbOperations.GetNextID("DataValues", "ValueID"); var query = new StringBuilder("BEGIN TRANSACTION; "); for (int i = 0; i < chunkLength; i++) { // Calculating value double newvalue = 0.0; if (isAlgebraic) { var currentvalue = Convert.ToDouble(dt.Rows[index]["DataValue"]); if (currentvalue != nodatavalue) { //NOTE:Equation = Fx ^ 5 + Ex ^ 4 + Dx ^ 3 + Cx ^ 2 + Bx + A newvalue = (F * (Math.Pow(currentvalue, 5))) + (E * (Math.Pow(currentvalue, 4))) + (D * (Math.Pow(currentvalue, 3))) + (C * (Math.Pow(currentvalue, 2))) + (B * currentvalue) + A; newvalue = Math.Round(newvalue, 5); } else { newvalue = nodatavalue; } } else { newvalue = Convert.ToDouble(dt.Rows[index]["DataValue"]); } var row = dt.Rows[index]; query.AppendFormat(insertQuery, newValueID + i, newSeriesID, newvalue, row["ValueAccuracy"].ToString() == "" ? "NULL" : row["ValueAccuracy"].ToString(), Convert.ToDateTime(row["LocalDateTime"]).ToString("yyyy-MM-dd HH:mm:ss"), row["UTCOffset"].ToString(), Convert.ToDateTime(row["DateTimeUTC"]).ToString("yyyy-MM-dd HH:mm:ss"), row["OffsetValue"].ToString() == "" ? "NULL" : row["OffsetValue"].ToString(), row["OffsetTypeID"].ToString() == "" ? "NULL" : row["OffsetTypeID"].ToString(), row["CensorCode"].ToString(), row["QualifierID"].ToString() == "" ? "NULL" : row["QualifierID"].ToString(), row["SampleID"].ToString() == "" ? "NULL" : row["SampleID"].ToString(), row["FileID"].ToString() == "" ? "NULL" : row["FileID"].ToString()); query.AppendLine(); if (index == dt.Rows.Count - 1) { break; } index = index + 1; } query.AppendLine("COMMIT;"); DbOperations.ExecuteNonQuery(query.ToString()); progressHandler.ReportProgress(index, null); } }
public DataTable AsDataTable() { var table = DbOperations.LoadTable(TableName, string.Format("Select * from {0}", TableName)); return(table); }
public Series GetSeriesByID(long seriesID) { var seriesTable = DbOperations.LoadTable("seriesTable", "select * from DataSeries where seriesID=" + seriesID); if (seriesTable.Rows.Count == 0) { return(null); } var series = new Series(); DataRow seriesRow = seriesTable.Rows[0]; series.BeginDateTime = Convert.ToDateTime(seriesRow["BeginDateTime"]); series.EndDateTime = Convert.ToDateTime(seriesRow["EndDateTime"]); series.BeginDateTimeUTC = Convert.ToDateTime(seriesRow["BeginDateTimeUTC"]); series.EndDateTimeUTC = Convert.ToDateTime(seriesRow["EndDatetimeUTC"]); series.Id = seriesID; series.IsCategorical = Convert.ToBoolean(seriesRow["IsCategorical"]); series.LastCheckedDateTime = Convert.ToDateTime(seriesRow["LastCheckedDateTime"]); series.UpdateDateTime = Convert.ToDateTime(seriesRow["UpdateDateTime"]); series.Subscribed = Convert.ToBoolean(seriesRow["Subscribed"]); series.ValueCount = Convert.ToInt32(seriesRow["ValueCount"]); int siteID = Convert.ToInt32(seriesRow["SiteID"]); string sqlSites = "SELECT SiteID, SiteCode, SiteName, Latitude, Longitude, Elevation_m, " + "VerticalDatum, LocalX, LocalY, State, County, Comments FROM Sites where SiteID = " + siteID; DataTable siteTable = DbOperations.LoadTable("siteTable", sqlSites); if (siteTable.Rows.Count == 0) { return(null); } DataRow siteRow = siteTable.Rows[0]; var newSite = new Site(); newSite.Id = Convert.ToInt32(siteRow[0]); newSite.Code = Convert.ToString(siteRow[1]); newSite.Name = Convert.ToString(siteRow[2]); newSite.Latitude = Convert.ToDouble(siteRow[3]); newSite.Longitude = Convert.ToDouble(siteRow[4]); newSite.Elevation_m = Convert.ToDouble(siteRow[5]); newSite.VerticalDatum = Convert.ToString(siteRow[6]); newSite.LocalX = Convert.ToDouble(siteRow["LocalX"]); newSite.LocalY = Convert.ToDouble(siteRow["LocalY"]); series.Site = newSite; int variableID = Convert.ToInt32(seriesRow["VariableID"]); series.Variable = RepositoryFactory.Instance.Get <IVariablesRepository>().GetByKey(variableID); var newMethod = new Method { Id = Convert.ToInt32(seriesRow["MethodID"]) }; series.Method = newMethod; var newSource = new Source { Id = Convert.ToInt32(seriesRow["SourceID"]) }; series.Source = newSource; var newQC = new QualityControlLevel { Id = Convert.ToInt32(seriesRow["QualityControlLevelID"]) }; series.QualityControlLevel = newQC; return(series); }
public long GetNextID() { return(DbOperations.GetNextID(TableName, PrimaryKeyName)); }
public bool Exists(string name) { var res = DbOperations.ExecuteSingleOutput("Select count(*) FROM Units where UnitsName=?", new object[] { name }); return(Convert.ToInt64(res) > 0); }
/// <summary> /// Validate database schema. /// If schema not valid - InvalidDatabaseSchemaException throws. /// </summary> /// <param name="databaseToCheck">Path to SQLite checked database.</param> /// <param name="databaseType">Database type.</param> /// <exception cref="InvalidDatabaseSchemaException">Throws if database schema not valid.</exception> public static void CheckDatabaseSchema(string databaseToCheck, DatabaseType databaseType) { if (!DatabaseExists(databaseToCheck)) { throw new InvalidDatabaseSchemaException("Database not exists"); } var originalDbPath = Path.GetTempFileName(); try { bool dbCreated; switch (databaseType) { case DatabaseType.DefaulDatabase: dbCreated = CreateSQLiteDatabase(originalDbPath); break; case DatabaseType.MetadataCacheDatabase: dbCreated = CreateMetadataCacheDb(originalDbPath); break; default: dbCreated = false; break; } if (!dbCreated) { throw new InvalidDatabaseSchemaException("Unable to create original database"); } var originalDbOperations = new DbOperations(GetSQLiteConnectionString(originalDbPath), Interfaces.DatabaseTypes.SQLite); var checkedlDbOperations = new DbOperations(GetSQLiteConnectionString(databaseToCheck), Interfaces.DatabaseTypes.SQLite); const string allTablesQuery = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"; var originalTables = originalDbOperations.LoadTable(allTablesQuery); var checkedTables = checkedlDbOperations.LoadTable(allTablesQuery); var sbErrors = new StringBuilder(); foreach (DataRow row in originalTables.Rows) { var tableName = row["name"].ToString(); // Check for table existing var rows = checkedTables.Select(string.Format("name = '{0}'", tableName)); if (rows.Length == 0) { sbErrors.AppendLine(string.Format("Table '{0}' not found", tableName)); continue; } // Check for table schema var schemaQuery = string.Format("PRAGMA table_info({0})", tableName); var originalSchema = originalDbOperations.LoadTable(schemaQuery); var checkedSchema = checkedlDbOperations.LoadTable(schemaQuery); foreach (DataRow schemaRow in originalSchema.Rows) { var columnName = schemaRow["name"].ToString(); var columnRows = checkedSchema.Select(string.Format("name = '{0}'", columnName)); if (columnRows.Length == 0) { sbErrors.AppendLine(string.Format("Table '{0}': column '{1}' not found", tableName, columnName)); } } } if (sbErrors.Length != 0) { sbErrors.Remove(sbErrors.Length - Environment.NewLine.Length, Environment.NewLine.Length); // remove last new line delimeter throw new InvalidDatabaseSchemaException(sbErrors.ToString()); } }catch (Exception ex) { if (ex is InvalidDatabaseSchemaException) { throw; } throw new InvalidDatabaseSchemaException("Unable to check database schema", ex); } finally { File.Delete(originalDbPath); } }
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 void DeleteById(long valueID) { DbOperations.ExecuteNonQuery("DELETE FROM DataValues WHERE ValueID =" + valueID); }