public ActionResult TestSql()
{
string tablename = Generate.GenerateTableName();
var flag = CreateTableForHoliday(tablename, this.ConnectionString);
HolidayRecord hr = new HolidayRecord();
hr.HolidayName = tablename;
hr.Number = "";
db.HolidayRecords.Add(hr);
db.SaveChanges();
if (flag == true)
{
@ViewBag.test = tablename;
}
return(View());
//string tablename = "Staffs";
//string sql = " select * from " + tablename;
////if exists(select name from sysobjects where Name = " + tablename + " and XType = 'U')
//string str = this.ConnectionString;
//SqlConnection conn = new SqlConnection(str);
////conn.Open(); //使用 SqlDataAdapter（数据适配器）不用写
////SqlCommand comm = new SqlCommand(sql, conn);
////SqlDataAdapter dr = new SqlDataAdapter(comm);
//SqlDataAdapter dr = new SqlDataAdapter(sql, conn);//上面两句可以合并成这一
//DataSet ds = new DataSet();//创建数据集；
//dr.Fill(ds); //填充数据集
//List<Object> obj = new List<Object>(); //实例化一个集合对象为测试对象
//string a;
//for (int i = 0; i < ds.Tables[0].Rows.Count; i++) //循环取出ds.tables中的值
//{
// a = ds.Tables[0].Rows[i]["Name"].ToString();
// obj.Add(new Staff { Name = a });
//}
//ViewBag.list = obj;
//return View();
}
public ActionResult CreatePerson([Bind(Include = "Nian,Range,Week1,Week2,Week3,week4,Week5,Week6,Week7")] WeekTag weektag)
{
string date = weektag.Nian;
var year = (from wt in db.WeekTags
where wt.Nian == date && wt.Range == weektag.Range
select wt).ToList();
if (year.Count() != 0)
{
//List<SelectListItem> departmentList = db.Departments.ToList().Select(d => new SelectListItem
//{
// Value = d.DepartmentId,
// Text = d.Name,
//}).ToList();
//ViewBag.departmentList = departmentList;
ModelState.AddModelError("", "本员工的该年日历已经存在！");
return(View(weektag));
}
else
{
DateTime begindate = Convert.ToDateTime(date + "-01-01");
//DateTime begindate = Convert.ToDateTime(date+"01-01");
//DateTime enddate = Convert.ToDateTime(date+"12-31");
//System.TimeSpan tsdiffer = enddate.Date - begindate.Date;
// int intdiffer = tsdiffer.Days + 1;
int intdiffer;
if (DateTime.IsLeapYear(int.Parse(weektag.Nian)))
{
intdiffer = 366;
}
else
{
intdiffer = 365;
}
//生成对应人员或者部门或者公司的假日表
string tablename = Generate.GenerateTableName();
var flag = CreateTableForHoliday(tablename, this.ConnectionString);
HolidayRecord hr = new HolidayRecord();
hr.HolidayName = tablename;
hr.Number = weektag.Range;
db.HolidayRecords.Add(hr);
db.SaveChanges();
//数据库连接
SqlConnection conn = new SqlConnection(this.ConnectionString);
conn.Open();
List <DateTime> list = new List <DateTime>();
for (int i = 0; i < intdiffer; i++)
{
DateTime dttemp = begindate.Date.AddDays(i);
if ((dttemp.DayOfWeek == System.DayOfWeek.Monday && "1" == weektag.Week1) ||
(dttemp.DayOfWeek == System.DayOfWeek.Tuesday && "1" == weektag.Week2) ||
(dttemp.DayOfWeek == System.DayOfWeek.Wednesday && "1" == weektag.Week3) ||
(dttemp.DayOfWeek == System.DayOfWeek.Thursday && "1" == weektag.Week4) ||
(dttemp.DayOfWeek == System.DayOfWeek.Friday && "1" == weektag.Week5) ||
(dttemp.DayOfWeek == System.DayOfWeek.Saturday && "1" == weektag.Week6) ||
(dttemp.DayOfWeek == System.DayOfWeek.Sunday && "1" == weektag.Week7))
{
//插入数据
DataTable dtInsert = new DataTable();
dtInsert.Columns.Add("Recordtime", typeof(DateTime));
dtInsert.Columns.Add("Tag", typeof(string));
dtInsert.Columns.Add("Year", typeof(string));
dtInsert.Columns.Add("Month", typeof(string));
dtInsert.Columns.Add("Day", typeof(string));
dtInsert.Rows.Add(new object[] { dttemp, "1", dttemp.Year.ToString(), dttemp.Month.ToString(), dttemp.Day.ToString() });
string sql = "insert into " + tablename + "(Recordtime,Tag,Year,Month,Day) values(@Recordtime,@Tag,@Year,@Month,@Day)";
SqlDataAdapter dr = new SqlDataAdapter(); //上面两句可以合并成这一
dr.InsertCommand = new SqlCommand(sql, conn);
dr.SelectCommand = new SqlCommand("select * from Staffs where 1=0", conn); //仅为了获得框架
dr.InsertCommand.Parameters.Add("Recordtime", SqlDbType.DateTime);
dr.InsertCommand.Parameters.Add("Tag", SqlDbType.VarChar);
dr.InsertCommand.Parameters.Add("Year", SqlDbType.VarChar);
dr.InsertCommand.Parameters.Add("Month", SqlDbType.VarChar);
dr.InsertCommand.Parameters.Add("Day", SqlDbType.VarChar);
dr.InsertCommand.Parameters["Recordtime"].SourceColumn = "Recordtime";
dr.InsertCommand.Parameters["Tag"].SourceColumn = "Tag";
dr.InsertCommand.Parameters["Year"].SourceColumn = "Year";
dr.InsertCommand.Parameters["Month"].SourceColumn = "Month";
dr.InsertCommand.Parameters["Day"].SourceColumn = "Day";
dr.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
if (dtInsert.Rows.Count > 0)
{
dr.Update(dtInsert);
}
//RecordDatetime temp = new RecordDatetime();
//temp.Recordtime = dttemp;
//temp.Tag = "1";
//temp.Year = dttemp.Year.ToString();
//temp.Month = dttemp.Month.ToString();
//temp.Day = dttemp.Day.ToString();
//db.RecordDatetimes.Add(temp);
//db.SaveChanges();
}
else
{
//插入数据
DataTable dtInsert = new DataTable();
dtInsert.Columns.Add("Recordtime", typeof(DateTime));
dtInsert.Columns.Add("Tag", typeof(string));
dtInsert.Columns.Add("Year", typeof(string));
dtInsert.Columns.Add("Month", typeof(string));
dtInsert.Columns.Add("Day", typeof(string));
dtInsert.Rows.Add(new object[] { dttemp, "0", dttemp.Year.ToString(), dttemp.Month.ToString(), dttemp.Day.ToString() });
string sql = "insert into " + tablename + "(Recordtime,Tag,Year,Month,Day) values(@Recordtime,@Tag,@Year,@Month,@Day)";
SqlDataAdapter dr = new SqlDataAdapter(); //上面两句可以合并成这一
dr.InsertCommand = new SqlCommand(sql, conn);
dr.SelectCommand = new SqlCommand("select * from Staffs where 1=0", conn); //仅为了获得框架
dr.InsertCommand.Parameters.Add("Recordtime", SqlDbType.DateTime);
dr.InsertCommand.Parameters.Add("Tag", SqlDbType.VarChar);
dr.InsertCommand.Parameters.Add("Year", SqlDbType.VarChar);
dr.InsertCommand.Parameters.Add("Month", SqlDbType.VarChar);
dr.InsertCommand.Parameters.Add("Day", SqlDbType.VarChar);
dr.InsertCommand.Parameters["Recordtime"].SourceColumn = "Recordtime";
dr.InsertCommand.Parameters["Tag"].SourceColumn = "Tag";
dr.InsertCommand.Parameters["Year"].SourceColumn = "Year";
dr.InsertCommand.Parameters["Month"].SourceColumn = "Month";
dr.InsertCommand.Parameters["Day"].SourceColumn = "Day";
dr.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
if (dtInsert.Rows.Count > 0)
{
dr.Update(dtInsert);
}
}
}
db.WeekTags.Add(weektag);
db.SaveChanges();
return(RedirectToAction("Index"));
}
}
