public string[] CreateTeachServiceInspect(TeachServiceInspect sTemperatureData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; string strSql = ""; string strSqlPara = ""; foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "RowNum": case "TeacherName": case "StudentName": case "txtstudentbirthday": case "checkNo": case "errorMsg": case "ClassIDName": case "DirectorName": break; case "isDeleted": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name; strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "0 "; break; case "CreateFileDate": case "UpFileDate": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name; strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "getdate() "; break; default: strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name; strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "@" : ",@") + fldInfo.Name; break; } } DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); string sql = " insert into TeachServiceInspect ( " + strSql + " )values( " + strSqlPara + ") select @@identity as ID "; Sqlconn.Open(); SqlCommand cmd = new SqlCommand(sql, Sqlconn); //cmd.Parameters.Add("@AcademicYear", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(sTemperatureData.AcademicYear.ToString()); //cmd.Parameters.Add("@VDID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(sTemperatureData.ID.ToString()); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "RowNum": case "TeacherName": case "StudentName": case "txtstudentbirthday": case "checkNo": case "errorMsg": case "ClassIDName": case "DirectorName": break; case "CreateFileBy": case "UpFileBy": //cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData)); cmd.Parameters.Add("@" + fldInfo.Name, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); break; case "ClassDate": if (fldInfo.GetValue(sTemperatureData) != null) { cmd.Parameters.Add("@" + fldInfo.Name, Convert.ToDateTime(fldInfo.GetValue(sTemperatureData).ToString()).AddYears(1911).ToShortDateString()); } else { cmd.Parameters.Add("@" + fldInfo.Name, ""); } break; default: if (fldInfo.GetValue(sTemperatureData) != null) { cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData).ToString()); } else { cmd.Parameters.Add("@" + fldInfo.Name, ""); } break; } } SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { returnValue[0] = dr["ID"].ToString(); } Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public List<AchievementAssessmentLoad> ShowTeachServiceInspect(int id) { List<AchievementAssessmentLoad> returnValue = new List<AchievementAssessmentLoad>(); string strSql = ""; TeachServiceInspect sTemperatureData = new TeachServiceInspect(); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "RowNum": case "TeacherName": case "StudentName": case "txtstudentbirthday": case "checkNo": case "errorMsg": case "isDeleted": case "CreateFileDate": case "CreateFileBy": case "UpFileBy": case "UpFileDate": case "ClassIDName": case "DirectorName": break; case "ClassDate": case "InspectDate": case "Date": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "convert(varchar, isnull(convert(date, a." + fldInfo.Name + ",1),'1912'), 111) as " + fldInfo.Name; break; default: strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + "a." + fldInfo.Name; break; } } DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = " SELECT " + strSql + " , b.StudentName ,c.TeacherName ,d.ClassIDName ,e.DirectorName "; sql += " from TeachServiceInspect a "; sql += " left join (select id, studentName from studentDatabase) b on a.studentid = b.id "; sql += " left join ( select staffid as cid , StaffName as TeacherName from staffDatabase ) c on a.teacherid = c.cid "; sql += " left join (select ClassIDName , ClassID as did from ClassName ) d on a.ClassNameID = d.did "; sql += " left join ( select staffid as eid , StaffName as DirectorName from staffDatabase ) e on a.Director = e.eid "; //DirectorName sql += " where 1=1 and a.ID = @ID "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", id); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { AchievementAssessmentLoad addValue = new AchievementAssessmentLoad(); addValue.IDname = "ClassType"; addValue.ThisValue = dr["ClassType"].ToString(); returnValue.Add(addValue); addValue.IDname = "studentName"; addValue.ThisValue = dr["studentName"].ToString(); returnValue.Add(addValue); addValue.IDname = "teacherName"; addValue.ThisValue = dr["TeacherName"].ToString(); returnValue.Add(addValue); addValue.IDname = "DirectorName"; addValue.ThisValue = dr["DirectorName"].ToString(); returnValue.Add(addValue); addValue.IDname = "ClassName"; addValue.ThisValue = dr["ClassIDName"].ToString(); returnValue.Add(addValue); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "RowNum": case "TeacherName": case "StudentName": case "txtstudentbirthday": case "checkNo": case "errorMsg": case "isDeleted": case "CreateFileDate": case "CreateFileBy": case "UpFileBy": case "UpFileDate": case "ClassType": case "ClassIDName": case "DirectorName": break; case "InspectDate": case "Date": case "ClassDate": addValue.IDname = fldInfo.Name; addValue.ThisValue = Convert.ToDateTime(dr[fldInfo.Name].ToString()).AddYears(-1911).AddDays(-1).ToShortDateString().Remove(0, 1); returnValue.Add(addValue); break; default: addValue.IDname = fldInfo.Name; addValue.ThisValue = dr[fldInfo.Name].ToString(); returnValue.Add(addValue); break; } } } Sqlconn.Close(); } catch (Exception e) { //AchievementAssessmentLoad addValue = new AchievementAssessmentLoad(); ////addValue.checkNo = "-1"; ////addValue.errorMsg = e.Message.ToString(); string ee = e.Message.ToString(); //returnValue.Add(addValue); } } return returnValue; }