|
Expression Column
In addition to static data, you can also assign a value to datacolumn based on the expression results. An expression is a string assigned to the datacolumn. Expression attribute.
When expressions are used together with related data, data columns can include:
The calculated value of the related data column.
Total Information of related data columns.
Logical comparison results of related data.
To describe the value expression column when processing the relevant data, we will introduce an example for each usage and add it to the datarelationexample application.
Add an expression column containing the calculated value
The calculated column contains the mathematical calculation result. Values can be calculated from existing columns. A new column named total will be added to the order list, which will contain the value returned by the expression unitprice * quantity (the total dollar value of the Order ).
Add expression Column
In Solution Explorer, right-click form1 and select view code from the shortcut menu ).
Add the following code to the existing code in the form1_load event handler:
'Visual basic
'Create an expression column named 'Total' in the order list.
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 list.
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 from the list box,
Check the Order details in the RTF text box, and note that each record has a new total column, showing the product of the unitprice and quantity fields.
Close the form.
Add an expression column containing the total information
The expression attribute supports several Aggregate functions (sum, AVG, count, and so on ). For more information, see datacolumn. Expression attributes.
To show how to generate the total information, you need to add a new column named ordertotal in the order table. This column uses the sum function to return the total USD value for all suborder details records based on the order selected in the list box (lborders. The value is displayed above the Order details in the RTF text box.
Create ordertotal Column
In Solution Explorer, right-click form1 and select view code from the shortcut menu ).
In the formateload event handler, add the following code to the bottom of the Code that creates the total column in the order list:
'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 the total information above all order details
In the lborders_selectedindexchanged event handler, add the following code to the dim details as string = "" or string details = "" line:
'Visual basic
Details = "total orders:" & string. Format ("{0: c }",_
Dsnorthwind1.orders. findbyorderid (ctype (lborders. selecteditem ,_
Integer) ("ordertotal") & controlchars. CRLF
// C #
Details = "total orders:" +
String. Format ("{0: c}", dsnorthwind1.orders. findbyorderid
(INT) lborders. selecteditem) ["ordertotal"]) + "/N ";
Run the application.
Select an order from the list box,
Note that the total amount of all order details for the selected order is displayed in the first line in the RTF text box.
Select another order in the list box. The new order is updated to reflect the new order.
Close the form.
Add an expression column containing the logical value
The expression attribute can be used to fill a data column based on the calculated values in other columns. For example, the ordersize column in the order table can contain the value "big" (if the total order amount is greater than 1000) or "small" (if the total order amount is less than 1000 ).
To demonstrate such expressions, you need to add code in the datarelationexample application to perform the following operations:
Add a data column named ordersize to the order table.
Fill the ordersize column according to the order details.
The ordersize column value and ordertotal value are displayed at the top of the RTF text box.
Code for creating an ordersize Column
In Solution Explorer, right-click form1 and select view code from the shortcut menu ).
In the formateload event handler, add the following code to 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 );
Display ordersize Value
In the lborders_selectedindexchanged event handler, add the following code to the top of the first for each row:
'Visual basic
Details & = "(" & ctype (dsnorthwind1.orders. findbyorderid _
(Ctype (lborders. selecteditem, integer) ("ordersize"), string )&")"_
& Controlchars. CRLF
// C #
Details + = "(" + dsnorthwind1.orders. findbyorderid
(INT) lborders. selecteditem) ["ordersize"] + ")/n ";
Run the application.
Select an order from the list box.
Check the first line in the RTF text box. The ordersize of the selected order is displayed on the Right of ordertotal.
Select another order in the list box. The new order is updated to reflect the new order.
Select Stop debugging from the Debug menu ).
Other information about related tables
It is necessary to mention other information here to enrich the content of this article.
The order of filling data tables is very important.
The filling sequence of related data tables has a great impact on data output. Therefore, you must consider it when designing an application. For example, pay attention to the last filled customer table. When filling the customer table, the combox will fill in the customer name value. When the combo box is filled, the selectedindexchanged event is triggered. This will execute the code in the event handler. Since the order table has not been filled, the getchildrows method returns zero (0) records, and the title of the form displays an error message. Try: change the code to first fill the customer table and run the application. The title of the form displays zero (0) Orders of alfki, which is incorrect.
Returns a specific version of the relevant record.
You can return a specific version of the data row by passing the required datarowversion as the second (optional) parameter to the getchildrows or getparentrow method. Take this application as an example. If you only want to view the original order of a specific customer, you can change the code in the selectedindexchanged event of the combo box to code similar to the following. Since the data in this application has not been changed, the following code will not produce significant results. Here is just a description.
'Visual basic
'Use only the original child row of the selected customer
'Fill the array.
Dim draorders as datarow () = dsnorthwind1.mers MERs. findbycustomerid _
(Cbcustomers. selectedvalue. tostring (). getchildrows _
("Customersorders", datarowversion. Original)
// C #
// Only use the original child row of the selected customer
// Fill in the array.
Datarow draorders = dsnorthwind1.mers MERs. findbycustomerid
(Cbcustomers. selectedvalue. tostring (). getchildrows
("Customerorders", datarowversion. Original );
Summary
To access related records of a specific data row, you can call the getchildrows or getparentrow method of the row to pass the Data Relationship connecting the data row and its related records. Then, you can access related records by checking the data rows (or data row arrays) returned by this method call.
Use datacolumn. the expression attribute allocates a valid expression string and adds the data column to the corresponding datatable. in the columns set, the values in the relevant records can be calculated, summed up, and logically evaluated.
|
