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