/// <summary> /// Get some data from SQL table Countries /// </summary> /// <returns>Countries list</returns> public List <Country> GetData() { List <Country> countries = new List <Country>(); try { string sql = "select name, alpha2Code, alpha3Code, capital, region, subregion, population, demonym, area, gini, nativeName, numericCode, cioc from countries"; command = new SQLiteCommand(sql, connection); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { currencyDataService = new CurrencyDataService(); countries.Add (new Country { Name = reader["name"].ToString(), Alpha2Code = reader["alpha2Code"].ToString(), Alpha3Code = reader["alpha3Code"].ToString(), Capital = reader["capital"].ToString(), Region = reader["region"].ToString(), Subregion = reader["subregion"].ToString(), Population = Convert.ToInt64(reader["population"]), Demonym = reader["demonym"].ToString(), Area = Convert.ToDouble(reader["area"]), Gini = Convert.ToDouble(reader["gini"]), NativeName = reader["nativeName"].ToString(), NumericCode = reader["numericCode"].ToString(), Cioc = reader["cioc"].ToString(), Currencies = currencyDataService.GetCurrenciesByCountryCode(reader["alpha3Code"].ToString()), }); } connection.Close(); return(countries); } catch (Exception e) { dialogService.ShowMessage("Error", e.Message); return(null); } }
/// <summary> /// Delete data from SQL table Countries /// </summary> public void DeleteData() { try { string sql = "delete from countries"; command = new SQLiteCommand(sql, connection); command.ExecuteNonQuery(); currencyDataService = new CurrencyDataService(); currencyDataService.DeleteData(); languageDataService = new LanguageDataService(); languageDataService.DeleteData(); regionalBlocsDataService = new RegionalBlocsDataService(); regionalBlocsDataService.DeleteData(); } catch (Exception e) { dialogService.ShowMessage("Error", e.Message); } }
/// <summary> /// Saving data of Countries in SQL with insert command /// </summary> /// <param name="countries"></param> public async Task SaveData(List <Country> countries, IProgress <ProgressReport> progress) { ProgressReport report = new ProgressReport(); byte p = 1; await Task.Run(() => { try { foreach (var country in countries) { string sql = string.Format("insert into countries (name, alpha2Code, alpha3Code, capital, region, subregion, population, demonym, area, gini, nativeName, numericCode, cioc) values (\"{0}\", '{1}', '{2}', \"{3}\", '{4}', '{5}', {6}, '{7}', '{8}', '{9}', \"{10}\", '{11}', '{12}')", country.Name, country.Alpha2Code, country.Alpha3Code, country.Capital, country.Region, country.Subregion, country.Population, country.Demonym, country.Area, country.Gini, country.NativeName, country.NumericCode, country.Cioc); command = new SQLiteCommand(sql, connection); command.ExecuteNonQuery(); currencyDataService = new CurrencyDataService(); currencyDataService.SaveData(country.Currencies, country.Alpha3Code); languageDataService = new LanguageDataService(); languageDataService.SaveData(country.Languages, country.Alpha3Code); regionalBlocsDataService = new RegionalBlocsDataService(); regionalBlocsDataService.SaveData(country.RegionalBlocs, country.Alpha3Code); report.Percentage = Convert.ToByte((p * 100) / countries.Count); progress.Report(report); p++; } connection.Close(); } catch (Exception e) { dialogService.ShowMessage("Error", e.Message); } }); }