时间戳

首页 > 解决方案 > C# for 循环：检查 DateTimePicker 是周末还是在数据库中

c# - C# for 循环：检查 DateTimePicker 是周末还是在数据库中

问题描述

我不是 IT 专业人士，所以下面的语法是错误的，但它显示了我想做的事情。

我有两个 DateTimePicker：DateTimePicker1 和 DateTimePicker2（DateTimePicker1 < DateTimePicker2）。对于这两个 DateTimePickers 之间的任何日子，我想检查：

  1. 如果是周末。

  2. 如果它是数据库表中的日期。

有人可以帮我清理下面的代码吗？

非常感谢你！

    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.
    }


}

标签： c#visual-studiowinforms

解决方案

要检查这一天是否是周末，首先，您可以参考此答案以获取两个日期之间所有日期的列表。然后使用 awhere clause过滤列表以获取所有周末。

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();

定义方法时，只需要声明其形参即可。至于“DateTimePicker.Value”，作为调用方法时的实参。

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

如果数据库中字段日期的类型是date，则需要转换dateTimePicker.Value为Date。

另外，为了防止sql注入，使用参数是更好的选择。

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;
        }
    }
}

推荐阅读