Wei-Meng Lee - C# 2008 Programmer's Reference

adapter = new SqlDataAdapter(comm);

adapter.Fill(ds);

The three tables loaded onto the DataSet can now be referenced using three DataTableobjects:

DataTable customersTable = ds.Tables[0]; //---Customers---

DataTable ordersTable = ds.Tables[1]; //---Orders---

DataTable orderDetailsTable = ds.Tables[2]; //---Order Details---

The following LINQ query joins two DataTable objects — customersTableand ordersTable— using the query syntax:

//---using query syntax to join two tables - Customers and Orders-

var query1 =

(from customer in customersTable.AsEnumerable()

join order in ordersTable.AsEnumerable() on

customer.Field("CustomerID") equals order.Field("CustomerID")

select new {

id = customer.Field("CustomerID"),

CompanyName = customer.Field("CompanyName"),

ContactName = customer.Field("ContactName"),

OrderDate = order.Field("OrderDate"),

ShipCountry = order.Field("ShipCountry")

}).ToList();

As evident in the query, the Customersand Orderstable are joined using the CustomerIDfield. The result is reshaped using an anonymous type and then converted to a List object using the ToList()extension method. You can now bind the result to a DataGridViewcontrol if desired. Figure 14-8 shows the result bound to a DataGridViewcontrol.

Figure 14-8

You can also rewrite the query using the method syntax:

//---using method syntax to join two tables - Customers and Orders

var query1 =

(customersTable.AsEnumerable().Join(ordersTable.AsEnumerable(),

customer => customer.Field("CustomerID"),

order => order.Field("CustomerID"),

(customer, order) => new {

id = customer.Field("CustomerID"),

CompanyName = customer.Field("CompanyName"),

ContactName = customer.Field("ContactName"),

OrderDate = order.Field("OrderDate"),

ShipCountry = order.Field("ShipCountry")

})).ToList();

The following query joins three DataTable objects — customersTable, ordersTable, and orderDetailsTable— and sorts the result according to the OrderIDfield:

//---three tables join---

var query2 =

(from customer in customersTable.AsEnumerable()

join order in ordersTable.AsEnumerable() on

customer.Field("CustomerID") equals order.Field("CustomerID")

join orderDetail in orderDetailsTable.AsEnumerable() on

order.Field("OrderID") equals orderDetail.Field("OrderID")

orderby order.Field("OrderID")

select new {

id = customer.Field("CustomerID"),

CompanyName = customer.Field("CompanyName"),

ContactName = customer.Field("ContactName"),

OrderDate = order.Field("OrderDate"),

ShipCountry = order.Field("ShipCountry"),

OrderID = orderDetail.Field("OrderID"),

ProductID = orderDetail.Field("ProductID")

}).ToList();

As evident from the query, the Customerstable is related to the Orderstable via the CustomerIDfield, and the Orderstable is related to the Order Detailstable via the OrderIDfield.

Figure 14-9 shows the result of the query.

Figure 14-9

So far you've used the Field()extension method to access the field of a DataTableobject. For example, the following program uses LINQ to DataSet to query all the customers living in the USA. The result is then reshaped using an anonymous type:

SqlConnection conn;

SqlCommand comm;

SqlDataAdapter adapter;

DataSet ds = new DataSet();

conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;" +

"Initial Catalog=Northwind;Integrated Security=True");

comm = new SqlCommand("SELECT * FROM Customers", conn);

adapter = new SqlDataAdapter(comm);

adapter.Fill(ds, "Customers");

var query1 =

(from customer in ds.Tables[0].AsEnumerable()

where customer.Field("Country") == "USA"

select new {

CustomerID = customer.Field("CustomerID"),

CompanyName = customer.Field("CompanyName"),

ContactName = customer.Field("ContactName"),

ContactTitle = customer.Field("ContactTitle")

}).ToList();

dataGridView1.DataSource = query1;

As your query gets more complex, the use of the Field()extension method makes the query unwieldy. A good way to resolve this is to use the typed DataSet feature in ADO.NET. A typed DataSet provides strongly typed methods, events, and properties and so this means you can access tables and columns by name, instead of using collection-based methods.

To add a typed DataSet to your project, first add a DataSetitem to your project in Visual Studio 2008 (see Figure 14-10). Name it TypedCustomersDataset.xsd.

Figure 14-10

In the Server Explorer window, open a connection to the database you want to use (in this case it is the Northwind database) and drag and drop the Customerstable onto the design surface of TypedCustomersDataSet.xsd(see Figure 14-11). Save the TypedCustomersDataSet.xsdfile.

Figure 14-11