public IList <FavoriteParks> GetSurveyResults()
{
IList <FavoriteParks> parks = new List <FavoriteParks>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select Count(*) as count, sr.parkCode, parkName FROM survey_result sr JOIN park p on p.parkCode = sr.parkCode GROUP by sr.parkCode, parkName ORDER BY count desc, parkName;", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
FavoriteParks park = new FavoriteParks();
park.Name = Convert.ToString(reader["parkName"]);
park.Code = Convert.ToString(reader["parkCode"]);
park.Count = Convert.ToInt32(reader["count"]);
parks.Add(park);
}
}
}
catch (Exception ex)
{
}
return(parks);
}
/// <summary>
/// Returns a list of Favorite parks that are ordered by the most votes received
/// </summary>
/// <returns>List of Favorite parks to be returned to the View</returns>
public List <FavoriteParks> GetSurveys()
{
List <FavoriteParks> parkList = new List <FavoriteParks>();
try
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
//SQL string to select all the parks that have received a Favorite Park vote ordered by the most favorite park votes
string SQL_GetFavoriteParks = "select park.parkName, park.state, park.parkCode, park.inspirationalQuote, park.inspirationalQuoteSource, " +
"count(survey_result.surveyId) as surveycount " +
"from survey_result join park on park.parkCode = " +
"survey_result.parkCode group by park.parkName, park.state, park.parkCode, " +
"park.inspirationalQuote, park.inspirationalQuoteSource order by surveycount desc, park.parkName";
cmd.CommandText = SQL_GetFavoriteParks;
cmd.Connection = conn;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//goes through each row returned from the database and adds it to a Favorite Parks object
FavoriteParks park = new FavoriteParks
{
ParkCode = Convert.ToString(reader["parkCode"]),
ParkName = Convert.ToString(reader["parkName"]),
SurveyCount = Convert.ToInt32(reader["surveycount"]),
InspirationalQuote = Convert.ToString(reader["inspirationalQuote"]),
QuoteSource = Convert.ToString(reader["inspirationalQuoteSource"]),
State = Convert.ToString(reader["state"])
};
parkList.Add(park);
}
}
}
//if the SQL query fails
catch (SqlException ex)
{
throw;
}
//returns the list of Favorite Parks
return(parkList);
}
/// <summary>
/// Gets a list of all surveys for each park
/// </summary>
/// <returns>list of surveys</returns>
public IList <FavoriteParks> GetFavoriteParks()
{
IList <FavoriteParks> surveys = new List <FavoriteParks>();
using (SqlConnection conn = new SqlConnection(_connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(_favoriteParks, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var f = new FavoriteParks();
f.NumberOfSurveys = Convert.ToInt32(reader["fav"]);
f.ParkCode = Convert.ToString(reader["parkCode"]);
f.ParkName = Convert.ToString(reader["parkName"]);
surveys.Add(f);
}
}
return(surveys);
}
