Esempio n. 1
0
        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();
        }
Esempio n. 2
0
        private void Logout_Button_Click(object sender, EventArgs e)
        {
            AddLabExercises rec = new AddLabExercises();

            rec.Show();
        }