public static void InsertAssignmentperStudentperCourse() { Select.selectAssignments(); Console.WriteLine("Choose one assignment id: "); int assignmentid = Convert.ToInt32(Console.ReadLine()); Select.selectCourses(); Console.WriteLine("Choose one course id: "); int courseid = Convert.ToInt32(Console.ReadLine()); string connectionString = @"Data Source=DESKTOP-JBG6OHV\SQLEXPRESS;Initial Catalog=SCHOOL;Integrated Security=True"; //INSERT ASSIGNMENT_COURSE string sqlqrCA = "INSERT INTO COURSE_ASSIGNMENT (IDC,IDA) VALUES(@courid,@assid)"; string sqlqrtest1 = "SELECT COUNT(*) FROM COURSE_ASSIGNMENT WHERE IDC=@idc1 AND IDA=@ida1"; try { using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); SqlCommand cmfinder = new SqlCommand(sqlqrtest1, cn); cmfinder.Parameters.Add(new SqlParameter("idc1", courseid)); cmfinder.Parameters.Add(new SqlParameter("ida1", assignmentid)); int count = (int)cmfinder.ExecuteScalar(); if (count >= 1) { throw new Exception("This assignment per course already exists."); } SqlCommand cm = new SqlCommand(sqlqrCA, cn); cm.Parameters.Add(new SqlParameter("courid", courseid)); cm.Parameters.Add(new SqlParameter("assid", assignmentid)); cm.ExecuteNonQuery(); } } catch (Exception ex) { Console.WriteLine(ex.Message); } Select.selectStudents(); Console.WriteLine("Choose one student id: "); int studentid = Convert.ToInt32(Console.ReadLine()); //INSERT STUDENT_COURSE string sqlqr = "INSERT INTO COURSE_STUDENT(CID,SID) VALUES(@cid,@sid)"; string sqlqrtest = "SELECT COUNT(*) FROM COURSE_STUDENT WHERE CID=@idc AND SID=@ids"; try { using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); SqlCommand cmfinder = new SqlCommand(sqlqrtest, cn); cmfinder.Parameters.Add(new SqlParameter("idc", courseid)); cmfinder.Parameters.Add(new SqlParameter("ids", studentid)); int count = (int)cmfinder.ExecuteScalar(); if (count >= 1) { throw new Exception("This student per course already exists."); } SqlCommand cm = new SqlCommand(sqlqr, cn); cm.Parameters.Add(new SqlParameter("cid", courseid)); cm.Parameters.Add(new SqlParameter("sid", studentid)); cm.ExecuteNonQuery(); } } catch (Exception ex) { Console.WriteLine(ex.Message); } }
static void Main(string[] args) { int option = 0; while (option != 3) { Console.WriteLine(); Console.WriteLine("-------SCHOOL MENU-------"); Console.WriteLine("Choose 1-3 for: "); Console.WriteLine("1.View data"); Console.WriteLine("2.Insert data"); Console.WriteLine("3.Exit"); Console.WriteLine(); option = Convert.ToInt32(Console.ReadLine()); switch (option) { case 1: option = 0; Console.WriteLine(); Console.WriteLine("------------view data-------------"); Console.WriteLine("Choose 1-9 for: "); Console.WriteLine("1.Student"); Console.WriteLine("2.Trainer"); Console.WriteLine("3.Assigment"); Console.WriteLine("4.Course"); Console.WriteLine("5.Students per course"); Console.WriteLine("6.Trainers per course"); Console.WriteLine("7.Assigments per course"); Console.WriteLine("8.Assigments per student"); Console.WriteLine("9.Students that belongs to more than one course"); Console.WriteLine(); option = Convert.ToInt32(Console.ReadLine()); switch (option) { case (1): Select.selectStudents(); break; case (2): Select.selectTrainers(); break; case (3): option = 0; Select.selectAssignments(); break; case (4): Select.selectCourses(); break; case (5): Select.selectStudentsperCourse(); break; case (6): Select.selectTrainersperCourse(); break; case (7): Select.selectAssignmentsperCourse(); break; case (8): Select.selectAssignmentsperCourseperStudent(); break; case (9): Select.selectStudentsBelongsToMoreThanOneCourse(); break; default: Console.WriteLine("Something went wrong"); break; } break; case 2: option = 0; Console.WriteLine(); Console.WriteLine("------------insert data-------------"); Console.WriteLine("Choose 1-7 for: "); Console.WriteLine("1.Student"); Console.WriteLine("2.Trainer"); Console.WriteLine("3.Assigment"); Console.WriteLine("4.Course"); Console.WriteLine("5.Students per course"); Console.WriteLine("6.Trainers per course"); Console.WriteLine("7.Assigments per student"); Console.WriteLine(); option = Convert.ToInt32(Console.ReadLine()); switch (option) { case (1): Insert.InsertStudent(); break; case (2): Insert.InsertTrainer(); break; case (3): option = 0; Insert.InsertAssignment(); break; case (4): Insert.InsertCourse(); break; case (5): Insert.InsertStudentperCourse(); break; case (6): Insert.InsertTrainer(); break; case (7): Insert.InsertAssignmentperStudentperCourse(); break; default: Console.WriteLine("Something went wrong"); break; } break; } } }