public static List<Course> GetCourseList(Student stud)
{
SqlDataReader reader;
List<Course> course_list = new List<Course>();
try
{
conn.Open();
string course_querryStr = "SELECT distinct g.CourseId,c.CourseName FROM Grade g , Course c where g.CourseId=c.CourseId and g.StudentId='" + stud.Studid + "'";
SqlCommand cmd = new SqlCommand(course_querryStr, conn);
reader = cmd.ExecuteReader();
Course course;
while (reader.Read())
{
course = new Course();
course.CourseID = (int)reader[0];
if (!reader.IsDBNull(1))
course.CourseNAme = reader.GetString(1);
course_list.Add(course);
}
return course_list;
}
catch (SqlException se)
{
Console.Out.WriteLine("Error:GetCourseList " + se.Message + "\nProblem while populating course");
return null;
}
finally
{
conn.Close();
}
}
public static ArrayList GetGrades(Course course, Student stud)
{
ArrayList list = new ArrayList();
try
{
conn.Open();
string course_querryStr = "SELECT MidTermExam, FinalExam, Assignments FROM Grade where CourseId='" + course.CourseID + "' AND StudentId='" + stud.Studid + "'";
SqlCommand cmd = new SqlCommand(course_querryStr, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < 3; i++)
{
list.Add(reader[i]);
}
}
return list;
}
catch (Exception se)
{
Console.Out.WriteLine("Error:@GetGrades "+se.Message + "\nProblem while populating grades");
return null;
}
finally
{
conn.Close();
}
}
public static bool AddCourse(Course course)
{
string insertStatement = "INSERT INTO Course (CourseId, CourseName)"
+ "VALUES (@CourseId, @CourseName)";
try
{
conn.Open();
SqlCommand insertCommand = conn.CreateCommand();
insertCommand.Connection = conn;
insertCommand.CommandText = insertStatement;
insertCommand.Parameters.AddWithValue("@CourseId", course.CourseID);
insertCommand.Parameters.AddWithValue("@CourseName", course.CourseNAme);
insertCommand.ExecuteNonQuery();
return true;
}
catch (SqlException ex)
{
Console.Out.WriteLine("Error: @AddCourse():Course \n " + ex.Message + "\nRecord not added");
return false;
}
finally
{
conn.Close();
}
}
public static List<Course> GetRegisteredCourses(Student stud)
{
SqlDataReader reader;
Course c;
List<Course> list = new List<Course>();
try
{
conn.Open();
// load first list box: all courses for which
// Noa Campbell is registered
SqlCommand cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT Grade.CourseId, Course.CourseName FROM Grade INNER JOIN Course ON (Grade.CourseId = Course.CourseId) WHERE (Grade.StudentId ='" + stud.Studid + "');";
reader = cmd.ExecuteReader();
while (reader.Read())
{
c = new Course();
c.CourseID = reader.GetInt32(0);
if (!reader.IsDBNull(1))
c.CourseNAme = reader.GetString(1);
list.Add(c);
}
reader.Close();
return list;
}
catch (SqlException ex)
{
Console.Out.WriteLine("Error:@GetRegisteredCourses() " + ex.Message);
return null;
}
finally
{
conn.Close();
}
}
public static bool DeleteCourse(Course course)
{
try
{
conn.Open();
SqlCommand updateCommand = conn.CreateCommand();
updateCommand.Connection = conn;
updateCommand.CommandText = "Delete from Course where CourseId='" + course.CourseID + "';";
updateCommand.ExecuteNonQuery();
return true;
}
catch (SqlException)
{
return false;
}
finally
{
conn.Close();
}
}
public static List<Course> GetAllCourses()
{
SqlDataReader reader;
List<Course> list= new List<Course>();
try
{
conn.Open();
SqlCommand sqlCmd = conn.CreateCommand();
sqlCmd.Connection = conn;
sqlCmd.CommandText = "select CourseId, CourseName from Course";
reader = sqlCmd.ExecuteReader();
while (reader.Read())
{
Course cr = new Course();
cr.CourseID = reader.GetInt32(0);
if (!reader.IsDBNull(1))
cr.CourseNAme = reader.GetString(1);
list.Add(cr);
}
reader.Close();
return list;
}
catch (SqlException ex)
{
Console.Out.WriteLine(ex.Message);
return null;
}
finally
{
conn.Close();
}
}
public static List<Course> GetUnRegisteredCourses(Student stud)
{
SqlDataReader reader;
Course c;
List<Course> list = new List<Course>();
try
{
conn.Open();
// now load the second list box: all courses for which
// Noa Campbell is not registered
SqlCommand cmd = conn.CreateCommand();
cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT CourseId, CourseName FROM Course WHERE NOT EXISTS (SELECT * FROM GRADE WHERE Grade.StudentId ='" + stud.Studid + "' AND Grade.CourseId = Course.CourseId);";
reader = cmd.ExecuteReader();
while (reader.Read())
{
c = new Course();
c.CourseID = reader.GetInt32(0);
if (!reader.IsDBNull(1))
c.CourseNAme = reader.GetString(1);
list.Add(c);
}
reader.Close();
return list;
}
catch (SqlException se)
{
Console.Out.WriteLine("Error:@GetUnRegisteredCourses() " + se.Message);
return null;
}
finally
{
conn.Close();
}
}
public static bool UpdateGrades(Course course,Student stud,int midterm, int final, int assignment)
{
try
{
conn.Open();
string querryStr = "UPDATE Grade set MidTermExam=@MidTermExam, FinalExam=@FinalExam, Assignments=@Assignments where CourseId='" + course.CourseID + "' AND StudentId='" + stud.Studid + "'";
SqlCommand cmd = new SqlCommand(querryStr, conn);
cmd.Parameters.AddWithValue("@MidTermExam", midterm);
cmd.Parameters.AddWithValue("@FinalExam", final);
cmd.Parameters.AddWithValue("@Assignments", assignment);
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
Console.Out.WriteLine("Error:@UpdateGrades "+ex.Message + "\nProblem while insserting grades to database");
return false;
}
finally
{
conn.Close();
}
}
public static bool UpdateCourse(Course course)
{
try
{
conn.Open();
SqlCommand updateCommand = conn.CreateCommand();
updateCommand.Connection = conn;
updateCommand.CommandText = "UPDATE Course SET CourseName='" + course.CourseNAme + "'" + " WHERE CourseId='" + course.CourseID + "'";;
updateCommand.ExecuteNonQuery();
return true;
}
catch (SqlException ex)
{
Console.Out.WriteLine("Error: @UpdateCourse() \n " + ex.Message + "\nRecord not added");
return false;
}
finally
{
conn.Close();
}
}
public static bool RegisterStudent(Student stud, Course course)
{
try
{
conn.Open();
SqlCommand insertCommand = conn.CreateCommand();
insertCommand.Connection = conn;
insertCommand.CommandText = "INSERT INTO Grade(StudentId,CourseId) VALUES (@StudentId, @CourseId)";
insertCommand.Parameters.AddWithValue("@CourseId", course.CourseID);
insertCommand.Parameters.AddWithValue("@StudentId", stud.Studid);
insertCommand.ExecuteNonQuery();
return true;
}
catch (SqlException ex)
{
Console.Out.WriteLine("Error:@RegisterStudent " + ex.Message + "\nRecord not added");
return false;
}
finally
{
conn.Close();
}
}
//student obj not needed hence omitted from use
public static bool UnRegisterStudent(Course course)
{
try
{
conn.Open();
SqlCommand insertCommand = conn.CreateCommand();
insertCommand.Connection = conn;
insertCommand.CommandText = "Delete from Grade where CourseId='" + course.CourseID + "'"; ;
insertCommand.ExecuteNonQuery();
return true;
}
catch (SqlException ex)
{
Console.Out.WriteLine("Error:@UnRegisterStudent() " + ex.Message + "\nRecord not added");
return false;
}
finally
{
conn.Close();
}
}
