C# for loop: Checking if DateTimePicker is weekend or in database

Question

I am not an IT professional, so below syntax is wrong, but it shows what I would like to do.

I have two DateTimePicker: DateTimePicker1 and DateTimePicker2 (DateTimePicker1 < DateTimePicker2). For any days between these two DateTimePickers, I would like to check:

1. If it is weekend.

2. If it is a date in Database table.

Could someone help me clean up below code?

Thank you very much!

    public static bool isDateInDatabaseAppointmentTable(DateTime DateTimePicker.Value)  //How to write this statement. Check if the date is in database table
{

    OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString);


    string select = "SELECT COUNT(*) from TableAppointment WHERE AppointmentDate = DataTimePicker.Value ";
    //How to write this SQL statement

    using (OdbcCommand cmd = new OdbcCommand(select, Cn))            
    {
        object obj = cmd.ExecuteScalar();

        int count = Convert.ToInt32(obj);

        if (count > 0)
        {
            return true;
        }
        else
        {
            return false;
        }                          

    }


}



for (DateTime dt = DateTimePicker1.Value to DateTimePicker2.Value) 
{
    bool isFound = GlobalMethod.isDateInDatabaseAppointmentTable(dt);

    if (dt == Satursday || dt == Sunday)
    {
    MessageBox.Show("It is weekend, you don't work today")
    //I will do something here, and I think I know how to do it. Just using messagebox to replace it.
    }  
    else if (isFound == true)   
    {
    MessageBox.Show("You have appointment today, and you don't work today")
    //I will do something here, and I think I know how to do it. Just using messagebox to replace it.
    }  
    else
    {
    //I will do something here.
    }


}

Answer 1

To check if the day is weekend, first, you can refer to this answer to get a list of all dates between two dates. Then use a where clause to filter the list to get all weekends.

DateTime start = dateTimePicker1.Value;
DateTime end = dateTimePicker2.Value;

List<DateTime> weekends = Enumerable.Range(0, 1 + end.Subtract(start).Days)
                        .Select(offset => start.AddDays(offset))
                        .Where(d => d.DayOfWeek == DayOfWeek.Saturday || d.DayOfWeek == DayOfWeek.Sunday)
                        .ToList();

When defining a method, you only need to declare its formal parameters. As for "DateTimePicker.Value", it is used as the actual parameter when calling the method.

// Just define the method like this, use a formal parameter "dt"
public static bool isDateInDatabaseAppointmentTable(DateTime dt)

If the type of field date in the database is date, you need to convert dateTimePicker.Value to Date.

In addition, in order to prevent sql injection, using parameters is a better choice.

public static bool isDateInDatabaseAppointmentTable(DateTime dt)
{
    string connSQL = @"connection string";
    using (SqlConnection conn = new SqlConnection(connSQL))
    {
        string strSQL = "select count(*) from TableAppointment WHERE AppointmentDate = CAST(@date AS DATE)";
        SqlCommand cmd = new SqlCommand(strSQL, conn);
        cmd.Parameters.AddWithValue("@date", dt.ToShortDateString());
        conn.Open();
        int rows = (int)cmd.ExecuteScalar();
        if (rows > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
}

Answer 2

Could someone help me clean up below code?

One of the first mistakes beginner programmers make, is that they try to do too much in one class and one procedure.

Creating smaller procedure that each do only one thing, enhances your code:

• easier to read and understand what it does,
• easier to reuse in similar situations
• easier to test
• easier to implement small changes, without having to change all other procedures.

You need a procedure that checks if a DateTime is in a weekend, or to be more precise: you want to check whether the date is a non-working day

bool IsNonWorkingDay(DateTime date)
{
    return date.DayOfWeek == DayOfWeek.Saturday
        || date.DayOfWeek == DayOfWeek.Sunday;
}

Apparently you already have a method to check whether the date is in the database:

bool IsInDataBase(DateTime date)
{
    return GlobalMethod.isDateInDatabaseAppointmentTable(date);
}

Of course you don't want to limit yourself to DateTimePickers. If in near future wants you to create a TextBox where the operator can type the date.

// Checks whether the date is in the database an not a Weekend.
// shows problems to the operator
bool CheckValidDate(DateTime date)
{
    const string errorNonWorkingDayMessage = ...
    const string errorNotInDatabaseMessage = ...
    const string errorCaption = "Problem with Date!";

    if (IsNonWorkingDay(date))
    {
        MessageBox.Show(errorNonWorkingDayMessage, errorCaption, MessageBoxIcon.Warning);
        return false;
    }
    if (!IsInDataBase(date))
    {
        MessageBox.Show(errorNotInDatabaseMessage, errorCaption, MessageBoxIcon.Warning);
        return false;
    }
    return true;       
}

I'm not sure when you will check this. Let's assume you have two buttons: SelectStartDateButton and SelectEndDateButton. If the operator presses one of these buttons, the operator is asked to select a date. The title bar of the DateTimePicker says which date, the initial value is Today for the start Date

private DateTime acceptedStartDate;
private DateTime acceptedEndDate;

// Asks the operator to select a date. Returns this date or null if no date is selected
public DateTime? SelectDate(string caption, DateTime initialValue)
{
    using (var dateTimePicker = new DateTimePicker)
    {
        dateTimePicker.Text = caption;
        dateTimePicker.Value = initialValue;
        // consider to set MinDate, MaxDate and others

        // show the DateTimePicker and evaluate the result
        var dlgResult = dateTimePicker.ShowDialog(this);

        if (dlgResult == DialogResult.Ok)
            return dateTimePicker.Value;
        else
            return null;
    }
}

The event handler when pressing button Select Start Date / Select End Date:

private void SelectDateButton_Clicked(object sender, ...)
{
    if (Object.ReferenceEquals(sender, this.buttonSelectStartDate)
        this.SelectStartDate();
    else
        this.SelectEndDate();
}

void SelectStartDate()
{
    const string caption = "Select the Start Date";
    bool startDateChanged = this.SelectDate(caption, ref this.acceptedStartDate);
    if (startDateChanged)
    {
         // TODO: process changed start date
    }
}

void SelectEndDate()
{
    const string caption = "Select the End Date";
    bool endDateChanged = this.SelectDate(caption, ref this.acceptedEndDate);
    if (endDateChanged)
    {
         // TODO: process changed end date
    }
}

The following method asks the operator to select a date, using the proper caption and initial value date

// if Ok, then date is changed. return value true if changed
bool SelectDate(string caption, ref DateTime date)
{     
    bool dateChanged = false;
    DateTime? selectedDate = this.SelectDate(caption, date);
    if (selectedDate.HasValue)
    {
        DateTime proposedDate = selectedDate.Value;
        if (this.CheckValidDate(proposedDate)
        && date != proposedDate)
        {
            date = proposedDate;
            dateChanged = true;
        }
    }
    return dateChanged;
}

Conclusion

I made a lot of small procedures:

• One to check whether any date is a weekend date,
• One to check whether any date is in the database
• One that checks validity of a Date, and shows the proper warning message
• One that asks the operator to select a date
• One that puts this all together: ask the operator for date, check the validity, and update the date
• One that handles button clicks to call the "put-it-all-together" method.

Because these methods are small, they are easy to understand, easy to reuse and easy to test.

For instance: IsNonWorkingDay can easily be reused to test for any date whether it is a NonWorkingDay. It is easy to unit test this function without the user interface. And if you want to change it such that Christmas is also a non-working day, there is only one method that needs to be changed.

Also the calling method is easy to change without a lot of code changes. If for instance you don't want to react on a button click, but start selecting the end date immediately after the start date has been selected: only one small change on one place.