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