public Int32 Insert(AssistanceDetailDto assistanceDetail) { SqlConnection conn = null; String sqlAssistanceDetailInsert; SqlCommand cmdAssistanceDetailInsert; SqlParameter prmassistanceDetailID; SqlParameter prmassistanceID; SqlParameter prmassistanceTypeID; SqlParameter prmstudentID; SqlParameter prmschoolID; SqlParameter prmcareerID; SqlParameter prmcourseID; SqlParameter prmschoolYearID; SqlParameter prmclassroomID; SqlParameter prmheadquartersID; SqlParameter prmprogrammingID; SqlParameter prmteacherID; SqlParameter prmturnID; SqlParameter prmsectionID; SqlParameter prmactive; SqlParameter prmevaluationPeriodID; SqlParameter prmenrollmentID; Int32 intassistanceDetailID; try { conn = new SqlConnection(Functions.GetConnectionString()); sqlAssistanceDetailInsert = "InsertAssistanceDetail"; cmdAssistanceDetailInsert = new SqlCommand(sqlAssistanceDetailInsert, conn); cmdAssistanceDetailInsert.CommandType = CommandType.StoredProcedure; prmassistanceDetailID = new SqlParameter(); prmassistanceDetailID.Direction = ParameterDirection.ReturnValue; prmassistanceDetailID.SqlDbType = SqlDbType.Int; cmdAssistanceDetailInsert.Parameters.Add(prmassistanceDetailID); prmassistanceID = new SqlParameter(); prmassistanceID.ParameterName = "@assistanceID"; prmassistanceID.SqlDbType = SqlDbType.Int; prmassistanceID.Value = assistanceDetail.assistanceID; cmdAssistanceDetailInsert.Parameters.Add(prmassistanceID); prmassistanceTypeID = new SqlParameter(); prmassistanceTypeID.ParameterName = "@assistanceTypeID"; prmassistanceTypeID.SqlDbType = SqlDbType.Int; prmassistanceTypeID.Value = assistanceDetail.assistanceTypeID; cmdAssistanceDetailInsert.Parameters.Add(prmassistanceTypeID); prmstudentID = new SqlParameter(); prmstudentID.ParameterName = "@studentID"; prmstudentID.SqlDbType = SqlDbType.Int; prmstudentID.Value = assistanceDetail.studentID; cmdAssistanceDetailInsert.Parameters.Add(prmstudentID); prmschoolID = new SqlParameter(); prmschoolID.ParameterName = "@schoolID"; prmschoolID.SqlDbType = SqlDbType.Int; prmschoolID.Value = assistanceDetail.schoolID; cmdAssistanceDetailInsert.Parameters.Add(prmschoolID); prmcareerID = new SqlParameter(); prmcareerID.ParameterName = "@careerID"; prmcareerID.SqlDbType = SqlDbType.Int; prmcareerID.Value = assistanceDetail.careerID; cmdAssistanceDetailInsert.Parameters.Add(prmcareerID); prmcourseID = new SqlParameter(); prmcourseID.ParameterName = "@courseID"; prmcourseID.SqlDbType = SqlDbType.Int; prmcourseID.Value = assistanceDetail.courseID; cmdAssistanceDetailInsert.Parameters.Add(prmcourseID); prmschoolYearID = new SqlParameter(); prmschoolYearID.ParameterName = "@schoolYearID"; prmschoolYearID.SqlDbType = SqlDbType.Int; prmschoolYearID.Value = assistanceDetail.schoolYearID; cmdAssistanceDetailInsert.Parameters.Add(prmschoolYearID); prmclassroomID = new SqlParameter(); prmclassroomID.ParameterName = "@classroomID"; prmclassroomID.SqlDbType = SqlDbType.Int; prmclassroomID.Value = assistanceDetail.classroomID; cmdAssistanceDetailInsert.Parameters.Add(prmclassroomID); prmheadquartersID = new SqlParameter(); prmheadquartersID.ParameterName = "@headquartersID"; prmheadquartersID.SqlDbType = SqlDbType.Int; prmheadquartersID.Value = assistanceDetail.headquartersID; cmdAssistanceDetailInsert.Parameters.Add(prmheadquartersID); prmprogrammingID = new SqlParameter(); prmprogrammingID.ParameterName = "@programmingID"; prmprogrammingID.SqlDbType = SqlDbType.Int; prmprogrammingID.Value = assistanceDetail.programmingID; cmdAssistanceDetailInsert.Parameters.Add(prmprogrammingID); prmteacherID = new SqlParameter(); prmteacherID.ParameterName = "@teacherID"; prmteacherID.SqlDbType = SqlDbType.Int; prmteacherID.Value = assistanceDetail.teacherID; cmdAssistanceDetailInsert.Parameters.Add(prmteacherID); prmturnID = new SqlParameter(); prmturnID.ParameterName = "@turnID"; prmturnID.SqlDbType = SqlDbType.Int; prmturnID.Value = assistanceDetail.turnID; cmdAssistanceDetailInsert.Parameters.Add(prmturnID); prmsectionID = new SqlParameter(); prmsectionID.ParameterName = "@sectionID"; prmsectionID.SqlDbType = SqlDbType.Int; prmsectionID.Value = assistanceDetail.sectionID; cmdAssistanceDetailInsert.Parameters.Add(prmsectionID); prmactive = new SqlParameter(); prmactive.ParameterName = "@active"; prmactive.SqlDbType = SqlDbType.Bit; prmactive.Value = assistanceDetail.active; cmdAssistanceDetailInsert.Parameters.Add(prmactive); prmevaluationPeriodID = new SqlParameter(); prmevaluationPeriodID.ParameterName = "@evaluationPeriodID"; prmevaluationPeriodID.SqlDbType = SqlDbType.Int; prmevaluationPeriodID.Value = assistanceDetail.evaluationPeriodID; cmdAssistanceDetailInsert.Parameters.Add(prmevaluationPeriodID); prmenrollmentID = new SqlParameter(); prmenrollmentID.ParameterName = "@enrollmentID"; prmenrollmentID.SqlDbType = SqlDbType.Int; prmenrollmentID.Value = assistanceDetail.enrollmentID; cmdAssistanceDetailInsert.Parameters.Add(prmenrollmentID); cmdAssistanceDetailInsert.Connection.Open(); cmdAssistanceDetailInsert.ExecuteNonQuery(); intassistanceDetailID = Convert.ToInt32(prmassistanceDetailID.Value); cmdAssistanceDetailInsert.Connection.Close(); conn.Dispose(); return(intassistanceDetailID); } catch (Exception ex) { conn.Dispose(); throw ex; } }
public List <AssistanceDetailDto> GetAssistanceDetailByStudent(Int32 enrollmentID, Int32 schoolID, Boolean active) { SqlConnection conn = null; SqlDataReader reader; String sql; SqlCommand command; SqlParameter prmenrollmentID = null; SqlParameter prmschoolID = null; SqlParameter prmactive = null; try { AssistanceDetailDto assistanceDetail; List <AssistanceDetailDto> lstAssistanceDetails; conn = new SqlConnection(Functions.GetConnectionString()); sql = "GetAssistanceDetailByStudent"; command = new SqlCommand(sql, conn); command.CommandType = CommandType.StoredProcedure; prmenrollmentID = new SqlParameter(); prmenrollmentID.ParameterName = "@enrollmentID"; prmenrollmentID.SqlDbType = SqlDbType.Int; prmenrollmentID.Value = enrollmentID; command.Parameters.Add(prmenrollmentID); prmschoolID = new SqlParameter(); prmschoolID.ParameterName = "@schoolID"; prmschoolID.SqlDbType = SqlDbType.Int; prmschoolID.Value = schoolID; command.Parameters.Add(prmschoolID); prmactive = new SqlParameter(); prmactive.ParameterName = "@active"; prmactive.SqlDbType = SqlDbType.Bit; prmactive.Value = active; command.Parameters.Add(prmactive); command.Connection.Open(); reader = command.ExecuteReader(); lstAssistanceDetails = new List <AssistanceDetailDto>(); while (reader.Read()) { assistanceDetail = new AssistanceDetailDto(); assistanceDetail.assistanceDetailID = reader.GetInt32(reader.GetOrdinal("assistanceDetailID")); assistanceDetail.assistanceID = reader.GetInt32(reader.GetOrdinal("assistanceID")); assistanceDetail.enrollmentID = reader.GetInt32(reader.GetOrdinal("enrollmentID")); assistanceDetail.programmingID = reader.GetInt32(reader.GetOrdinal("programmingID")); assistanceDetail.assistanceTypeID = reader.GetInt32(reader.GetOrdinal("assistanceTypeID")); assistanceDetail.studentID = reader.GetInt32(reader.GetOrdinal("studentID")); assistanceDetail.assistanceType_name = reader.GetString(reader.GetOrdinal("assistanceType_name")); assistanceDetail.assistanceType_abbreviation = reader.GetString(reader.GetOrdinal("assistanceType_abbreviation")); lstAssistanceDetails.Add(assistanceDetail); } command.Connection.Close(); conn.Dispose(); return(lstAssistanceDetails); } catch (Exception ex) { conn.Dispose(); throw ex; } }
public void Update(AssistanceDetailDto assistanceDetailDto) { SqlConnection conn = null; String sql; SqlCommand command; SqlParameter prmUassistanceDetailID; SqlParameter prmUassistanceID; SqlParameter prmUassistanceTypeID; SqlParameter prmUstudentID; SqlParameter prmUschoolID; SqlParameter prmUcareerID; SqlParameter prmUcourseID; SqlParameter prmUschoolYearID; SqlParameter prmUclassroomID; SqlParameter prmUheadquartersID; SqlParameter prmUprogrammingID; SqlParameter prmUteacherID; SqlParameter prmUturnID; SqlParameter prmUsectionID; SqlParameter prmUactive; SqlParameter prmUevaluationPeriodID; SqlParameter prmUenrollmentID; try { conn = new SqlConnection(Functions.GetConnectionString()); sql = "UpdateAssistanceDetail"; command = new SqlCommand(sql, conn); command.CommandType = CommandType.StoredProcedure; prmUassistanceDetailID = new SqlParameter(); prmUassistanceDetailID.ParameterName = "@assistanceDetailID"; prmUassistanceDetailID.SqlDbType = SqlDbType.Int; prmUassistanceDetailID.Value = assistanceDetailDto.assistanceDetailID; command.Parameters.Add(prmUassistanceDetailID); prmUassistanceID = new SqlParameter(); prmUassistanceID.ParameterName = "@assistanceID"; prmUassistanceID.SqlDbType = SqlDbType.Int; prmUassistanceID.Value = assistanceDetailDto.assistanceID; command.Parameters.Add(prmUassistanceID); prmUassistanceTypeID = new SqlParameter(); prmUassistanceTypeID.ParameterName = "@assistanceTypeID"; prmUassistanceTypeID.SqlDbType = SqlDbType.Int; prmUassistanceTypeID.Value = assistanceDetailDto.assistanceTypeID; command.Parameters.Add(prmUassistanceTypeID); prmUstudentID = new SqlParameter(); prmUstudentID.ParameterName = "@studentID"; prmUstudentID.SqlDbType = SqlDbType.Int; prmUstudentID.Value = assistanceDetailDto.studentID; command.Parameters.Add(prmUstudentID); prmUschoolID = new SqlParameter(); prmUschoolID.ParameterName = "@schoolID"; prmUschoolID.SqlDbType = SqlDbType.Int; prmUschoolID.Value = assistanceDetailDto.schoolID; command.Parameters.Add(prmUschoolID); prmUcareerID = new SqlParameter(); prmUcareerID.ParameterName = "@careerID"; prmUcareerID.SqlDbType = SqlDbType.Int; prmUcareerID.Value = assistanceDetailDto.careerID; command.Parameters.Add(prmUcareerID); prmUcourseID = new SqlParameter(); prmUcourseID.ParameterName = "@courseID"; prmUcourseID.SqlDbType = SqlDbType.Int; prmUcourseID.Value = assistanceDetailDto.courseID; command.Parameters.Add(prmUcourseID); prmUschoolYearID = new SqlParameter(); prmUschoolYearID.ParameterName = "@schoolYearID"; prmUschoolYearID.SqlDbType = SqlDbType.Int; prmUschoolYearID.Value = assistanceDetailDto.schoolYearID; command.Parameters.Add(prmUschoolYearID); prmUclassroomID = new SqlParameter(); prmUclassroomID.ParameterName = "@classroomID"; prmUclassroomID.SqlDbType = SqlDbType.Int; prmUclassroomID.Value = assistanceDetailDto.classroomID; command.Parameters.Add(prmUclassroomID); prmUheadquartersID = new SqlParameter(); prmUheadquartersID.ParameterName = "@headquartersID"; prmUheadquartersID.SqlDbType = SqlDbType.Int; prmUheadquartersID.Value = assistanceDetailDto.headquartersID; command.Parameters.Add(prmUheadquartersID); prmUprogrammingID = new SqlParameter(); prmUprogrammingID.ParameterName = "@programmingID"; prmUprogrammingID.SqlDbType = SqlDbType.Int; prmUprogrammingID.Value = assistanceDetailDto.programmingID; command.Parameters.Add(prmUprogrammingID); prmUteacherID = new SqlParameter(); prmUteacherID.ParameterName = "@teacherID"; prmUteacherID.SqlDbType = SqlDbType.Int; prmUteacherID.Value = assistanceDetailDto.teacherID; command.Parameters.Add(prmUteacherID); prmUturnID = new SqlParameter(); prmUturnID.ParameterName = "@turnID"; prmUturnID.SqlDbType = SqlDbType.Int; prmUturnID.Value = assistanceDetailDto.turnID; command.Parameters.Add(prmUturnID); prmUsectionID = new SqlParameter(); prmUsectionID.ParameterName = "@sectionID"; prmUsectionID.SqlDbType = SqlDbType.Int; prmUsectionID.Value = assistanceDetailDto.sectionID; command.Parameters.Add(prmUsectionID); prmUactive = new SqlParameter(); prmUactive.ParameterName = "@active"; prmUactive.SqlDbType = SqlDbType.Bit; prmUactive.Value = assistanceDetailDto.active; command.Parameters.Add(prmUactive); prmUevaluationPeriodID = new SqlParameter(); prmUevaluationPeriodID.ParameterName = "@evaluationPeriodID"; prmUevaluationPeriodID.SqlDbType = SqlDbType.Int; prmUevaluationPeriodID.Value = assistanceDetailDto.evaluationPeriodID; command.Parameters.Add(prmUevaluationPeriodID); prmUenrollmentID = new SqlParameter(); prmUenrollmentID.ParameterName = "@enrollmentID"; prmUenrollmentID.SqlDbType = SqlDbType.Int; prmUenrollmentID.Value = assistanceDetailDto.enrollmentID; command.Parameters.Add(prmUenrollmentID); command.Connection.Open(); command.ExecuteNonQuery(); command.Connection.Close(); conn.Dispose(); } catch (Exception ex) { conn.Dispose(); throw ex; } }