Displaying Columns from a Related DataTable

Chia sẻ: Bui Tuan | Ngày: | Loại File: PDF | Số trang:4

Thêm vào BST

Báo xấu

72
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

[ Team LiB ] Recipe 2.17 Displaying Columns from a Related DataTable Problem You want to add a column to a DataTable that displays a value from a row in a related table in the DataSet. Solution Use expression columns to retrieve lookup values based on DataRelation objects.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Displaying Columns from a Related DataTable

[ Team LiB ] Recipe 2.17 Displaying Columns from a Related DataTable Problem You want to add a column to a DataTable that displays a value from a row in a related table in the DataSet. Solution Use expression columns to retrieve lookup values based on DataRelation objects. The sample code creates a new DataSet containing the Orders table and the Order Details table from Northwind. A DataRelation is created between the tables. A column is added to the Order Details table that gets the CustomerID from the parent Order using the relation created between the tables. Finally, the default view of the Orders table is bound to the data grid on the form. The C# code is shown in Example 2-22. Example 2-22. File: LookupColumnsForm.cs // Namespaces, variables, and constants using System; using System.Configuration; using System.Data; using System.Data.SqlClient; // Table name constants private const String ORDERS_TABLE = "Orders"; private const String ORDERDETAILS_TABLE = "OrderDetails"; // Relation name constants private const String ORDERS_ORDERDETAILS_RELATION = "Orders_OrderDetails_Relation"; // Field name constants private const String ORDERID_FIELD = "OrderID"; private const String CUSTOMERID_FIELD = "CustomerID"; private const String QUANTITY_FIELD = "Quantity"; // . . .
DataSet ds = new DataSet( ); // Fill the Orders table and add it to the DataSet. SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders", ConfigurationSettings.AppSettings["Sql_ConnectString"]); DataTable ordersTable = new DataTable(ORDERS_TABLE); da.Fill(ordersTable); ds.Tables.Add(ordersTable); // Fill the OrderDetails table and add it to the DataSet. da = new SqlDataAdapter("SELECT * FROM [Order Details]", ConfigurationSettings.AppSettings["Sql_ConnectString"]); DataTable orderDetailsTable = new DataTable(ORDERDETAILS_TABLE); da.Fill(orderDetailsTable); ds.Tables.Add(orderDetailsTable); // Create a relation between the tables. ds.Relations.Add(ORDERS_ORDERDETAILS_RELATION, ordersTable.Columns[ORDERID_FIELD], orderDetailsTable.Columns[ORDERID_FIELD], true); // Add a column to Orders for the total items in all Order Detail rows. ordersTable.Columns.Add("TotalQuantity", typeof(int), "Sum(Child." + QUANTITY_FIELD + ")"); // Add a column to Order Details getting CustomerID from Order parent. orderDetailsTable.Columns.Add(CUSTOMERID_FIELD, typeof(string), "Parent(" + ORDERS_ORDERDETAILS_RELATION + ")." + CUSTOMERID_FIELD); // Bind the default view of the Order table to the grid. resultDataGrid.DataSource = ordersTable.DefaultView; Discussion An expression column creates a calculated column that displays information from a related record. You can refer to a column in a parent record by affixing Parent. to the beginning of the name of the column in the parent table. If there are multiple parent records because of multiple data relations, the name of the DataRelation is specified within parentheses and Parent(DataRelationName). is affixed to the name of the column
in the parent table. The sample code creates a calculated column in the Order Details table retrieving the CustomerID from the parent Orders record for each Order Details record: orderDetailsTable.Columns.Add(CUSTOMERID_FIELD, typeof(string), "Parent(" + ORDERS_ORDERDETAILS_RELATION + ")." + CUSTOMERID_FIELD); When the constants are replaced with their values, this code is equivalent to: orderDetailsTable.Columns.Add("CustomerID", typeof(string), "Parent(Orders_OrderDetails_Relation).CustomerID"); Similarly, you can refer to child records within an expression by affixing the beginning of the column name in the child table with Child. or Child(DataRelationName). depending on whether the row has child records from multiple tables. Additionally, since multiple child records can exist for a parent record, the reference to the child column must be used within an aggregate function. Supported aggregates are listed in Table 2-15. Table 2-15. Aggregate functions supported in DataColumn expressions Aggregate Description Avg Average of all values Count Number of values Max Largest value Min Smallest value StDev Statistical standard deviation of all values Sum Sum of all values Var Statistical variance of all values The sample code creates a calculated column in the Orders table for the total quantity of all order detail items for an order with the following code: ordersTable.Columns.Add("TotalQuantity", typeof(int), "Sum(Child." + QUANTITY_FIELD + ")"); When the constants are replaced with their values, the code is: ordersTable.Columns.Add("TotalQuantity", typeof(int),
"Sum(Child.Quantity)"); Unlike the sample code referencing a parent row, the DataRelation name is omitted because only one DataRelation exists and it is unnecessary in this case. If the parent record has no child records, the aggregate function returns a null reference in C# or Nothing in VB.NET. [ Team LiB ]