public List<AchievementAssessmentLoad> ShowCaseStudy(int ID) { string strSql = ""; List<AchievementAssessmentLoad> returnvalue = new List<AchievementAssessmentLoad>(); AchievementAssessmentLoad temp = new AchievementAssessmentLoad(); CaseStudy sTemperatureData = new CaseStudy(); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "WriteNameName": case "WriteName1Name": case "RecordedByName": break; case "WriteDate": case "RecordedDateTime": 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 { StaffDataBase sDB = new StaffDataBase(); Sqlconn.Open(); string sql = "select b.studentname ,convert(varchar, isnull(convert(date, b.studentbirthday ,1),'1912'), 23) as studentbirthday , c.WriteNameName , d.WriteName1Name,e.RecordedByName , " + strSql + " from CaseStudy a "; sql += " left join ( select staffid as cid , StaffName as WriteNameName from staffDatabase ) c on a.WriteName = c.cid "; sql += " left join ( select staffid as did , StaffName as WriteName1Name from staffDatabase ) d on a.WriteName1 = d.did "; sql += " left join ( select staffid as eid , StaffName as RecordedByName from staffDatabase ) e on a.RecordedBy = e.eid "; sql += " left join studentDatabase b on a.studentid = b.id where a.ID=@ID and isnull(a.isDeleted,0) = 0 "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID; SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { temp.IDname = "studentName"; temp.ThisValue = dr["studentName"].ToString(); returnvalue.Add(temp); temp.IDname = "WriteNameName"; temp.ThisValue = dr["WriteNameName"].ToString(); returnvalue.Add(temp); temp.IDname = "WriteName1Name"; temp.ThisValue = dr["WriteName1Name"].ToString(); returnvalue.Add(temp); temp.IDname = "RecordedByName"; temp.ThisValue = dr["RecordedByName"].ToString(); returnvalue.Add(temp); temp.IDname = "studentbirthday"; temp.ThisValue = dr["studentbirthday"].ToString(); returnvalue.Add(temp); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "WriteNameName": case "WriteName1Name": case "RecordedByName": break; case "WriteDate": case "RecordedDateTime": temp.IDname = fldInfo.Name; temp.ThisValue = Convert.ToDateTime(dr[fldInfo.Name].ToString()).AddYears(-1911).ToShortDateString().Remove(0, 1); returnvalue.Add(temp); break; default: string namee = fldInfo.Name; string namee2 = dr[fldInfo.Name].ToString(); temp.IDname = fldInfo.Name; temp.ThisValue = dr[fldInfo.Name].ToString(); returnvalue.Add(temp); break; } } } dr.Close(); Sqlconn.Close(); } catch (Exception e) { string error = e.Message; } } return returnvalue; }
public List<CaseStudy> UpdateCaseStudy(CaseStudy sTemperatureData) { List<CaseStudy> returnValue = new List<CaseStudy>(); CaseStudy temp = new CaseStudy(); DataBase Base = new DataBase(); string strSql = ""; foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "CreateFileBy": case "CreateFileDate": case "ID": case "Unit": case "WriteNameName": case "WriteName1Name": case "RecordedByName": // strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + " = " + fldInfo.GetValue(sTemperatureData); break; //case "CreateFileDate": case "UpFileDate": strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + "=getdate()"; // strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "" : ",") + "getdate() "; //cmd.Parameters.Add("@" + fldInfo.Name, SqlDbType.Date).Value = DateTime.Now.ToLongDateString(); break; default: strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + "=@" + fldInfo.Name; //strSqlPara += (string.IsNullOrEmpty(strSqlPara) ? "@" : ",@") + fldInfo.Name; break; } } using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); Sqlconn.Open(); string sql = " update CaseStudy set " + strSql + " where id=@ID "; //sql += " select @@identity as id "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { //case "ID": case "CreateFileDate": case "Unit": // strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + " = " + fldInfo.GetValue(sTemperatureData); break; case "UpFileBy": //cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData)); cmd.Parameters.Add("@" + fldInfo.Name, SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); break; default: if (fldInfo.GetValue(sTemperatureData) != null) { cmd.Parameters.Add("@" + fldInfo.Name, fldInfo.GetValue(sTemperatureData).ToString()); } else { cmd.Parameters.Add("@" + fldInfo.Name, ""); } break; } } cmd.ExecuteNonQuery(); temp.ID = sTemperatureData.ID; } catch (Exception e) { string a = e.Message.ToString(); } Sqlconn.Close(); } returnValue.Add(temp); return returnValue; }
public List<CaseStudy> CreatCaseStudy(CaseStudy sTemperatureData) { List<CaseStudy> returnValue = new List<CaseStudy>(); CaseStudy temp = new CaseStudy(); DataBase Base = new DataBase(); string strSql = ""; string strSqlPara = ""; foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "WriteNameName": case "WriteName1Name": case "RecordedByName": 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; } } using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); Sqlconn.Open(); string sql = " insert into CaseStudy ( " + strSql + ")values(" + strSqlPara + ") "; sql += " select @@identity as id "; SqlCommand cmd = new SqlCommand(sql, Sqlconn); foreach (System.Reflection.FieldInfo fldInfo in sTemperatureData.GetType().GetFields()) { switch (fldInfo.Name) { case "ID": case "Unit": case "WriteNameName": case "WriteName1Name": case "RecordedByName": // strSql += (string.IsNullOrEmpty(strSql) ? "" : ",") + fldInfo.Name + " = " + fldInfo.GetValue(sTemperatureData); 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; 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()) { temp.ID = dr["id"].ToString(); } } catch (Exception e) { string ex = e.Message.ToString(); } Sqlconn.Close(); } returnValue.Add(temp); return returnValue; }