How do you loop through a class of start and end DateTimes, storing the days between each date range to a list, with each iteration?

Question

Basically, I'm trying to get a list of available dates for a given Cottage_ID (making a holiday booking system).

Not sure if my logic is sound but I'm thinking, if there's a List<DateTime> of all dates from Today till the last day of 2019.

Then I iterate through a class of date ranges populated with booked dates in the database and populate a new List with those; then create a 3rd List like availableDates = allDays.Except(bookedDates).

The issue is one list is List<DateTime> and the other is List<DateRange> which has 2 DateTime Properties.

How do I iterate through the dateRange class and then for each date Range store all the days in between arrive and depart date?

Service method to get booked dates from database

  public List<DateRange> GetBookedDates(int id)
    {
        DateRange dateRange = new DateRange();

        DateTime start = Convert.ToDateTime(dateRange.Arrive);

        DateTime endDate = Convert.ToDateTime(dateRange.Depart);

        SqlConnection conn = new SqlConnection(dataSource);

        List<DateRange> bookedDates = new List<DateRange>();

        //To avoid sql injections parameters are used so the sql query is not concatinated with user input.  
        string sqlQueryDeleteBooking = "SELECT Arrive, Depart FROM dbo.Bookings WHERE Cottage_ID=@id";

        SqlCommand cmd = new SqlCommand(sqlQueryDeleteBooking, conn);
        cmd.Parameters.AddWithValue("@id", id);

        conn.Open();

        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            if (!reader.HasRows)
            {
                throw new Exception();
            }
            else
            {
                bookedDates.Add(new DateRange()
                {


                    Arrive = Convert.ToDateTime(reader["Arrive"]),
                    Depart = Convert.ToDateTime(reader["Depart"]),

                });

                for (DateTime dates = start; dates <= endDate; dates = dates.AddDays(1))
                {

                    bookedDates.Add(dates);

                }
            }


        }reader.NextResult();

        return bookedDates;
    }

The loop worked for getting a list of days from today till 31st December but obviously bookedDates is a List<DateRange> not List<DateTime> can I cast dates as type of DateRange or visa versa?

Service Method to get all days in the year

     public List<DateTime> GetAvailableDates(int id)
    {
        DateTime start = DateTime.Today;
        string end = "31/12/2019";
        DateTime endDate = DateTime.Parse(end);

        List<DateTime> allDays = new List<DateTime>();

        for (DateTime dates = start; dates <= endDate; dates = dates.AddDays(1))
        {

            allDays.Add(dates);

        }

        return allDays;
    }

Date Range Class

[DataContract]
public class DateRange
{
   [DataMember]
   public DateTime Arrive { get; set; }
   [DataMember]
   public DateTime Depart { get; set; }

}

}

So yeah the idea is remove all the booked days in DateRange from the allDays list.

I've got a List<DateTime> allDays and a List<DateRange> bookedDate how do I use them together?

TIA!

Answer 1

I would prefer to return the available dates as a deferred enumerable:

public static IEnumerable<DateTime> GetAvailableDates(int id)
{
    DateTime startDate = DateTime.Today;
    DateTime endDate = new DateTime(2019, 12, 31);
    var bookedDates = GetBookedDates(id);
    for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
    {
        if (bookedDates.Any(range => date >= range.Arrive && date <= range.Depart)) continue;
        yield return date;
    }
}