Adding a Column to a Parent DataTable That Aggregates a Child Table's Column Values : DataTable « ADO.Net « C# / CSharp Tutorial

1. C# / CSharp Tutorial
2. ADO.Net
3. DataTable

using System;
using System.Data;
using System.Data.SqlClient;

    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();

            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",
                ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"],
                ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]);
            ds.Relations.Add(dr);

            ds.Tables["SalesOrderHeader"].Columns.Add("SumDetailLineTotal",typeof(decimal), "SUM(Child.LineTotal)");

            for (int i = 0; i < 2; i++){
                DataRow rowHeader = ds.Tables["SalesOrderHeader"].Rows[i];
                Console.WriteLine(rowHeader["SalesOrderID"]);
                Console.WriteLine(rowHeader["CustomerID"]);
                Console.WriteLine(rowHeader["SumDetailLineTotal"]);
                foreach (DataRow rowDetail in rowHeader.GetChildRows(dr))
                {
                    Console.WriteLine(rowDetail["SalesOrderID"]);
                    Console.WriteLine(rowDetail["SalesOrderDetailID"]);
                    Console.WriteLine(rowDetail["LineTotal"]);
                }
            }
        }
    }