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); ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.