Bulk Insert DataSet with multiple related tables i

Question

I have a DataSet with multiple tables the source document is an XML file.

mydataset.ReadXML(filename);

There are multiple tables with multiple relations. I have the database in the SQL created via code - tables, columns and relations.

Now I would like to insert the data . (Yes I want everything). EDIT: The SQL tables use Identity Columns autogenerate - because I am not sure the incoming data will never duplicate one of the parameters that I would like to assume is unique.

So what methods should I do to ensure data is input considering I have foreign key constraints , how should I iterate the the tables in the dataset to make sure I don't try to insert in tables requiring an existing id. Do I create a hard coded map (I prefer not too) , or do I walk the tables looking for a Foreign key and verify the parent table etc..

Any Ideas ? I am sure someone has done this before and has a simple answer for me.

Answer 1

You have a couple of options. Assuming the database is not generating the key values, this is pretty simple. Either

1) You discover the order to load the tables so that each table with a Foreign Key is loaded after the table to which it refers.

2) You turn off constraint checking in SqlBulkCopy, and then optionally check the constraints after loading.

To check the constraints after load, run a command like

alter table SomeTable with check check constraint fk_SomeTable_OtherTable

If you do have database-generated keys, this is all harder. But the best way to tackle that is to use SEQUENCE objects isntead of IDENTITY columns and run sp_sequence_get_range to fetch the new key ranges to the client and apply the keys there first.