public bool Delete(string name, string surname, string SSN) { bool result; DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "select count(*) from Students where SSN='" + SSN + "'"; int countStudents = (int)command.ExecuteScalar(); if (countStudents != 0) { command.CommandText = String.Format("select id from Students where SSN='{0}' and name='{1}' and surname='{2}'", SSN, name, surname); command.ExecuteNonQuery(); SqlDataReader sqlDataReader = command.ExecuteReader(); sqlDataReader.Read(); string idStudent = sqlDataReader.GetInt32(0).ToString(); sqlDataReader.Close(); command.CommandText = String.Format("delete from CoursesStudentsRelation where id_student='{0}'", idStudent); command.ExecuteNonQuery(); command.CommandText = String.Format("delete from Students where id='{0}'", idStudent); command.ExecuteNonQuery(); result = true; } else { result = false; } } } return(result); }
public bool Insert(string name, string surname, string ssn) { bool result; DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = connection.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "select count(*) from Students where SSN='" + ssn + "'"; int countStudents = (int)command.ExecuteScalar(); if (countStudents == 0) { command.CommandText = "insert into Students values('" + name + "','" + surname + "','" + ssn + "')"; command.ExecuteNonQuery(); result = true; } else { result = false; } } } 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 int UpdateGrade(string studentSsn, string courseName, double grade) { DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = new SqlCommand("select count(*) from Students where SSN='" + studentSsn + "'", connection)) { int existStudent = (int)command.ExecuteScalar(); if (existStudent == 0) { return(1); } command.CommandType = CommandType.Text; command.CommandText = "select count(*) from Courses where title='" + courseName + "'"; int existCourse = (int)command.ExecuteScalar(); if (existCourse == 0) { return(2); } command.CommandText = "select id from Students where SSN = '" + studentSsn + "'"; string idStudent = command.ExecuteScalar().ToString(); command.CommandText = String.Format("update CoursesStudentsRelation " + "set grade = {0} where id_student = '{1}' " + "and title_course = '{2}'", grade, idStudent, courseName); int affected = command.ExecuteNonQuery(); if (affected == 0) { return(3); } return(4); } } }
public Teacher GetOneTeacher(string teacherSsn) { Teacher result = new Teacher(); int count; DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = new SqlCommand()) { command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = System.String.Format("select count(*) from Teachers where SSN='{0}'", teacherSsn); count = (int)command.ExecuteScalar(); } } if (count == 0) { result.SSN = "---"; } else { result = GetTeachers().Where(teacher => teacher.SSN == teacherSsn).First(); } return(result); }
public bool Insert(string courseName, string topicName) { bool result; DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = connection.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "select count(*) from Courses where title='" + courseName + "'"; int countCourses = (int)command.ExecuteScalar(); if (countCourses == 0) { return(false); } command.CommandText = "select count(*) from Topics join Courses on Topics.course_title=Courses.title where course_title='" + courseName + "' and name='" + topicName + "'"; int countTopics = (int)command.ExecuteScalar(); if (countTopics == 0) { command.CommandText = "insert into Topics values('" + topicName + "','" + courseName + "')"; command.ExecuteNonQuery(); result = true; } else { result = false; } } } return(result); }
public Student GetOneStudent(string studentSsn) { Student result = new Student(); int count; DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = new SqlCommand()) { command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = System.String.Format("select count(*) from Students where SSN='{0}'", studentSsn); count = (int)command.ExecuteScalar(); } } if (count == 0) { result.SSN = "---"; } else { result = GetStudents().Where(student => student.SSN == studentSsn).First(); } return(result); }
public List <Course> FillComboBox(string SSN) { List <Course> result = new List <Course>(); DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = new SqlCommand("select id from Students where SSN='" + SSN + "'", connection)) { string idStudent = command.ExecuteScalar().ToString(); command.CommandType = CommandType.Text; command.CommandText = "select title from Courses where title not in " + "(select title_course from " + "CoursesStudentsRelation where id_student='" + idStudent + "')"; using (SqlDataReader dataReader = command.ExecuteReader()) { if (dataReader.HasRows) { int title = dataReader.GetOrdinal("title"); while (dataReader.Read()) { Course course = new Course(); course.title = dataReader.GetString(title); result.Add(course); } } } } } return(result); }
public bool Delete(string courseName, string topicName) { bool result; DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = connection.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "select count(*) from Topics where name='" + topicName + "' and course_title='" + courseName + "' "; int countTopics = (int)command.ExecuteScalar(); if (countTopics == 0) { result = false; } else { command.CommandText = "delete from Topics where name='" + topicName + "' and course_title='" + courseName + "'"; command.ExecuteNonQuery(); result = true; } } } return(result); }
public StudentGrades OneStudentGradeOneCourse(string studentSsn, string courseName) { StudentGrades result = new StudentGrades(); int count; DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = new SqlCommand()) { command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = System.String.Format("select count(*) from Students " + "join CoursesStudentsRelation on Students.id = CoursesStudentsRelation.id_student " + "where SSN = '{0}' and title_course='{1}'", studentSsn, courseName); count = (int)command.ExecuteScalar(); } } if (count == 0) { result.course = "aa"; result.grade = -100; } else { result = ShowStudentGrades(studentSsn).Where(studentGrades => studentGrades.course == courseName).First(); } return(result); }
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 <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 bool Insert(string title, string description) { bool result; DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = connection.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "select count(*) from Courses where title='" + title + "'"; int countCourse = (int)command.ExecuteScalar(); if (countCourse == 0) { command.CommandText = "insert into Courses values('" + title + "','" + description + "')"; command.ExecuteNonQuery(); result = true; } else { result = false; } } } return(result); }
public int Insert(string name, string surname, string ssn, string course) { int result; DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = connection.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "select count(id) from Teachers where exists(select * from " + "CoursesTeachersRelation where title_course= '" + course + "'" + " and name = '" + name + "' and surname = '" + surname + "' and CoursesTeachersRelation.id_teacher=Teachers.id and SSN='" + ssn + "')"; int numberRowsCourseTeacher = (int)command.ExecuteScalar(); if (numberRowsCourseTeacher != 0) { result = 1; } else { command.CommandText = "select count(id) from Teachers where name = '" + name + "'" + " and surname = '" + surname + "' and SSN='" + ssn + "'"; int numberRowsTeacher = (int)command.ExecuteScalar(); string idTeacher; if (numberRowsTeacher != 0) { command.CommandText = "select id from Teachers where name='" + name + "' and surname='" + surname + "'" + "and SSN='" + ssn + "'"; idTeacher = command.ExecuteScalar().ToString(); command.CommandText = "insert into CoursesTeachersRelation values('" + idTeacher + "','" + course + "')"; command.ExecuteNonQuery(); result = 2; } else { command.CommandText = "select count(*) from Teachers where SSN='" + ssn + "'"; int areSSN = (int)command.ExecuteScalar(); if (areSSN == 0) { command.CommandText = "insert into Teachers values('" + name + "','" + surname + "','" + ssn + "')"; command.ExecuteNonQuery(); command.CommandText = "select id from Teachers where name='" + name + "' and surname='" + surname + "'" + "and SSN='" + ssn + "'"; idTeacher = command.ExecuteScalar().ToString(); command.CommandText = "insert into CoursesTeachersRelation values('" + idTeacher + "','" + course + "')"; command.ExecuteNonQuery(); result = 3; } else { result = 4; } } } } } return(result); }
public bool Delete(string title) { bool result; DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = connection.CreateCommand()) { command.CommandType = CommandType.Text; List <string> courses = new List <string>(); command.CommandText = "select count(title) from Courses where title='" + title + "'"; int existCourse = (int)command.ExecuteScalar(); if (existCourse == 0) { return(result = false); } else { command.CommandText = "delete from Topics where course_title='" + title + "'"; command.ExecuteNonQuery(); command.CommandText = "delete from CoursesStudentsRelation where title_course='" + title + "'"; command.ExecuteNonQuery(); List <string> idTeachers = new List <string>(); command.CommandText = "select id_teacher from CoursesTeachersRelation where title_course='" + title + "'"; SqlDataReader sqlDataReader = command.ExecuteReader(); while (sqlDataReader.Read()) { idTeachers.Add(sqlDataReader["id_teacher"].ToString()); } sqlDataReader.Close(); command.CommandText = "delete from CoursesTeachersRelation where title_course='" + title + "'"; command.ExecuteNonQuery(); command.CommandType = CommandType.Text; foreach (string id in idTeachers) { command.CommandText = "delete from Teachers where id='" + id + "'"; command.ExecuteNonQuery(); } command.CommandText = "delete from Courses where title ='" + title + "'"; command.ExecuteNonQuery(); result = true; } } } return(result); }
public int Delete(string name, string surname, string ssn, string course) { int result; DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = connection.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "select count(id) from Teachers where name = '" + name + "'" + " and surname = '" + surname + "' and SSN='" + ssn + "'"; int numberRowsTeacher = (int)command.ExecuteScalar(); if (numberRowsTeacher == 0) { result = 1; } else { command.CommandText = "select id from Teachers where SSN='" + ssn + "'"; string idTeacher = command.ExecuteScalar().ToString(); command.CommandText = "delete from CoursesTeachersRelation where id_teacher='" + idTeacher + "' and title_course='" + course + "'"; int deletedrows = command.ExecuteNonQuery(); if (deletedrows == 0) { result = 2; } else { result = 3; } command.CommandText = "select count(*) from CoursesTeachersRelation where id_teacher='" + idTeacher + "'"; int numberRowsTeaching = (int)command.ExecuteScalar(); if (numberRowsTeaching == 0) { command.CommandText = "delete from Teachers where SSN='" + ssn + "'"; command.ExecuteNonQuery(); result = 4; } } } } 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); try { temp.grade = dataReader.GetDouble(grade); } catch (System.Data.SqlTypes.SqlNullValueException) { temp.grade = null; } result.Add(temp); } } } } } return(result); }
public bool IsExistTopic(string courseName, string topicName) { int count; DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = new SqlCommand(String.Format("select count(*) from Topics where course_title='{0}' and name='{1}'", courseName, topicName), connection)) { count = (int)command.ExecuteScalar(); } } if (count == 1) { return(true); } return(false); }
public bool IsExistCourse(string course) { DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = new SqlCommand()) { command.Connection = connection; command.CommandType = CommandType.Text; command.CommandText = System.String.Format("select count(*) from Courses where title='{0}'", course); int count = (int)command.ExecuteScalar(); if (count == 0) { return(false); } return(true); } } }
public int Unenrol(string SSN, string course) { int result; DbInfo dbInfo = new DbInfo(); SqlConnection connection = dbInfo.OpenConnection(); using (connection) { using (SqlCommand command = connection.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "select count(*) from Courses where title='" + course + "'"; int countCourse = (int)command.ExecuteScalar(); if (countCourse == 0) { result = 1; } else { command.CommandText = "select id from Students where SSN='" + SSN + "'"; string idStudent = command.ExecuteScalar().ToString(); command.CommandText = "select count(*) from CoursesStudentsRelation where title_course='" + course + "' and id_student='" + idStudent + "'"; int countNotEnrol = (int)command.ExecuteScalar(); if (countNotEnrol == 0) { result = 2; } else { command.CommandText = "delete from CoursesStudentsRelation where id_student='" + idStudent + "' and title_course='" + course + "'"; command.ExecuteNonQuery(); result = 3; } } } } 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); }