ADO.NET 3.5 Cookbook, 2nd Edition

2.13. Adding a Column to a Parent DataTable That Aggregates a Child Table's Column Values

Problem

You want to add summary information such as averages, sums, and counts to a table based on related child rows.

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. An expression column is added to the Sales.SalesOrderHeader table that gets the sum of the related LineTotal from the child Sales.SalesOrderDetail table. Parent and child rows are output to the console.

The C# code in Program.cs in the project AddAggregateChildColumnDataTable is shown in Example 2-13.

Example 2-13. File: Program.cs for AddAggregateChildColumnDataTable solution

using System; using System.Data; using System.Data.SqlClient; namespace AddAggregateChildColumnDataTable { 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 ...