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.
}
