/// <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);
}
});
}