/// <summary> /// Get weather items (forecast) for park by ID /// </summary> /// <param name="parkCode"></param> /// <returns>List of weather items in ascending value (current day = 1, tomorrow = 2, etc)</returns> public IList <ParkWeatherItem> GetWeatherForPark(string parkCode) { List <ParkWeatherItem> weatherItems = new List <ParkWeatherItem>(); using (SqlConnection connection = new SqlConnection(_connectionString)) { connection.Open(); SqlCommand command = new SqlCommand("SELECT * FROM weather " + "WHERE parkCode = @parkCode " + "ORDER BY fiveDayForecastValue ASC;", connection); command.Parameters.AddWithValue("@parkCode", parkCode); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { ParkWeatherItem weatherItem = GetWeatherItemFromReader(reader); weatherItems.Add(weatherItem); } } return(weatherItems); }
/// <summary> /// Add a weather item /// </summary> /// <param name="weatherItem"></param> /// <returns>Bool - true if successfully entered to DB, else false.</returns> public bool AddWeatherItem(ParkWeatherItem weatherItem) { bool result = false; // Create a connection using (SqlConnection conn = new SqlConnection(_connectionString)) { // Open the connection conn.Open(); // Create the command SqlCommand cmd = new SqlCommand("INSERT INTO weather (parkCode, " + "fiveDayForecastValue, " + "low, " + "high, " + "forecast) " + "VALUES (@parkCode, " + "@fiveDayForecastValue, " + "@low, " + "@high, " + "@forecast);", conn); cmd.Parameters.AddWithValue("@parkCode", weatherItem.ParkCode); cmd.Parameters.AddWithValue("@fiveDayForecastValue", weatherItem.Day); cmd.Parameters.AddWithValue("@low", weatherItem.Low); cmd.Parameters.AddWithValue("@high", weatherItem.High); cmd.Parameters.AddWithValue("@forecast", weatherItem.Forecast); // Execute the command int rowsAffected = cmd.ExecuteNonQuery(); result = rowsAffected == 1; } return(result); }
/// <summary> /// Translates values from SqlDataReader to ParkWeatherItem /// </summary> /// <param name="reader"></param> /// <returns>ParkWeatherItem from DB.</returns> private ParkWeatherItem GetWeatherItemFromReader(SqlDataReader reader) { ParkWeatherItem weather = new ParkWeatherItem(); weather.ParkCode = Convert.ToString(reader["parkCode"]); weather.Day = Convert.ToInt32(reader["fiveDayForecastValue"]); weather.Low = Convert.ToInt32(reader["low"]); weather.High = Convert.ToInt32(reader["high"]); weather.Forecast = Convert.ToString(reader["forecast"]); return(weather); }