private List <DataResponseDTO> getDataForCountryFromDb(DataRequestDTO dataRequestDTO) { List <DataResponseDTO> result = new List <DataResponseDTO>(); string sqlQuery = buildDataRequestQuery(dataRequestDTO); try { string conString = ConfigurationExtensions.GetConnectionString(_configuration, "Default"); using (SqlConnection connection = new SqlConnection(conString)) { connection.Open(); using (SqlCommand command = new SqlCommand(sqlQuery, connection)) { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { DataResponseDTO tempEntry = new DataResponseDTO { CountryName = reader.GetString(0), Year = Convert.ToInt32(reader.GetString(1)) }; Dictionary <string, int> langsDictionary = new Dictionary <string, int>(); for (int i = 0; i < dataRequestDTO.Languages.Count; i++) { langsDictionary.Add(dataRequestDTO.Languages[i], reader.GetInt32(i + 2)); } ; tempEntry.LanguagesPercent = langsDictionary; result.Add(tempEntry); } } } } } catch (SqlException e) { result = new List <DataResponseDTO>(); } return(result); }
public async Task <IActionResult> GetDataForCountry(DataRequestDTO dataRequestDTO) { if (ModelState.IsValid) { try { var data = await _deliveryService.GetDataForCountry(dataRequestDTO); var payload = JsonSerializer.Serialize(data); return(Ok(payload)); } catch (Exception) { return(BadRequest()); } } ; return(BadRequest()); }
private string buildDataRequestQuery(DataRequestDTO dataRequestDTO) { string countryName = dataRequestDTO.CountryName; string alternateCountryName = getAlternateCountryName(countryName); int[] years = { 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020 }; StringBuilder sb = new StringBuilder(); foreach (var year in years) { sb.Append("SELECT Country,"); sb.Append($"'{year}' as SurveyYear,"); for (int i = 0; i < dataRequestDTO.Languages.Count; i++) { string language = dataRequestDTO.Languages[i]; if (language.Equals("CPlusPlus")) { sb.Append($@"( (SELECT COUNT(*) FROM SurveyResponses{year} WHERE (Country = '{countryName}' OR Country = '{alternateCountryName}') AND LanguagePro{language} = 'C++') * 100 / (SELECT COUNT(*) FROM SurveyResponses{year} WHERE Country = '{countryName}' OR Country = '{alternateCountryName}') ) as '{language}_Percent'" ); } else if (language.Equals("CSharp")) { sb.Append($@"( (SELECT COUNT(*) FROM SurveyResponses{year} WHERE (Country = '{countryName}' OR Country = '{alternateCountryName}') AND LanguagePro{language} = 'C#') * 100 / (SELECT COUNT(*) FROM SurveyResponses{year} WHERE Country = '{countryName}' OR Country = '{alternateCountryName}') ) as '{language}_Percent'" ); } else if (language.Equals("ObjectiveC")) { sb.Append($@"( (SELECT COUNT(*) FROM SurveyResponses{year} WHERE (Country = '{countryName}' OR Country = '{alternateCountryName}') AND LanguagePro{language} = 'Objective-C') * 100 / (SELECT COUNT(*) FROM SurveyResponses{year} WHERE Country = '{countryName}' OR Country = '{alternateCountryName}') ) as '{language}_Percent'" ); } else { sb.Append($@"( (SELECT COUNT(*) FROM SurveyResponses{year} WHERE (Country = '{countryName}' OR Country = '{alternateCountryName}') AND LanguagePro{language} = '{language}') * 100 / (SELECT COUNT(*) FROM SurveyResponses{year} WHERE Country = '{countryName}' OR Country = '{alternateCountryName}') ) as '{language}_Percent' "); } if (i < dataRequestDTO.Languages.Count - 1) { sb.Append(","); } } sb.Append($"FROM SurveyResponses{year} WHERE Country = '{countryName}' OR Country = '{alternateCountryName}'"); if (year != 2020) { sb.Append("UNION "); } } sb.Append("GROUP BY Country ORDER BY SurveyYear"); string dataQuery = sb.ToString(); return(dataQuery); }
public async Task <List <DataResponseDTO> > GetDataForCountry(DataRequestDTO dataRequestDTO) { var data = await Task.Run(() => getDataForCountryFromDb(dataRequestDTO)); return(data); }