public DataTable GetSeriesIDsWithNoDataValueTable(IEnumerable <int?> themeIDs)
{
var themeList = themeIDs.ToList();
var hasNulls = themeList.Any(value => !value.HasValue);
var notNullsFilter = new StringBuilder();
const string separator = ", ";
foreach (var themeID in themeList.Where(themeID => themeID.HasValue))
{
notNullsFilter.Append(themeID + separator);
}
if (notNullsFilter.Length > 0)
{
notNullsFilter.Remove(notNullsFilter.Length - separator.Length, separator.Length);
}
var query = "SELECT v.NoDataValue, ds.SeriesID " +
"FROM DataSeries ds INNER JOIN variables v ON ds.VariableID = v.VariableID " +
"LEFT JOIN DataThemes t ON ds.SeriesID = t.SeriesID " +
"WHERE t.ThemeID in (" + notNullsFilter + ")";
if (hasNulls)
{
query += " or t.ThemeID is null";
}
var dtSeries = DbOperations.LoadTable("series", query);
return(dtSeries);
}
public DataTable GetAllOrderByLocalDateTime(long seriesID)
{
var query = "SELECT * FROM DataValues WHERE SeriesID = " + seriesID + " ORDER BY LocalDateTime";
var result = DbOperations.LoadTable(TableName, query);
return(result);
}
/// <summary>
/// Deletes a theme and all its series as long as the series don't belong to any other theme.
/// </summary>
/// <param name="themeID">The Theme ID</param>
/// <returns>true if successful, false otherwise</returns>
public bool DeleteTheme(int themeID)
{
string sqlTheme = "SELECT SeriesID FROM DataThemes where ThemeID = " + themeID;
DataTable tblSeries = DbOperations.LoadTable("tblSeries", sqlTheme);
foreach (DataRow seriesRow in tblSeries.Rows)
{
int seriesID = Convert.ToInt32(seriesRow["SeriesID"]);
var seriesRepository = RepositoryFactory.Instance.Get <IDataSeriesRepository>();
seriesRepository.DeleteSeries(seriesID);
}
//delete the actual theme
string sqlDeleteTheme = "DELETE FROM DataThemeDescriptions WHERE ThemeID = " + themeID;
try
{
DbOperations.ExecuteNonQuery(sqlDeleteTheme);
}
catch { };
//re-check the number of series in the theme
return(true);
}
public DataTable GetThemesTableForThemeManager(long?themeID)
{
var sql =
"SELECT src.Organization as 'DataSource', ds.SeriesID, " +
"s.SiteName as 'SiteName', s.Latitude as 'Latitude', s.Longitude as 'Longitude', s.SiteCode as 'SiteCode', " +
"v.VariableName as 'VarName', v.DataType as 'DataType', v.SampleMedium as 'SampleMed', " +
"v.VariableCode as 'VarCode', u.UnitsName as 'Units', " +
"v.VariableCode as 'ServiceCode', " +
"m.MethodDescription as 'Method', qc.Definition as 'QCLevel', " +
"ds.BeginDateTime as 'StartDate', ds.EndDateTime as 'EndDate', ds.ValueCount as 'ValueCount', " +
"null as 'ServiceURL' " +
"FROM DataSeries ds " +
"LEFT JOIN DataThemes dt on dt.SeriesID = ds.SeriesID " +
"LEFT JOIN Sites s on ds.SiteID = s.SiteID " +
"LEFT JOIN Variables v on ds.VariableID = v.VariableID " +
"LEFT JOIN Units u on u.UnitsID = v.VariableUnitsID " +
"LEFT JOIN Methods m on ds.MethodID = m.MethodID " +
"LEFT JOIN Sources src on ds.SourceID = src.SourceID " +
"LEFT JOIN QualityControlLevels qc on ds.QualityControlLevelID = qc.QualityControlLevelID " +
(themeID.HasValue ? "WHERE dt.ThemeID = " + themeID : "WHERE dt.ThemeID is null");
var table = DbOperations.LoadTable("ThemeTable", sql);
return(table);
}
public DataTable GetAll(long seriesID)
{
var query = "SELECT * FROM DataValues WHERE SeriesID = " + seriesID;
var result = DbOperations.LoadTable(TableName, query);
return(result);
}
public Unit GetByName(string name)
{
var dt = DbOperations.LoadTable(TableName, string.Format("Select * FROM Units where UnitsName='{0}'", name));
if (dt == null || dt.Rows.Count == 0)
return null;
var res = DataRowToEntity(dt.Rows[0]);
return res;
}
public DataTable GetDetailedSeriesTable()
{
string sql = DetailedSeriesSQLQuery();
sql += " ORDER BY VariableName, SiteName";
var table = DbOperations.LoadTable("SeriesListTable", sql);
return(table);
}
public DataTable GetSeriesTable(string seriesDataFilter)
{
string sql = DetailedSeriesSQLQuery() +
" WHERE " + seriesDataFilter;
var table = DbOperations.LoadTable("SeriesListTable", sql);
return(table);
}
public DataTable GetTableForEditView(long seriesID)
{
var query = "SELECT ValueID, SeriesID, DataValue, ValueAccuracy, LocalDateTime, UTCOffset, " +
"DateTimeUTC, QualifierCode, OffsetValue, OffsetTypeID, CensorCode, SampleID, " +
"FileID, 0 as Other FROM DataValues AS d LEFT JOIN Qualifiers AS q ON (d.QualifierID = q.QualifierID) " +
"WHERE SeriesID = " + seriesID;
var table = DbOperations.LoadTable(TableName, query);
return(table);
}
public DataTable GetTableForExportFromTimeSeriesPlot(long seriesID)
{
var query =
"SELECT ds.SeriesID, s.SiteName, v.VariableName, dv.DataValue, dv.LocalDateTime, U.UnitsName, S.Citation " +
"FROM DataSeries ds, Sites s, Variables v, DataValues dv, Units U, Sources S " +
"WHERE v.VariableID = ds.VariableID AND s.SiteID = ds.SiteID AND dv.SeriesID = ds.SeriesID AND U.UnitsID = v.VariableUnitsID AND ds.SeriesID = " +
seriesID + " AND S.SourceID = ds.SourceID ";
return(DbOperations.LoadTable(TableName, query));
}
/// <summary>
/// Delete a theme - a background worker and progress bar is used
/// </summary>
/// <param name="themeID">The themeID (this needs to be a valid ID)</param>
/// <param name="worker">The background worker component</param>
/// <param name="e">The arguments for background worker</param>
/// <returns></returns>
public bool DeleteTheme(int themeID, BackgroundWorker worker, DoWorkEventArgs e)
{
string sqlTheme = "SELECT SeriesID FROM DataThemes where ThemeID = " + themeID;
DataTable tblSeries = DbOperations.LoadTable("tblSeries", sqlTheme);
int numSeries = tblSeries.Rows.Count;
int count = 0;
if (numSeries == 0)
{
return(false);
}
foreach (DataRow seriesRow in tblSeries.Rows)
{
if (worker != null)
{
//check cancellation
if (e != null && worker.CancellationPending)
{
e.Cancel = true;
return(false);
}
}
int seriesID = Convert.ToInt32(seriesRow["SeriesID"]);
var seriesRepository = RepositoryFactory.Instance.Get <IDataSeriesRepository>();
seriesRepository.DeleteSeries(seriesID);
//progress report
count++;
if (worker != null && worker.WorkerReportsProgress)
{
var percent = (int)(((float)count / (float)numSeries) * 100);
var userState = "Deleting series " + count + " of " + numSeries + "...";
worker.ReportProgress(percent, userState);
}
}
//delete the actual theme
string sqlDeleteTheme = "DELETE FROM DataThemeDescriptions WHERE ThemeID = " + themeID;
try
{
DbOperations.ExecuteNonQuery(sqlDeleteTheme);
e.Result = "Theme deleted successfully";
}
catch { };
return(true);
}
public Qualifier FindByCode(string qualifierCode)
{
var res = DbOperations.LoadTable(string.Format("select * from {0} where QualifierCode='{1}'", TableName,
qualifierCode));
if (res.Rows.Count == 0)
{
return(null);
}
return(DataRowToEntity(res.Rows[0]));
}
public DataTable GetUnitSiteVarForFirstSeries(long seriesID)
{
var sqlQuery = string.Format("SELECT UnitsName, SiteName, VariableName FROM DataSeries " +
"INNER JOIN Variables ON Variables.VariableID = DataSeries.VariableID " +
"INNER JOIN Units ON Variables.VariableUnitsID = Units.UnitsID " +
"INNER JOIN Sites ON Sites.SiteID = DataSeries.SiteID WHERE SeriesID = {0} limit 1",
seriesID);
var seriesNameTable = DbOperations.LoadTable("table", sqlQuery);
return(seriesNameTable);
}
public T GetByKey(object key)
{
var table = DbOperations.LoadTable(TableName,
string.Format("select * from {0} where {1}={2} LIMIT 1", TableName, PrimaryKeyName, key));
if (table.Rows.Count == 0)
{
return(default(T));
}
return(DataRowToEntity(table.Rows[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 UnitConversion GetConversion(Unit unitA, Unit unitB)
{
var table = DbOperations.LoadTable(
string.Format("select * from UnitConversions where FromUnitsID = {0} and ToUnitsID = {1}",
unitA.Id, unitB.Id));
if (table.Rows.Count == 0)
{
return(null);
}
return(DataRowToEntity(table.Rows[0]));
}
public DataTable GetTableForAllFieldsInSequence(IList <int> seriesIDs, int valuesPerPage, int currentPage)
{
var whereClause = GetWhereClauseForIds(seriesIDs);
var dataQuery =
"SELECT ValueID, SeriesID, DataValue, LocalDateTime, UTCOffset, CensorCode FROM DataValues WHERE " +
whereClause;
var limitQuery = string.Format("{0} limit {1} offset {2}", dataQuery, valuesPerPage,
currentPage * valuesPerPage);
var table = DbOperations.LoadTable(limitQuery);
return(table);
}
public DataTable GetTableForGraphView(long seriesID, double nodatavalue, DateTime startDate, DateTime endDate)
{
var strStartDate = startDate.ToString("yyyy-MM-dd HH:mm:ss");
var strEndDate = endDate.ToString("yyyy-MM-dd HH:mm:ss");
var strNoDataValue = nodatavalue.ToString(CultureInfo.InvariantCulture);
var query =
"SELECT DataValue, LocalDateTime, CensorCode, strftime('%m', LocalDateTime) as DateMonth, strftime('%Y', LocalDateTime) as DateYear FROM DataValues WHERE (SeriesID = " +
+seriesID + ") AND (DataValue <> " + strNoDataValue + ") AND (LocalDateTime between '" + strStartDate +
"' AND '" + strEndDate + "') ORDER BY LocalDateTime";
var table = DbOperations.LoadTable("DataValues", query);
return(table);
}
public IList <Series> GetAllSeriesForSite(Site mySite)
{
if (mySite.Id <= 0)
{
throw new ArgumentException("The site must have a valid ID");
}
Contract.EndContractBlock();
string sqlQuery = DetailedSeriesSQLQuery();
sqlQuery += string.Format(" WHERE DataSeries.SiteID = {0}", mySite.Id);
DataTable tbl = DbOperations.LoadTable(sqlQuery);
return(SeriesListFromTable(tbl));
}
public DataTable GetTableForAllFieldsInSequence(IList <int> seriesIDs, int valuesPerPage, int currentPage)
{
var whereClause = GetWhereClauseForIds(seriesIDs);
var dataQuery =
"SELECT ValueID, SeriesID, DataValue, LocalDateTime, UTCOffset, CensorCode, OffsetValue, OT.OffsetDescription, Un.UnitsAbbreviation as OffsetUnits FROM DataValues DV " +
"LEFT JOIN OffsetTypes OT on DV.OffsetTypeId = OT.OffsetTypeId " +
"LEFT JOIN Units Un on Un.UnitsID = OT.OffsetUnitsId " +
"WHERE " +
whereClause;
var limitQuery = string.Format("{0} limit {1} offset {2}", dataQuery, valuesPerPage,
currentPage * valuesPerPage);
var table = DbOperations.LoadTable(limitQuery);
return(table);
}
public DataTable GetTableForExport(long seriesID, double?noDataValue = null, string dateColumn = null, DateTime?firstDate = null, DateTime?lastDate = null)
{
var sql =
"SELECT ds.SeriesID, s.SiteName, v.VariableName, dv.LocalDateTime, dv.DataValue, U1.UnitsName As VarUnits, v.DataType, s.SiteID, s.SiteCode, v.VariableID, v.VariableCode, " +
"S.Organization, S.SourceDescription, S.SourceLink, S.Citation, v.ValueType, v.TimeSupport, U2.UnitsName As TimeUnits, v.IsRegular, v.NoDataValue, " +
"dv.UTCOffset, dv.DateTimeUTC, s.Latitude, s.Longitude, dv.ValueAccuracy, dv.CensorCode, m.MethodDescription, q.QualityControlLevelCode, v.SampleMedium, v.GeneralCategory, " +
"OffsetValue, OT.OffsetDescription, Un.UnitsAbbreviation as OffsetUnits, QL.QualifierCode " +
"FROM DataSeries ds, Sites s, Variables v, DataValues dv, Units U1, Units U2, Methods m, QualityControlLevels q, Sources S " +
"LEFT JOIN OffsetTypes OT on DV.OffsetTypeId = OT.OffsetTypeId " +
"LEFT JOIN Units Un on Un.UnitsID = OT.OffsetUnitsId " +
"LEFT JOIN Qualifiers QL on QL.QualifierID = dv.QualifierID " +
"WHERE v.VariableID = ds.VariableID " +
"AND s.SiteID = ds.SiteID " +
"AND m.MethodID = ds.MethodID " +
"AND q.QualityControlLevelID = ds.QualityControlLevelID " +
"AND S.SourceID = ds.SourceID " +
"AND dv.SeriesID = ds.SeriesID " +
"AND U1.UnitsID = v.VariableUnitsID " +
"AND U2.UnitsID = v.TimeUnitsID " +
"AND ds.SeriesID = " + seriesID;
if (noDataValue.HasValue)
{
sql += " AND dv.DataValue != " + noDataValue;
}
var cmd = DbOperations.CreateCommand(sql);
// Append date range filter
if (!string.IsNullOrEmpty(dateColumn) &&
firstDate.HasValue && lastDate.HasValue)
{
cmd.CommandText += string.Format(" AND ({0} >= @p1 and {0} <= @p2)", dateColumn);
var startDateParameter = DbOperations.AddParameter(cmd, "@p1", DbType.DateTime);
var endDateParemater = DbOperations.AddParameter(cmd, "@p2", DbType.DateTime);
startDateParameter.Value = firstDate.Value;
endDateParemater.Value = lastDate.Value;
}
var tbl = DbOperations.LoadTable("values", cmd);
return(tbl);
}
public Method GetMethod(int methodID)
{
var dt = DbOperations.LoadTable(string.Format("select MethodID, MethodDescription, MethodLink from Methods where MethodID = {0}", methodID));
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
var methodRow = dt.Rows[0];
return(new Method
{
Id = Convert.ToInt32(methodRow["MethodID"]),
Code = Convert.ToInt32(methodRow["MethodID"]),
Description = Convert.ToString(methodRow["MethodDescription"]),
Link = Convert.ToString(methodRow["MethodLink"]),
});
}
public int InsertNewSeries(long sourceSeriesID, long variableID, long methodID, long qualityControlLevelID)
{
var newSeriesID = DbOperations.GetNextID("DataSeries", "SeriesID");
var dt = DbOperations.LoadTable("DataSeries", "SELECT * FROM DataSeries WHERE SeriesID = " + sourceSeriesID);
var row = dt.Rows[0];
//Making the INSERT SQL string for the new data series
var sqlString = new StringBuilder();
sqlString.Append("INSERT INTO DataSeries(SeriesID, SiteID, VariableID, IsCategorical, MethodID, SourceID, ");
sqlString.Append("QualityControlLevelID, BeginDateTime, EndDateTime, BeginDateTimeUTC, EndDateTimeUTC, ");
sqlString.Append("ValueCount, CreationDateTime, Subscribed, UpdateDateTime, LastcheckedDateTime) Values (");
//SeriesID value
sqlString.Append(newSeriesID + ", ");
//SiteID value
sqlString.Append(Convert.ToInt64(row[1]) + ", ");
//VariableID values
sqlString.Append(variableID + ", ");
//IsCategorical value
sqlString.Append(row[3].ToString() == "True" ? "1, " : "0, ");
//MethodID value
sqlString.Append(methodID + ", ");
//SourceID value
sqlString.Append(Convert.ToInt64(row[5]) + ", ");
//QualityControlLevelID value
sqlString.Append(qualityControlLevelID + ", ");
//BeginDateTime, EndDateTime, BeginDateTimeUTC and EndDateTimeUTC values
for (int i = 7; i <= 10; i++)
{
var tempstring = Convert.ToDateTime(row[i]).ToString("yyyy-MM-dd HH:mm:ss");
sqlString.Append("'" + tempstring + "', ");
}
var todaystring = DateTime.Today.ToString("yyyy-MM-dd HH:mm:ss");
//ValueCount, CreationDateTime, Subscribed, UpdateDateTime and LastcheckedDateTime values
sqlString.Append(row[11] + ", '" + todaystring + "', 0, '" + todaystring + "','" + todaystring +
"')");
//Execute the SQL string
DbOperations.ExecuteNonQuery(sqlString.ToString());
return(newSeriesID);
}
public IList <Site> GetSitesWithBothVariables(Variable variable1, Variable variable2)
{
if (variable1.Id <= 0)
{
throw new ArgumentException("variable1 must have a valid ID");
}
if (variable2.Id <= 0)
{
throw new ArgumentException("variable2 must have a valid ID");
}
string sqlQuery = String.Format("select s1.SeriesID as 'SeriesID1', s2.SeriesID as 'SeriesID2', " +
"site.SiteID, site.SiteName, site.SiteCode, site.Latitude, site.Longitude " +
"FROM DataSeries s1 INNER JOIN DataSeries s2 ON s1.SiteID = s2.SiteID " +
"INNER JOIN Sites site ON s1.SiteID = site.SiteID " +
"WHERE s1.VariableID = {0} AND s2.VariableID = {1}", variable1.Id, variable2.Id);
DataTable tbl = DbOperations.LoadTable(sqlQuery);
List <Site> siteList = new List <Site>();
foreach (DataRow r in tbl.Rows)
{
Site s = new Site();
s.Id = (long)r["SiteID"];
s.Code = (string)r["SiteCode"];
s.Latitude = (double)r["Latitude"];
s.Longitude = (double)r["Longitude"];
s.Name = (string)r["SiteName"];
Series s1 = new Series(s, variable1, Method.Unknown, QualityControlLevel.Unknown, Source.Unknown);
s1.Id = (long)r["SeriesID1"];
s.AddDataSeries(s1);
Series s2 = new Series(s, variable2, Method.Unknown, QualityControlLevel.Unknown, Source.Unknown);
s2.Id = (long)r["SeriesID2"];
s.AddDataSeries(s2);
siteList.Add(s);
}
return(siteList);
}
public DataTable GetTableForJustValuesInParallel(IList <int> seriesIDs, int valuesPerPage, int currentPage)
{
/*
* Example of builded query:
*
* select
* A.LocalDateTime as DateTime,
* (select DV1.DataValue from DataValues DV1 where DV1.LocalDateTime = A.LocalDateTime and DV1.seriesId = 1 limit 1) as D1,
* (select DV2.DataValue from DataValues DV2 where DV2.LocalDateTime = A.LocalDateTime and DV2.seriesId = 2 limit 1) as D2
* from
* (select distinct LocalDateTime from DataValues where seriesId in (1,2)) A
* order by LocalDateTime
*
*/
var whereClause = GetWhereClauseForIds(seriesIDs);
var dataQueryBuilder = new StringBuilder();
dataQueryBuilder.Append("select A.LocalDateTime as DateTime");
foreach (var id in seriesIDs)
{
dataQueryBuilder.AppendFormat(
", (select DV{0}.DataValue from DataValues DV{0} where DV{0}.LocalDateTime = A.LocalDateTime and DV{0}.seriesId = {0} limit 1) as D{0}",
id);
}
dataQueryBuilder.AppendFormat(" from (select distinct LocalDateTime from DataValues where {0}) A",
whereClause);
dataQueryBuilder.Append(" order by LocalDateTime");
var dataQuery = dataQueryBuilder.ToString();
var table = DbOperations.LoadTable(string.Format("{0} limit {1} offset {2}", dataQuery, valuesPerPage, currentPage * valuesPerPage));
return(table);
}
/// <summary>
/// Delete a theme - a background worker and progress bar is used
/// </summary>
/// <param name="themeID">The themeID (this needs to be a valid ID)</param>
/// <param name="worker">The background worker component</param>
/// <returns>True - on success, otherwise false.</returns>
public bool DeleteTheme(long themeID, BackgroundWorker worker = null)
{
var sqlTheme = "SELECT SeriesID FROM DataThemes where ThemeID = " + themeID;
var sqlDeleteTheme = "DELETE FROM DataThemeDescriptions WHERE ThemeID = " + themeID;
var sqlDeleteTheme2 = "DELETE FROM DataThemes WHERE ThemeID = " + themeID;
var tblSeries = DbOperations.LoadTable("tblSeries", sqlTheme);
var seriesRepository = RepositoryFactory.Instance.Get <IDataSeriesRepository>();
for (var i = 0; i < tblSeries.Rows.Count; i++)
{
// Check cancellation
if (worker != null && worker.CancellationPending)
{
return(false);
}
var seriesRow = tblSeries.Rows[i];
var seriesID = Convert.ToInt32(seriesRow["SeriesID"]);
seriesRepository.DeleteSeries(seriesID, themeID);
// Progress report
if (worker != null && worker.WorkerReportsProgress)
{
var percent = (int)(((i + 1) / (float)tblSeries.Rows.Count) * 100);
var userState = "Deleting series " + (i + 1) + " of " + tblSeries.Rows.Count + "...";
worker.ReportProgress(percent, userState);
}
}
// Delete the actual theme
DbOperations.ExecuteNonQuery(sqlDeleteTheme2);
DbOperations.ExecuteNonQuery(sqlDeleteTheme);
return(true);
}
public DataTable AsDataTable()
{
var table = DbOperations.LoadTable(TableName, string.Format("Select * from {0}", TableName));
return(table);
}
/// <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
{
try
{
File.Delete(originalDbPath);
}
catch (IOException ex)
{
Trace.TraceError("Unable to delete temp file: {0}. Reason: {1}", originalDbPath, ex.Message);
}
}
}
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);
}