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());
}
}
