Книга: C# 2008 Programmer

Joining Tables

Joining Tables

So far you've been dealing with a single table. In real life, you often have multiple, related tables. A good example is the Northwind sample database, which contains a number of related tables, three of which are shown in Figure 14-7.

Figure 14-7

Here, the Customers table is related to the Orders table via the CustomerID field, while the Orders table is related to the Order_Details table via the OrderID field.

You can use LINQ to DataSet to join several tables stored in a DataSet. Here's how. First, load the three tables into the DataSet, using the following code:

conn = new SqlConnection(@"Data Source=.SQLEXPRESS;" +
 "Initial Catalog=Northwind;Integrated Security=True");
comm =
 new SqlCommand("SELECT * FROM Customers; SELECT * FROM Orders; SELECT * FROM [Order Details]",
 conn);
adapter = new SqlDataAdapter(comm);
adapter.Fill(ds);

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

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 — customersTable and 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<string>("CustomerID") equals order.Field<string>("CustomerID")
 select new {
  id = customer.Field<string>("CustomerID"),
  CompanyName = customer.Field<string>("CompanyName"),
  ContactName = customer.Field<string>("ContactName"),
  OrderDate = order.Field<DateTime>("OrderDate"),
  ShipCountry = order.Field<string>("ShipCountry")
 }).ToList();

As evident in the query, the Customers and Orders table are joined using the CustomerID field. 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 DataGridView control if desired. Figure 14-8 shows the result bound to a DataGridView control.

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<string>("CustomerID"),
 order => order.Field<string>("CustomerID"),
 (customer, order) => new {
  id = customer.Field<string>("CustomerID"),
  CompanyName = customer.Field<string>("CompanyName"),
  ContactName = customer.Field<string>("ContactName"),
  OrderDate = order.Field<DateTime>("OrderDate"),
  ShipCountry = order.Field<string>("ShipCountry")
 })).ToList();

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

//---three tables join---
var query2 =
 (from customer in customersTable.AsEnumerable()
 join order in ordersTable.AsEnumerable() on
 customer.Field<string>("CustomerID") equals order.Field<string>("CustomerID")
 join orderDetail in orderDetailsTable.AsEnumerable() on
 order.Field<int>("OrderID") equals orderDetail.Field<int>("OrderID")
 orderby order.Field<int>("OrderID")
 select new {
  id = customer.Field<string>("CustomerID"),
  CompanyName = customer.Field<string>("CompanyName"),
  ContactName = customer.Field<string>("ContactName"),
  OrderDate = order.Field<DateTime>("OrderDate"),
  ShipCountry = order.Field<string>("ShipCountry"),
  OrderID = orderDetail.Field<int>("OrderID"),
  ProductID = orderDetail.Field<int>("ProductID")
 }).ToList();

As evident from the query, the Customers table is related to the Orders table via the CustomerID field, and the Orders table is related to the Order Details table via the OrderID field.

Figure 14-9 shows the result of the query.

Figure 14-9

Оглавление книги