public int storeUncommonEvent(UncommonEvents userInfo) { int linesReturned = -1; string query = string.Format(@" INSERT INTO {0} (CityId, Type, Description, UserName, Temperature, DateTime) VALUES (@cityId, @type, @description, @username, @temperature, @dateTime)", DatabaseTableConstant.tableUncommonEvents); if (userInfo.Username == null || userInfo.Username.Trim().Count() == 0) { return(-1); } int cityId = fetchCityIdFromName(userInfo.CityName); if (cityId == -1) { return(-1); } using (connection) { SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("cityId", cityId); command.Parameters.AddWithValue("type", userInfo.Type); command.Parameters.AddWithValue("description", userInfo.Description); command.Parameters.AddWithValue("username", userInfo.Username); command.Parameters.AddWithValue("dateTime", DateTime.Now); command.Parameters.AddWithValue("temperature", userInfo.Temperature); try { connection.Open(); linesReturned = command.ExecuteNonQuery(); Debug.WriteLine("Commands executed! Total rows affected are " + linesReturned); } catch (Exception e) { Debug.WriteLine("Erro ao escrever na BD: " + e.ToString()); } } return(linesReturned); }
public List <UncommonEvents> getUncommonEventsBetweenDates(string cityName, DateTime startDate, DateTime endDate) { string query; bool allcity = false; int cityId = -1; if (startDate == null || endDate == null || startDate > endDate) { return(null); } if (cityName == null) { query = string.Format(@" SELECT City_Name, Type, DateTime, Temperature, UserName, Description FROM {0} uevt JOIN {1} ct ON uevt.CityId = ct.Id WHERE DateTime >= @startDate AND DateTime <= @endDate ORDER BY 3 DESC", DatabaseTableConstant.tableUncommonEvents, DatabaseTableConstant.tableCity); allcity = true; } else { query = string.Format(@" SELECT City_Name, Type, DateTime, Temperature, UserName, Description FROM {0} uevt JOIN {1} ct ON uevt.CityId = ct.Id WHERE DateTime >= @startDate AND DateTime <= @endDate AND CityId = @cityId ORDER BY 3 DESC", DatabaseTableConstant.tableUncommonEvents, DatabaseTableConstant.tableCity); cityId = fetchCityIdFromName(cityName); if (cityId == -1) { return(null); } } List <UncommonEvents> events = new List <UncommonEvents>(); using (connection) { SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("startDate", startDate.ToString("yyyy-MM-dd 00:00:00")); command.Parameters.AddWithValue("endDate", endDate.ToString("yyyy-MM-dd 23:59:59")); if (!allcity) { command.Parameters.AddWithValue("cityId", cityId); } try { connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { UncommonEvents event_ = new UncommonEvents { CityName = reader["City_Name"] + "", Type = reader["Type"] + "", DateTime = (DateTime)reader["DateTime"], Temperature = float.Parse(reader["Temperature"] + ""), Username = reader["UserName"] + "", Description = reader["Description"] + "" }; events.Add(event_); } } } catch (Exception e) { Debug.WriteLine((e.ToString())); } return(events); } }