ado| Data Expression Column
In addition to containing static data, you can assign values to DataColumn based on the result of an expression. An expression is a string assigned to the DataColumn.Expression property.
When an expression is used in conjunction with related data, the data column can contain:
Calculated value of the related data column.
Aggregate information about the related data columns.
Logical comparison results of related data.
To illustrate the value expression columns when working with related data, we will introduce an example for each usage and add it to the Datarelationexample application.
Add an expression column that contains calculated values
Computed columns contain the results of mathematical operations. You can calculate from an existing column by taking a value. A new column named Total will be added to the order BOM containing the value returned by the expression UnitPrice * Quantity (the total dollar value of the order).
Add an expression column
In Solution Explorer (Solution Explorer), right-click Form1 and select View Code from the shortcut menu.
Add the following code above the code already in the Form1_Load event handler:
' Visual Basic
' Create an expression column named Total in the order BOM.
Dim dctotal as DataColumn = new DataColumn ("Total")
Dctotal.datatype = System.Type.GetType ("System.Decimal")
Dctotal.expression = "UnitPrice * Quantity"
DSNORTHWIND1.ORDER_DETAILS.COLUMNS.ADD (Dctotal)
C#
Create an expression column named Total in the order BOM.
DataColumn dctotal = new DataColumn ("Total");
Dctotal.datatype = System.Type.GetType ("System.Decimal");
Dctotal.expression = "UnitPrice * Quantity";
DSNORTHWIND1.ORDER_DETAILS.COLUMNS.ADD (dctotal);
Run the application.
Select an order in the list box.
Check the order details in the rich text box, and note that each record has a new total column showing the product of the UnitPrice and Quantity fields.
Closes the form.
Add an expression column that contains aggregate information
The Expression property supports several aggregate functions (Sum, AVG, Count, and so on). For more information, see DataColumn.Expression Properties.
To demonstrate how to generate aggregate information, you need to add a new column named OrderTotal to the order table. This column uses the sum feature to return the total dollar value of all child order detail records based on the order selected in the list box (lborders). The value is then displayed above each order detail in the rich text box.
Creating OrderTotal Columns
In Solution Explorer (Solution Explorer), right-click Form1 and select View Code from the shortcut menu.
In the Form1_Load event handler, add the following code below the code that created the total column in the order BOM:
' Visual Basic
' Create an expression column named OrderTotal in the order table.
Dim dcordertotal as DataColumn = new DataColumn ("OrderTotal")
Dcordertotal.datatype = System.Type.GetType ("System.Decimal")
Dcordertotal.expression = "Sum (child.total)"
DSNORTHWIND1.ORDERS.COLUMNS.ADD (Dcordertotal)
C#
Create an expression column named OrderTotal in the order table.
DataColumn dcTotal2 = new DataColumn ("OrderTotal");
Dctotal2.datatype = System.Type.GetType ("System.Decimal");
Dctotal2.expression = "Sum (child.total)";
DSNORTHWIND1.ORDERS.COLUMNS.ADD (DCTOTAL2);
Display aggregate information above all order details
In the Lborders_selectedindexchanged event handler, add the following code below the Dim details As String = "" or the String details = "line:
Run the application.
Select an order in the list box.
Note that the total amount for all order details for the selected order will appear in the first line in the rich text box.
Select another order in the list box to update the display to reflect the newly selected order.
Closes the form.
Add an expression column that contains a logical evaluation
The Expression property can populate a data column based on calculated values in other columns. For example, the Ordersize column in the Order table can contain the value "big" (if the total order is greater than 1000) or "Small" (if the total order is less than 1000).
To demonstrate this type of expression, you need to add code to the Datarelationexample application to do the following:
Add a data column named Ordersize to the order table.
Populate the Ordersize column based on the value of the related order details.
Displays both the value of the Ordersize column and the OrderTotal value at the top of the rich text box.
Add code to create ordersize column
In Solution Explorer (Solution Explorer), right-click Form1 and select View Code from the shortcut menu.
In the Form1_Load event handler, add the following code below the code that creates the OrderTotal column in the Order table:
' Visual Basic
' Create an expression column named Ordersize in the order table.
Dim dcordersize as DataColumn = new DataColumn ("Ordersize")
Dcordersize.datatype = System.Type.GetType ("System.String")
Dcordersize.expression = "IIF (Sum (child.total) <1000, ' Small ', ' big ')"
DSNORTHWIND1.ORDERS.COLUMNS.ADD (Dcordersize)
C#
Create an expression column named Ordersize in the order table.
DataColumn dcordersize = new DataColumn ("Ordersize");
Dcordersize.datatype = System.Type.GetType ("System.String");
Dcordersize.expression = "IIF (Sum (child.total) <1000, ' Small ', ' big ')";
DSNORTHWIND1.ORDERS.COLUMNS.ADD (dcordersize);
Displays the value of the Ordersize
In the Lborders_selectedindexchanged event handler, add the following code above the first for each row:
Run the application.
Select an order in the list box.
Check the first line in the rich text box. The ordersize of the selected order is displayed on the right side of the OrderTotal.
Select another order in the list box to update the display to reflect the newly selected order.
From the Debug (Debug) menu, select Stop Debugging (Stop Debugging).
Additional information about related tables
There is a need to mention some other information to enrich the content of this article.
The order in which the related data tables are populated is important
The fill order of the related data tables has a significant impact on the output of the data and must be considered when designing the application. For example, note the case of the last populated Customer table. When the Customer table is populated, the combo box fills the customer name value. The SelectedIndexChanged event is raised when the combo box is populated. This will execute the code in the event handler. Because the order table has not been populated, the GetChildRows method returns 0 (0) records, and the title of the form displays an error message. Try this: Change the code to populate the Customer table first and run the application. The title of the form shows the ALFKI 0 (0) order, which is not correct.
Returns a specific version of a related record
You can return a specific version of a data row by passing the required DataRowVersion as a second (optional) parameter to the GetChildRows or GetParentRow method. As an example of this application, if you only want to view the original order for a particular customer, you can change the code in the SelectedIndexChanged event of the combo box to code similar to the following. Because the data in this application has not changed, the following code does not have a noticeable effect, and here is just a description.
' Visual Basic
' Use only the original child rows of the selected customer
' Populate the array.
Dim draorders as DataRow () = DsNorthwind1.Customers.FindByCustomerID _
(CbCustomers.SelectedValue.ToString ()). GetChildRows _
("CustomersOrders", DataRowVersion.Original)
C#
Use only the original child rows of the selected customer
Fills the array.
DataRow draorders = DsNorthwind1.Customers.FindByCustomerID
(CbCustomers.SelectedValue.ToString ()). GetChildRows
("CustomerOrders", datarowversion.original);
Summarize
To access related records for a particular data row, you can call the GetChildRows or GetParentRow method of the row to pass the data relationship that connects the data row and its related records. You can then access the related records by examining the data row (or array of data rows) returned by the method call.
By assigning a valid expression string to the DataColumn.Expression property and adding the data column to the corresponding DataTable.Columns collection, you can calculate, sum, and logically evaluate the values in the related records.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
