// My (overly simplified way) of assigning an existing exercise to an existing student. // INSERT a new ExerciseIntersection public void AddExerciseIntersection(ExerciseIntersection exerciseIntersection) { using (SqlConnection conn = Connection) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = @"INSERT INTO ExerciseIntersection VALUES (@studentId, @exerciseId)"; cmd.Parameters.Add(new SqlParameter("@studentId", exerciseIntersection.StudentId)); cmd.Parameters.Add(new SqlParameter("@exerciseId", exerciseIntersection.ExerciseId)); cmd.ExecuteNonQuery(); } } }
// THEN FIND all Exercise Intersection in the Table - Adding the Student Names and Exercise Name // HOWEVER The problem with me making Exercise intersection the main table is I don't see students // without exercises - Where NULL - SO SEE BOTTOM FOR ANDY'S CODE // ********** THIS DOESN'T Work public List <ExerciseIntersection> GetAllExerciseIntersections() { using (SqlConnection conn = Connection) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = @"SELECT s.StudentFirstName, s.StudentLastName, e.ExerciseName, c.CohortName From ExerciseIntersection j RIGHT JOIN Student s on j.StudentId = s.id RIGHT JOIN Exercise e on j.ExerciseId = e.id LEFT JOIN Cohort c on s.CohortId = c.id"; SqlDataReader reader = cmd.ExecuteReader(); List <ExerciseIntersection> exerciseIntersections = new List <ExerciseIntersection>(); while (reader.Read()) { ExerciseIntersection exerciseIntersection = new ExerciseIntersection() { StudentId = reader.GetInt32(reader.GetOrdinal("Id")), Student = new Student { Id = reader.GetInt32(reader.GetOrdinal("StudentId")), StudentFirstName = reader.GetString(reader.GetOrdinal("StudentFirstName")), StudentLastName = reader.GetString(reader.GetOrdinal("StudentLastName")), }, ExerciseId = reader.GetInt32(reader.GetOrdinal("ExerciseId")), Exercise = new Exercise { Id = reader.GetInt32(reader.GetOrdinal("Id")), ExerciseName = reader.GetString(reader.GetOrdinal("ExerciseName")), } }; exerciseIntersections.Add(exerciseIntersection); } reader.Close(); return(exerciseIntersections); } } }
static void Main(string[] args) { // We must create an instance of the Repository class in order to use it's methods to // interact with the database. Repository repository = new Repository(); // ********** Place where 1. calling functions in repository and 2. defining methods? ********** // 1. Query the database for all the Exercises. List <Exercise> exercises = repository.GetAllExercises(); PrintExerciseReport("All Exercises", exercises); Pause(); // 2. Find all the exercises in the database where the language is JavaScript. List <Exercise> jExercises = repository.GetAllJavaScriptExercises("JavaScript"); PrintExerciseReport("Only JavaScript Exercises", jExercises); Pause(); // 3. Insert a new exercise into the Exercise table Exercise nutshell = new Exercise() { ExerciseName = "Nutshell", Language = "React" }; repository.AddExercise(nutshell); exercises = repository.GetAllExercises(); PrintExerciseReport("All Exercises after adding one", exercises); Pause(); // 4. Find all instructors in the database. Include each instructor's cohort // Seems easier to print to Console than create a unique report for each query like PrintExerciseReport?? So ... List <Instructor> instructors = repository.GetAllInstructorsWithCohort(); Console.WriteLine("All Instructors With Cohort"); foreach (Instructor i in instructors) { Console.WriteLine($"{i.InstructorFirstName} {i.InstructorLastName} is in {i.Cohort.CohortName}"); } Pause(); //Insert a new instructor into the database. Assign the instructor to an existing cohort Instructor joeShmoe = new Instructor() { InstructorFirstName = "Joe", InstructorLastName = "Shmoe", InstructorSlackHandle = "JoeShmoe@Nss", CohortId = 3 }; repository.AddInstructor(joeShmoe); // List<Instructor> instructors = repository.GetAllInstructorsWithCohort(); Console.WriteLine("All Instructors With Cohort after adding one"); foreach (Instructor i in repository.GetAllInstructorsWithCohort()) { Console.WriteLine($"{i.InstructorFirstName} {i.InstructorLastName} is in {i.Cohort.CohortName}"); } ; Pause(); // Assign an existing exercise to an existing student ExerciseIntersection newInt = new ExerciseIntersection() { ExerciseId = 3, StudentId = 4 }; repository.AddExerciseIntersection(newInt); Console.WriteLine("Assigned an Exercise to an existing student"); Pause(); /* THIS DOESN'T WORK * * // List<ExerciseIntersection> exerciseIntersections = repository.GetAllExerciseIntersections();???? * Console.WriteLine("List of Exercise Intersections"); * * // foreach (ExerciseIntersection j in exerciseIntersections) * foreach (ExerciseIntersection j in repository.GetAllExerciseIntersections()) * { * Console.WriteLine($"{j.ExerciseId} {j.Exercise} {j.StudentId} {j.Student}"); * }; */ Pause(); }