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