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