public string[] createHearLoss(UpdateHearLoss temperatureDataSystem, int SID)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DateTime now = DateTime.Now;
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);
Sqlconn.Open();
string sql = " DECLARE @MasterID int " +
" insert into Hearing_Loss_AnsMaster ( StudentID , LossDate , Tool,page) values ( @StudentID ,@LossDate, @Tool,@page) " +
" select @MasterID = (select @@identity) ";
for (int i = 0; i < Chk.CheckStringFunction(temperatureDataSystem.anser).Split('|').Length; i++)
{
sql += " insert into Hearing_Loss_AnsDetail (MasterID , Anser) values (@MasterID , @Anser" + i.ToString() + ") ";
}
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = SID;
string date = temperatureDataSystem.Date;
cmd.Parameters.Add("@LossDate", SqlDbType.NVarChar).Value = date;
cmd.Parameters.Add("@Tool", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(temperatureDataSystem.tool);
cmd.Parameters.Add("@page", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(temperatureDataSystem.page);
for (int i = 0; i < Chk.CheckStringFunction(temperatureDataSystem.anser).Split('|').Length; i++)
{
cmd.Parameters.Add("@Anser" + i.ToString(), SqlDbType.NVarChar).Value = Chk.CheckStringFunction(temperatureDataSystem.anser.Split('|').GetValue(i).ToString());
}
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public List<UpdateHearLoss> searchHearLossDataBase(int SID, int page)
{
List<UpdateHearLoss> returnvalue = new List<UpdateHearLoss>();
UpdateHearLoss temp = new UpdateHearLoss();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
Sqlconn.Open();
string sql = "select * from Hearing_Loss_AnsMaster where StudentID=@StudentID and page = @page ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = SID;
cmd.Parameters.Add("@page", SqlDbType.Int).Value = page;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
temp.Date = dr["LossDate"].ToString();
temp.tool = dr["Tool"].ToString();
temp.page = dr["page"].ToString();
using (SqlConnection Sqlconn2 = new SqlConnection(Base.GetConnString()))
{
Sqlconn2.Open();
string sql2 = "select Anser from Hearing_Loss_AnsDetail where MasterID=@MasterID";
SqlCommand cmd2 = new SqlCommand(sql2, Sqlconn2);
cmd2.Parameters.Add("@MasterID", SqlDbType.Int).Value = int.Parse(dr["ID"].ToString());
SqlDataReader dr2 = cmd2.ExecuteReader();
int counter = 0;
temp.anser = "";// 不清空會造成讀取錯誤
while (dr2.Read())
{
if (counter > 0)
{
temp.anser += "|";
}
counter++;
temp.anser += dr2["Anser"].ToString();
}
dr2.Close();
Sqlconn2.Close();
}
returnvalue.Add(temp);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
}
}
return returnvalue;
}
