C# 动态查询一年中每个月的记录

int y = DateTime.Now.Year;
DateTime datetime = Convert.ToDateTime("" + y + "-01-01");
string mstart1 = datetime.AddDays(1 - datetime.Day).ToString("yyyy-MM-dd");
string mend1 = datetime.AddDays(1 - datetime.Day).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");

DateTime datetime2 = Convert.ToDateTime("" + y + "-02-01");
string mstart2 = datetime2.AddDays(1 - datetime2.Day).ToString("yyyy-MM-dd");
string mend2 = datetime2.AddDays(1 - datetime2.Day).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");

DateTime datetime3 = Convert.ToDateTime("" + y + "-03-01");
string mstart3 = datetime3.AddDays(1 - datetime3.Day).ToString("yyyy-MM-dd");
string mend3 = datetime3.AddDays(1 - datetime3.Day).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");

DateTime datetime4 = Convert.ToDateTime("" + y + "-04-01");
string mstart4 = datetime4.AddDays(1 - datetime4.Day).ToString("yyyy-MM-dd");
string mend4 = datetime4.AddDays(1 - datetime4.Day).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");

DateTime datetime5 = Convert.ToDateTime("" + y + "-05-01");
string mstart5 = datetime5.AddDays(1 - datetime5.Day).ToString("yyyy-MM-dd");
string mend5 = datetime5.AddDays(1 - datetime5.Day).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");

DateTime datetime6 = Convert.ToDateTime("" + y + "-06-01");
string mstart6 = datetime6.AddDays(1 - datetime6.Day).ToString("yyyy-MM-dd");
string mend6 = datetime6.AddDays(1 - datetime6.Day).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");

DateTime datetime7 = Convert.ToDateTime("" + y + "-07-01");
string mstart7 = datetime7.AddDays(1 - datetime7.Day).ToString("yyyy-MM-dd");
string mend7 = datetime7.AddDays(1 - datetime7.Day).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");

DateTime datetime8 = Convert.ToDateTime("" + y + "-08-01");
string mstart8 = datetime8.AddDays(1 - datetime8.Day).ToString("yyyy-MM-dd");
string mend8 = datetime8.AddDays(1 - datetime8.Day).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");

DateTime datetime9 = Convert.ToDateTime("" + y + "-09-01");
string mstart9 = datetime9.AddDays(1 - datetime9.Day).ToString("yyyy-MM-dd");
string mend9 = datetime9.AddDays(1 - datetime9.Day).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");

DateTime datetime10 = Convert.ToDateTime("" + y + "-10-01");
string mstart10 = datetime10.AddDays(1 - datetime10.Day).ToString("yyyy-MM-dd");
string mend10 = datetime10.AddDays(1 - datetime10.Day).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");

DateTime datetime11 = Convert.ToDateTime("" + y + "-11-01");
string mstart11 = datetime11.AddDays(1 - datetime11.Day).ToString("yyyy-MM-dd");
string mend11 = datetime11.AddDays(1 - datetime11.Day).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");

DateTime datetime12 = Convert.ToDateTime("" + y + "-12-01");
string mstart12 = datetime12.AddDays(1 - datetime12.Day).ToString("yyyy-MM-dd");
string mend12 = datetime12.AddDays(1 - datetime12.Day).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");

 

string q1 = "BETWEEN '" + mstart1 + "' and '" + mend1 + "'";
string q2 = "BETWEEN '" + mstart2 + "' and '" + mend2 + "'";
string q3 = "BETWEEN '" + mstart3 + "' and '" + mend3 + "'";
string q4 = "BETWEEN '" + mstart4 + "' and '" + mend4 + "'";
string q5 = "BETWEEN '" + mstart5 + "' and '" + mend5 + "'";
string q6 = "BETWEEN '" + mstart6 + "' and '" + mend6 + "'";
string q7 = "BETWEEN '" + mstart7 + "' and '" + mend7 + "'";
string q8 = "BETWEEN '" + mstart8 + "' and '" + mend8 + "'";
string q9 = "BETWEEN '" + mstart9 + "' and '" + mend9 + "'";
string q10 = "BETWEEN '" + mstart10 + "' and '" + mend10 + "'";
string q11 = "BETWEEN '" + mstart11 + "' and '" + mend11 + "'";
string q12 = "BETWEEN '" + mstart12 + "' and '" + mend12 + "'";

 

var tab = DbHelperSQL.GetDataTable(@"select sum(case when State in (0, 1) and Hidden_TotheDate "+q1+" then 1 else 0 end ) 一月," +
"sum(case when State in (0, 1) and Hidden_TotheDate "+q2+" then 1 else 0 end ) 二月," +
"sum(case when State in (0, 1) and Hidden_TotheDate "+q3+" then 1 else 0 end) 三月," +
"sum(case when State in (0, 1) and Hidden_TotheDate "+q4+ " then 1 else 0 end) 四月," +
"sum(case when State in (0, 1) and Hidden_TotheDate " + q5 + " then 1 else 0 end) 五月," +
"sum(case when State in (0, 1) and Hidden_TotheDate " + q6 + " then 1 else 0 end) 六月," +
"sum(case when State in (0, 1) and Hidden_TotheDate " + q7 + " then 1 else 0 end) 七月," +
"sum(case when State in (0, 1) and Hidden_TotheDate " + q8 + " then 1 else 0 end) 八月," +
"sum(case when State in (0, 1) and Hidden_TotheDate " + q9 + " then 1 else 0 end) 九月," +
"sum(case when State in (0, 1) and Hidden_TotheDate " + q10 + " then 1 else 0 end) 十月," +
"sum(case when State in (0, 1) and Hidden_TotheDate " + q11 + " then 1 else 0 end) 十一月," +
"sum(case when State in (0, 1) and Hidden_TotheDate " + q12 + " then 1 else 0 end) 十二月 from Hidden_TotheManage where Dw_Id in ('" + Dw_Id + "')");
int mon1 = Convert.ToInt32(tab.Rows[0]["一月"].ToString());
int mon2 = Convert.ToInt32(tab.Rows[0]["二月"].ToString());
int mon3 = Convert.ToInt32(tab.Rows[0]["三月"].ToString());
int mon4 = Convert.ToInt32(tab.Rows[0]["四月"].ToString());
int mon5 = Convert.ToInt32(tab.Rows[0]["五月"].ToString());
int mon6 = Convert.ToInt32(tab.Rows[0]["六月"].ToString());
int mon7 = Convert.ToInt32(tab.Rows[0]["七月"].ToString());
int mon8 = Convert.ToInt32(tab.Rows[0]["八月"].ToString());
int mon9 = Convert.ToInt32(tab.Rows[0]["九月"].ToString());
int mon10 = Convert.ToInt32(tab.Rows[0]["十月"].ToString());
int mon11 = Convert.ToInt32(tab.Rows[0]["十一月"].ToString());
int mon12 = Convert.ToInt32(tab.Rows[0]["十二月"].ToString());

 

HiddenEcharts hidden = new HiddenEcharts();

List<int> n1 = new List<int>();

n1.Add(mon1);
n1.Add(mon2);
n1.Add(mon3);
n1.Add(mon4);
n1.Add(mon5);
n1.Add(mon6);
n1.Add(mon7);
n1.Add(mon8);
n1.Add(mon9);
n1.Add(mon10);
n1.Add(mon11);
n1.Add(mon12);
hidden.Weizhili = n1;

return hidden;
}

 

 

 

public class HiddenEcharts
{
public List<int> Yizhili { get; set; }
public List<int> Weizhili { get; set; }

}