static void Main(string[] args) { // COHORTS Cohort Cohort26 = new Cohort() { CohortName = "Cohort 26", }; Cohort Cohort27 = new Cohort() { CohortName = "Cohort 27", }; Cohort Cohort28 = new Cohort() { CohortName = "Cohort 28", }; // STUDENTS Student Daniel = new Student() { FirstName = "Daniel", LastName = "Figueroa", SlackHandle = "Daniel Figueroa", StudentCohort = Cohort27, }; Student Ricky = new Student() { FirstName = "Ricky", LastName = "Bruner", SlackHandle = "Ricky Brunder", StudentCohort = Cohort27, }; Student Adelaide = new Student() { FirstName = "Adelaide", LastName = "Yoder", SlackHandle = "Adelaide Yoder", StudentCohort = Cohort26, }; Student Jennifer = new Student() { FirstName = "Jennifer", LastName = "Lawson", SlackHandle = "Jennifer Lawson", StudentCohort = Cohort26, }; Student John = new Student() { FirstName = "John", LastName = "Wood", SlackHandle = "John Wood", StudentCohort = Cohort28, }; // INSTRUCTORS Instructor Brenda = new Instructor() { FirstName = "Brenda", LastName = "Long", SlackHandle = "Brenda Long", InstructorCohort = Cohort27, }; Instructor Jisie = new Instructor() { FirstName = "Jisie", LastName = "David", SlackHandle = "Jisie David", InstructorCohort = Cohort26, }; Instructor Steve = new Instructor() { FirstName = "Steve", LastName = "Brownlee", SlackHandle = "Steve Brownlee", InstructorCohort = Cohort27, }; // ExerciseS Exercise ExerciseHTML = new Exercise() { ExerciseName = "HTML", ExerciseLanguage = "HTML" }; Exercise ExerciseSets = new Exercise() { ExerciseName = "Sets", ExerciseLanguage = "C#" }; Exercise ExerciseDictionaries = new Exercise() { ExerciseName = "Dictionaries", ExerciseLanguage = "C#" }; Exercise ExerciseNutshell = new Exercise() { ExerciseName = "Nutshell", ExerciseLanguage = "Javascript" }; // Assigning Excersises to Students Brenda.AssignStudent(ExerciseHTML, Daniel); Brenda.AssignStudent(ExerciseNutshell, Ricky); Brenda.AssignStudent(ExerciseDictionaries, Ricky); // For this Exercise, you need to create 4 new List instances to Program.cs: one to contain students, one to contain Exercises, one to contain instructors, and one to contain cohorts. List <Student> StudentList = new List <Student>(); StudentList.Add(Daniel); StudentList.Add(Ricky); StudentList.Add(Adelaide); StudentList.Add(Jennifer); StudentList.Add(John); List <Exercise> ExerciseList = new List <Exercise>(); ExerciseList.Add(ExerciseHTML); ExerciseList.Add(ExerciseSets); ExerciseList.Add(ExerciseDictionaries); ExerciseList.Add(ExerciseNutshell); List <Instructor> InstructorList = new List <Instructor>(); InstructorList.Add(Brenda); InstructorList.Add(Jisie); InstructorList.Add(Steve); List <Cohort> CohortList = new List <Cohort>(); CohortList.Add(Cohort26); CohortList.Add(Cohort27); CohortList.Add(Cohort28); // List exercises for the JavaScript language by using the Where() LINQ method. var filteredExercise = ExerciseList.Where(e => e.ExerciseLanguage == "Javascript").ToList(); foreach (Exercise e in filteredExercise) { Console.WriteLine($"{e.ExerciseName}"); } Console.WriteLine("--------------------------------------"); // List students in a particular cohort by using the Where() LINQ method. var filteredStudent = StudentList.Where(s => s.StudentCohort == Cohort27).ToList(); foreach (Student e in filteredStudent) { Console.WriteLine($"{e.FirstName} {e.LastName}"); } Console.WriteLine("--------------------------------------"); // List instructors in a particular cohort by using the Where() LINQ method. var filteredInstructor = InstructorList.Where(s => s.InstructorCohort == Cohort27).ToList(); foreach (Instructor e in filteredInstructor) { Console.WriteLine($"{e.FirstName} {e.LastName}"); } Console.WriteLine("--------------------------------------"); // Sort the students by their last name. var SortedByLastName = StudentList.OrderByDescending(b => b.LastName); foreach (Student e in SortedByLastName) { Console.WriteLine($"{e.FirstName} {e.LastName}"); } Console.WriteLine("--------------------------------------"); // Display any students that aren't working on any exercises var filteredStudent2 = StudentList.Where(e => e.StudentsExercises.Count() == 0).ToList(); foreach (Student e in filteredStudent2) { Console.WriteLine($"{e.FirstName} {e.LastName}"); } Console.WriteLine("--------------------------------------"); // Which student is working on the most exercises? Make sure one of your students has more exercises than the others. var filteredStudent3 = StudentList.OrderByDescending(e => e.StudentsExercises.Count).Take(1); foreach (Student e in filteredStudent3) { Console.Write($"{e.FirstName} {e.LastName}"); } ; // Could have also done this // var filteredStudent3 = StudentList.OrderByDescending(e => e.StudentsExercises.Count).First(); // Console.WriteLine($"{filteredStudent3.SlackHandle}"); Console.WriteLine("--------------------------------------"); //How many students in each cohort? // var results = from student in StudentList // group student.SlackHandle by student.StudentCohort into g // select new { Cohort = g.Key, Name = g.ToList() }; // foreach(var e in results) { // Console.WriteLine($"{e.Cohort.CohortName}: {e.Name}"); // } var results = StudentList.GroupBy(e => e.StudentCohort.CohortName); foreach (var item in results) { Console.WriteLine($"{item.Key} has {item.Count()}"); } Console.WriteLine("--------------------------------------"); SqliteConnection db = DatabaseInterface.Connection; DatabaseInterface.CheckExerciseTable(); // Query the database for all the Exercises. db.Query <Exercise>(@"SELECT * FROM Exercise") .ToList() .ForEach(ex => Console.WriteLine($"Exercise: {ex.ExerciseName}, written in {ex.ExerciseLanguage}")); Console.WriteLine("--------------------------------------"); // Find all the exercises in the database where the language is JavaScript. db.Query <Exercise>(@"SELECT * FROM Exercise") .Where(ex => ex.ExerciseLanguage == "Javascript") .ToList() .ForEach(ex => Console.WriteLine($"Exercise: {ex.ExerciseName}, written in {ex.ExerciseLanguage}")); Console.WriteLine("--------------------------------------"); // Insert a new exercise into the database. // db.Execute(@" // INSERT INTO Exercise (ExerciseName, ExerciseLanguage) // VALUES ('Map', 'Javascript')"); // db.Query<Exercise>(@"SELECT * FROM Exercise") // .Where(ex => ex.ExerciseLanguage == "Javascript") // .ToList() // .ForEach(ex => Console.WriteLine($"Exercise: {ex.ExerciseName}, written in {ex.ExerciseLanguage}")); Console.WriteLine("--------------------------------------"); // Find all instructors in the database. Include each instructor's cohort. IEnumerable <Instructor> instructor2 = db.Query <Instructor, Cohort, Instructor>(@" SELECT i.Id, i.FirstName, i.LastName, i.SlackHandle, i.InstructorCohortId, c.Id, c.CohortName FROM Instructor i JOIN Cohort c ON c.Id = i.InstructorCohortId", (instructor, cohort) => { instructor.InstructorCohort = cohort; return(instructor); }); foreach (Instructor ins in instructor2) { Console.WriteLine($"{ins.FirstName} {ins.LastName} teaches {ins.InstructorCohort.CohortName}"); } Console.WriteLine("--------------------------------------"); // Insert a new instructor into the database. Assign the instructor to an existing cohort. // db.Execute(@" // INSERT INTO Instructor (FirstName, LastName, SlackHandle, InstructorCohortId) // VALUES ('Andy', 'Collins', 'Andy Collins', 2)"); db.Query <Instructor, Cohort, Instructor>(@" SELECT i.Id, i.FirstName, i.LastName, i.SlackHandle, i.InstructorCohortId, c.Id, c.CohortName FROM Instructor i JOIN Cohort c ON c.Id = i.InstructorCohortId", (instructor, cohort) => { instructor.InstructorCohort = cohort; return(instructor); }).ToList().ForEach(ins => Console.WriteLine($"{ins.FirstName} {ins.LastName} teaches {ins.InstructorCohort.CohortName}")); Console.WriteLine("--------------------------------------"); // Assign an existing exercise to an existing student. DatabaseInterface.CheckAssignedExerciseTable(); // db.Execute(@" // INSERT INTO AssignedExercise (StudentId, ExerciseId) // VALUES ('2', '2'); // "); // Challenge - Find all the students in the database. Include each student's cohort AND each student's list of exercises. Dictionary <Student, List <Exercise> > CulminatedExercise = new Dictionary <Student, List <Exercise> >(); db.Query <Student, Cohort, Exercise, Student>(@" SELECT s.Id, s.FirstName, s.LastName, s.SlackHandle, s.StudentCohortId, c.Id, c.CohortName, e.Id, e.ExerciseName, e.ExerciseLanguage FROM Student s JOIN AssignedExercise ae ON ae.StudentId = s.Id JOIN Cohort c ON c.Id = s.StudentCohortId JOIN Exercise e ON e.Id = ae.ExerciseId", (student, cohort, exercise) => { if (!CulminatedExercise.ContainsKey(student)) { student.StudentCohort = cohort; CulminatedExercise[student] = new List <Exercise>(); } CulminatedExercise[student].Add(exercise); return(student); }); foreach (KeyValuePair <Student, List <Exercise> > ex in CulminatedExercise) { Console.WriteLine($"{ex.Key.SlackHandle} is in {ex.Key.StudentCohort.CohortName} and is working:"); foreach (Exercise item in ex.Value) { Console.WriteLine($"{item.ExerciseName} written in {item.ExerciseLanguage}"); } } }
static void Main(string[] args) { // Create exercises Exercise LittleDebbie = new Exercise() { ExerciseName = "Little Debbie", Language = "JavaScript" }; Exercise Probes = new Exercise() { ExerciseName = "Probes", Language = "C#" }; Exercise Nutshell = new Exercise() { ExerciseName = "Nutshell", Language = "Alluvem" }; // Create cohorts Cohort Twenty6 = new Cohort() { CohortName = "26", }; Cohort Twenty7 = new Cohort() { CohortName = "27" }; Cohort Twenty8 = new Cohort() { CohortName = "28" }; // Create students Student Mike = new Student() { StudentFirstName = "Michael", StudentLastName = "McClenton", StudentSlackId = "@night", Cohort = Twenty6, }; // Add me to the student list inside Cohort.cs Twenty7.studentsList.Add(Mike); Student John = new Student() { StudentFirstName = "John", StudentLastName = "Wood", StudentSlackId = "@JohnWood", Cohort = Twenty8, }; // Add to 28 Twenty8.studentsList.Add(John); Student Dez = new Student() { StudentFirstName = "Desmond", StudentLastName = "N`uts", StudentSlackId = "@dez", Cohort = Twenty7, }; Twenty7.studentsList.Add(Dez); Student Bob = new Student() { StudentFirstName = "Bob", StudentLastName = "Saggett", StudentSlackId = "@Saggett", Cohort = Twenty6, }; Twenty6.studentsList.Add(Bob); Student Sonny = new Student() { StudentFirstName = "Sonny", StudentLastName = "Cheeba", StudentSlackId = "@Cheeba", Cohort = Twenty6, }; Twenty6.studentsList.Add(Sonny); // Create instructors Instructor Meg = new Instructor() { InstFirstName = "Meg", InstLasttName = "Ducharme", InstSlackHandle = "@meg", InstCohort = "27" }; Instructor Steve = new Instructor() { InstFirstName = "Steve", InstLasttName = "Brownlee", InstSlackHandle = "@coach", InstCohort = "26" }; Instructor Jenna = new Instructor() { InstFirstName = "Jenna", InstLasttName = "Solis", InstSlackHandle = "@jenna", InstCohort = "28" }; Instructor Brenda = new Instructor() { InstFirstName = "Brenda", InstLasttName = "Long", InstSlackHandle = "@bLong", InstCohort = "27" }; // Give students assignments from instructors Meg.GiveAssignment(LittleDebbie, Mike); Meg.GiveAssignment(Probes, Mike); Meg.GiveAssignment(Nutshell, Mike); Brenda.GiveAssignment(LittleDebbie, John); Brenda.GiveAssignment(Nutshell, John); Steve.GiveAssignment(Probes, Bob); Steve.GiveAssignment(Nutshell, Bob); Jenna.GiveAssignment(LittleDebbie, Dez); Jenna.GiveAssignment(Nutshell, Dez); List <Student> students = new List <Student> (); students.Add(Mike); students.Add(John); students.Add(Dez); students.Add(Bob); students.Add(Sonny); List <Exercise> exercises = new List <Exercise> (); exercises.Add(LittleDebbie); exercises.Add(Probes); exercises.Add(Nutshell); List <Cohort> cohorts = new List <Cohort> (); cohorts.Add(Twenty6); cohorts.Add(Twenty7); cohorts.Add(Twenty8); List <Instructor> instructors = new List <Instructor>(); instructors.Add(Meg); instructors.Add(Steve); instructors.Add(Jenna); instructors.Add(Brenda); // List exercises with LINQ Where() var JSExersise = from ex in exercises where ex.Language.Contains("JavaScript") orderby ex select ex; foreach (Exercise ex in JSExersise) { // Console.WriteLine(ex.ExerciseName); } // List students in a cohort var StudentCohorts = from stu in students where stu.Cohort.CohortName.Contains("26") select stu; foreach (Student stu in StudentCohorts) { // Console.WriteLine (stu.StudentFirstName); } // List instructors for a cohort var InstCohorts = from ins in instructors where ins.InstCohort.Contains("27") select ins; foreach (Instructor ins in InstCohorts) { // Console.WriteLine(ins.InstFirstName); } // List students by last name List <Student> byLastName = students.OrderBy(lastname => lastname.StudentLastName).ToList(); foreach (Student lastname in byLastName) { // Console.WriteLine(lastname.StudentLastName); } // Students with no work to do List <Student> noWork = students.Where(work => work.StudentExercises.Count == 0).ToList(); foreach (Student student in noWork) { // Console.WriteLine(student.StudentFirstName); } // Who has the most assignments List <Student> mostWork = students.Where(most => most.StudentExercises.Count > 2).ToList(); foreach (Student stu in mostWork) { // Console.WriteLine(stu.StudentFirstName); } // How many students in each cohort foreach (Cohort coh in cohorts) { // Console.WriteLine(coh.studentsList.Count); } // Loop over students foreach (Student student in students) { // Loop over exercises foreach (Exercise exercise in exercises) { // Loop over exercises again in order to match with previous loop foreach (Exercise match in exercises) { // If first exercise loop matches the second then write to console if (exercise == match) { ; } // Console.WriteLine($"{student.StudentFirstName}"+" is working on "+ $"{exercise.ExerciseName}"); } } } SqliteConnection NewConnection = DatabaseInterface.NewConnection; DatabaseInterface.CheckExerciseTable(); // IEnumerable<Exercise> newExerciseList = NewConnection.Query<Exercise>(@"SELECT * from Exercise"); // exercises.ForEach(ex => Console.WriteLine($"{ex.ExerciseName}")); NewConnection.Query <Exercise>(@" SELECT * FROM Exercise where Exercise.Language == 'JavaScript' ") .ToList() .ForEach(jsex => Console.WriteLine($"{jsex.ExerciseName}")); }
static void Main(string[] args) { SqliteConnection db = DatabaseInterface.Connection; DatabaseInterface.CheckExerciseTable(); //DatabaseInterface.CheckTable(); List <Exercise> exercisesList = db.Query <Exercise>(@"SELECT * FROM Exercise").ToList(); DatabaseInterface.CheckInstructorsTable(); db.Query <Instructor, Cohort, Instructor>(@" SELECT i.Id, i.lastName, c.Id, c.cohortName FROM Instructor i JOIN Cohort c ON c.Id = i.Cohort", (generatedInstructor, generatedCohort) => { generatedInstructor.cohort = generatedCohort; return(generatedInstructor); }) .ToList() .ForEach(ins => Console.WriteLine($"{ins.lastName} {ins.firstName} is in {ins.cohort.cohortName}")); //add to joiner table StudentExercises //db.Execute(@"Insert into StudentExercises (studentId, exerciseId) Values (2, 3)"); DatabaseInterface.CheckStudentTable(); db.Query <Student, Cohort, Student>(@" SELECT s.`Id `, s.lastName, c.Id, c.cohortName FROM Student s JOIN Cohort c ON c.Id = s.cohort", (generatedStudent, generatedCohort) => { generatedStudent.Cohort = generatedCohort; return(generatedStudent); }) .ToList() .ForEach(stu => Console.WriteLine($"{stu.lastName} {stu.firstName} is in {stu.Cohort.cohortName}")); List <Student> ExercisesList = db.Query <Student>(@" SELECT * FROM Exercise").ToList().ForEach(s => Console.WriteLine($"{s.lastName} {s.ExerciseList}")); /* * Dictionary<string, List<Student>> report = new Dictionary<string, List<Student>>(); * IEnumerable<int> StudentExer = db.Query<int>((@" * SELECT studentId, * exerciseId * from StudentExercises * JOIN Exercise e ON * * ") */ /* * DatabaseInterface.CheckStudentExercisesTable(); * db.Query<Student, Exercise, Student>(@" * SELECT s.Id, * s.lastName, * e.Id, * e.exerciseName * */ //instructorList.ForEach(i => Console.WriteLine($"{i.lastName}")); //exercisesList.ForEach(exer => Console.WriteLine($"{exer.exerciseName} {exer.language}")); //var newList2 = from e in exercisesList //where e.language == "Python" //select e; //oreach (Exercise i in newList2) { //Console.WriteLine(i.exerciseName); // } //db.Execute(@"INSERT INTO Exercise (exerciseName, language) VALUES ('omg no', 'SQL');"); //Console.WriteLine(exercisesList); exercisesList.ForEach(exer => Console.WriteLine($"{exer.exerciseName} {exer.language}")); //db.Execute(@"DELETE FROM Exercise where exerciseName = 'omg no'"); /* * Exercise SmashFace = new Exercise () { * exerciseName = "Smash my face", * language = "Python", * }; * * Exercise PunchFace = new Exercise () { * exerciseName = "Punch my face", * language = "C#", * }; * * Exercise GutPunch = new Exercise () { * exerciseName = "Punch in gut", * language = "ISODUM", * }; * * Exercise EsteemKill = new Exercise () { * exerciseName = "Good Luck", * language = "CVerySharp", * }; * * Cohort Cohort45 = new Cohort () { * cohortName = "Cohort 45", * }; * * Cohort Cohort46 = new Cohort () { * cohortName = "Cohort 46", * }; * * Cohort Cohort47 = new Cohort () { * cohortName = "Cohort 47", * }; * * Student Klaus = new Student () { * firstName = "Klaus", * lastName = "Hardt", * SlackHandle = "ookie", * StudentCohort = Cohort45 * }; * * Student Matt = new Student () { * firstName = "Matt", * lastName = "Harding", * SlackHandle = "dook", * StudentCohort = Cohort46 * }; * * Student Ricky = new Student () { * firstName = "Ricky", * lastName = "Burden", * SlackHandle = "dooker", * StudentCohort = Cohort47 * }; * Student Fig = new Student () { * firstName = "Fig", * lastName = "Newton", * SlackHandle = "mook", * StudentCohort = Cohort46 * }; * * Instructor DrDoom = new Instructor () { * firstName = "Dr", * lastName = "Doom", * SlackHandle = "Doomy", * cohort = Cohort45 * }; * * Instructor DrDespair = new Instructor () { * firstName = "D", * lastName = "Despair", * SlackHandle = "Despairy", * cohort = Cohort46 * }; * * Instructor DrHappy = new Instructor () { * firstName = "Really", * lastName = "Happy", * SlackHandle = "HappyFace", * cohort = Cohort47 * }; * * DrDoom.AssignExercise (SmashFace, Klaus); * DrDoom.AssignExercise (PunchFace, Klaus); * DrDespair.AssignExercise (SmashFace, Fig); * //Klaus.ExerciseList.ForEach(emp => Console.WriteLine(emp.Name)); * //drives me nuts there's not more req to console log to verify * DrDespair.AssignExercise (PunchFace, Fig); * DrHappy.AssignExercise (SmashFace, Ricky); * DrHappy.AssignExercise (EsteemKill, Ricky); * DrDoom.RemoveExercise(SmashFace, Klaus); * DrDoom.RemoveExercise(PunchFace, Klaus); * * List<Student> students = new List<Student> (); * students.Add (Klaus); * students.Add (Ricky); * students.Add (Fig); * //students.ForEach (pl2 => Console.WriteLine (pl2.lastName)); * * List<Exercise> exercises = new List<Exercise> (); * exercises.Add (SmashFace); * exercises.Add (PunchFace); * exercises.Add (EsteemKill); * exercises.Add (GutPunch); * * List<Instructor> instructors = new List<Instructor> (); * instructors.Add (DrDoom); * instructors.Add (DrDespair); * instructors.Add (DrHappy); * * List<Cohort> cohorts = new List<Cohort> (); * cohorts.Add (Cohort45); * cohorts.Add (Cohort46); * cohorts.Add (Cohort47); * * //List<Exercise> newList = from e in exercises * var newList = from e in exercises * where e.language == "Python" * select e; * foreach (Exercise i in newList) { * //Console.WriteLine(i.exerciseName); * } * * IEnumerable<Cohort> cohortList = from c in cohorts * where c.cohortName == "Cohort 45" * select c; * foreach (Cohort i in cohortList) { * //Console.WriteLine(i.Name + " is the best"); * }; * * List<Instructor> newInstructorList = (from i in instructors * where i.cohort == Cohort45 * select i).ToList(); * foreach (Instructor i in newInstructorList) { * //Console.WriteLine(i.firstName + i.lastName); * } * * IEnumerable<Student> newStudentList = from s in students * orderby s.lastName * select s; //s is a callback or lamda * foreach (Student s in newStudentList) { * //Console.WriteLine(s.lastName); * } * * * foreach (Exercise ex in exercises) { * List<string> assignedStudents = new List<string> (); * * foreach (Student stu in students) { * if (stu.ExerciseList.Contains (ex)) { * assignedStudents.Add (stu.firstName); * } * } * Console.WriteLine ($"{ex.Name} is being worked on by {String.Join(", ", assignedStudents)}"); * } * * foreach (Student s in students) { * string sExercises = ""; * int count = 0; * * foreach (Exercise e in s.ExerciseList) { * if (count == 0) { * sExercises = $"{e.exerciseName} in {e.language}"; * count ++; * } else { * sExercises = $"{e.exerciseName} in {e.language} and {sExercises}."; * count++; * } * } * * //Console.WriteLine ($"{s.lastName} of {s.StudentCohort.Name} is working on {sExercises}"); * * } * * * IEnumerable<Student> studentWithNone = students.Where(stu => * stu.ExerciseList.Count ==0 ); //ToList() or Count() doesnt matter * foreach (var stu in studentWithNone) { * //Console.WriteLine(stu.lastName); * } * * * var numberOfStudentEachCohort = students.GroupBy(c => c.StudentCohort.cohortName); * foreach (var studentGroup in numberOfStudentEachCohort) { * //Console.WriteLine($"{studentGroup.Key} has {studentGroup.Count()} student"); * } * * //list within a list. Need to iterate over both- build up a string * //students.AddRange (exercises); * * var i = * for (i = 0; i < 10; i++) { * for (int j = i; j < 10; j++) * Console.WriteLine ("Value of i: {0}, J: {1} ", i, j); * } * * * * * * //db.Execute(@"INSERT INTO Instructor (firstName, lastName, slackHandle, cohort) VALUES ('Jim', 'Carey', 'PetDet', 1);"); * */ }
static void Main(string[] args) { SqliteConnection db = DatabaseInterface.Connection; DatabaseInterface.CheckExerciseTable(); DatabaseInterface.CheckInstructorTable(); DatabaseInterface.CheckCohortTable(); /* * 1. Query database * 2. Convert result to list * 3. Use ForEach to iterate the collection */ // List<Exercise> exercises = db.Query<Exercise>(@"SELECT * FROM Exercise").ToList(); // exercises.ForEach(ex => Console.WriteLine($"{ex.Name}")); // Chaining LINQ statements together db.Query <Exercise>(@"SELECT * FROM Exercise ") .ToList() .ForEach(ex => Console.WriteLine($"{ex.Name}")); db.Execute($@" INSERT INTO Exercise (Name, Language) VALUES ('SQL', 'C#'); "); db.Execute($@" INSERT INTO Instructor (FirstName,LastName,SlackHandle,CohortId) VALUES ('Kimmie', 'Bird','KimmieBird','3'); "); db.Query <Instructor, Cohort, Instructor>(@" SELECT instr.CohortId, instr.FirstName, instr.LastName, instr.Id, c.Id, c.Name FROM Instructor instr JOIN Cohort c ON c.Id = instr.CohortId ", (instructor, cohort) => { instructor.Cohort = cohort; return(instructor); }) .ToList() .ForEach(instr => Console.WriteLine($"{instr.FirstName} {instr.LastName} {instr.Cohort}")); db.Query <Student>(@"SELECT * FROM Student ") .ToList() .ForEach(stu => Console.WriteLine($"{stu.FirstName}{stu.LastName}{stu.Cohort}")); // //Cohort Evening8 = new Cohort("Evening 8"); // Cohort Day25 = new Cohort("Day 25"); // Cohort Day26 = new Cohort("Day 26"); // Cohort Day27 = new Cohort("Day 27"); // //instructors // Instructor Meg = new Instructor("Meg", "Ducharme", "slack", Day25); // Instructor Jenna = new Instructor("Jenna", "Solis", "slack", Day26); // Instructor Steve = new Instructor("Steve", "Lastname", "Slack", Day27); // //students // Student Gretchen = new Student("Gretchen", "Ward", "slack", Day27); // Student Maddie = new Student("Maddie", "lastname", "slack", Day27); // Student Leah = new Student("Leah", "Gwinn", "LeahGwinn", Day26); // Student Wyatt = new Student("Wyatt", "Nutter", "WyattN", Day27); // //exercises // Exercise classes = new Exercise("classes", "C#"); // Exercise hashsets = new Exercise("hashsets", "C#"); // Exercise chickenMonkey = new Exercise("chickenMonkey", "JavaScript"); // Exercise nutshell = new Exercise("nutshell", "React"); // Exercise loops = new Exercise("loops", "JavaScript"); // Exercise practice = new Exercise("practice", "JavaScript"); // //assign exercises // Meg.AssignExercise(classes, Leah); // Meg.AssignExercise(loops, Leah); // Meg.AssignExercise(nutshell, Wyatt); // Meg.AssignExercise(loops, Wyatt); // Jenna.AssignExercise(chickenMonkey, Maddie); // Jenna.AssignExercise(nutshell, Maddie); // Jenna.AssignExercise(chickenMonkey, Leah); // Jenna.AssignExercise(practice, Leah); // Jenna.AssignExercise(classes, Wyatt); // Jenna.AssignExercise(loops, Wyatt); // Steve.AssignExercise(nutshell, Leah); // Steve.AssignExercise(hashsets, Leah); // Steve.AssignExercise(classes, Maddie); // Steve.AssignExercise(hashsets, Maddie); // // Create a list of students. Add all of the student instances to it. // List<Student> students = new List<Student> () // { // Gretchen, // Maddie, // Leah, // Wyatt // }; // List<Cohort> Cohorts = new List<Cohort> () // { // Evening8, // Day25, // Day26, // Day27 // }; // // Create a list of exercises. Add all of the exercise instances to it. // List<Exercise> exercises = new List<Exercise> () // { // classes, // hashsets, // loops, // chickenMonkey, // nutshell, // practice // }; // // list instructors // List<Instructor> instructors = new List<Instructor>() // { // Meg, // Steve, // Jenna // }; // // Display any students that aren't working on any exercises // var studentsWithNoExercises = students.Where(stu => stu.Exercises.Count() == 0); // foreach (var stu in studentsWithNoExercises) // { // Console.WriteLine($"Students who aren't working on exercises: {stu.FirstName} {stu.LastName}"); // } // var studentsWithExercises = students.Where(stu => stu.Exercises.Count() != 0); // foreach (var stu in studentsWithExercises) // { // Console.WriteLine($"Students with exercises: {stu.FirstName} {stu.LastName}"); // } // // List exercises for Javascript with where linq method // IEnumerable<Exercise> JS = from JavaScript in exercises // where JavaScript.Language == "JavaScript" // select JavaScript; // JS.ToList().ForEach(e => Console.WriteLine(e.Name)); // // List instructors in a cohort with linq method // IEnumerable<Instructor> inst = from instructor in instructors // where instructor.Cohort == Day25 // select instructor; // inst.ToList().ForEach(i => Console.WriteLine($"instructor {i.FirstName} {i.Cohort.Name}")); }
static void Main(string[] args) { // Create 4, or more, exercises. Exercise loops = new Exercise("Loops", "C#"); Exercise localStorage = new Exercise("Local Storage", "Javascript"); Exercise modules = new Exercise("Modularization", "Javascript"); Exercise props = new Exercise("Props", "React"); // Create 3, or more, cohorts. Cohort day26 = new Cohort("Day Cohort 26"); Cohort day27 = new Cohort("Day Cohort 27"); Cohort day28 = new Cohort("Day Cohort 28"); Cohort night8 = new Cohort("Evening Cohort 8"); // Create 4, or more, students and assign them to one of the cohorts. Student taylor = new Student("Taylor", "Gulley", "taylor1", day27); Student mark = new Student("Mark", "Hale", "mark1", day27); Student vik = new Student("Sathvik", "Reddy", "vik1", day26); Student nolan = new Student("Nolan", "Little", "nolan1", day28); Student jon = new Student("Jon", "Snow", "King of the North", night8); day27.ListOfStudents.Add(taylor); day27.ListOfStudents.Add(mark); day26.ListOfStudents.Add(vik); day28.ListOfStudents.Add(nolan); night8.ListOfStudents.Add(jon); // Create 3, or more, instructors and assign them to one of the cohorts. Instructor steve = new Instructor("Steve", "Brownlee", "coach", day27); Instructor meg = new Instructor("Meg", "Ducharme", "meg", day27); Instructor kimmy = new Instructor("Kimmy", "Bird", "kimmy", day27); Instructor andy = new Instructor("Andy", "Collins", "andy", day27); day27.ListOfInstructors.Add(steve); day27.ListOfInstructors.Add(meg); day27.ListOfInstructors.Add(kimmy); day27.ListOfInstructors.Add(andy); // Have each instructor assign 2 exercises to each of the students. steve.AssignExercise(taylor, modules); steve.AssignExercise(taylor, props); meg.AssignExercise(mark, modules); meg.AssignExercise(mark, loops); kimmy.AssignExercise(vik, props); kimmy.AssignExercise(vik, localStorage); andy.AssignExercise(nolan, localStorage); andy.AssignExercise(nolan, loops); steve.AssignExercise(taylor, props); List <Student> students = new List <Student>() { taylor, mark, vik, nolan, jon }; List <Exercise> exercises = new List <Exercise>() { loops, localStorage, modules, props }; // can reference probes and planets for this, do something like loop over the exercises then loop over the students to match up who is working on what like Modularization : Taylor, Mark // foreach (Student student in students) // { // Console.WriteLine($"{student.FirstName} {student.LastName} is working on {student.StudentExercises[0].Name} and {student.StudentExercises[1].Name}"); // } foreach (Exercise exercise in exercises) { List <string> matchingExercises = new List <string>(); foreach (Student student in students) { if (student.StudentExercises.Contains(exercise)) { matchingExercises.Add(student.FirstName); } } // Console.WriteLine($"{exercise.ExerciseName} is assigned to: {String.Join(", ", matchingExercises)}"); } // Student Exercises Part 2 List <Instructor> instructors = new List <Instructor>() { steve, meg, kimmy, andy }; List <Cohort> cohorts = new List <Cohort>() { day26, day27, day28, night8 }; //List exercises for the JavaScript language by using the Where() LINQ method. IEnumerable <Exercise> javascriptExercises = from exercise in exercises where exercise.ExerciseLanguage == "Javascript" select exercise; //List students in a particular cohort by using the Where() LINQ method. List <Student> cohort27Students = (from student in students where student.Cohort == day27 select student).ToList(); //List instructors in a particular cohort by using the Where() LINQ method. List <Instructor> cohort27Instructors = instructors.Where(i => i.Cohort == day27).ToList(); //Sort the students by their last name. List <Student> sortedStudents = (from student in students orderby student.LastName select student).ToList(); //Display any students that aren't working on any exercises (Make sure one of your student instances don't have any exercises. Create a new student if you need to.) List <Student> studentWithoutExercises = (from student in students where student.StudentExercises.Count == 0 select student ).ToList(); // Which student is working on the most exercises? Make sure one of your students has more exercises than the others. var studentWithMostExercises = (from student in students select new { FirstName = student.FirstName, Exercises = student.StudentExercises.Count() }).OrderByDescending(s => s.Exercises) .Take(1).ToList()[0]; // How many students in each cohort? // GroupBy gives you a collection of groups - each group has something that it's being grouped by (the key). The group itself is the list of all of the values of the group. Returns a collection of groups. // collection of groups (numberOfStudentsInEachCohort) // METHOD WAY var numberOfStudentsInEachCohort = students.GroupBy(c => c.Cohort.CohortName); // looks at every group of students foreach (var studentGroup in numberOfStudentsInEachCohort) { // key is the thing you grouped by // Console.WriteLine($"{studentGroup.Key} has {studentGroup.Count()} students"); } // SQL/QUERY WAY var totalStudents = from student in students group student by student.Cohort into sorted select new { Cohort = sorted.Key, Students = sorted.ToList() }; foreach (var total in totalStudents) { // Console.WriteLine($"Cohort {total.Cohort.Name} has {total.Students.Count()} students"); } //Using Dapper to query the database SqliteConnection db = DatabaseInterface.Connection; DatabaseInterface.CheckExerciseTable(); DatabaseInterface.CheckInstructorsTable(); DatabaseInterface.CheckCohortsTable(); List <Exercise> exercisesQuery = db.Query <Exercise>(@"SELECT * FROM Exercise").ToList(); exercisesQuery.ForEach(ex => Console.WriteLine($"{ex.ExerciseName}")); db.Execute($@" INSERT INTO Exercise (ExerciseName, ExerciseLanguage) VALUES ('DOM', 'Javascript') "); List <Exercise> javascriptExercisesQuery = db.Query <Exercise>(@"SELECT * FROM Exercise WHERE ExerciseLanguage == 'Javascript'").ToList(); javascriptExercisesQuery.ForEach(ex => Console.WriteLine($"This is a Javascript exercise {ex.ExerciseName}")); List <Instructor> instructorQuery = db.Query <Instructor>(@"SELECT * FROM Instructor").ToList(); instructorQuery.ForEach(ins => Console.WriteLine($"{ins.FirstName} {ins.LastName}")); List <Cohort> cohortQuery = db.Query <Cohort>(@"SELECT * FROM Cohort").ToList(); cohortQuery.ForEach(co => Console.WriteLine($"{co.CohortName}")); }
static void Main(string[] args) { // --------------- DAPPER --------------- SqliteConnection db = DatabaseInterface.Connection; DatabaseInterface.CheckExerciseTable(); DatabaseInterface.CheckCohortTable(); DatabaseInterface.CheckStudentTable(); DatabaseInterface.CheckInstructorTable(); DatabaseInterface.CheckStudentExerciseTable(); // 3. Query the database for all the Exercises. // whenever dapper does a query, it'll do the query, and get a row back and then make a new exercise object. so then new exercise - then it'll make an assumption that it'll have a parameterless constructor because it must be generic. db.Query <Exercise> (@"SELECT * FROM Exercise") .ToList() .ForEach(ex => Console.WriteLine($"{ex.Name}: {ex.Language}")); // 4. Fnd all the exercises in the database where the language is JavaScript. db.Query <Exercise> (@"SELECT * FROM Exercise WHERE Exercise.Language == 'Javascript'") .ToList() .ForEach(ex => Console.WriteLine($"{ex.Name} are Javascript exercises")); // 5. Insert a new exercise into the database. // db.Execute(@" // INSERT INTO Exercise (Name, Language) VALUES ('Dapper', 'C#') // "); // --------------- LINQ --------------- // Create 4, or more, exercises. Exercise loops = new Exercise("loops", "Javascript"); Exercise objects = new Exercise("objects", "Javascript"); Exercise dictionaries = new Exercise("dictionaries", "C#"); Exercise lists = new Exercise("lists", "C#"); // Create 3, or more, cohorts. Cohort twentyFive = new Cohort("Day 25"); Cohort twentySix = new Cohort("Day 26"); Cohort twentySeven = new Cohort("Day 27"); // Create 4, or more, students and assign them to one of the cohorts. Student Rachel = new Student("Rachel", "Greene", "haha", twentyFive); Student Monica = new Student("Monica", "Geller", "keep it clean", twentyFive); Student Ross = new Student("Ross", "Geller", "we were on a break", twentySix); Student Chandler = new Student("Chandler", "Bing", "could I be more ...", twentySix); Student Joey = new Student("Joey", "Tribiani", "sandwiches", twentySeven); Student Phoebe = new Student("Phoebe", "Bouffay", "nestle tollhouse", twentySeven); Student Phoebe2 = new Student("Phoebe2", "Bouffay2", "nestle tollhouse2", twentySeven); // Create 3, or more, instructors and assign them to one of the cohorts. Instructor Joe = new Instructor("Joe", "Shepherd", "joes", twentyFive); Instructor Jisie = new Instructor("Jisie", "David", "jisie", twentySix); Instructor Jordan = new Instructor("Jordan", "C", "jordan", twentySix); Instructor Steve = new Instructor("Steve", "Brownlee", "coach", twentySeven); // Have each instructor assign 2 exercises to each of the students. Joe.AssignExercise(loops, Rachel); Joe.AssignExercise(objects, Rachel); Joe.AssignExercise(loops, Monica); Joe.AssignExercise(objects, Monica); Joe.AssignExercise(dictionaries, Monica); Joe.AssignExercise(lists, Monica); Jisie.AssignExercise(dictionaries, Ross); Jisie.AssignExercise(lists, Ross); Jisie.AssignExercise(loops, Ross); Jordan.AssignExercise(lists, Chandler); Jordan.AssignExercise(dictionaries, Chandler); Steve.AssignExercise(lists, Joey); Steve.AssignExercise(dictionaries, Joey); // Create a list of students. Add all of the student instances to it. List <Student> students = new List <Student> () { Rachel, Monica, Ross, Chandler, Joey, Phoebe, Phoebe2 }; // Create a list of exercises. Add all of the exercise instances to it. List <Exercise> exercises = new List <Exercise> () { loops, objects, dictionaries, lists }; // list instructors List <Instructor> instructors = new List <Instructor> () { Joe, Jisie, Jordan, Steve }; // list of cohorts List <Cohort> cohorts = new List <Cohort> () { twentyFive, twentySix, twentySeven }; // 1. List exercises for the JavaScript language by using the Where() LINQ method. IEnumerable <Exercise> JSEx = exercises.Where(ex => ex.Language == "Javascript"); foreach (var ex in JSEx) { // Console.WriteLine($"Javascript exercises: {ex.Name}"); } // 2. List students in a particular cohort by using the Where() LINQ method. IEnumerable <Student> studentsIn27 = students.Where(stu => stu.Cohort == twentySeven); foreach (var stu in studentsIn27) { // Console.WriteLine($"Students in Cohort 27: {stu.FirstName} {stu.LastName}"); } // 3. List instructors in a particular cohort by using the Where() LINQ method. IEnumerable <Instructor> instructorsIn26 = instructors.Where(ins => ins.Cohort == twentySix); foreach (var i in instructorsIn26) { // Console.WriteLine($"Instructors in Cohort 26: {i.FirstName} {i.LastName}"); } // 4. Sort the students by their last name. IEnumerable <Student> sortedStudents = students.OrderBy(stu => stu.LastName); foreach (var stu in sortedStudents) { // Console.WriteLine($"Sorted students by last name: {stu.LastName}, {stu.FirstName}"); } // 5. Display any students that aren't working on any exercises List <Student> studentsWithNoExercises = students.Where(stu => stu.Exercises.Count == 0).ToList(); foreach (var stu in studentsWithNoExercises) { // Console.WriteLine($"Students who aren't working on exercises: {stu.FirstName} {stu.LastName}"); } // 6. Which student is working on the most exercises? var studentWithMostExercises = (from s in students // select is like .map and generates a new thing and put it into the final collection select new { FirstName = s.FirstName, Exercises = s.Exercises.Count() }) // put in order of descending number of exercises .OrderByDescending(s => s.Exercises) // grab just the first one -> first or default if the list is empty .Take(1).ToList() [0]; // Console.WriteLine($"Student working on most exercises: {studentWithMostExercises.FirstName} {studentWithMostExercises.Exercises}"); // 7. How many students in each cohort? // GroupBy gives you a collection of groups - each group has something that it's being grouped by (the key). The group itself is the list of all of the values of the group. Returns a collection of groups. // collection of groups (numberOfStudentsInEachCohort) // METHOD WAY var numberOfStudentsInEachCohort = students.GroupBy(c => c.Cohort.Name); // looks at every group of students foreach (var studentGroup in numberOfStudentsInEachCohort) { // key is the thing you grouped by // Console.WriteLine($"{studentGroup.Key} has {studentGroup.Count()} students"); } // SQL/QUERY WAY var totalStudents = from student in students group student by student.Cohort into sorted select new { Cohort = sorted.Key, Students = sorted.ToList() }; foreach (var total in totalStudents) { // Console.WriteLine($"Cohort {total.Cohort.Name} has {total.Students.Count()} students"); } // Generate a report that displays which students are working on which exercises. foreach (Exercise ex in exercises) { List <string> assignedStudents = new List <string> (); foreach (Student stu in students) { if (stu.Exercises.Contains(ex)) { assignedStudents.Add(stu.FirstName); } } // Console.WriteLine ($"{ex.Name} is being worked on by {String.Join(", ", assignedStudents)}"); } // Query the database for all the Exercises. }