public int setCoursePlanTemplate(setCoursePlan CoursePlanTemplate) { int returnValue = 0; Int64 Column = 0; DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = ""; SqlCommand cmd = new SqlCommand(); for (int i = 0; i < CoursePlanTemplate.targetDomain.Count; i++) { if (CoursePlanTemplate.targetDomain[i].LongColumn != 0) { sql = "UPDATE CoursePlanTemplateLong SET TargetContent=@TargetContent WHERE CPTLID=@CPTLID AND CPTID=@CPTID AND isDeleted=0"; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@CPTLID", SqlDbType.BigInt).Value = CoursePlanTemplate.targetDomain[i].LongColumn; cmd.Parameters.Add("@CPTID", SqlDbType.BigInt).Value = CoursePlanTemplate.targetDomain[i].ISPColumn; cmd.Parameters.Add("@TargetContent", SqlDbType.NVarChar).Value = CoursePlanTemplate.targetDomain[i].Content; cmd.ExecuteNonQuery(); returnValue=this.setShortISPCoursePlanTemplate(CoursePlanTemplate.targetDomain[i].ShortTarget); } else if (CoursePlanTemplate.targetDomain[i].Content.Length > 0) { sql = "INSERT INTO CoursePlanTemplateLong(Domain, TargetContent, CPTID) VALUES (@Domain, @TargetContent, @CPTID)"; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Domain", SqlDbType.TinyInt).Value = CoursePlanTemplate.targetDomain[i].Domain; cmd.Parameters.Add("@CPTID", SqlDbType.BigInt).Value = CoursePlanTemplate.targetDomain[i].ISPColumn; cmd.Parameters.Add("@TargetContent", SqlDbType.NVarChar).Value = CoursePlanTemplate.targetDomain[i].Content; returnValue = cmd.ExecuteNonQuery(); if (returnValue != 0) { sql = "select IDENT_CURRENT('CoursePlanTemplateLong') AS ISPLong"; cmd = new SqlCommand(sql, Sqlconn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Column = Int64.Parse(dr["ISPLong"].ToString()); } dr.Close(); returnValue = this.createGroupISPShort(CoursePlanTemplate.Column, Column, CoursePlanTemplate.targetDomain[i].ShortTarget); } } } Sqlconn.Close(); } catch (Exception e) { string item = e.Message; returnValue = -1; } } return returnValue; }
public setCoursePlan getCoursePlan(string CPTID) { setCoursePlan returnValue = new setCoursePlan(); returnValue.targetDomain = new List<ISPLong>(); DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT CoursePlanTemplate.*,StaffDatabase.StaffName,ClassName.ClassIDName,CourseName.CourseIDName " + "FROM CoursePlanTemplate INNER JOIN StaffDatabase ON CoursePlanTemplate.TeacherID=StaffDatabase.StaffID " + "INNER JOIN ClassName ON CoursePlanTemplate.ClassID=ClassName.ClassID "+ "INNER JOIN CourseName ON CoursePlanTemplate.CourseNameID=CourseName.CourseID "+ "WHERE CoursePlanTemplate.isDeleted=0 AND CPTID=@ID "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = CPTID; SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { returnValue.Column = Int64.Parse(dr["CPTID"].ToString()); returnValue.unitNum = int.Parse(dr["Unit"].ToString()); returnValue.courseIDName = dr["CourseIDName"].ToString(); returnValue.courseID = int.Parse(dr["CourseNameID"].ToString()); returnValue.classIDName = dr["ClassIDName"].ToString(); returnValue.classID = int.Parse(dr["ClassID"].ToString()); returnValue.teacherIDName = dr["StaffName"].ToString(); returnValue.teacherID = int.Parse(dr["TeacherID"].ToString()); returnValue.startPlanDate=DateTime.Parse(dr["StartPeriod"].ToString()); returnValue.endPlanDate=DateTime.Parse(dr["EndPeriod"].ToString()); returnValue.targetDomain = new List<ISPLong>(); } dr.Close(); sql = "SELECT CoursePlanTemplateLong.CPTLID, CoursePlanTemplateLong.CPTID, CoursePlanTemplateLong.TargetContent, CoursePlanTemplateLong.Domain " + "FROM CoursePlanTemplate INNER JOIN CoursePlanTemplateLong ON CoursePlanTemplate.CPTID=CoursePlanTemplateLong.CPTID "+ "WHERE CoursePlanTemplate.isDeleted=0 AND CoursePlanTemplateLong.isDeleted=0 AND CoursePlanTemplate.CPTID=@ID ORDER BY CoursePlanTemplateLong.CPTLID"; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Int64.Parse(CPTID); dr = cmd.ExecuteReader(); while (dr.Read()) { ISPLong ItemLong = new ISPLong(); ItemLong.ShortTarget = new List<ISPShort>(); ItemLong.ISPColumn = Int64.Parse(dr["CPTID"].ToString()); ItemLong.LongColumn = Int64.Parse(dr["CPTLID"].ToString()); ItemLong.Domain = int.Parse(dr["Domain"].ToString()); ItemLong.Content = dr["TargetContent"].ToString(); ItemLong.ShortTarget = this.getTeachModelShort(Int64.Parse(CPTID), Int64.Parse(ItemLong.LongColumn.ToString())); returnValue.targetDomain.Add(ItemLong); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { returnValue.Column=-1; returnValue.courseIDName=e.Message.ToString(); } } return returnValue; }
public int setCoursePlanTemplate(setCoursePlan CoursePlanTemplate) { TeachDataBase tDB = new TeachDataBase(); return tDB.setCoursePlanTemplate(CoursePlanTemplate); }