Creating Dataset Columns Using Expressions
As in some databases, a data table in a dataset can contain columns in which the value is calculated rather than read from a store. For example, in an orders detail record, it might make sense to define an ExtendedPrice column by multiplying the quantity and the price, rather than actually storing the total in the record. Calculated columns can also add or count values in child records and filter other records. You can define calculated columns using expressions.
The syntax of the expression consists of standard arithmetic, Boolean, and string operators and literal values. You can reference a data value using its column name (as you would in an SQL statement) and include aggregate functions such as Count.
For example, the expression for an ExtendedPrice column based on the UnitPrice and Quantity columns of the current record might simply be:
UnitPrice * Quantity
To refer to columns in child tables, you can use the reserved word "Child." followed by the name of the column. For example, the following expression returns the average value of the Price column in all related child records:
Column Expressions in Typed Datasets
You can create the column and expression for typed datasets in the XML Designer. For more information about creating columns using the XML Designer, see Creating Tables in XML Schemas.
To create column expressions in typed datasets
- If the schema is not already open in the XML Designer, double-click the XML Schema (.xsd) file in Solution Explorer.
- In the grid representing the table you want to work with, add a new column by performing the following steps:
- In the first blank line of the table grid, select Element from the drop-down list.
- In the next box in the same line, enter a name for the column, using a valid column identifier.
- In the next box, select an appropriate data type for the results of the calculation.
- Select the new column in the table grid, and then in the Properties window, enter an expression in the Expression property. For more information about column expression syntax, see the DataColumn.Expression property.
Column Expressions in Untyped Datasets
If you are working with an untyped dataset, you cannot use the XML Designer because there is no schema (unless you generate one from the dataset using WriteXmlSchema). However, you can create the column and expression in the form or Component Designer.
To create column expressions in untyped datasets
- Select the untyped dataset in the form or component and open the Properties window.
- Click the button in the Tables property to open the DataTable Collection Editor dialog box.
- In the collection editor, click the button in the Columns property to open the DataColumn Collection Editor dialog box.
- Click Add to create a new column.
- Select the new column, and in the Properties grid, set the ColumnName property to provide a name by which you can refer to the column in code.
Note You can also set the (Name) property to give the column a different name at design time.
- Select an appropriate data type for the results of the calculation.
- Enter an expression in the Expression property. For more information about column-expression syntax, see the DataColumn.Expression property.