private void button5_Click(object sender, EventArgs e) { PrintThatFetchingData(); using (var db = new EFSchoolSystemContext()) { db.Database.CommandTimeout = 300; string city = "New York"; var pupils = db.Pupils .Where(p => p.City == city) .OrderBy(p => p.LastName) .Select(x => new { x.FirstName, x.LastName }) .ToList(); var sb = new StringBuilder(); foreach (var pupil in pupils) { sb.Append(pupil.FirstName); sb.Append(" "); sb.Append(pupil.LastName); sb.Append(Environment.NewLine); } textBox_Output.Text = sb.ToString(); } }
private void button6_Click(object sender, EventArgs e) { PrintThatFetchingData(); using (var db = new EFSchoolSystemContext()) { //Search data as input by user var searchModel = new Pupil { FirstName = "Ben", LastName = null, City = null, PostalZipCode = null }; List <Pupil> pupils = db.Pupils.Where(p => (searchModel.FirstName == null || p.FirstName == searchModel.FirstName) && (searchModel.LastName == null || p.LastName == searchModel.LastName) && (searchModel.City == null || p.LastName == searchModel.City) && (searchModel.PostalZipCode == null || p.PostalZipCode == searchModel.PostalZipCode) ) .Take(100) .ToList(); var sb = new StringBuilder(); foreach (var pupil in pupils) { sb.Append(pupil.FirstName); sb.Append(" "); sb.Append(pupil.LastName); sb.Append(Environment.NewLine); } textBox_Output.Text = sb.ToString(); } }
//Search pupils based on optional criteria public string DoProblem6() { using (var db = new EFSchoolSystemContext()) { //Search data as input by user var searchModel = new Pupil { FirstName = "Ben", LastName = null, City = null, PostalZipCode = null }; List <PupilName> pupils = db.Pupils.Where(p => (searchModel.FirstName == null || p.FirstName == searchModel.FirstName) && (searchModel.LastName == null || p.LastName == searchModel.LastName) && (searchModel.City == null || p.LastName == searchModel.City) && (searchModel.PostalZipCode == null || p.PostalZipCode == searchModel.PostalZipCode) ) .Take(100) .Select(p => new PupilName { FirstName = p.FirstName, LastName = p.LastName }) .ToList(); foreach (var pupil in pupils) { _outputBuffer.Append($"{pupil.FirstName} {pupil.LastName}"); } return(_outputBuffer.ToString()); } }
public List <Pupil> GetSchoolsWithEntityFramework(string schoolName) { using (var db = new EFSchoolSystemContext()) { return(db.Pupils.Where(p => p.School.Name == schoolName).ToList()); } }
//Add some pupils to the database public string DoProblem8() { using (var db = new EFSchoolSystemContext()) { for (int i = 0; i < 500; i++) { Pupil pupil = GetNewPupil(); db.Pupils.Add(pupil); } db.SaveChanges(); return("Finished adding data"); } }
private void button8_Click(object sender, EventArgs e) { PrintThatFetchingData(); using (var db = new EFSchoolSystemContext()) { for (int i = 0; i < 2000; i++) { Pupil pupil = GetNewPupil(); db.Pupils.Add(pupil); } db.SaveChanges(); textBox_Output.Text = "Finished adding data"; } }
//Retrieve school names in a city with the number of pupils public string DoProblem2() { using (var db = new EFSchoolSystemContext()) { string city = "New York"; List <School> schools = db.Schools.Where(s => s.City == city).ToList(); foreach (var school in schools) { WriteOutput($"{school.Name}: {school.Pupils.Count}"); } return(_outputBuffer.ToString()); } }
//Retrieve school names in a city public string DoProblem1() { using (var db = new EFSchoolSystemContext()) { string city = "New York"; List <School> schools = db.Schools.ToList(); List <School> newYorkSchools = schools.Where(s => s.City == city).ToList(); foreach (var school in newYorkSchools) { WriteOutput(school.Name); } return(_outputBuffer.ToString()); } }
//Retrieve pupil names in a single school public string DoProblem3() { using (var db = new EFSchoolSystemContext()) { int schoolId = 1; List <Pupil> pupils = db.Pupils .Where(p => p.SchoolId == schoolId) .ToList(); foreach (var pupil in pupils) { WriteOutput($"{pupil.FirstName} {pupil.LastName}"); } return(_outputBuffer.ToString()); } }
private void button2_Click(object sender, EventArgs e) { PrintThatFetchingData(); using (var db = new EFSchoolSystemContext()) { string city = "New York"; List <School> schools = db.Schools.Where(s => s.City == city).ToList(); var sb = new StringBuilder(); foreach (var school in schools) { sb.Append(school.Name); sb.Append(": "); sb.Append(school.Pupils.Count); sb.Append(Environment.NewLine); } textBox_Output.Text = sb.ToString(); } }
//Retrieve pupil names in a postcode public string DoProblem4() { using (var db = new EFSchoolSystemContext()) { string zipCode = "90210"; var pupils = db.Pupils .Where(p => p.PostalZipCode == zipCode) .Select(x => new { x.FirstName, x.LastName }) .ToList(); foreach (var pupil in pupils) { WriteOutput($"{pupil.FirstName} {pupil.LastName}"); } return(_outputBuffer.ToString()); } }
//Retrieve pupil names in a city public string DoProblem5() { using (var db = new EFSchoolSystemContext()) { db.Database.CommandTimeout = 300; string city = "New York"; var pupils = db.Pupils .Where(p => p.City == city) .OrderBy(p => p.LastName) .Select(x => new { x.FirstName, x.LastName }) .ToList(); foreach (var pupil in pupils) { _outputBuffer.Append($"{pupil.FirstName} {pupil.LastName}"); } return(_outputBuffer.ToString()); } }
private void button3_Click(object sender, EventArgs e) { PrintThatFetchingData(); using (var db = new EFSchoolSystemContext()) { int schoolId = 1; List <Pupil> pupils = db.Pupils .Where(p => p.SchoolId == schoolId) .ToList(); var sb = new StringBuilder(); foreach (var pupil in pupils) { sb.Append(pupil.FirstName); sb.Append(" "); sb.Append(pupil.LastName); sb.Append(Environment.NewLine); } textBox_Output.Text = sb.ToString(); } }
private void button4_Click(object sender, EventArgs e) { PrintThatFetchingData(); using (var db = new EFSchoolSystemContext()) { string zipCode = "90210"; var pupils = db.Pupils .Where(p => p.PostalZipCode == zipCode) .Select(x => new { x.FirstName, x.LastName }) .ToList(); var sb = new StringBuilder(); foreach (var pupil in pupils) { sb.Append(pupil.FirstName); sb.Append(" "); sb.Append(pupil.LastName); sb.Append(Environment.NewLine); } textBox_Output.Text = sb.ToString(); } }
private void button7_Click(object sender, EventArgs e) { PrintThatFetchingData(); using (var db = new EFSchoolSystemContext()) { var model = new ResultsModel(); var rnd = new Random(); model.Page = rnd.Next(1, 1000); model.ResultsPerPage = rnd.Next(10, 100); var schools = db.Schools .OrderBy(s => s.PostalZipCode) .Skip(model.Page * model.ResultsPerPage) .Take(model.ResultsPerPage) .ToList(); var sb = new StringBuilder(); foreach (var school in schools) { sb.Append(school.Name); sb.Append(Environment.NewLine); } textBox_Output.Text = sb.ToString(); } }
//Page through schools public string DoProblem7() { using (var db = new EFSchoolSystemContext()) { var model = new ResultsModel(); var rnd = new Random(); model.Page = rnd.Next(1, 1000); model.ResultsPerPage = rnd.Next(10, 100); var resultsToSkip = model.Page * model.ResultsPerPage; List <string> schools = db.Schools .OrderBy(s => s.PostalZipCode) .Skip(resultsToSkip) .Take(model.ResultsPerPage) .Select(s => s.Name) .ToList(); foreach (var school in schools) { _outputBuffer.Append(school); } return(_outputBuffer.ToString()); } }