public StudentGrades GetGrade(string studentSsn, string courseName)
{
StudentGrades studentGrades = new StudentGrades();
studentGrades.course = courseName;
DbInfo dbInfo = new DbInfo();
SqlConnection connection = dbInfo.OpenConnection();
using (connection)
{
using (SqlCommand command = new SqlCommand(String.Format("select id from Students where SSN='{0}'", studentSsn), connection))
{
string studentId = command.ExecuteScalar().ToString();
command.CommandText = String.Format("select grade from CoursesStudentsRelation where id_student='{0}' and title_course='{1}'", studentId, courseName);
using (SqlDataReader dataReader = command.ExecuteReader())
{
dataReader.Read();
if (dataReader.HasRows)
{
studentGrades.grade = dataReader.GetDouble(dataReader.GetOrdinal("grade"));
}
else
{
studentGrades.grade = null;
}
}
}
}
return(studentGrades);
}
public List <Topic> GetTopics(string courseName)
{
List <Topic> result = new List <Topic>();
DbInfo dbInfo = new DbInfo();
SqlConnection connection = dbInfo.OpenConnection();
using (connection)
{
using (SqlCommand command = new SqlCommand(String.Format("select name,course_title from Topics where course_title='{0}'", courseName), connection))
{
using (SqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.HasRows)
{
int name = dataReader.GetOrdinal("name");
int course = dataReader.GetOrdinal("course_title");
while (dataReader.Read())
{
Topic topic = new Topic();
topic.name = dataReader.GetString(name);
topic.course = dataReader.GetString(course);
result.Add(topic);
}
}
}
}
}
return(result);
}
public List <Student> GetStudents()
{
List <Student> result = new List <Student>();
DbInfo dbInfo = new DbInfo();
SqlConnection connection = dbInfo.OpenConnection();
using (connection)
{
using (SqlCommand command = new SqlCommand("select name,surname,SSN from Students", connection))
{
using (SqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.HasRows)
{
int name = dataReader.GetOrdinal("name");
int surname = dataReader.GetOrdinal("surname");
int SSN = dataReader.GetOrdinal("SSN");
while (dataReader.Read())
{
Student student = new Student();
student.name = dataReader.GetString(name);
student.surname = dataReader.GetString(surname);
student.SSN = dataReader.GetString(SSN);
result.Add(student);
}
}
}
}
}
return(result);
}
public List <Course> GetCourses()
{
List <Course> result = new List <Course>();
DbInfo dbInfo = new DbInfo();
SqlConnection connection = dbInfo.OpenConnection();
using (connection)
{
using (SqlCommand command = new SqlCommand("select title,description from Courses", connection))
{
using (SqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.HasRows)
{
int title = dataReader.GetOrdinal("title");
int description = dataReader.GetOrdinal("description");
while (dataReader.Read())
{
Course course = new Course();
course.title = dataReader.GetString(title);
course.description = dataReader.GetString(description);
result.Add(course);
}
}
}
}
}
return(result);
}
public List <Teacher> GetTeachers()
{
List <Teacher> result = new List <Teacher>();
DbInfo dbInfo = new DbInfo();
SqlConnection connection = dbInfo.OpenConnection();
using (connection)
{
using (SqlCommand command = new SqlCommand("select name,surname,SSN,title_course from " +
"Teachers left join CoursesTeachersRelation on " +
"Teachers.id=CoursesTeachersRelation.id_teacher", connection))
{
using (SqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.HasRows)
{
int name = dataReader.GetOrdinal("name");
int surname = dataReader.GetOrdinal("surname");
int SSN = dataReader.GetOrdinal("SSN");
int titleCourse = dataReader.GetOrdinal("title_course");
while (dataReader.Read())
{
Teacher teacher = new Teacher();
teacher.name = dataReader.GetString(name);
teacher.surname = dataReader.GetString(surname);
teacher.SSN = dataReader.GetString(SSN);
teacher.course = dataReader.GetString(titleCourse);
result.Add(teacher);
}
}
}
}
}
return(result);
}
public List <StudentGrades> ShowStudentGrades(string ssnStudent)
{
List <StudentGrades> result = new List <StudentGrades>();
DbInfo dbInfo = new DbInfo();
SqlConnection connection = dbInfo.OpenConnection();
using (connection)
{
using (SqlCommand command = new SqlCommand(String.Format("select title_course,grade from Students " +
"join CoursesStudentsRelation on Students.id = CoursesStudentsRelation.id_student " +
"where SSN = '{0}'", ssnStudent), connection))
{
using (SqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.HasRows)
{
int course = dataReader.GetOrdinal("title_course");
int grade = dataReader.GetOrdinal("grade");
while (dataReader.Read())
{
StudentGrades studentGrades = new StudentGrades();
studentGrades.course = dataReader.GetString(course);
if (dataReader.IsDBNull(grade))
{
studentGrades.grade = null;
}
else
{
studentGrades.grade = dataReader.GetDouble(grade);
}
result.Add(studentGrades);
}
}
}
}
}
return(result);
}
public List <AllStudentsInSingleCourse> AllStudentsInCourse(string courseName)
{
List <AllStudentsInSingleCourse> result = new List <AllStudentsInSingleCourse>();
DbInfo dbInfo = new DbInfo();
SqlConnection connection = dbInfo.OpenConnection();
using (connection)
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "select name,surname,SSN,grade from Students join " +
"CoursesStudentsRelation on Students.id=CoursesStudentsRelation.id_student " +
"where title_course='" + courseName + "'";
using (SqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.HasRows)
{
int name = dataReader.GetOrdinal("name");
int surname = dataReader.GetOrdinal("surname");
int SSN = dataReader.GetOrdinal("SSN");
int grade = dataReader.GetOrdinal("grade");
while (dataReader.Read())
{
AllStudentsInSingleCourse temp = new AllStudentsInSingleCourse();
temp.name = dataReader.GetString(name);
temp.surname = dataReader.GetString(surname);
temp.SSN = dataReader.GetString(SSN);
temp.grade = dataReader.GetDouble(grade);
result.Add(temp);
}
}
}
}
}
return(result);
}