static void Main(string[] args) { Exercise Loops = new Exercise("Loops", "JavaScript"); Exercise ArrayMothods = new Exercise("Array Methods", "JavaScript"); Exercise Objects = new Exercise("Objects", "JavaScript"); Exercise Classes = new Exercise("Classes", "CSharp"); Exercise Syntax = new Exercise("Syntax", "JavaScript"); Exercise Terminal = new Exercise("Terminal", "Everything"); Cohort Day26 = new Cohort("Day Cohort 26"); Cohort Day27 = new Cohort("Day Cohort 27"); Cohort Day28 = new Cohort("Day Cohort 28"); Student Ricky = new Student("Ricky", "Bruner", "rbruner", Day27); Student Kayla = new Student("Kayla", "Reid", "reid615", Day27); Student Jen = new Student("Jen", "Claws", "jenclaws", Day28); Student Jessica = new Student("Jessica", "Barnett", "jbarnett", Day28); Student Avett = new Student("Avett", "Ducharme", "avett", Day27); Instructor Steve = new Instructor("Steve", "Brownlee", "steveBrownlee", Day27); Instructor Meg = new Instructor("Meg", "Ducharme", "mDucharme", Day27); Instructor Andy = new Instructor("Andy", "Collins", "andy", Day27); Steve.AssignExercises(Loops, Ricky); Steve.AssignExercises(Objects, Ricky); Steve.AssignExercises(Classes, Ricky); Steve.AssignExercises(Loops, Kayla); Steve.AssignExercises(Objects, Kayla); Steve.AssignExercises(Loops, Jen); Steve.AssignExercises(Objects, Jen); Steve.AssignExercises(Loops, Jessica); Steve.AssignExercises(Objects, Jessica); Andy.AssignExercises(ArrayMothods, Jessica); Andy.AssignExercises(Classes, Jessica); Andy.AssignExercises(ArrayMothods, Jen); Andy.AssignExercises(Classes, Jen); Andy.AssignExercises(ArrayMothods, Kayla); Andy.AssignExercises(Classes, Kayla); Andy.AssignExercises(ArrayMothods, Ricky); Andy.AssignExercises(Classes, Ricky); Meg.AssignExercises(Syntax, Jessica); Meg.AssignExercises(Terminal, Jessica); Meg.AssignExercises(Syntax, Jen); Meg.AssignExercises(Terminal, Jen); Meg.AssignExercises(Syntax, Kayla); Meg.AssignExercises(Terminal, Kayla); Meg.AssignExercises(Syntax, Ricky); Meg.AssignExercises(Terminal, Ricky); List <Student> students = new List <Student>() { Ricky, Kayla, Jen, Jessica, Avett }; List <Exercise> exercises = new List <Exercise>() { Loops, ArrayMothods, Objects, Classes, Syntax, Terminal }; List <Cohort> cohort = new List <Cohort>() { Day26, Day27, Day28 }; List <Instructor> instructor = new List <Instructor>() { Steve, Meg, Andy }; // 1. List exercises for the JavaScript language by using the Where() LINQ method. IEnumerable <Exercise> javaScriptexercies = from ex in exercises where ex.Language == "JavaScript" select ex; javaScriptexercies.ToList(); // foreach(Exercise e in javaScriptexercies) // { // Console.WriteLine(e.ExerciseName); // } // 2. List students in a particular cohort by using the Where() LINQ method. IEnumerable <Student> studentsincohort = from s in students where s.Cohort == Day27 select s; studentsincohort.ToList(); // foreach(Student s in studentsincohort) // { // Console.WriteLine($"{s.FirstName} {s.LastName} Student of cohort 27"); // } // 3. List instructors in a particular cohort by using the Where() LINQ method. IEnumerable <Instructor> instructorCohort = from inst in instructor where inst.Cohort == Day27 select inst; instructorCohort.ToList(); // foreach(Instructor inst in instructorCohort) // { // Console.WriteLine($"{inst.FirstName} {inst.LastName} Instructor for cohort 27"); // } // 4. Sort the students by their last name. IEnumerable <Student> studentsByLastname = from oStudents in students orderby oStudents.LastName select oStudents; studentsByLastname.ToList(); // foreach(Student stu in studentsByLastname) // { // Console.WriteLine($"{stu.FirstName} {stu.LastName}"); // } // 5. Display any students that aren't working on any exercises IEnumerable <Student> noExStudents = students.Where(s => s.ExerciseList.Count() == 0).ToList(); foreach (Student noExStu in noExStudents) { Console.WriteLine($"this student doesn't have any exercies {noExStu.FirstName} {noExStu.LastName}"); } // 6. Which student is working on the most exercises? Make sure one of your students has more exercises than the others. var studentwithmostex = (from s in students select new{ FirstName = s.FirstName, ExerciseList = s.ExerciseList.Count() }) .OrderByDescending(s => exercises) .FirstOrDefault(); Console.WriteLine($"{studentwithmostex.FirstName} is working on {studentwithmostex.ExerciseList} exercises"); // 7. How many students in each cohort? var numberOfStudentsInCohort = students.GroupBy(c => c.Cohort.Name); foreach (var studnetGroup in numberOfStudentsInCohort) { Console.WriteLine($"{studnetGroup.Key} has {studnetGroup.Count()} students"); } SqliteConnection db = DatabaseInterface.Connection; DatabaseInterface.CheckInstructorTable(); // the name of the colloms names from the db have to match the property names // List<Exercise> exercisesFromDB = db.Query<Exercise>(@" // SELECT * FROM Exercise // WHERE Exercise.Language == 'JavaScript' // ").ToList(); // foreach(Exercise ex in exercisesFromDB) // { // Console.WriteLine($"{ex.Language} {ex.Name}"); // } // db.Execute(@" // INSERT INTO Exercise (Name, Language) // VALUES ('Overly Excited', 'JavaScript') // "); // List<Exercise> exercisesFromDB = db.Query<Exercise>(@" // SELECT * FROM Exercise // WHERE Exercise.Language == 'JavaScript' // ").ToList(); // foreach(Exercise ex in exercisesFromDB) // { // Console.WriteLine($"{ex.Language} {ex.Name}"); // } List <Instructor> instructorList = db.Query <Instructor, Cohort, Instructor>(@" SELECT i.FirstName, i.LastName, i.SlackHandle, i.CohortId, c.Id, c.Name FROM Instructor i JOIN Cohort c WHERE c.id = i.CohortId ", (instructor1, cohort1) => { instructor1.Cohort = cohort1; return(instructor1); }).ToList(); foreach (Instructor inst in instructorList) { Console.WriteLine($"{inst.FirstName} {inst.LastName} {inst.Cohort.Name}"); } }
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) { // --------------- 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. }