public List <CampgroundSite> GetTop5SitesByParkId(int parkId) { List <CampgroundSite> campgroundSites = new List <CampgroundSite>(); const string sql = "Select top 5 campground.*, site.* From park join campground " + "on park.park_id = campground.park_id " + "join site " + "on campground.campground_id = site.campground_id " + "where park.park_id = @parkId;"; using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@parkId", parkId); var reader = cmd.ExecuteReader(); while (reader.Read()) { CampgroundSite campgroundSite = new CampgroundSite(); campgroundSite.site = GetSiteFromReader(reader); campgroundSite.campground = GetCampgroundFromReader(reader); campgroundSites.Add(campgroundSite); } } return(campgroundSites); }
public List <CampgroundSite> GetSitesByParkIdAndResevationAvailability(int parkId, DateTime startDate, DateTime endDate) { List <CampgroundSite> campgroundSites = new List <CampgroundSite>(); const string sql = "select top 5 campground.*, site.* from site " + "join campground on site.campground_id = campground.campground_id " + "join park on park.park_id = campground.park_id " + "where park.park_id = @parkId and " + "site_id not in " + "(select site.site_id " + "from site join reservation " + "on site.site_id = reservation.site_id " + "where " + "((reservation.from_date >= @startDate and " + "reservation.from_date <= @endDate) or " + "(reservation.to_date >= @startDate and reservation.to_date <= @endDate))) " + "and campground.open_from_mm <= month(@startDate) and " + "campground.open_to_mm >= month(@endDate);"; using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@parkId", parkId); cmd.Parameters.AddWithValue("@startDate", startDate); cmd.Parameters.AddWithValue("@endDate", endDate); var reader = cmd.ExecuteReader(); while (reader.Read()) { CampgroundSite campgroundSite = new CampgroundSite(); campgroundSite.site = GetSiteFromReader(reader); campgroundSite.campground = GetCampgroundFromReader(reader); campgroundSites.Add(campgroundSite); } } return(campgroundSites); }
public List <CampgroundSite> GetSitesByParkIdAndResevationAvailability(int parkId, DateTime startDate, DateTime endDate, int maxOccupancy, AccessibleOptions accessible, int maxRVLength, UtilitiesAvailble utilities) { List <CampgroundSite> campgroundSites = new List <CampgroundSite>(); bool accessibleClause1 = true; bool accessibleClause2 = true; bool utilityClause1 = true; bool utilityClause2 = true; if (accessible == AccessibleOptions.yes) { accessibleClause1 = true; accessibleClause2 = true; } else if (accessible == AccessibleOptions.no) { accessibleClause1 = false; accessibleClause2 = false; } else if (accessible == AccessibleOptions.either) { accessibleClause1 = true; accessibleClause2 = false; } if (utilities == UtilitiesAvailble.yes) { utilityClause1 = true; utilityClause2 = true; } else if (utilities == UtilitiesAvailble.no) { utilityClause1 = false; utilityClause2 = false; } else if (utilities == UtilitiesAvailble.either) { utilityClause1 = true; utilityClause2 = false; } string sql = "select top 5 campground.*, site.* from site " + "join campground on site.campground_id = campground.campground_id " + "join park on park.park_id = campground.park_id " + "where park.park_id = @parkId and " + "site_id not in " + "(select site.site_id " + "from site join reservation " + "on site.site_id = reservation.site_id " + "where " + "((reservation.from_date >= @startDate and " + "reservation.from_date <= @endDate) or " + "(reservation.to_date >= @startDate and reservation.to_date <= @endDate))) " + "and campground.open_from_mm <= month(@startDate) and " + "campground.open_to_mm >= month(@endDate)" + "and max_occupancy >= @maxOccupancy " + "and (accessible = @accessibleClause1 or accessible = @accessibleClause2) " + "and max_rv_length >= @maxRVLength " + "and (utilities = @accessibleClause1 or utilities = @accessibleClause2);"; using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@parkId", parkId); cmd.Parameters.AddWithValue("@startDate", startDate); cmd.Parameters.AddWithValue("@endDate", endDate); cmd.Parameters.AddWithValue("@maxOccupancy", maxOccupancy); cmd.Parameters.AddWithValue("@maxRVLength", maxRVLength); cmd.Parameters.AddWithValue("@utilityClause1", utilityClause1); cmd.Parameters.AddWithValue("@utilityClause2", utilityClause2); cmd.Parameters.AddWithValue("@accessibleClause1", accessibleClause1); cmd.Parameters.AddWithValue("@accessibleClause2", accessibleClause2); var reader = cmd.ExecuteReader(); while (reader.Read()) { CampgroundSite campgroundSite = new CampgroundSite(); campgroundSite.site = GetSiteFromReader(reader); campgroundSite.campground = GetCampgroundFromReader(reader); campgroundSites.Add(campgroundSite); } } return(campgroundSites); }