private void Apply_Button_Click(object sender, EventArgs e) { try { this.connetionString = "Data Source = whitesnow.database.windows.net; Initial Catalog = Mazal; Integrated Security = False; User ID = Grimm; Password = #!7Dwarfs; Connect Timeout = 15; Encrypt = False; TrustServerCertificate = True; ApplicationIntent = ReadWrite; MultiSubnetFailover = False; MultipleActiveResultSets=true"; this.sqlcon = new SqlConnection(connetionString); this.sqlcon.Open(); checkString(CourseIDButton.Text, "Course"); checkString(StartTimeButton.Text, "Start"); checkString(DayButton.Text, "Day"); checkString(SemesterButton.Text, "Semester"); checkString(ClassButton.Text, "Class"); checkString(LabExercise.Text, "Lab_Exercise"); int first, second, third; string firstString, secondString, thirdString; first = Convert.ToInt32(StartTimeButton.Text); second = Convert.ToInt32(StartTimeButton.Text) + 1; third = Convert.ToInt32(StartTimeButton.Text) + 2; firstString = first.ToString(); secondString = second.ToString(); thirdString = third.ToString(); int end = Convert.ToInt32(StartTimeButton.Text) + 2; string endString = end.ToString(); // check if the course and semester are as needed. SqlCommand cmd = new SqlCommand("select * from courses where Course_id = '" + CourseIDButton.Text + "' and Semester = '" + SemesterButton.Text + "'", sqlcon); SqlDataReader dre = cmd.ExecuteReader(); if (dre.Read() == false) { throw new ArgumentException("The course and the semester are not good!"); } // check if the teaching assistant already teaches this class. dre.Close(); cmd = new SqlCommand("select * from Teaching_Stuff where ID = '" + Utility.User.ID + "' and Course_id = '" + CourseIDButton.Text + "'", sqlcon); dre = cmd.ExecuteReader(); if (dre.Read()) { throw new ArgumentException("You have already a course with this id!"); } dre.Close(); // check if the teacher is already occupied during this time. cmd = new SqlCommand("select * from Lecture_Course where Teacher ='" + Utility.User.ID + "' and Date = '" + DayButton.Text + "' and Start_time = '" + firstString + "'", sqlcon); dre = cmd.ExecuteReader(); if (dre.Read() == true) { throw new ArgumentException("You already teach a class during this time!"); } dre.Close(); // check if the teacher is already occupied during this time. cmd = new SqlCommand("select * from Lecture_Course where Teacher ='" + Utility.User.ID + "' and Date = '" + DayButton.Text + "' and Start_time = '" + secondString + "'", sqlcon); dre = cmd.ExecuteReader(); if (dre.Read() == true) { throw new ArgumentException("You already teach a class during this time!"); } dre.Close(); // check if the class is already occupied in this time and class. if (SemesterButton.Text.Equals("a")) { if (first == 8) { cmd = new SqlCommand("select * from Classes_SM1 where day = '" + DayButton.Text + "' and Class_Id = '" + ClassButton.Text + "' and [0" + firstString + "-0" + secondString + "]= 1 and [0" + secondString + "-" + thirdString + "]= 1", sqlcon); } else if (first == 9) { cmd = new SqlCommand("select * from Classes_SM1 where day = '" + DayButton.Text + "' and Class_Id = '" + ClassButton.Text + "' and [0" + firstString + "-" + secondString + "]= 1 and [" + secondString + "-" + thirdString + "]= 1", sqlcon); } else { cmd = new SqlCommand("select * from Classes_SM1 where day = '" + DayButton.Text + "' and Class_Id = '" + ClassButton.Text + "' and [" + firstString + "-" + secondString + "]= 1 and [" + secondString + "-" + thirdString + "]= 1", sqlcon); } } else if (SemesterButton.Text.Equals("b")) { if (first == 8) { cmd = new SqlCommand("select * from Classes_SM2 where day = '" + DayButton.Text + "' and Class_Id = '" + ClassButton.Text + "' and [0" + firstString + "-0" + secondString + "]= 1 and [0" + secondString + "-" + thirdString + "]= 1", sqlcon); } else if (first == 9) { cmd = new SqlCommand("select * from Classes_SM2 where day = '" + DayButton.Text + "' and Class_Id = '" + ClassButton.Text + "' and [0" + firstString + "-" + secondString + "]= 1 and [" + secondString + "-" + thirdString + "]= 1", sqlcon); } else { cmd = new SqlCommand("select * from Classes_SM2 where day = '" + DayButton.Text + "' and Class_Id = '" + ClassButton.Text + "' and [" + firstString + "-" + secondString + "]= 1 and [" + secondString + "-" + thirdString + "]= 1", sqlcon); } } dre = cmd.ExecuteReader(); if (dre.Read() == true) { throw new ArgumentException("The class you have chosen is already occupied!"); } dre.Close(); // check if needed to add lab. if (LabExercise.Text.Equals("Lab")) { // check if someone teaches this lab. cmd = new SqlCommand("select * from Lecture_Course where Course_ID = '" + CourseIDButton.Text + "' and Course_type = 3", sqlcon); dre = cmd.ExecuteReader(); if (dre.Read() == true) { int num = 1; using (Entities db = new Entities()) { foreach (Lecture_Course l in db.Lecture_Course) { if (l.Course_ID == Convert.ToInt32(CourseIDButton.Text) && l.Course_type == 3) { num = l.Course_Serial + 1; } } dre.Close(); SqlCommand cmd4 = new SqlCommand("insert into Lecture_Course (Course_ID, Course_Serial, Course_type, Teacher, Date, Start_time, End_time, Class_number, Student_Count) Values('" + CourseIDButton.Text + "', '" + num + "', 3, '" + Utility.User.ID + "', '" + DayButton.Text + "', '" + StartTimeButton.Text + "', '" + endString + "', '" + ClassButton.Text + "', 0) ; ", sqlcon); dre = cmd4.ExecuteReader(); dre.Close(); // insert your course to data base teaching stuff cmd = new SqlCommand("insert into Teaching_Stuff (ID, Course_id) Values ('" + Utility.User.ID + "', '" + CourseIDButton.Text + "' ) ; ", sqlcon); dre = cmd.ExecuteReader(); dre.Close(); } } else { // insert to lecture course the data of the lab. dre.Close(); SqlCommand cmd3 = new SqlCommand("insert into Lecture_Course (Course_ID, Course_Serial, Course_type, Teacher, Date, Start_time, End_time, Class_number, Student_Count) Values('" + CourseIDButton.Text + "', '" + CourseIDButton.Text + "1', 3, '" + Utility.User.ID + "', '" + DayButton.Text + "', '" + StartTimeButton.Text + "', '" + endString + "', '" + ClassButton.Text + "', 0) ; ", sqlcon); dre = cmd3.ExecuteReader(); dre.Close(); // insert your course to data base teaching stuff cmd = new SqlCommand("insert into Teaching_Stuff (ID, Course_id) Values ('" + Utility.User.ID + "', '" + CourseIDButton.Text + "' ) ; ", sqlcon); dre = cmd.ExecuteReader(); dre.Close(); } } // check if needed to add exercise. else if (LabExercise.Text.Equals("Exercise")) { // check if someone teaches this exercise. cmd = new SqlCommand("select * from Lecture_Course where Course_ID = '" + CourseIDButton.Text + "' and Course_type = 2", sqlcon); dre = cmd.ExecuteReader(); if (dre.Read() == true) { int num = 1; using (Entities db = new Entities()) { foreach (Lecture_Course l in db.Lecture_Course) { if (l.Course_ID == Convert.ToInt32(CourseIDButton.Text) && l.Course_type == 2) { num = l.Course_Serial + 1; } } dre.Close(); SqlCommand cmd4 = new SqlCommand("insert into Lecture_Course (Course_ID, Course_Serial, Course_type, Teacher, Date, Start_time, End_time, Class_number, Student_Count) Values('" + CourseIDButton.Text + "', '" + num + "', 2, '" + Utility.User.ID + "', '" + DayButton.Text + "', '" + StartTimeButton.Text + "', '" + endString + "', '" + ClassButton.Text + "', 0) ; ", sqlcon); dre = cmd4.ExecuteReader(); dre.Close(); // insert your course to data base teaching stuff cmd = new SqlCommand("insert into Teaching_Stuff (ID, Course_id) Values ('" + Utility.User.ID + "', '" + CourseIDButton.Text + "' ) ; ", sqlcon); dre = cmd.ExecuteReader(); dre.Close(); } } else { // insert to lecture course the data of the exercise. dre.Close(); SqlCommand cmd4 = new SqlCommand("insert into Lecture_Course (Course_ID, Course_Serial, Course_type, Teacher, Date, Start_time, End_time, Class_number, Student_Count) Values('" + CourseIDButton.Text + "', '" + CourseIDButton.Text + "1', 2, '" + Utility.User.ID + "', '" + DayButton.Text + "', '" + StartTimeButton.Text + "', '" + endString + "', '" + ClassButton.Text + "', 0) ; ", sqlcon); dre = cmd4.ExecuteReader(); dre.Close(); // insert your course to data base teaching stuff cmd = new SqlCommand("insert into Teaching_Stuff (ID, Course_id) Values ('" + Utility.User.ID + "', '" + CourseIDButton.Text + "' ) ; ", sqlcon); dre = cmd.ExecuteReader(); dre.Close(); } } else { throw new ArgumentException("Error choosing option!"); } // take specific date, start time and class number from lecture course. cmd = new SqlCommand("select Date, Start_time, Class_number from Lecture_Course where Teacher = '" + Utility.User.ID + "' and Course_ID = '" + CourseIDButton.Text + "'", sqlcon); SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { SqlCommand cmd1, cmd2; // update the database of the classes according to the course. if (SemesterButton.Text.Equals("a")) { if (first == 8) { cmd1 = new SqlCommand("update Classes_SM1 set [0" + firstString + "-0" + secondString + "]= 1 where day ='" + dr[0].ToString() + "' and Class_Id = '" + dr[2].ToString() + "' and holiday = 0", sqlcon); cmd2 = new SqlCommand("update Classes_SM1 set [0" + secondString + "-" + thirdString + "]= 1 where day ='" + dr[0].ToString() + "' and Class_Id = '" + dr[2].ToString() + "' and holiday = 0", sqlcon); } else if (first == 9) { cmd1 = new SqlCommand("update Classes_SM1 set [0" + firstString + "-" + secondString + "]= 1 where day ='" + dr[0].ToString() + "' and Class_Id = '" + dr[2].ToString() + "' and holiday = 0", sqlcon); cmd2 = new SqlCommand("update Classes_SM1 set [" + secondString + "-" + thirdString + "]= 1 where day ='" + dr[0].ToString() + "' and Class_Id = '" + dr[2].ToString() + "' and holiday = 0", sqlcon); } else { cmd1 = new SqlCommand("update Classes_SM1 set [" + firstString + "-" + secondString + "]= 1 where day ='" + dr[0].ToString() + "' and Class_Id = '" + dr[2].ToString() + "' and holiday = 0", sqlcon); cmd2 = new SqlCommand("update Classes_SM1 set [" + secondString + "-" + thirdString + "]= 1 where day ='" + dr[0].ToString() + "' and Class_Id = '" + dr[2].ToString() + "' and holiday = 0", sqlcon); } SqlDataAdapter sda1 = new SqlDataAdapter(); sda1.SelectCommand = cmd1; SqlDataAdapter sda2 = new SqlDataAdapter(); sda2.SelectCommand = cmd2; DataTable dbdataset = new DataTable(); sda1.Fill(dbdataset); sda2.Fill(dbdataset); BindingSource bsource = new BindingSource(); bsource.DataSource = dbdataset; sda1.Update(dbdataset); sda2.Update(dbdataset); } else if (SemesterButton.Text.Equals("b")) { if (first == 8) { cmd1 = new SqlCommand("update Classes_SM2 set [0" + firstString + "-0" + secondString + "]= 1 where day ='" + dr[0].ToString() + "' and Class_Id = '" + dr[2].ToString() + "' and holiday = 0", sqlcon); cmd2 = new SqlCommand("update Classes_SM2 set [0" + secondString + "-" + thirdString + "]= 1 where day ='" + dr[0].ToString() + "' and Class_Id = '" + dr[2].ToString() + "' and holiday = 0", sqlcon); } else if (first == 9) { cmd1 = new SqlCommand("update Classes_SM2 set [0" + firstString + "-" + secondString + "]= 1 where day ='" + dr[0].ToString() + "' and Class_Id = '" + dr[2].ToString() + "' and holiday = 0", sqlcon); cmd2 = new SqlCommand("update Classes_SM2 set [" + secondString + "-" + thirdString + "]= 1 where day ='" + dr[0].ToString() + "' and Class_Id = '" + dr[2].ToString() + "' and holiday = 0", sqlcon); } else { cmd1 = new SqlCommand("update Classes_SM2 set [" + firstString + "-" + secondString + "]= 1 where day ='" + dr[0].ToString() + "' and Class_Id = '" + dr[2].ToString() + "' and holiday = 0", sqlcon); cmd2 = new SqlCommand("update Classes_SM2 set [" + secondString + "-" + thirdString + "]= 1 where day ='" + dr[0].ToString() + "' and Class_Id = '" + dr[2].ToString() + "' and holiday = 0", sqlcon); } SqlDataAdapter sda1 = new SqlDataAdapter(); sda1.SelectCommand = cmd1; SqlDataAdapter sda2 = new SqlDataAdapter(); sda2.SelectCommand = cmd2; DataTable dbdataset = new DataTable(); sda1.Fill(dbdataset); sda2.Fill(dbdataset); BindingSource bsource = new BindingSource(); bsource.DataSource = dbdataset; sda1.Update(dbdataset); sda2.Update(dbdataset); } } dr.Close(); MessageBox.Show("Added succesfully!"); } catch (Exception ex) { MessageBox.Show(ex.Message); this.Close(); AddLabExercises form2 = new AddLabExercises(); form2.Show(); } this.sqlcon.Close(); this.Close(); }
private void Logout_Button_Click(object sender, EventArgs e) { AddLabExercises rec = new AddLabExercises(); rec.Show(); }