Description:
How to Implement an Unbound Calculated Grid Column
Answer:
The XtraGrid supports the Unbound Columns feature, which allows you to create a grid column not bound to any field in the grid's data source. Values for this column need to be calculated manually, via a dedicated event or using an expression.
This article shows how you can create an unbound column on the data source level.
It demonstrates how to use this column to calculate the sum of a detail table's fields and display the result in a master grid view. When the corresponding detail field is modified, the sum is automatically updated in the master view.
This example uses the sample Northwind database included in MS SQL Server. Customers represent the master table and the detail table is Orders.
The SQL SELECT statement creates a calculated field in the Customers table, which is the total freight payments of a given customer (OrdersSum):
SELECT
CustomerID,
CompanyName,
( SELECT SUM(o.Freight) FROM Orders o
WHERE (o.CustomerID = c.CustomerID)
) AS OrdersSum
FROM
Customers c
When generating a dataset, the OrdersSum column is automatically created in the Customers data table. However, it is a read-only column, because it is a calculated field. To enable modifications, set the DataColumn.ReadOnly property to False. This is necessary, because OrdersSum must be automatically updated, when the Freight column is changed in the detail dataset.
It's best to use the ColumnChanging and RowChanged events of the Orders data table to update the Customers' OrdersSum column. This approach allows you to refresh calculated column values on the client side without re-querying the server. The following code demonstrates how to make a column editable and assign ColumnChanging and RowChanged handlers to a detail table. The event handlers explicitly modify the OrdersSum field. Please download and review the attached sample to see the complete implementation of this feature. Note: You will need to establish a connection string to your MS SQL server.
C#gridView1.Columns["OrdersSum"].Options |= ColumnOptions.ReadOnly;
dataSet11.Customers.Columns["OrdersSum"].ReadOnly = false;
DataTable orders = dataSet11.Tables["Orders"];
orders.ColumnChanging += new DataColumnChangeEventHandler(Orders_ColumnChanging);
orders.RowChanged += new DataRowChangeEventHandler(Orders_RowChanged);
See also:
How to Implement an Unbound Calculated Column II
How to select rows via an unbound checkbox column
</para>