CS 112D Lecture Notes - Using DataAdapters and DataGrids with Multiple Related Tables (Chapter 6 part 3)

Using DataAdapters with a DataSet that Combines Multiple Related Tables

• One DataAdapter's SelectCommand can contain two different queries of two different tables to produce two different DataTables. Simply put the two queries in the same CommandText string, separated by a semi-colon.
• The DataAdapter's Fill method can then be used specifying the DataSet only, and two DataTables will be created with the results of the queries. The DataTables will be named "Table" and "Table1" because no names were specified in the Fill method call.
• The above procedure is better than calling Fill twice because it requires only one round-trip to the database. (So it's faster)
• To update the database with changes made in the DataSet, separate DataAdapter objects are needed for each table. Those DataAdapters aren't used to fill the DataSet; only to update the database.
• As usual, a Select query is used to generate the needed update, delete, and insert commands for a DataAdapter
• To avoid Foreign Key violations, changes to parent/child data should be made in the following order: (e.g. parent may be Customers, child may be Orders)
  1. Delete rows in the child table
  2. Insert, update, and delete rows in the parent table (deleting a parent record will delete corresponding child records if cascading delete triggers are in place (as they are in this example)
  3. Insert and update rows in the child table (done last in case references to changed parent records are made)
• The DataTable's GetChanges method can be used to get a DataTable that is filled only with records repesenting a certain kind of change (insert, update, or delete)
• Such a DataTable can then be used with Update.

Using the DataGrid Control with a DataSet that Combines Multiple Related Tables

• A DataGrid's DataSource can be a DataSet with multiple tables as described above.
• When the DataSource is a DataSet instead of a DataTable, the user is given a choice of which DataTable to display.
• If a DataRelation links two DataTables in a DataSet, the DataControl uses that relation to allow user to see related parent records.
• The top-right corner of the DataGrid has two buttons that can be used at runtime to change what's displayed. Furthermore, plus-signs (+) are used to show where an expanded detail view is available.
• Play around with it!

Return to the main CS 112D page