public static void InsertStudentperCourse() { Select.selectCourses(); Console.WriteLine("Choose one course id: "); int courseid = Convert.ToInt32(Console.ReadLine()); Select.selectStudents(); Console.WriteLine("Choose one student id: "); int studentid = Convert.ToInt32(Console.ReadLine()); //INSERT STUDENT_COURSE string connectionString = @"Data Source=DESKTOP-JBG6OHV\SQLEXPRESS;Initial Catalog=SCHOOL;Integrated Security=True"; 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 id 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); } }
public static void InsertTrainer() { //INSERT TRAINER & INSERT TRAINERS PER COURSE string connectionString = @"Data Source=DESKTOP-JBG6OHV\SQLEXPRESS;Initial Catalog=SCHOOL;Integrated Security=True"; string sqlqr = "INSERT INTO TRAINER (IDTRAINER,FIRSTNAME,LASTNAME,SUBJECT,COURSEID) VALUES(@id,@fname,@lname,@subject,@idc)"; string sqlqr1 = "INSERT INTO TRAINER (IDTRAINER,FIRSTNAME,LASTNAME,SUBJECT) VALUES(@id1,@fname1,@lname1,@subject1)"; string sqlqrtest = "SELECT COUNT(*) FROM TRAINER WHERE IDTRAINER= @id2"; try { using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); Console.WriteLine("If you want to pair a trainer with a course press 1 else press 2:"); int option = Convert.ToInt32(Console.ReadLine()); if (option == 1) { Console.WriteLine("Enter Trainer ID: "); int id = Convert.ToInt32(Console.ReadLine()); SqlCommand cmfinder = new SqlCommand(sqlqrtest, cn); cmfinder.Parameters.Add(new SqlParameter("id2", id)); int count = (int)cmfinder.ExecuteScalar(); if (count >= 1) { throw new Exception("This id already exists."); } Console.WriteLine("Enter firstname: "); string fname = Console.ReadLine(); Console.WriteLine("Enter lastname: "); string lname = Console.ReadLine(); Console.WriteLine("Enter subject: "); string subject = Console.ReadLine(); Select.selectCourses(); Console.WriteLine(); Console.WriteLine(); Console.WriteLine("Enter Course id: "); int idC = Convert.ToInt32(Console.ReadLine()); SqlCommand cm = new SqlCommand(sqlqr, cn); cm.Parameters.Add(new SqlParameter("@id", id)); cm.Parameters.Add(new SqlParameter("fname", fname)); cm.Parameters.Add(new SqlParameter("lname", lname)); cm.Parameters.Add(new SqlParameter("subject", subject)); cm.Parameters.Add(new SqlParameter("idc", idC)); cm.ExecuteNonQuery(); } else if (option == 2) { Console.WriteLine("Enter Trainer ID: "); int id1 = Convert.ToInt32(Console.ReadLine()); SqlCommand cmfinder = new SqlCommand(sqlqrtest, cn); cmfinder.Parameters.Add(new SqlParameter("id2", id1)); int count = (int)cmfinder.ExecuteScalar(); if (count >= 1) { throw new Exception("This id already exists."); } Console.WriteLine("Enter firstname: "); string fname1 = Console.ReadLine(); Console.WriteLine("Enter lastname: "); string lname1 = Console.ReadLine(); Console.WriteLine("Enter subject: "); string subject1 = Console.ReadLine(); SqlCommand cm = new SqlCommand(sqlqr1, cn); cm.Parameters.Add(new SqlParameter("id1", id1)); cm.Parameters.Add(new SqlParameter("fname1", fname1)); cm.Parameters.Add(new SqlParameter("lname1", lname1)); cm.Parameters.Add(new SqlParameter("subject1", subject1)); 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; } } }