Using Expression Columns to Display Aggregate Values

[ Team LiB ] Recipe 3.7 Using Expression Columns to Display Aggregate 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 perform aggregate calculations based on child rows.
Nội dung trích xuất từ tài liệu:
Using Expression Columns to Display Aggregate Values[ Team LiB ]Recipe 3.7 Using Expression Columns to Display Aggregate ValuesProblemYou want to add summary information such as averages, sums, and counts to a tablebased on related child rows.SolutionUse expression columns to perform aggregate calculations based on child rows.The sample code starts by creating a DataSet containing the Orders and Order Detailstables from Northwind sample database and a relation between them. An expression isadded to the Order Details table to calculate the extended price for each row. Aggregatevalues for the total extended price of the order and the number of Order Detail rows areadded to the Orders table. Finally, the default view of the Orders table is bound to thedata grid to display the results.The C# code is shown in Example 3-7.Example 3-7. File: ChildAggregateForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Data;using System.Data.SqlClient;// Table name constantsprivate const String ORDERS_TABLE = Orders;private const String ORDERDETAILS_TABLE = OrderDetails;// Relation name constantsprivate const String ORDERS_ORDERDETAILS_RELATION = Orders_OrderDetails_Relation;// Field name constantsprivate const String ORDERID_FIELD = OrderID;// . . .DataSet ds = new DataSet( );SqlDataAdapter da;// Fill the Order table and add it to the DataSet.da = new SqlDataAdapter(SELECT * FROM Orders, ConfigurationSettings.AppSettings[Sql_ConnectString]);DataTable orderTable = new DataTable(ORDERS_TABLE);da.Fill(orderTable);ds.Tables.Add(orderTable);// Fill the OrderDetails table and add it to the DataSet.da = new SqlDataAdapter(SELECT * FROM [Order Details], ConfigurationSettings.AppSettings[Sql_ConnectString]);DataTable orderDetailTable = new DataTable(ORDERDETAILS_TABLE);da.Fill(orderDetailTable);ds.Tables.Add(orderDetailTable);// Create a relation between the tables.ds.Relations.Add(ORDERS_ORDERDETAILS_RELATION, ds.Tables[ORDERS_TABLE].Columns[ORDERID_FIELD], ds.Tables[ORDERDETAILS_TABLE].Columns[ORDERID_FIELD], true);// Create the expression column for the line total.orderDetailTable.Columns.Add(OrderDetailTotal, typeof(Decimal), (Quantity * UnitPrice) * (1-Discount));// Create the OrderDetails aggregate values in the Order table.orderTable.Columns.Add(OrderDetailCount, typeof(int), COUNT(Child.ProductId));orderTable.Columns.Add(OrderTotal, typeof(Decimal), SUM(Child.OrderDetailTotal));// Bind the DataSet to the grid.childAggregateDataGrid.DataSource = ds.DefaultViewManager;childAggregateDataGrid.DataMember = ORDERS_TABLE;DiscussionYou can create aggregate columns within a table to display summary information forrelated child records. When a DataRelation exists between a parent and child table in aDataSet, you can refer to a child record by adding the prefix Child. to the column name inthe child table. In the sample code, COUNT(Child.ProductID) returns the number ofOrder Details child records for the parent Order record. Expression columns supportaggregate functions as shown in Table 3-2. Table 3-2. Aggregate functions supported by expression columns Function DescriptionAVG Average of all valuesCOUNT Number of valuesMAX Largest valueMIN Smallest valueSTDEV Statistical standard deviation of all valuesSUM Sum of all valuesVAR Statistical variance of all valuesIf the parent table has more than one child table, the relationship must be specified in theaggregate function. The fully qualified syntax to access the count of child Order Detailsrecords would be:COUNT(Child(Orders_OrderDetails_Relation).ProductId)You can refer to the parent table for a child in a similar manner by adding the prefixParent. to the column name. In the previous example, for a row in the Order Details table,Parent.CustomerID refers to the CustomerID for the parent Orders row.[ Team LiB ]