Custom datasets are a great way to work with data in .NET, but sometimes the more complex datasets that consist of multiple tables with foreign key constraints can cause issues when trying to save the data. If you want your dataset to enforce the foreign keys (which is preferred so you don't have to write custom code to do data integrity checks) when saving the dataset table data through a DataAdapter you need to make sure things are done in the proper order. Here are some useful tips I've found when working with custom datasets:
1. Make sure your foreign keys between tables use the cascade setting for the update and delete rule. When the custom dataset is created this setting defaults to none which means you would need to ensure any new parent and child rows added to the tables have the child row foreign key ID column updated manually after you save the parent table and if key is being determined by the database insert (such as when a database uses an identity column). To change the rule to cascade your foreign key on the dataset double click on the foreign key relation ship line in the dataset designer.
2. During your save process for the dataset use a data adapter to save each table, but be sure you save the tables in the proper order. This means you need to save the parent table first so that any new IDs assigned by the database can be set and will then automatically be updated on the child rows which then allows the child table to be saved next and its data will match the new parent ID values preserving the relationship. To ensure you save datasets in the proper order you can do something like making a string array of table names from which you'll base your dataset save order upon. To create your save order array, loop through all the tables in the dataset, if the table is not listed as a parent table in the DataRelation collection then that table can be added to the array to be one of the first table saved. If the table does have a parent table in the DataRelation collection then ensure the parent table is already in the save order array before adding the child table as a later element. Once you create the save order array of table names then loop through the list and save each dataset by table name using a DataAdapter. How can you tell if a table has a parent table relation defined? Loop through the DataRelation collection and check if the DataRelation.ChildTable.TableName is the same as the current table, if so then ensure the name of the DataRelation.ParentTable.TableName is already listed earlier in your table save order array. If so, then you can add the child table name to the end of the save order, otherwise add the parent table name then the child table name to the save order so that the parent will be saved first. In pseudo code this logic is:
For each table in customDataset
for each datarelation in customDataset.Relations
if datarelation.ChildTable.TableName = table.TableName then
if datarelation.ParentTable.TableName not in table save order array then add parent table name to save order array. *
end if
Add ChildTable.TableName to save order array.
end if
next datarelation
next table
for each tableName in save order array
Create DataAdapter and save table
next tableName
*Note if you have grand-child table relationships you'll need to make this more complex to ensure before adding any table to the save order that you've checked all the way up it's chain and all tables as the parent key above it are already in the list. The order should come out as Parent table, Child table, GrandChild table, etc.
3. Use a DataAdapter to save each table in your custom dataset. You could pass the whole dataset to a single DataAdapter, but I like the control my code has to validate table data distinctly before executing the adapter instead of writing logic into database stored procedures to handle the multiple table saves.
4. Make a generic class to support saving a dataset and reuse your code! This will save you a lot of time on larger projects as you won't have to write the same tedious save logic in every application, instead you can reuse code from a proven class that you have already tested and will benefit all the developers on your team.
If you haven't heard from me before, Tech Ed is by far my favorite developers conference. Unfortunately I couldn't attend in person this year due to project timeline commitments but luckly for me (and everyone) you can get access to all the content online at the Virtual Tech Ed site - http://virtualteched.com/default.aspx
You can also watch some of the sessions as web casts https://www1.msteched.com/content/webcasts.aspx