// 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();
}
