/// <summary>
/// This method works with the strong typed DataSet
/// </summary>
private static void QueryDBByStrongTypedDataSet()
{
Console.WriteLine("Use LINQ to query strong typed DataSet...");
/////////////////////////////////////////////////////////////////////
// Fill the strong typed DataSet && Insert data into database
//
// Create a strong typed DataSet object
SQLServer2005DBDataSet dsSQLServer = new SQLServer2005DBDataSet();
// Create the PersonTableAdapter and fill the Person DataTable
PersonTableAdapter taPerson = new PersonTableAdapter();
SQLServer2005DBDataSet.PersonDataTable tblPerson = dsSQLServer.
Person;
taPerson.Fill(tblPerson);
// Create a new row into the Person DataTable
// We don't set the PersonCategory value because we have set the
// default value property of the DataSet PersonCategory column.
SQLServer2005DBDataSet.PersonRow rowPerson = tblPerson.NewPersonRow();
rowPerson.LastName = "Sun";
rowPerson.FirstName = "Hongye";
rowPerson.SetHireDateNull();
rowPerson.EnrollmentDate = DateTime.Now;
rowPerson.Picture = ReadImage(@"MSDN.jpg");
tblPerson.AddPersonRow(rowPerson);
// Update the database
taPerson.Update(tblPerson);
// Use PersonTableAdapter to insert new record into the database
// directly
taPerson.Insert("Ge", "Jialiang", DateTime.Now, null,
ReadImage(@"MSDN.jpg"), 2);
// Create the CourseTableAdapter and fill the Course DataTable
CourseTableAdapter taCourse = new CourseTableAdapter();
SQLServer2005DBDataSet.CourseDataTable tblCourse = dsSQLServer.Course;
taCourse.Fill(tblCourse);
// Create the CourseGradeTableAdapter and fill the CourseGrade
// DataTable
CourseGradeTableAdapter taCourseGrade = new CourseGradeTableAdapter();
SQLServer2005DBDataSet.CourseGradeDataTable tblCourseGrade =
dsSQLServer.CourseGrade;
taCourseGrade.Fill(tblCourseGrade);
/////////////////////////////////////////////////////////////////////
// Perform the query operation in one DataTable
//
Console.WriteLine("\nQuery people whose first name is Roger:");
// Person the query
var query = from p in tblPerson.AsEnumerable()
where p.FirstName == "Roger"
select p;
// Display the query results
foreach (var p in query)
{
Console.WriteLine("ID = {0}, Name = {1} {2}", p.PersonID,
p.FirstName, p.LastName);
}
/////////////////////////////////////////////////////////////////////
// Perform the query operation across multiple DataTables
//
Console.WriteLine("\nQuery the max grade of each course:");
// Perform the query and get a collection of hte anonymous type,
// new { int CourseID, string Title, decimal TopGrade }
var courses = from grade in tblCourseGrade.AsEnumerable()
group grade by grade.CourseID into g
join cur in tblCourse.AsEnumerable()
on g.Key equals cur.CourseID
select new
{
CourseID = g.Key,
Title = cur.Title,
TopGrade = g.Max(gra => gra.IsGradeNull() ?
decimal.Zero : gra.Grade)
};
// Display the query results
foreach (var c in courses)
{
Console.WriteLine("Course = {0}, TopGrade = {1}", c.Title,
c.TopGrade);
}
/////////////////////////////////////////////////////////////////////
// Perform the query operation across multiple related DataTables
//
Console.WriteLine("\nQuery all grades that Nino got:");
// Perform the query betweem related DataTables and get the anonymous
// type, new { PersonRow Person, CourseRow Course, CourseGradeRow
// CourseGrade}
var grades = from per in tblPerson.AsEnumerable()
from gra in per.GetCourseGradeRows()
join cur in tblCourse.AsEnumerable()
on gra.CourseID equals cur.CourseID
where per.FirstName == "Nino"
select new
{
Person = per,
Course = cur,
CourseGrade = gra
};
// Display the query results
foreach (var grade in grades)
{
Console.WriteLine("FirstName = {0}, Course= {1}, Grade= {2}",
grade.Person.FirstName, grade.Course.Title,
grade.CourseGrade.Grade);
}
}
