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