How can I compare chosen date from calendar with dates in my database?

Question

I'm doing this for a school project. I'm quite new to C# (sorry for my bad english)

I'm making a reservation system, where it should be possible to see available hours to a chosen date. I'm using Entity Framework. I have created an array with numbers from 0 to 23 to represent the hours. But I want the reserved ones to be deleted from the array, so only the available ones will be added to the dropdownlist.

First I have a DBHandler class:

class DBHandler
{
     public void GetReservedTimes(string hour, DateTime StartDate)
    {
        using (systemEntities forbindelse = new systemEntities())
        {
            SqlCommand command = new SqlCommand("SELECT hourofday FROM reservation WHERE date = " + StartDate + "");
        }
    }
}

I'm not sure that this SQL statement is correct..

And here is my reserve class:

public partial class reserve : Page
{
    public reserve()
    {
        InitializeComponent();
        List<String> hours = new List<string>();
        for (int i = 0; i < 24; i++)
            hours.Add(i.ToString());

        //I'm not sure how this should end up:
        Times.Remove(string hourofday);

        for (int i = 0; i < hours.Count; i++)

            dropdown_hours.Items.Add(i);
    }

    private void Calendar_SelectedDatesChanged(object sender, SelectionChangedEventArgs e)
    {
        //Get reference
        var reservationcal = sender as Calendar;

        //See if a date is chosen
        if (reservationcal.SelectedDate.HasValue)
        {
            DateTime SelectedDate = reservationcal.SelectedDate.Value;

            //Show available times for the chosen date
            DBHandler dbh = new DBHandler();
            //Here I am stuck:
            reservation r = dbh.GetReservedTimes                
        }
    }
}

I have added SelectedDatesChanged="Calendar_SelectedDatesChanged" to the Calendar control in my .xaml file.

Thanks in advance - Katharina

Answer 1

Katharina, most of this functionality is baked right into the DateTime object. Rather than reinvent the wheel, I'd use it instead. There is no one way to do anything in the world of programming but sometimes, there are more efficient ways to come to a solution. DateTime and TimeSpan objects can reduce the complexity, increase readability and ultimately manageabilty. Depending on what format or data type is of the database column, 'hourofday', you may be able to use the value as is. Otherwise, you may need to parse the value into an object. (See DateTime.Parse)

The following are the steps I'd take to get the result you are looking for:

1. Determine the business requirements. (Eg. What do you need to do? What is it that you are trying to accomplish?)

2. Put together dummy test data that can be used throughout the design process.

3. Using the dummy test data, determine what the expected results would be. This is why test cases are often used. (Eg. What can you use for comparison?)

4. Determine what the overall process would be in order to get the same results. (Eg. To get the exact same result from the dummy data, what did you need to do?)

5. Determine how the process you used above can be modularized to it's most granular form. (Eg. The process in steps. First you get that value, then you do this to that value.)

6. Determine how you can objectify or generalize those steps into container classes. (Eg. Only a reservation should know if it is visible or not. It's parent should only be allowed to ask.)

7. Code it!

The following solution in a bit of detail: The datetime object already contains all the information you should need. (eg year, month, day,hour, minute, second, millisecond, etc) The reservation class object would be very basic. It would only include information relevant to a reservation. It would have no knowledge of it's parent container. The reservations class is essentially a collection of reservations and any methods relevant to handling a reservation that it contains. (Eg. If you follow the msdn naming convention and best practices, this class would inherit from ICollections and would probably be called 'ReservationCollection')

This methodology would eliminate the need for an array of integers as it would be redundant.

var reserved = this._reservations.GetAllReserved();  //Returns a list of all existing reservations

or

var reserved = this._reservations.GetReservedOn(someDate);  //Return a list of all reservations on a given date

I've added a very simple demonstration of a potential solution which I have commented in fair detail. (See below)

Also, just a note. You mentioned in the comment below that you want 0-23. There are 24 hours in a day. The object at index 0 counts as 1 element. So 0 - 23 can be translated to 1 - 24.

    //Contains a collection of reservations and provides methods to get a 
    //reservation, get all reservations and set a new reservation
    Public class Reservations
    {
        private List<Reservation> _reservations;  //Collection of reservations

        public Reservations()
        {
            //Initialize properties
            this._reservations = new List<Reservation>();
        }

        //Returns all reservations
        public List<Reservation> GetAllReserved()
        {
            //If you are able to use linq, then use the following line
            //return this._reservations.Where(res => res.IsReserved() = true).ToList();

            //Otherwise use this
            List<Reservation> reserved = new List<Reservation>();
            foreach (var res in this._reservations)
            {
                if (res.IsReserved())
                    reserved.Add(res);
            }

            return reserved;
        }

        //Returns all reservations on a given date
        public List<Reservation> GetReservedOnDate(DateTime reservedOn)
        {
            //If you are able to use linq, then use the following line
            //return this._reservations.Where(res => res.GetReservedOn() == reservedOn && res.IsReserved() = true).ToList();

            //Otherwise use this
            List<Reservation> reserved = new List<Reservation>();
            foreach (var res in this._reservations)
            {
                if (res.GetReservedOn.Date == reservedOn.Date && res.IsReserved())
                    reserved.Add(res);
            }

            return reserved;
        }

        //Sets a reservation on a given date. Returns true if one is created, returns false if one can not.
        public bool SetReservation(DateTime reserveOn)
        {
            //Check to see if the date to reserve is already booked
            //If you are able to use linq, then use the following line
            if (_reservations.SingleOrDefault(res => res.GetReservedOn.Date == reservedOn.Date && 
                res.GetReservedOn().Hour == reserveOn.Hour && 
                res.IsReserved()) != null)
            {
                return false;
            }

            //It has made it this far so no reservation exists at that time on that date, create a reservation
            this._reservations.Add(new Reservation(reserveOn));

            return true;
        }
    }

    //Contains the properties of a reservation. The reservation is ignorant of the hours as this provides
    //flexibility in the reservations collection handling class.
    public class Reservation
    {
        private DateTime _reserveOn;    //Date to reserve
        private bool _isReserved;       //Whether it is reserved

        //Overloaded constructor takes 1 parameter
        public Reservation(DateTime reserveOn)
        {
            //Initialize properties with assigned parameters
            this._reserveOn = reserveOn;  
            this._isReserved = true;
        }

        //Overloaded constructor takes 2 parameters
        public Reservation(DateTime reserveOn, bool isReserved)
            : this(reservedOn) //Calls the base constructor and passes 
                                           //in the reserved on parameter
        {
            //Initialize properties with assigned parameters 
            this._isReserved = true;
        }

        //Returns the date the reservation is on
        public GetReservedOn()
        {
            return this._reservedOn;
        }

        //Sets the reservation
        public void SetReservedOn(DateTime reserveOn)
        {
            this._reservedOn = reserveOn;
        }

        //Returns whether the date time has been reserved
        public bool GetIsReserved()
        {
            return this._isReserved;
        }

        //Set the reservation as reserved
        public void SetIsReserved(bool isReserved)
        {
            this._isReserved = isReserved;
        }
    }

To get an idea of how to use the following script: Review the following link.

Binding a collection of objects to a control

Answer 2

it is not a exactly solution for this problem but you can chance datatype to Text on your database and control this cell in program whit calender. It easy way to combine them all. Your way more complex than this way.