public IList <SurveyResultVM> GetAllParkNamesWithSurvey() { List <SurveyResultVM> parks = new List <SurveyResultVM>(); try { // Create a new connection object using (SqlConnection conn = new SqlConnection(connectionString)) { // Open the connection conn.Open(); string sql = @"select survey_result.parkcode, parkname, count(survey_result.parkcode) as Count from survey_result join park on park.parkcode = survey_result.parkcode group by survey_result.parkCode, parkname order by count desc, parkName"; SqlCommand cmd = new SqlCommand(sql, conn); // Execute the command SqlDataReader reader = cmd.ExecuteReader(); // Loop through each row while (reader.Read()) { SurveyResultVM vm = RowToObject1(reader); parks.Add(vm); } } } catch (SqlException) { throw; } return(parks); }
public ActionResult SurveyResult() { SurveyResultVM model = new SurveyResultVM(); model.ListAllSurveys = dal.GetAllSurveys(); model.TotalSurveys = dal.SurveyCount(); return(View("SurveyResult", model)); }
private SurveyResultVM MapRowToSurvey(SqlDataReader reader) { SurveyResultVM surveyResult = new SurveyResultVM(); surveyResult.SurveyCount = Convert.ToInt32(reader["tally"]); surveyResult.ParkCode = Convert.ToString(reader["parkCode"]); surveyResult.ParkName = Convert.ToString(reader["ParkName"]); return(surveyResult); }
private SurveyResultVM RowToObject1(SqlDataReader reader) { SurveyResultVM vm = new SurveyResultVM() { ParkCode = Convert.ToString(reader["parkcode"]), ParkName = Convert.ToString(reader["parkname"]), CountOfSurveys = Convert.ToInt32(reader["Count"]), }; return(vm); }
public IActionResult Index() { //Get a list of parks for the select list in the view. IList <Park> parks = parkDAO.GetParks(); SurveyResultVM vm = new SurveyResultVM(); vm.Parks = new SelectList(parks, "ParkCode", "ParkName"); //Return the view. return(View(vm)); }
public IList <SurveyResultVM> GetSurveyResultsOrdered() { List <SurveyResultVM> surveyResults = new List <SurveyResultVM>(); try { // Create a new connection object using (SqlConnection conn = new SqlConnection(connectionString)) { // Open the connection conn.Open(); string sql = @"SELECT p.parkName as ParkName, s.parkCode as Code, COUNT(s.parkCode) as NumOfFaves from survey_result s JOIN park p ON p.parkCode = s.parkCode Group BY p.parkName, s.parkCode ORDER BY NumOfFaves DESC"; SqlCommand cmd = new SqlCommand(sql, conn); // Execute the command SqlDataReader rdr = cmd.ExecuteReader(); // Loop through each row while (rdr.Read()) { SurveyResultVM sResult = new SurveyResultVM(); sResult.ParkName = Convert.ToString(rdr["ParkName"]); sResult.ParkCode = Convert.ToString(rdr["Code"]); sResult.NumOfFaves = Convert.ToInt32(rdr["NumOfFaves"]); surveyResults.Add(sResult); } } } catch (SqlException) { throw; } return(surveyResults); }
public IActionResult Index(SurveyResultVM vm) { //If the form is not completely filled out. if (!ModelState.IsValid) { //Get a list of the parks for the selectlist in the view. IList <Park> parks = parkDAO.GetParks(); vm.Parks = new SelectList(parks, "ParkCode", "ParkName"); //return the view with the errors. return(View(vm)); } //If the form is completely filled out, save the survey to the database. SurveyResult survey = vm.Survey; surveyResultDAO.SaveSurvey(survey); //Return the result view. return(RedirectToAction("Results")); }
/// <summary> /// Get survey results from database /// Returns count of votes by park name /// If the votes are tied, it sorts the parks alphabetically /// </summary> public IList <SurveyResultVM> GetSurveyResults() { IList <SurveyResultVM> results = new List <SurveyResultVM>(); try { using (SqlConnection conn = new SqlConnection(connectionString)) { //Open connection conn.Open(); string sql = @"select survey_result.parkCode, parkName, count(survey_result.parkCode) AS votes from survey_result join park on park.parkCode = survey_result.parkCode group by survey_result.parkCode, parkName order by votes desc, parkName asc"; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader reader = cmd.ExecuteReader(); //Loop through each row //All we want are the parkCode (for the image), the parkName and the number of votes per park //Add them to the list of survey results while (reader.Read()) { SurveyResultVM resultVM = new SurveyResultVM(); resultVM.ParkCode = Convert.ToString(reader["parkCode"]); resultVM.ParkName = Convert.ToString(reader["parkName"]); resultVM.Votes = Convert.ToInt32(reader["votes"]); results.Add(resultVM); } } } catch (SqlException ex) { throw ex; } return(results); }