/// <summary>
/// Retrieves a list of countries and their total populations
/// </summary>
/// <returns>A list with the country and population</returns>
public List <CountryPopulation> GetCountryPopulations()
{
List <CountryPopulation> countryPopulations = new List <CountryPopulation>();
IDbManager db = new SqliteDbManager();
DbConnection conn = db.getConnection();
if (conn == null)
{
Console.WriteLine("Failed to get connection");
}
var sqlCommand = conn.CreateCommand();
sqlCommand.CommandText = "SELECT sum(Population) as TotalPopulation, CountryName FROM Country c JOIN State s ON c.CountryId = s.CountryId JOIN City cty ON s.StateId = cty.StateId GROUP BY c.CountryName";
var dataReader = sqlCommand.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dataReader);
foreach (DataRow row in dt.Rows)
{
countryPopulations.Add(new CountryPopulation()
{
CountryName = row["CountryName"].ToString(), Population = Convert.ToInt32(row["TotalPopulation"])
});
}
return(countryPopulations);
}
public static Task <List <Tuple <string, int> > > GetCountryPopulationsFromSQLAsync()
{
IDbManager db = new SqliteDbManager();
DbConnection conn = db.getConnection();
if (conn == null)
{
Console.WriteLine("Failed to get connection");
}
List <Tuple <string, int> > countryPopulationFromSql = new List <Tuple <string, int> >();
// Retrive distince records while joining the country, state, and city tables. Return CountryName and SUM of city.population
string stm = "SELECT DISTINCT country.CountryName, SUM(city.population) AS Population FROM country JOIN state ON state.countryId = country.countryId JOIN city ON city.stateId = state.stateId GROUP BY country.CountryName;";
using var cmd = new SQLiteCommand(stm, (SQLiteConnection)conn);
using SQLiteDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
// Build new tuple items and add to list
string countryName = (string)rdr[0];
int population = Convert.ToInt32(rdr[1]);
Tuple <string, int> item = new Tuple <string, int>(countryName, population);
countryPopulationFromSql.Add(item);
}
rdr.Close();
return(Task.FromResult <List <Tuple <string, int> > >(countryPopulationFromSql));
}
static async Task Main(string[] args)
{
var dbManager = new SqliteDbManager();
var statService = new ConcreteStatService();
List <Tuple <string, int> > serviceResults = await statService.GetCountryPopulationsAsync();
List <Tuple <string, int> > dbResults = await dbManager.GetCountryPopulationsAsync();
List <Tuple <string, int> > results = AccumulateResults.Aggregate(dbResults, serviceResults);
PrintResults.Print(results);
}
static void Main(string[] args)
{
Console.WriteLine("Started");
Console.WriteLine("Getting DB Connection...");
IDbManager db = new SqliteDbManager();
DbConnection conn = db.getConnection();
if (conn == null)
{
Console.WriteLine("Failed to get connection");
}
}
static void Main(string[] args)
{
Console.WriteLine("Started");
Console.WriteLine("Getting DB Connection...");
IDbManager db = new SqliteDbManager();
DbConnection conn = db.getConnection();
if (conn == null)
{
Console.WriteLine("Failed to get connection");
}
StatsUtilityServices helperServices = new StatsUtilityServices();
IStatAggregateService statAggregateService = new StatAggregateServiceImpl();
//Retrieving countries and its populations
Dictionary <string, int> populationResults = statAggregateService.GetCountryPopulations();
helperServices.PrintDictionary(populationResults);
Console.ReadKey();
}
