[ 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 Text: Using Expression Columns to Display Aggregate Values
[ Team LiB ]
Recipe 3.7 Using Expression Columns to Display Aggregate Values
You want to add summary information such as averages, sums, and counts to a table
based on related child rows.
Use expression columns to perform aggregate calculations based on child rows.
The sample code starts by creating a DataSet containing the Orders and Order Details
tables from Northwind sample database and a relation between them. An expression is
added to the Order Details table to calculate the extended price for each row. Aggregate
values for the total extended price of the order and the number of Order Detail rows are
added to the Orders table. Finally, the default view of the Orders table is bound to the
data grid to display the results.
The C# code is shown in Example 3-7.
Example 3-7. File: ChildAggregateForm.cs
// Namespaces, variables, and constants
// Table name constants
private const String ORDERS_TABLE = "Orders";
private const String ORDERDETAILS_TABLE = "OrderDetails";
// Relation name constants
private const String ORDERS_ORDERDETAILS_RELATION =
// Field name constants
private const String ORDERID_FIELD = "OrderID";
// . . .
DataSet ds = new DataSet( );
// Fill the Order table and add it to the DataSet.
da = new SqlDataAdapter("SELECT * FROM Orders",
DataTable orderTable = new DataTable(ORDERS_TABLE);
// Fill the OrderDetails table and add it to the DataSet.
da = new SqlDataAdapter("SELECT * FROM [Order Details]",
DataTable orderDetailTable = new DataTable(ORDERDETAILS_TABLE);
// Create a relation between the tables.
// Create the expression column for the line total.
"(Quantity * UnitPrice) * (1-Discount)");
// Create the OrderDetails aggregate values in the Order table.
// Bind the DataSet to the grid.
childAggregateDataGrid.DataSource = ds.DefaultViewManager;
childAggregateDataGrid.DataMember = ORDERS_TABLE;
You can create aggregate columns within a table to display summary information for
related child records. When a DataRelation exists between a parent and child table in a
DataSet, you can refer to a child record by adding the prefix Child. to the column name in
the child table. In the sample code, COUNT(Child.ProductID) returns the number of
Order Details child records for the parent Order record. Expression columns support
aggregate functions as shown in Table 3-2.
Table 3-2. Aggregate functions supported by expression columns
AVG Average of all values
COUNT Number of values
MAX Largest value
MIN Smallest value
STDEV Statistical standard deviation of all values
SUM Sum of all values
VAR Statistical variance of all values
If the parent table has more than one child table, the relationship must be specified in the
aggregate function. The fully qualified syntax to access the count of child Order Details
records would be:
You can refer to the parent table for a child in a similar manner by adding the prefix
Parent. 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 ]