public static void StationData(List <Station> newStations) { SQLiteConnection db = DatabaseCon.GetConnection(); db.Open(); SQLiteTransaction trans = db.BeginTransaction(); try { for (int i = 0; i < newStations.Count; i++) { string adddataQuery = $"INSERT INTO Stations (Name, Identifier, Lattitude, Longitude) VALUES ('{newStations[i].Name}', {newStations[i].Identifier}, {newStations[i].Lattitude}, {newStations[i].Longitude})"; db.Execute(adddataQuery, transaction: trans); //trans.Commit(); } Logging.Log("Stations added to the database"); trans.Commit(); } catch (Exception ex) { Logging.LogEr("Station insertion Error", ex.Message); trans.Rollback(); } }
public static int CheckTableEmpty(string TableName) { string checkempty = $"SELECT COUNT (*) FROM {TableName}"; SQLiteConnection db = DatabaseCon.GetConnection(); var Count = db.Query <int>(checkempty); db.Close(); return(Count.First()); }
public static long QueryLastReading() { SQLiteConnection db = DatabaseCon.GetConnection(); db.Open(); string querylast = "SELECT ReadingTimeIdent FROM Brisbane ORDER BY ReadingTimeIdent DESC LIMIT 1"; long steve = db.QuerySingle <long>(querylast); return(steve); }
//TODO Method to read in Suburb lat and lang from .csv file // filtering by QLD to suit the station's only being QLD stations //TODO Method to calculate the difference between each suburb lat and long // and compare to each stations lat and long // find the three closest stations based on lat and long diff //TODO Method to present the user a selection of the 3 closest stations // using this selection to build the first reading table // maybe even allowing for multiple picks, up to 3, creating 3 tables //TODO Set up the saving of these stations names and entered postcode // in the App.Config file // can use to check that the database contains matching tables //TODO Create a basic GUI for entering a postcode // GUI should allow for some basic testing queries // and testing of some graphing //TODO investigate htmlscraping vs using the JSON files for single suburb databases. public static void BuildTables() { SQLiteConnection db = DatabaseCon.GetConnection(); const string DEFAULT_SCHEMA_TABLES = "CREATE TABLE 'Brisbane' ( `ReadingID` INTEGER PRIMARY KEY AUTOINCREMENT, `StationID` INTEGER, `StationName` TEXT, 'ReadingTimeIdent' INTEGER, 'ReadingYear' INTEGER, 'ReadingMonth' INTEGER, 'ReadingDay' INTEGER, 'ReadingTime' NUMERIC, `ApparentTemperature` NUMERIC, `DeltaT` NUMERIC, `WindGustKmh` INTEGER, `WindGustKt` INTEGER, `ActualTemperature` NUMERIC, `DewPoint` NUMERIC, `PressureHpa` NUMERIC, `RainFallmm` TEXT, `RelativeHumidity` INTEGER, `BasicForecast` TEXT, `WindDirection` TEXT, `WindSpeedKmh` INTEGER, `WindSpeedKts` INTEGER)"; const string StationTable = "CREATE TABLE 'Stations' ( `Name` TEXT NOT NULL UNIQUE, `Identifier` INTEGER PRIMARY KEY, `Lattitude` NUMERIC, `Longitude` NUMERIC)"; // , PRIMARY KEY(`Identifier`)"; const string SuburbTable = "CREATE TABLE 'Suburb' ( 'SubID' INTEGER PRIMARY KEY AUTOINCREMENT, 'Name' TEXT NOT NULL, 'PostCode' INTEGER NOT NULL, 'Lattitude' NUMERIC, 'Longitude' NUMERIC)"; db.Execute(DEFAULT_SCHEMA_TABLES); db.Execute(StationTable); db.Execute(SuburbTable); }
//public static void BuildStations(List<Station> newStations) //{ // SQLiteConnection db = DatabaseCon.GetConnection(); // db.Open(); // SQLiteTransaction trans = db.BeginTransaction(); // try // { // for (int i = 0; i < newStations.Count; i++) // { // string adddataQuery = $"INSERT INTO Stations (Name, Identifier, Lattitude, Longitude) VALUES ('{newStations[i].Name}', {newStations[i].Identifier}, 1, 1)"; // db.Execute(adddataQuery, transaction: trans); // //trans.Commit(); // } // trans.Commit(); // Logging.Log("Stations added to the database"); // } // catch(Exception ex) // { // Logging.LogEr("Station insertion Error", ex.Message); // trans.Rollback(); // } //} public static void BuildReadings(List <ReadingDatav2> Buildlist) { SQLiteConnection db = DatabaseCon.GetConnection(); db.Open(); SQLiteTransaction trans = db.BeginTransaction(); try { long LastDate; if (Database.CheckTableEmpty("Brisbane") == 0) { LastDate = 0; } else { LastDate = Database.QueryLastReading(); } int count = 0; for (int i = Buildlist.Count - 1; i > 0; i--) { ReadingDatav2 newData = new ReadingDatav2(); newData = Buildlist[i]; if (newData.ReadingTimeIdent > LastDate || LastDate == 0) { string insertLine = $"INSERT INTO Brisbane (StationID, StationName, ReadingTimeIdent, ReadingYear, ReadingMonth, ReadingDay, ReadingTime, ApparentTemperature, DeltaT, WindGustKmh, WindGustKt, ActualTemperature, DewPoint, PressureHpa, RainFallmm, RelativeHumidity, BasicForecast, WindDirection, WindSpeedKmh, WindSpeedKts)" + $" VALUES ({newData.StationID}, '{newData.StationName}', {newData.ReadingTimeIdent}, {newData.ReadingYear}, {newData.ReadingMonth}, {newData.ReadingDay}, {newData.ReadingTime}, {newData.ApparentTemperature}, {newData.DeltaT}, {newData.WindGustKmh}, {newData.WindGustKt}, {newData.ActualTemperature}, {newData.DewPoint}, {newData.PressureHpa}, '{newData.RainFallmm}', {newData.RelativeHumidity}, '{newData.BasicForecast}', '{newData.WindDirection}', {newData.WindSpeedKmh}, {newData.WindSpeedKt})"; db.Execute(insertLine); count++; } } trans.Commit(); if (count == 0) { Console.WriteLine("The database is up to date"); Logging.Log("The Database is up to date"); } else { Console.WriteLine(count + " Entries added to the database"); Logging.Log(count + " Entries added to the database"); } Console.Read(); } catch (Exception ex) { Console.WriteLine(ex.Message); Logging.LogEr("Reading Insertion Error", ex.Message); trans.Rollback(); } }
public static void CheckTableExists() { SQLiteConnection db = DatabaseCon.GetConnection(); string TableExists = "SELECT COUNT ('Brisbane') FROM sqlite_sequence"; var Count = db.Query <int>(TableExists); if (Count.First() == 0) { string makeNewTable = $"CREATE TABLE 'Brisbane'( `ReadingID` INTEGER PRIMARY KEY AUTOINCREMENT, `StationID` INTEGER, `StationName` TEXT, 'ReadingTimeIdent' INTEGER, 'ReadingYear' INTEGER, 'ReadingMonth' INTEGER, 'ReadingDay' INTEGER, 'ReadingTime' INTEGER, `ApparentTemperature` NUMERIC, `DeltaT` NUMERIC, `WindGustKmh` INTEGER, `WindGustKt` INTEGER, `ActualTemperature` NUMERIC, `DewPoint` NUMERIC, `PressureHpa` NUMERIC, `RainFallmm` NUMERIC, `RelativeHumidity` INTEGER, `BasicForecast` TEXT, `WindDirection` TEXT, `WindSpeedKmh` INTEGER, `WindSpeedKts` INTEGER)"; db.Execute(makeNewTable); Logging.Log("'Brisbane' Table created"); } }
public static bool CheckStationTableExists() { SQLiteConnection db = DatabaseCon.GetConnection(); string TableExists = "SELECT COUNT ('Name') FROM Stations"; var Count = db.Query <int>(TableExists); if (Count.First() == 0) { return(true); } else { return(false); } }
public static void LoadSuburbs(List <Suburb> SubList) { SQLiteConnection db = DatabaseCon.GetConnection(); db.Open(); SQLiteTransaction trans = db.BeginTransaction(); try { for (int i = 0; i < SubList.Count; i++) { string adddataQuery = $"INSERT INTO Suburb (Name, PostCode, Lattitude, Longitude) VALUES (@Name, @PostCode, @Lattitude, @Longitude)"; db.Execute(adddataQuery, SubList[i], trans); //trans.Commit(); } trans.Commit(); Logging.Log("Suburbs added to the database"); } catch (Exception ex) { Logging.LogEr("Suburb insertion Error", ex.Message); trans.Rollback(); } }