Mega Code Archive

 

Categories / C# Tutorial / ADO Net

 

Adding a Column to a Parent DataTable That Aggregates a Child Tables Column Values

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"]);                 }             }         }     }