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);
}
}