c# - 如何使用周数和年份获取一周的开始和结束日期，并将这些日期插入 C# 中的 mysql 表中

Question

我想要做的是从 asp 下拉列表中选择一年中的周数，我想将一周的开始日期和结束日期之间的日期插入 MySql 表中。但是当我尝试它时，我没有收到错误，而是表中填充了 01-01-0001 并且它会继续运行而不会停止，并且连接不会关闭，因为它处于循环状态。

    private IEnumerable<Week> GetWeeksOfTheYear(int year)
    {
        var firstDayOfYear = new DateTime(year, 1, 1);
        var beginningDayOfWeek = firstDayOfYear.AddDays(-1 * Convert.ToInt32(firstDayOfYear.DayOfWeek));
        var endingDayOfWeek = beginningDayOfWeek.AddDays(6);
        var weekOfYear = 1;
        var weeksOfTheYear = new List<Week>();

        while (beginningDayOfWeek.Year < year + 1)
        {
            var week = new Week { Number = weekOfYear, BeginningOfWeek = beginningDayOfWeek };
            weeksOfTheYear.Add(week);

            beginningDayOfWeek = beginningDayOfWeek.AddDays(7);
            weekOfYear++;
        }

        return weeksOfTheYear;
    }


    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            ddlweek.DataSource = this.GetWeeksOfTheYear(DateTime.Now.Year);
            ddlweek.DataBind();
            ddlweek.Items.Insert(0, new ListItem("--Select--", ""));
        }
    }
    private class Week
    {
        public DateTime BeginningOfWeek { get; set; }
        public DateTime EndOfWeek { get { return this.BeginningOfWeek.AddDays(6); } }
        public int Number { get; set; }
        public string Text { get { return this.ToString(); } }

        public override string ToString()
        {
            return DateTime.Now > BeginningOfWeek && DateTime.Now < EndOfWeek
                ? String.Format(
                    "Week {0} or current week: {1} - {2}",
                    this.Number,
                    this.BeginningOfWeek.ToShortDateString(),
                    this.EndOfWeek.ToShortDateString())
                : String.Format(
                    "Week {0}: {1} - {2}",
                    this.Number,
                    this.BeginningOfWeek.ToShortDateString(),
                    this.EndOfWeek.ToShortDateString());
        }
    }
    DateTime BeginningOfWeek { get; set; }
    DateTime EndOfWeek { get; set; }

我得到了上面填充的下拉列表。

    protected void Test_Click(object sender, EventArgs e)
    {
        for(DateTime date = BeginningOfWeek; date <= EndOfWeek; date.AddDays(1))
        {
            MySqlConnection con = new MySqlConnection("Server=localhost;Database=mydb;Uid=myid;Pwd=abc123;");
            MySqlCommand cmd = new MySqlCommand("INSERT INTO shrinkageTool(date) VALUES(@date)");
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@date", date);
            con.Open();
            int result = cmd.ExecuteNonQuery();
            con.Close();

        }

    }

那么我该如何解决这个问题呢？提前致谢。

Answer 1

1)要获取特定年份的周数，您必须使用Calendar.GetWeekOfYear，例如

public List<int> GetWeeksInYear(int year)
{
    DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;
    DateTime date1 = new DateTime(year, 12, 31);
    System.Globalization.Calendar cal = dfi.Calendar;
    int weeks = cal.GetWeekOfYear(date1, dfi.CalendarWeekRule,
                                            dfi.FirstDayOfWeek);

    List<int> weekList = new List<int>();
    for (int i = 1; i <= weeks; i++)
    {
        weekList.Add(i);
    }

    return weekList;
}

2)要获取一周的第一天，然后获取年份，请从上面的列表中获取周数和文化信息，例如

public static DateTime FirstDateOfWeek(int year, int weekOfYear, System.Globalization.CultureInfo ci)
{
    DateTime jan1 = new DateTime(year, 1, 1);
    int daysOffset = (int)ci.DateTimeFormat.FirstDayOfWeek - (int)jan1.DayOfWeek;
    DateTime firstWeekDay = jan1.AddDays(daysOffset);
    int firstWeek = ci.Calendar.GetWeekOfYear(jan1, ci.DateTimeFormat.CalendarWeekRule, ci.DateTimeFormat.FirstDayOfWeek);
    if ((firstWeek <= 1 || firstWeek >= 52) && daysOffset >= -3)
    {
        weekOfYear -= 1;
    }
    return firstWeekDay.AddDays(weekOfYear * 7);
}

3) 要获取一周的最后一天，请向 firstDayOfWeek 添加 6 天，如

public static DateTime LastDayOfWeek(DateTime firstDayOfWeek)
{
    return firstDayOfWeek.AddDays(6);
}

4)并像这样使用上面的日期

protected void Test_Click(object sender, EventArgs e)
{
    List<int> weekNumbers = GetWeeksInYear(2018);    

    DateTime firstDayOfWeek = FirstDateOfWeek(2018, weekNumbers.ElementAt(3), CultureInfo.CurrentCulture);
    DateTime lastDayOfWeek = LastDayOfWeek(firstDayOfWeek);    

    for (var date = firstDayOfWeek; date.Date <= lastDayOfWeek; date = date.AddDays(1))
    {
        //Your rest of code is same here
    }
}