public String update_marks(iMarks temp) { string query = "select MarksDistribution.TotalMarks from MarksDistribution where MarksDistribution.MDID = " + temp.MDID.ToString(); bool all_checked = true; string connectionString = ConfigurationManager.AppSettings["SqlDBConn"].ToString(); foreach (var data in temp.SMs) { query = " select CourseEnrollment.StudentID from MarksRecord, MarksDistribution, FacultySections, CourseEnrollment where MarksRecord.MDID = MarksDistribution.MDID and MarksDistribution.FSID = FacultySections.FSID and FacultySections.FSID = CourseEnrollment.FSID and MarksRecord.StudentID = CourseEnrollment.StudentID and" + " MarksDistribution.TotalMarks <= " + data.ObtainedMarks.ToString() + " and MarksDistribution.MDID = " + temp.MDID.ToString() + " and CourseEnrollment.StudentID = " + data.StudentID.ToString(); using (SqlConnection connection = new SqlConnection(connectionString)) { bool any_record = false; SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value"); connection.Open(); SqlDataReader reader = command.ExecuteReader(); try { while (reader.Read()) { any_record = true; } if (any_record) { all_checked = false; break; } } finally { reader.Close(); } } } if (all_checked) { foreach (var data in temp.SMs) { query = "update MarksRecord set ObtainedMarks = " + data.ObtainedMarks.ToString() + " where MarksRecord.MDID =" + temp.MDID.ToString() + " and MarksRecord.StudentID=" + data.StudentID.ToString(); SqlConnection connection = new SqlConnection(connectionString); connection.Open(); SqlCommand cmd = new SqlCommand(query, connection); int return_value = cmd.ExecuteNonQuery(); connection.Close(); } return("Success"); } else { return("Failed"); } }
//Given list of MDID and student marks uploads it to the database public String insert_marks(iMarks temp) { string query= "select MarksDistribution.TotalMarks from MarksDistribution where MarksDistribution.MDID = "+temp.MDID.ToString();bool all_checked=true; bool no_repeat=true; string connectionString = ConfigurationManager.AppSettings["SqlDBConn"].ToString(); double total_marks = 0; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value"); connection.Open(); SqlDataReader reader = command.ExecuteReader(); try { while (reader.Read()) { total_marks = reader.GetDouble(reader.GetOrdinal("TotalMarks")); } } finally { reader.Close(); } } //check if MDID not already inserted then use update request query = " select distinct(MarksRecord.MDID) from MarksRecord where MarksRecord.MDID = "+temp.MDID.ToString(); using (SqlConnection connection = new SqlConnection(connectionString)) { /*bool any_record = true;*/ SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value"); connection.Open(); SqlDataReader reader = command.ExecuteReader(); try { while (reader.Read()) { no_repeat = false; } } finally { reader.Close(); } } if(no_repeat== false) { return "Please use update query cause record already exists"; } //check if their marks < total and student exist in course foreach (var data in temp.SMs) { /* query= " select CourseEnrollment.StudentID from MarksRecord, MarksDistribution, FacultySections, CourseEnrollment where MarksRecord.MDID = MarksDistribution.MDID and MarksDistribution.FSID = FacultySections.FSID and FacultySections.FSID = CourseEnrollment.FSID and MarksRecord.StudentID = CourseEnrollment.StudentID and"+ " MarksDistribution.TotalMarks <= "+data.ObtainedMarks.ToString()+" and MarksDistribution.MDID = "+temp.MDID.ToString()+" and CourseEnrollment.StudentID = "+data.StudentID.ToString(); using (SqlConnection connection = new SqlConnection(connectionString)) { bool any_record = false; SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value"); connection.Open(); SqlDataReader reader = command.ExecuteReader(); try { while (reader.Read()) { any_record = true; } if(any_record) { all_checked = false; break; } } finally { reader.Close(); } } */ if ((data.ObtainedMarks > total_marks) || (data.ObtainedMarks < 0 )) return "One or more of the Students Marks are greater than total marks"; } if (all_checked) { foreach (var data in temp.SMs) { query = "insert into MarksRecord values (" + data.ObtainedMarks.ToString() + "," + temp.MDID.ToString() + "," + data.StudentID.ToString() + ")"; SqlConnection connection = new SqlConnection(connectionString); connection.Open(); SqlCommand cmd = new SqlCommand(query, connection); int return_value = cmd.ExecuteNonQuery(); connection.Close(); } return "Success"; } else return "Failed"; }
public String update_marks(iMarks temp) { string query = "select MarksDistribution.TotalMarks from MarksDistribution where MarksDistribution.MDID = " + temp.MDID.ToString(); bool all_checked = true; string connectionString = ConfigurationManager.AppSettings["SqlDBConn"].ToString(); double total_marks = 0; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value"); connection.Open(); SqlDataReader reader = command.ExecuteReader(); try { while (reader.Read()) { total_marks = reader.GetDouble(reader.GetOrdinal("TotalMarks")); } } finally { reader.Close(); } } foreach (var data in temp.SMs) { /* * query = " select CourseEnrollment.StudentID from MarksRecord, MarksDistribution, FacultySections, CourseEnrollment where MarksRecord.MDID = MarksDistribution.MDID and MarksDistribution.FSID = FacultySections.FSID and FacultySections.FSID = CourseEnrollment.FSID and MarksRecord.StudentID = CourseEnrollment.StudentID and" + * " MarksDistribution.TotalMarks <= " + data.ObtainedMarks.ToString() + " and MarksDistribution.MDID = " + temp.MDID.ToString() + " and CourseEnrollment.StudentID = " + data.StudentID.ToString(); * using (SqlConnection connection = new SqlConnection(connectionString)) * { * bool any_record = false; * SqlCommand command = new SqlCommand(query, connection); * command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value"); * connection.Open(); * SqlDataReader reader = command.ExecuteReader(); * try * { * while (reader.Read()) * { * any_record = true; * } * if (any_record) * { * all_checked = false; * break; * } * } * finally * { * reader.Close(); * } * } */ if ((data.ObtainedMarks > total_marks) || (data.ObtainedMarks < 0)) { return("One or more of the Students Marks are greater than total marks"); } } if (all_checked) { foreach (var data in temp.SMs) { query = "update MarksRecord set ObtainedMarks = " + data.ObtainedMarks.ToString() + " where MarksRecord.MDID =" + temp.MDID.ToString() + " and MarksRecord.StudentID=" + data.StudentID.ToString(); SqlConnection connection = new SqlConnection(connectionString); connection.Open(); SqlCommand cmd = new SqlCommand(query, connection); int return_value = cmd.ExecuteNonQuery(); connection.Close(); } return("Success"); } else { return("Failed"); } }