TableAdapter with multiple related DataTables

In my ongoing VB6-to-VS2005 project, we have started using .NET 2.0 TableAdapters in the data access layer instead of SqlDataReaders to get strongly typed parameters and data when using our stored procedures. The main reason for this is that the customer in the existing solution has change management problems due to the untyped nature of DataReaders and the "late bound" style of accessing column values using .GetString[columnIdx].

When the customer has to change the result set of a stored procedure, this type of data access provides no compile time verification when getting a column value, and introduced bugs will not show up until run-time. Using TableAdapters "can dramatically increase your development and stabilization phase productivity by leveraging compile time verification for column changes or additions" [VB.NET team]. Compile time catching of bugs is way better than run-time exceptions, logging and debugging efforts.

The downside is of course lesser performance as a TableAdapter uses the DataSet/DataTable classes, but the size of our result sets are generally small and should thus not incure too much overhead. In addition, the number of required stored procedure calls might go down when retrieving 1:M related data from several related tables (e.g. master-details, multi-select picklists, etc). More on this below.

Read more about using TableAdapters in the VB.NET team blog and at MSDN. What is not covered in details in these articles, however, is how to work with hierarchical data; i.e. how to use TableAdapters to work with a DataSet that contains several related DataTables (e.g. order+order lines, person+favorite colors+favorite cars, etc).

Before you start to working with TableAdapters, you should recognize that a TableAdapter is not a new class in the .NET 2.0 framework. A TableAdapter is just a 'code generated' class that internally uses the classic DataAdapter, DataSet and DataTable classes. The generated code provides you with a typed dataset based on the SQL query / stored procedure you specify in the TableAdapter wizard. The generated class may also provide you with insert, update, and delete methods (see details at MSDN2). Note that datatable must have a defined key column before the insert, update, and delete SQL statements can be generated.

There are two main ways to create a set of TableAdapters. Start by selecting 'Data-Data Sources' from the VS2005 main menu, this opens the 'Data Sources' explorer. Then either create a new data source and select multiple stored procedures in the checkbox list, this will generate one TableAdapter for each selected "sproc"; or open a dataset in designer mode, right click and select 'Add-TableAdapter', this will add another TableAdapter to your dataset. Both ways will create multiple DataTables within a DataSet, however without any DataRelations.

Use the dataset designer to add the relations you need to be able to navigate between the DataTables at run-time. Adding relations allows you e.g. to use .GetChildRows(relationName)to read data related to the current master record.

Note that the relations are added to the .Relations collection of the DataSet, not as .ChildRelations of the "parent" DataTable. You must use the .Fill() method of each TableAdapter to populate all the DataTables and their DataRelations at run-time. Note that the .GetData() method returns a single DataTable without populating any relations.

Using TableAdapters have allowed us to reduce the number of stored procedure calls (round-trips to SQL Server) dramatically. The SqlDataReader code first read all the master records, then did three extra stored procedure calls for each of the master records to get the 1:M related data. E.g. with 20 master records, 61 round-trips to the SQL Server was needed. With TableAdapters, only 4 stored procedure calls are needed to fill the four related DataTables. Reading data for all master records is now as simple as doing a foreach() on the root DataTable and using .GetChildRows(relationName)to get the related data. This reduction should gain most of the raw performance difference between DataReaders and TableAdapters, or even perform better.

How to update related DataTables using TableAdapters is documented here at MSDN2.