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