public USState GetStateForName(string name)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand("select * from States where Name = @StateName", conn))
{
command.Parameters.AddWithValue("@StateName", name);
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
USState state = new USState();
state.ID = reader.GetInt64(0);
state.Name = reader.GetString(1);
return state;
}
}
}
throw new ArgumentException(string.Format("State name {0} is not recognized", name));
}
public ICollection<DroughtMonitorWeek> FindBy(USState state, DateTime? week = null, int weeksPrevious = 0)
{
week = DroughtMonitorWeek.ConvertDateToTuesday(week.Value);
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(null, conn))
{
if (week == null && weeksPrevious == 0)
{
command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, State_ID from StateDMValues where State_ID = @state order by PublishedDate, State_ID");
command.Parameters.AddWithValue("@state", state.ID);
}
else if (week == null && weeksPrevious != 0)
{
command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, State_ID from StateDMValues where State_ID = @state and PublishedDate >= @rangestart order by PublishedDate, State_ID");
command.Parameters.AddWithValue("@state", state.ID);
command.Parameters.AddWithValue("@rangestart", DateTime.Now.AddDays(7 * (0 - weeksPrevious)).ToString("yyyy/MM/dd"));
}
else if (week != null)
{
command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, State_ID from StateDMValues where State_ID = @state and PublishedDate >= @rangestart and PublishedDate <= @rangeend order by PublishedDate, State_ID");
command.Parameters.AddWithValue("@state", state.ID);
command.Parameters.AddWithValue("@rangestart", week.Value.AddDays(7 * (0 - weeksPrevious)).ToString("yyyy/MM/dd"));
command.Parameters.AddWithValue("@rangeend", week.Value.ToString("yyyy/MM/dd"));
}
return ProcessQuery(command, DMDataType.STATE);
}
}
}
