public List <Campsite> GetCampgroundSites(int campgroundId, DateTime arriveDate, DateTime departDate)
{
List <Campsite> output = new List <Campsite>();
int arriveMonth = arriveDate.Month;
int departMonth = departDate.Month;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
const string sqlGetCampgroundSites = "select top 5 site.* from site " +
"join campground on site.campground_id = campground.campground_id " +
"where site.campground_id = @campground_id " +
"and site_id not in (select site_id from reservation " +
"where @from_date <= to_date " +
"and @to_date >= from_date);";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sqlGetCampgroundSites;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@campground_id", campgroundId);
cmd.Parameters.AddWithValue("@from_date", arriveDate.ToShortDateString());
cmd.Parameters.AddWithValue("@to_date", departDate.ToShortDateString());
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Campsite site = PopulateSitesFromReader(reader);
output.Add(site);
}
}
return(output);
}
public List <Reservation> GetAllReservations(Campsite site)
{
List <Reservation> allReservations = new List <Reservation>();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
const string sqlAllReservations = "Select * from reservation where site_id = @site_id;";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sqlAllReservations;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@site_id", site.SiteId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Reservation reservation = GetReservationFromReader(reader);
allReservations.Add(reservation);
}
}
return(allReservations);
}
public List <Campsite> GetTop5CampsitesInPark(int parkId, DateTime fromDate, DateTime toDate)
{
List <Campsite> output = new List <Campsite>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
CampgroundSqlDAL dal = new CampgroundSqlDAL(connectionString);
List <Campground> listOfCampgrounds = dal.GetCampground(parkId);
SqlCommand cmd = new SqlCommand();
foreach (Campground cg in listOfCampgrounds)
{
cmd = new SqlCommand(@"SELECT TOP 5*, campground.daily_fee FROM site
INNER JOIN campground ON campground.campground_id = site.campground_id
WHERE site.site_id not in
(SELECT site_id
FROM reservation
WHERE (@toDate > reservation.from_date AND @toDate < reservation.to_date)
OR (@fromDate > reservation.from_date AND @fromDate < reservation.to_date)
OR (@fromDate < reservation.from_date AND @toDate > reservation.to_date)
)
AND site.campground_id = @campground ;", conn);
cmd.Parameters.AddWithValue("@campground", cg.campgroundID);
cmd.Parameters.AddWithValue("@fromDate", fromDate);
cmd.Parameters.AddWithValue("@toDate", toDate);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Campsite c = new Campsite();
c.siteID = Convert.ToInt32(reader["site_id"]);
c.campgroundID = Convert.ToInt32(reader["campground_id"]);
c.siteNumber = Convert.ToInt32(reader["site_number"]);
c.maxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
c.accessible = Convert.ToBoolean(reader["accessible"]);
c.maxRVLength = Convert.ToInt32(reader["max_rv_length"]);
c.utilities = Convert.ToBoolean(reader["utilities"]);
c.dailyFee = Convert.ToDouble(reader["daily_fee"]);
c.totalPrice = c.CalcTotalPrice(fromDate, toDate, c.dailyFee);
output.Add(c);
}
}
}
}
}
catch (SqlException e)
{
throw;
}
return(output);
}
public Campsite PopulateSitesFromReader(SqlDataReader reader)
{
Campsite item = new Campsite();
item.SiteId = Convert.ToInt32(reader["site_id"]);
item.CampgroundId = Convert.ToInt32(reader["campground_id"]);
item.SiteNumber = Convert.ToInt32(reader["site_number"]);
item.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
item.IsAccessible = Convert.ToBoolean(reader["accessible"]);
item.MaxRvLength = Convert.ToInt32(reader["max_rv_length"]);
item.Utilities = Convert.ToBoolean(reader["utilities"]);
return(item);
}
public static List <Campsite> SearchCampsites(int campgroundID, DateTime arrivalDate, DateTime departureDate)
{
List <Campsite> campsites = new List <Campsite>();
Reservation reservation = new Reservation();
reservation.FromDate = new DateTime(arrivalDate.Year, arrivalDate.Month, arrivalDate.Day, 15, 0, 0);
reservation.ToDate = new DateTime(departureDate.Year, departureDate.Month, departureDate.Day, 11, 0, 0);
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL_SearchReservations, connection);
command.Parameters.AddWithValue("@campground_id", campgroundID);
command.Parameters.AddWithValue("@req_from_date", reservation.FromDate);
command.Parameters.AddWithValue("@req_to_date", reservation.ToDate);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Campsite campsite = new Campsite()
{
SiteID = Convert.ToInt32(reader["site_id"]),
CampgroundID = Convert.ToInt32(reader["campground_id"]),
SiteNumber = Convert.ToInt32(reader["site_number"]),
MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]),
Accessible = Convert.ToBoolean(reader["accessible"]),
MaxRVLength = Convert.ToInt32(reader["max_rv_length"]),
Utilities = Convert.ToBoolean(reader["utilities"]),
DailyFee = Convert.ToDecimal(reader["daily_fee"])
};
campsites.Add(campsite);
}
}
}
catch (Exception)
{
throw;
}
return(campsites);
}
public List <Campsite> GetCampsites(Campground cg, string connectionString)
{
List <Campsite> campsites = new List <Campsite>();
string campName = cg.Name;
int campNameId = 0;
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cg_id = new SqlCommand("SELECT campground_id FROM campground WHERE campground.name = @name", conn);
cg_id.Parameters.AddWithValue("@name", campName);
SqlDataReader reader = cg_id.ExecuteReader();
while (reader.Read())
{
campNameId = Convert.ToInt32(reader["campground_id"]);
}
reader.Close();
SqlCommand cmd = new SqlCommand("SELECT * FROM site JOIN campground ON site.campground_id = campground.campground_id WHERE site.campground_id = @cg_campground_id", conn);
cmd.Parameters.AddWithValue("@cg_campground_id", campNameId);
SqlDataReader reader2 = cmd.ExecuteReader();
while (reader2.Read())
{
Campsite cs = new Campsite();
cs.SiteNumber = Convert.ToInt32(reader2["site_number"]);
cs.MaxOccupancy = Convert.ToInt32(reader2["max_occupancy"]);
cs.Accessible = Convert.ToBoolean(reader2["accessible"]);
cs.Utilities = Convert.ToBoolean(reader2["utilities"]);
cs.MaxRvLength = Convert.ToInt32(reader2["max_rv_length"]);
campsites.Add(cs);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(campsites);
}
public List <Campsite> GetCampsite(int campgroundId, DateTime fromDate, DateTime toDate)
{
List <Campsite> output = new List <Campsite>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT TOP 5*, campground.daily_fee FROM site LEFT JOIN reservation ON site.site_id=reservation.site_id LEFT JOIN campground ON site.campground_id = campground.campground_id WHERE site.campground_id = @campgroundId AND (@fromDate > to_date OR @toDate < from_date);", conn);
cmd.Parameters.AddWithValue("@campgroundId", campgroundId);
cmd.Parameters.AddWithValue("@fromDate", fromDate);
cmd.Parameters.AddWithValue("@toDate", toDate);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Campsite c = new Campsite();
c.siteID = Convert.ToInt32(reader["site_id"]);
c.campgroundID = Convert.ToInt32(reader["campground_id"]);
c.siteNumber = Convert.ToInt32(reader["site_number"]);
c.maxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
c.accessible = Convert.ToBoolean(reader["accessible"]);
c.maxRVLength = Convert.ToInt32(reader["max_rv_length"]);
c.utilities = Convert.ToBoolean(reader["utilities"]);
c.dailyFee = Convert.ToDouble(reader["daily_fee"]);
c.totalPrice = c.CalcTotalPrice(fromDate, toDate, c.dailyFee);
output.Add(c);
}
}
}
catch (SqlException e)
{
throw;
}
return(output);
}
// GetCampsitesByPark
// 5 results per campground
// GetCampsitesByRequirement
// ADD OFF-SEASON FILTER
/// <summary>
/// Gets a list of unbooked campsites
/// </summary>
/// <param name="campgroundID">Campground from whence to pull sites</param>
/// <param name="startDate">Desired day to begin reservation</param>
/// <param name="endDate">Desired day to end reservation</param>
/// <returns></returns>
public List <Campsite> GetCampsitesByCampground(int campgroundID, DateTime?startDate, DateTime?endDate)
{
List <Campsite> sites = new List <Campsite>();
try
{
using (var conn = new SqlConnection(dbConnectionString))
{
conn.Open();
var cmd = new SqlCommand(SQL_GetUnbookedCampsites, conn);
cmd.Parameters.AddWithValue("@campground_id", campgroundID);
cmd.Parameters.AddWithValue("@from_date", startDate);
cmd.Parameters.AddWithValue("@to_date", endDate);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
Campsite s = new Campsite();
s.Accessible = Convert.ToBoolean(reader["accessible"]);
s.CampgroundID = Convert.ToInt32(reader["campground_id"]);
s.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
s.MaxRVLength = Convert.ToInt32(reader["max_rv_length"]);
s.SiteID = Convert.ToInt32(reader["site_id"]);
s.SiteNumber = Convert.ToInt32(reader["site_number"]);
s.UtilityAccess = Convert.ToBoolean(reader["utilities"]);
sites.Add(s);
}
}
}
catch (SqlException ex)
{
Console.WriteLine("Sorry, an error occurred: " + ex.Message);
throw;
}
return(sites);
}
//returns all available campgrounds (not just the top 5- for testing purposes)
public List <Campsite> AllAvailableCampsites(int campgroundId, string startDate, string endDate)
{
List <Campsite> campsites = new List <Campsite>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(SQL_GetSitesAll, conn);
cmd.Parameters.AddWithValue("@campgroundid", campgroundId);
cmd.Parameters.AddWithValue("@requested_start", startDate);
cmd.Parameters.AddWithValue("@requested_end", endDate);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Campsite campsite = new Campsite();
campsite.SiteId = Convert.ToInt32(reader["site_id"]);
campsite.CampgroundId = Convert.ToInt32(reader["campground_id"]);
campsite.SiteNum = Convert.ToInt32(reader["site_number"]);
campsite.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
campsite.Accessible = Convert.ToString(reader["accessible"]);
campsite.MaxRVLength = Convert.ToString(reader["max_rv_length"]);
campsite.Utilities = Convert.ToString(reader["utilities"]);
campsite.DailyFee = Convert.ToDecimal(reader["daily_fee"]);
campsites.Add(campsite);
}
}
}
catch (Exception ex)
{
}
return(campsites);
}
public List <Campsite> SearchForReservation(string park, string campground, DateTime arrivalDate, DateTime departureDate)
{
List <Campsite> campsites = new List <Campsite>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(SQL_SearchForReservation, conn);
cmd.Parameters.AddWithValue("@park", park);
cmd.Parameters.AddWithValue("@campground", campground);
cmd.Parameters.AddWithValue("@arrivalDate", arrivalDate);
cmd.Parameters.AddWithValue("@departureDate", departureDate);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Campsite campsite = new Campsite();
campsite.SiteId = Convert.ToInt32(reader["site_id"]);
campsite.SiteNumber = Convert.ToInt32(reader["site_number"]);
campsite.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
campsite.Accessible = Convert.ToByte(reader["accessible"]);
campsite.MaxRvLength = Convert.ToInt32(reader["max_rv_length"]);
campsite.Utilities = Convert.ToByte(reader["utilities"]);
campsite.DailyFee = Convert.ToDecimal(reader["daily_fee"]);
campsites.Add(campsite);
}
}
}
catch
{
}
return(campsites);
}
public decimal CalculateCostOfReservation(Campsite site, DateTime arrival, DateTime departure, string connectionString)
{
//return the rate based on the site chosen (the math from the dates will be done back in the cli? <--it would be better to do it here
decimal dailyFee = 0.0M;
decimal finalFee = 0.0M;
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT campground.daily_fee
FROM campground
JOIN site ON campground.campground_id = site.campground_id
WHERE site.site_id = @siteID", conn);
cmd.Parameters.AddWithValue("@siteID", site.SiteID);
TimeSpan difference = departure - arrival;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
dailyFee = Convert.ToDecimal(reader["daily_fee"]);
finalFee = (int)difference.TotalDays * dailyFee;
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
return(finalFee);
}
private List <Campsite> GetListOfCampsites(SqlCommand cmd)
{
List <Campsite> output = new List <Campsite>();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Campsite c = new Campsite();
c.SiteId = Convert.ToInt32(reader["site_id"]);
c.ParkId = Convert.ToInt32(reader["park_id"]);
c.ParkName = Convert.ToString(reader["park_name"]);
c.CampgroundId = Convert.ToInt32(reader["campground_id"]);
c.CampgroundName = Convert.ToString(reader["campground_name"]);
c.Accessible = (Convert.ToBoolean(reader["accessible"])) ? "Yes" : "No";
c.Utilities = (Convert.ToBoolean(reader["utilities"])) ? "Yes" : "No";
c.MaxRVLength = Convert.ToInt32(reader["max_rv_length"]);
c.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
c.DailyFee = Convert.ToDecimal(reader["daily_fee"]);
output.Add(c);
}
return(output);
}
public List <Campsite> BookReservationByPark(DateTime arrival, DateTime departure, Park park, string connectionString)
{
List <Campsite> campsites = new List <Campsite>();
int fromMonth = arrival.Month;
int toMonth = departure.Month;
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT s.*
FROM site s
JOIN campground c ON s.campground_id = c.campground_id
JOIN park p ON c.park_id = p.park_id
WHERE NOT EXISTS
(
SELECT *
FROM reservation r
WHERE r.site_id = s.site_id
AND r.from_date <= DATEADD(day, -1, @departure)
AND r.to_date >= DATEADD(day, 1, @arrival)
)
AND p.name = @park_name
AND (RIGHT(c.open_from_mm, 2) <= @from_month
AND RIGHT(c.open_to_mm, 2) > @to_month)
GROUP BY site_id, site_number, s.campground_id
, max_occupancy, accessible, max_rv_length, utilities", conn);
cmd.Parameters.AddWithValue("@park_name", park.Name);
cmd.Parameters.AddWithValue("@departure", departure);
cmd.Parameters.AddWithValue("@arrival", arrival);
cmd.Parameters.AddWithValue("@from_month", fromMonth);
cmd.Parameters.AddWithValue("@to_month", toMonth);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Campsite cs = new Campsite();
cs.SiteID = Convert.ToInt32(reader["site_id"]);
cs.SiteNumber = Convert.ToInt32(reader["site_number"]);
cs.MaxOccupancy = Convert.ToInt32(reader["max_occupancy"]);
cs.Accessible = Convert.ToBoolean(reader["accessible"]);
cs.Utilities = Convert.ToBoolean(reader["utilities"]);
cs.MaxRvLength = Convert.ToInt32(reader["max_rv_length"]);
campsites.Add(cs);
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
return(campsites);
}
public IList <Campsite> ParkAvailability(int park_Id, DateTime start_date, DateTime end_date)
{
List <Campsite> output = new List <Campsite>();
try
{
using (SqlConnection conn = new SqlConnection(this.connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand($"SELECT * FROM campground WHERE campground.park_id = {park_Id}", conn);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Campground campgroundToBook = new Campground();
campgroundToBook.Campground_Id = Convert.ToInt32(reader["campground_id"]);
campgroundToBook.Park_Id = Convert.ToInt32(reader["park_id"]);
campgroundToBook.Name = Convert.ToString(reader["name"]);
campgroundToBook.Opening_Month = Convert.ToInt32(reader["open_from_mm"]);
campgroundToBook.Closing_Month = Convert.ToInt32(reader["open_to_mm"]);
campgroundToBook.Daily_Fee = Convert.ToDecimal(reader["daily_fee"]);
if (start_date.Month < campgroundToBook.Opening_Month || end_date.Month > campgroundToBook.Closing_Month)
{
Console.WriteLine("GO AWAY! WE CLOSED!!!!");
Console.Read();
return(new List <Campsite>());
}
using (SqlConnection conn2 = new SqlConnection(this.connectionString))
{
conn2.Open();
string query = "SELECT site.site_id, campground.campground_id, site.site_number, site.max_occupancy, site.accessible, site.max_rv_length, site.utilities, campground.daily_fee FROM site INNER JOIN campground " +
"ON campground.campground_id = site.campground_id WHERE site.site_id IN (SELECT site_id FROM site " +
$"WHERE site.campground_id = {campgroundToBook.Campground_Id} " +
"EXCEPT " +
"SELECT reservation.site_id FROM reservation " +
"INNER JOIN site ON site.site_id = reservation.site_id " +
$"WHERE campground_id = {campgroundToBook.Campground_Id} AND ((to_date BETWEEN \'{start_date}\' AND \'{end_date}\') " +
$"OR (from_date BETWEEN \'{start_date}\' AND \'{end_date}\') OR " +
$"((to_date >= '{start_date}') AND (from_date <= '{end_date}'))))";
SqlCommand cmd = new SqlCommand(query, conn2);
SqlDataReader reader2 = cmd.ExecuteReader();
while (reader2.Read())
{
Campsite campsite = new Campsite();
campsite.Campground_Id = Convert.ToInt32(reader2["campground_id"]);
campsite.Site_Id = Convert.ToInt32(reader2["site_id"]);
campsite.Site_Number = Convert.ToInt32(reader2["site_number"]);
campsite.Max_Occupancy = Convert.ToInt32(reader2["max_occupancy"]);
campsite.IsAccessible = Convert.ToBoolean(reader2["accessible"]);
campsite.Max_RV_Length = Convert.ToInt32(reader2["max_rv_length"]);
campsite.HasUtilities = Convert.ToBoolean(reader2["utilities"]);
output.Add(campsite);
}
}
}
}
}
catch (SqlException ex)
{
Console.WriteLine("Creativity is in another castle; I have no idea.");
throw;
}
return(output);
}
public IList <Campsite> CampgroundAvailability(int campground_Id, DateTime startDate, DateTime endDate)
{
List <Campsite> output = new List <Campsite>();
Campground campgroundToBook = this.CampgroundDetails(campground_Id);
try
{
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
conn.Open();
//SqlCommand command = new SqlCommand($"SELECT * FROM campground WHERE campground_id = {campground_Id};", conn);
//SqlDataReader read = command.ExecuteReader();
//Campground campgroundToBook = new Campground();
//while (read.Read())
//{
// campgroundToBook.Campground_Id = Convert.ToInt32(read["campground_id"]);
// campgroundToBook.Park_Id = Convert.ToInt32(read["park_id"]);
// campgroundToBook.Name = Convert.ToString(read["name"]);
// campgroundToBook.Opening_Month = Convert.ToInt32(read["open_from_mm"]);
// campgroundToBook.Closing_Month = Convert.ToInt32(read["open_to_mm"]);
// campgroundToBook.Daily_Fee = Convert.ToDecimal(read["daily_fee"]);
//}
//read.Close();
if (startDate.Month < campgroundToBook.Opening_Month || endDate.Month > campgroundToBook.Closing_Month)
{
Console.WriteLine("GO AWAY! WE CLOSED!!!!");
return(output);
}
string query = $" SELECT * FROM site "
+ $" INNER JOIN campground ON "
+ $" campground.campground_id = site.campground_id "
+ $" WHERE site.site_id IN ("
+ $" SELECT site_id FROM site"
+ $" WHERE campground_id = {campground_Id}"
+ $" EXCEPT"
+ $" SELECT site.site_id FROM reservation INNER JOIN site ON site.site_id = reservation.site_id"
+ $" WHERE campground_id = {campground_Id} "
+ $" AND ((to_date BETWEEN @startDate AND @endDate)"
+ $" OR (from_date BETWEEN @startDate AND @endDate)"
+ $" OR (from_date >= @endDate AND to_date <= @startDate)))";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@startDate", startDate);
cmd.Parameters.AddWithValue("@endDate", endDate);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Campsite campsite = new Campsite();
campsite.Campground_Id = Convert.ToInt32(reader["campground_id"]);
campsite.Site_Id = Convert.ToInt32(reader["site_id"]);
campsite.Site_Number = Convert.ToInt32(reader["site_number"]);
campsite.Max_Occupancy = Convert.ToInt32(reader["max_occupancy"]);
campsite.IsAccessible = Convert.ToBoolean(reader["accessible"]);
campsite.Max_RV_Length = Convert.ToInt32(reader["max_rv_length"]);
campsite.HasUtilities = Convert.ToBoolean(reader["utilities"]);
output.Add(campsite);
}
}
}
catch (SqlException ex)
{
Console.WriteLine("Creativity is in another castle; I have no idea.");
throw;
}
return(output);
}
public List <Campsite> GetCampsitesByAvailability(string connectionString, Campground cg,
DateTime desiredArrival, DateTime desiredDeparture)
{
List <Campsite> campsites = new List <Campsite>();
string userCampgroundName = cg.Name;
int userCampgroundID = 0;
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cg_id = new SqlCommand("SELECT campground_id FROM campground WHERE campground.name = @name", conn);
cg_id.Parameters.AddWithValue("@name", userCampgroundName);
SqlDataReader reader = cg_id.ExecuteReader();
while (reader.Read())
{
userCampgroundID = Convert.ToInt32(reader["campground_id"]);
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
try
{
using (SqlConnection conn2 = new SqlConnection(connectionString))
{
conn2.Open();
SqlCommand cmd = new SqlCommand(@"SELECT s.*
FROM site s
WHERE NOT EXISTS
(
SELECT * FROM reservation r
WHERE r.site_id = s.site_id
AND r.from_date <= DATEADD(day, -1, @todate)
AND r.to_date >= DATEADD(day, 1, @fromdate)
)
AND s.campground_id = @campgroundid
GROUP BY site_id, site_number, s.campground_id,
max_occupancy, accessible, max_rv_length, utilities", conn2);
cmd.Parameters.AddWithValue("@campgroundid", userCampgroundID);
cmd.Parameters.AddWithValue("@fromdate", desiredArrival);
cmd.Parameters.AddWithValue("@todate", desiredDeparture);
SqlDataReader reader2 = cmd.ExecuteReader();
while (reader2.Read())
{
Campsite cs = new Campsite();
cs.SiteID = Convert.ToInt32(reader2["site_id"]);
cs.SiteNumber = Convert.ToInt32(reader2["site_number"]);
cs.MaxOccupancy = Convert.ToInt32(reader2["max_occupancy"]);
cs.Accessible = Convert.ToBoolean(reader2["accessible"]);
cs.Utilities = Convert.ToBoolean(reader2["utilities"]);
cs.MaxRvLength = Convert.ToInt32(reader2["max_rv_length"]);
campsites.Add(cs);
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
return(campsites);
}