/// <summary> /// Calculates the statistics for the Northwind database and shows them in the form, illustrating the /// direct database power LLBLGen Pro offers you through scalar functions, aggregates, group by and expressions. /// </summary> /// <remarks>No error checking is applied to this routine, it's for illustrating the framework functionality.</remarks> private void CalculateStatistics() { // get amount customers CustomerCollection customers = new CustomerCollection(); int amountCustomers = customers.GetDbCount(); _amountCustomersTextBox.Text = amountCustomers.ToString(); // get all order prices ResultsetFields orderPricesFields = new ResultsetFields(2); orderPricesFields.DefineField(OrderDetailFields.OrderId, 0, "OrderId"); orderPricesFields.DefineField(OrderDetailFields.ProductId, 1, "OrderPrice", "", AggregateFunction.Sum); orderPricesFields[1].ExpressionToApply = (OrderDetailFields.Quantity * OrderDetailFields.UnitPrice); IGroupByCollection groupBy = new GroupByCollection(); groupBy.Add(orderPricesFields[0]); // fetch in a datatable, orderid + the order price. DataTable orderPrices = new DataTable(); TypedListDAO dao = new TypedListDAO(); dao.GetMultiAsDataTable(orderPricesFields, orderPrices, 0, null, null, null, true, groupBy, null, 0, 0); // calculate average order price and which customer has the most expensive order decimal averageOrderPrice = 0.0M; decimal highestOrderPrice = 0.0M; int orderIdWithHighestPrice = 0; for (int i = 0; i < orderPrices.Rows.Count; i++) { decimal currentOrderPrice = (decimal)orderPrices.Rows[i]["OrderPrice"]; if (currentOrderPrice > highestOrderPrice) { highestOrderPrice = currentOrderPrice; orderIdWithHighestPrice = (int)orderPrices.Rows[i]["OrderId"]; } averageOrderPrice += currentOrderPrice; } averageOrderPrice = averageOrderPrice / orderPrices.Rows.Count; _highestOrderPriceTextBox.Text = highestOrderPrice.ToString("C"); _averageOrderPriceTextBox.Text = averageOrderPrice.ToString("C"); // get order with highest price. Pull customer also in 1 go IPrefetchPath prefetchPathCustomer = new PrefetchPath((int)EntityType.OrderEntity); prefetchPathCustomer.Add(OrderEntity.PrefetchPathCustomer); OrderEntity highestOrder = new OrderEntity(); highestOrder.FetchUsingPK(orderIdWithHighestPrice, prefetchPathCustomer); _customerWithHighestOrder = highestOrder.Customer; // already loaded through prefetch path. _highestOrderCustomerTextBox.Text = _customerWithHighestOrder.CompanyName; // get customer with most orders. ResultsetFields orderCustomerFields = new ResultsetFields(2); orderCustomerFields.DefineField(OrderFields.CustomerId, 0, "CustomerID"); orderCustomerFields.DefineField(OrderFields.OrderId, 1, "AmountOrders", "", AggregateFunction.Count); groupBy = new GroupByCollection(); groupBy.Add(orderCustomerFields[0]); SortExpression sorter = new SortExpression(); sorter.Add(new SortClause(orderCustomerFields[1], SortOperator.Descending)); // now fetch the list, just 1 row, ordered descending on amount DataTable orderCustomer = new DataTable(); dao.GetMultiAsDataTable(orderCustomerFields, orderCustomer, 1, sorter, null, null, true, groupBy, null, 0, 0); _mostOrdersPerCustomerTextBox.Text = orderCustomer.Rows[0]["AmountOrders"].ToString(); // we'll assume the data was there, so there is a row. _customerWithMostOrders = new CustomerEntity(orderCustomer.Rows[0]["CustomerID"].ToString()); _mostOrdersCustomerTextBox.Text = _customerWithMostOrders.CompanyName; }