[ Team LiB ] Recipe Creating DataSet Relationships from SQL Server Relationships Problem You need to create relationships between DataTable objects within your DataSet at runtime based on the relationships that are defined in your SQL Server database. | Team LiB Recipe Creating DataSet Relationships from SQL Server Relationships Problem You need to create relationships between DataTable objects within your DataSet at runtime based on the relationships that are defined in your SQL Server database. Solution Use INFORMATION_SCHEMA views and system tables to create relationships automatically at runtime. The schema of table TBL1011a used in this solution is shown in Table 10-5. Table 10-5. TBL1011a schema Column name Data type Length Allow nulls a int 4 No b int 4 No c int 4 No The schema of table TBL1011b used in this solution is shown in Table 10-6. Table 10-6. TBL1011b schema Column name Data type Length Allow nulls d int 4 No e int 4 No a2 int 4 No b2 int 4 No The sample code creates a DataSet containing the Orders table and Order Details table from the Northwind sample database. The tables TBL1011a and TBL1011b related through a multicolumn key are also added to the DataSet. Next the result set of a query of the INFORMATION_SCHEMA views are examined to determine the relationships specified in the data source between the tables in the DataSet. DataRelation objects are created in the DataSet for the identified relationships. The C code is shown in Example 10-11. Example 10-11. File Namespaces variables and constants using System using using using using using . . . DataSet ds new DataSet SqlDataAdapter da Add the Orders and Order Details tables to the DataSet. da new SqlDataAdapter SELECT FROM Orders Sql_ConnectString ds ORDERS_TABLE da new SqlDataAdapter SELECT FROM Order Details Sql_ConnectString ds ORDERDETAILS_TABLE Add the TBL1011a and TBL1101b tables to the DataSet. da new SqlDataAdapter SELECT FROM TBL1011a Sql_ConnectString ds PARENTMULTICOLKEYTABLE da new .
