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 bool DeleteStudent(Student stud)
{
string deletegradeStatement = "Delete from Grade where StudentId='" + stud.Studid + "';";
String deletestudentStatement = "Delete from Student where StudentId='" + stud.Studid + "';";
try
{
conn.Open();
SqlCommand updateCommand = conn.CreateCommand();
updateCommand.Connection = conn;
updateCommand.CommandText = deletegradeStatement;
updateCommand.CommandText = deletestudentStatement;
updateCommand.ExecuteNonQuery();
return true;
}
catch (SqlException)
{
return false;
}
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 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 AddStudent(Student stud)
{
Console.Beep();
try
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO Student (StudentId,StudentFirstName,StudentLastName,StudentAddress,StudentCity,StudentProvince,StudentZipCode,StudentPhone,StudentDateOfBirth,StudentEmail) VALUES (@StudentId,@StudentFirstName,@StudentLastName,@StudentAddress,@StudentCity,@StudentProvince,@StudentZipCode,@StudentPhone,@StudentDateOfBirth,@StudentEmail)";
cmd.Parameters.AddWithValue("@StudentId", stud.Studid);
cmd.Parameters.AddWithValue("@StudentFirstName", stud.Fname);
cmd.Parameters.AddWithValue("@StudentLastName", stud.Lname);
cmd.Parameters.AddWithValue("@StudentAddress", stud.Address);
cmd.Parameters.AddWithValue("@StudentCity", stud.City);
cmd.Parameters.AddWithValue("@StudentProvince", stud.Province);
cmd.Parameters.AddWithValue("@StudentZipCode", stud.Zip);
cmd.Parameters.AddWithValue("@StudentPhone", stud.Phno);
cmd.Parameters.AddWithValue("@StudentDateOfBirth", stud.Dob);
cmd.Parameters.AddWithValue("@StudentEmail", "*****@*****.**");
cmd.ExecuteNonQuery();
Console.Beep();
return true;
}
catch (SqlException ex)
{
Console.Out.WriteLine("Error: ", ex.Message + "\nStudent not added");
return false;
}
catch (Exception ex)
{
Console.Out.WriteLine(ex.Message);
return false;
}
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 List<Student> GetAllStudents()
{
SqlDataReader reader;
List<Student> studList = new List<Student>();
try
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = "Select * from Student;";
reader = cmd.ExecuteReader();
while (reader.Read())
{
Student s = new Student();
//Province p = new Province();
s.Studid = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
s.Fname = reader.GetString(1);
}
if (!reader.IsDBNull(2))
{
s.Lname = reader.GetString(2);
}
if (!reader.IsDBNull(3))
{
s.Address = reader.GetString(3);
}
if (!reader.IsDBNull(4))
{
s.City = reader.GetString(4);
}
if (!reader.IsDBNull(5))
{
s.Province=reader.GetString(5);
}
if (!reader.IsDBNull(6))
{
s.Zip = reader.GetString(6);
}
if (!reader.IsDBNull(7))
{
s.Phno = reader.GetString(7);
}
if (!reader.IsDBNull(8))
{
s.Dob = reader.GetDateTime(8).ToString();
}
if (!reader.IsDBNull(9))
{
s.Email = reader.GetString(9);
}
studList.Add(s);
// listBox1.Items.Add(s);
}
reader.Close();
}
catch (SqlException ex)
{
Console.Out.WriteLine("Error: ", ex.Message + "\nTable was not fully loaded");
}
catch (Exception ex)
{
Console.Out.WriteLine(ex.Message);
}
finally
{
conn.Close();
}
return studList;
}
public static bool UpdateStudent(Student stud)
{
string updateStatement = "UPDATE Student SET StudentFirstName='" + stud.Fname + "'" + ", StudentLastName='" + stud.Lname + "'" + ", StudentAddress='" + stud.Address + "'" + ", StudentCity='" + stud.City + "'" + ", StudentProvince='" + stud.Province + "'" + ", StudentZipCode='" + stud.Zip + "'" + ", StudentPhone='" + stud.Phno + "'" + ", StudentDateOfBirth='" + stud.Dob + "'" + ", StudentEmail='" + stud.Email + "'" + " WHERE StudentId='" + stud.Studid + "'";
try
{
conn.Open();
SqlCommand updateCommand = conn.CreateCommand();
updateCommand.Connection = conn;
updateCommand.CommandText = updateStatement;
updateCommand.ExecuteNonQuery();
return true;
}
catch (SqlException ex)
{
Console.Out.WriteLine("Error: @UpdateStudent() " + ex.Message + "\nRecord not added");
return false;
}
finally
{
conn.Close();
}
}
public static List<Student> GetStudentList()
{
List<Student> list = new List<Student>();
try
{
conn.Open();
string querryStr = "SELECT distinct g.StudentId,s.StudentFirstName,s.StudentLastName FROM Grade g , Student s where g.StudentId=s.StudentId";
SqlCommand cmd = new SqlCommand(querryStr, conn);
SqlDataReader reader = cmd.ExecuteReader();
Student stud;
while (reader.Read())
{
stud = new Student();
stud.Studid = (int)reader[0];
if (!reader.IsDBNull(1))
// g.stud_name = reader.GetString(1);
stud.Fname = reader.GetString(1);
stud.Lname= reader.GetString(2);
list.Add(stud);
}
return list;
}
catch (Exception ex)
{
Console.Out.WriteLine("Error:@GetStudentsList " + ex.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 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();
}
}
private void OpenStudentDetail(object sender, ExecutedRoutedEventArgs e)
{
Student st = new Student();
StudentDetailForm studentdetail = new StudentDetailForm(Mode.Add,st);
studentdetail.ShowDialog();
}
private void listboxStudent_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
st = (Student)listboxStudent.SelectedItem;
}
public void setValues(Mode mode, Student st)
{
if (mode == Mode.Add)
{
try
{
st.Studid = Int32.Parse(txtId.Text);
}
catch (FormatException ex)
{
}
st.Fname = txtfname.Text;
st.Lname = txtlname.Text;
st.Address = txtaddress.Text;
st.City = txtcity.Text;
st.Province = cmbprovince.Text;
st.Zip = txtzip.Text;
try
{
st.Dob = txtdob.DisplayDate.ToString();
}
catch (Exception ex)
{
}
st.Phno = txtphone.Text;
}
else if (mode == Mode.Delete)
{
initvalues(st);
try
{
st.Studid = Int32.Parse(txtId.Text);
}
catch (FormatException ex)
{
}
st.Fname = txtfname.Text;
st.Lname = txtlname.Text;
st.Address = txtaddress.Text;
st.City = txtcity.Text;
st.Province = cmbprovince.Text;
st.Zip = txtzip.Text;
try
{
st.Dob = txtdob.DisplayDate.ToString();
}
catch (Exception ex)
{
}
st.Phno = txtphone.Text;
}
else if (mode == Mode.Update)
{
initvalues(st);
try
{
st.Studid = Int32.Parse(txtId.Text);
}
catch (FormatException ex)
{
}
st.Fname = txtfname.Text;
st.Lname = txtlname.Text;
st.Address = txtaddress.Text;
st.City = txtcity.Text;
st.Province = cmbprovince.Text;
st.Zip = txtzip.Text;
try
{
st.Dob = txtdob.DisplayDate.ToString();
}
catch (Exception ex)
{
}
st.Phno = txtphone.Text;
}
}
public void initvalues(Student st)
{
try
{
txtId.Text=st.Studid.ToString();
}
catch (FormatException ex)
{
}
txtfname.Text= st.Fname ;
txtlname.Text= st.Lname ;
txtaddress.Text = st.Address;
txtcity.Text = st.City;
cmbprovince.Text = st.Province;
txtzip.Text = st.Zip;
try
{
txtdob.Text= st.Dob;
}
catch (Exception ex)
{
}
txtphone.Text= st.Phno;
}
public StudentDetailForm(Mode mode, Student st)
{
InitializeComponent();
this.mode = mode;
this.st = st;
loadProvince();
}
