如何使用周数和年份获取一周的开始和结束日期，并将这些日期插入 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(要获得一周的最后一天，请添加6天firstDayOfWeek例如

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