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