ADO.NET 3.5 Cookbook, 2nd Edition

2.12. Adding a Column to a Child DataTable That Displays Data from the Parent Table

Problem

You want to add a column to a DataTable that displays a value from a row in a related table in the DataSet.

Solution

Use expression columns to retrieve lookup values based on DataRelation objects.

The solution creates a new DataSet containing the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables from AdventureWorks.A DataRelation is created between the tables. A column is added to the Sales.SalesOrderDetail table that gets the CustomerID from the parent Sales.SalesOrderHeader table using the relation between the tables. Parent and child rows are output to the console.

The C# code in Program.cs in the project AddParentColumnDataTable is shown in Example 2-12.

Example 2-12. File: Program.cs for AddParentColumnDataTable solution

using System; using System.Data; using System.Data.SqlClient; namespace AddParentColumnDataTable { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = @"SELECT * FROM Sales.SalesOrderHeader; SELECT * FROM Sales.SalesOrderDetail;"; DataSet ds = new DataSet(); // Fill the DataSet SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); da.TableMappings.Add("Table", "SalesOrderHeader"); da.TableMappings.Add("Table1", "SalesOrderDetail"); da.Fill(ds); // Relate the Header and Order tables in the DataSet DataRelation dr = new DataRelation("SalesOrderHeader_SalesOrderDetail", ...