We've been doing a lot of work with DataSets recently on this project and have had some issues on standards for creating new DataTables and TableAdapters. We have tried to enforce having DataTables with TableAdapters that directly reflect the base tables in a DataSet so that you can use the default Insert, Update, Delete, Select methods provided.

Complex Procedures

One problem occurs when you have specific database functions that process specific updates where you want to update a subset of data, not necessarily just from one underlying table. The first option is to select it using TableAdapters and then modifying the data in the DataTable and then calling the Update method on it.

The second option is to pass the criteria as parameters to a Stored Procedure or create a custom query method off the TableAdapter. Things start to get messy when you attach methods/procedures off of a TableAdapter that are related to the underlying Table that the DataTable represents, but is also related to other tables that may not even be in the DataSet. Mistakes start to happen where people look in different places for these methods and duplication occurs. 

Views

Another problem occurs when you want to display a view of data, up until now we have been creating new DataTables with the main query for columns being the view required. A naming standard is useful to indicate this is a view and not a underlying table and this prevents people adding methods to the DataTable that are doing inserts, deletes etc. which obviously should not be done on a view and only selects. This does lead to confusion and also duplicate DataTables for various tasks.

This problem also means that if new columns are added to this view and Inserts are attached to it the Insert methods will have more parameters and the whole solution will need modifying! 

The JoinView class allows you to take two DataTables and join them in a view in a very similar way to what you would do in a view at the SQL level. Issues occur on where these joins should be created. If they are put in the Broker level (between the client app and the data layer) it gives flexibility of what columns are needed and prevents the Data Layer becoming too complexed with all these different methods for different views required by the Client application. The Data Layer then is still just a representation of the underlying Tables in the database. It will mean that you have to call a few methods in the Data Layer to get all the tables required, but the Broker layer could keep cached versions of particular reference data tables that are frequently joined to.