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