public ICollection<DroughtMonitorWeek> FindBy(USCounty county, 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, County_ID from CountyDMValues where County_ID = @county order by PublishedDate, County_ID"); command.Parameters.AddWithValue("@county", county.ID); } else if (week == null && weeksPrevious != 0) { command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, County_ID from CountyDMValues where County_ID = @county and PublishedDate >= @rangestart order by PublishedDate, County_ID"); command.Parameters.AddWithValue("@county", county.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, County_ID from CountyDMValues where County_ID = @county and PublishedDate >= @rangestart and PublishedDate <= @rangeend order by PublishedDate, County_ID"); command.Parameters.AddWithValue("@county", county.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")); } else { throw new Exception("No command was specified"); } return ProcessQuery(command, DMDataType.COUNTY); } } }
public USCounty GetCountyForFips(int fips) { using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); using (SqlCommand command = new SqlCommand("select * from Counties where FIPS = @Fips", conn)) { command.Parameters.AddWithValue("@Fips", fips); SqlDataReader reader = command.ExecuteReader(); if (reader.Read()) { USCounty county = new USCounty(); county.ID = reader.GetInt64(0); county.Fips = reader.GetInt32(1); county.Name = reader.GetString(2); county.State = GetState(reader.GetInt64(3)); return county; } } } throw new ArgumentException(string.Format("Fips code {0} is not recognized", fips)); }