Mixing TableAdapterManager and BulkCopy RRS feed

  • Question

  • Hi!

    I'm using a rather large DataSet that contains a 4 level table hierarchy. The first two tables contain a relatively small amount of rows, but the child and grand-child tables contain hundreds of thousands of rows. This make UpdateAll rather slow. I'm currently mixing it up by not using UpdateAll, but by Updating the parent tables, then using SqlBulkCopy on the last 2 tables. When all I do is update and inserts, the results are incredibly fast and I'm quite satisfied.

    My question is: I would like to get the nifty UpdateAll feature that orders my deletes and updates properly to preserve referential integrity, but I would also like it to skip the child tables that I handle myself with SqlBulkCopy. Is that possible?

    I hope I'm communicating my problem clearly.

    Thanks for your help.
    Wednesday, August 19, 2009 2:13 PM


All replies

  • Hi Billy,


    You can update single table in this way:



    Does this work for you?

    If not, please provide more details about the primary key and foreign key constraints that you are making.


    Best Regards

    Yichun Feng

    Friday, August 21, 2009 8:04 AM
  • The problem with using single tables is that I have to manage foreign keys myself: I have to update the tables in a parent-child order when adding or updating, and then in a child-parent order for deletions. The TableAdapterManager does all this by itself, which I think is very cool. Unfortunately I lose this feature with my BulkCopies. I was wondering if there's a way to have my cake and eat it too.
    Friday, August 21, 2009 1:11 PM
  • There maybe a way using the BindingSource.AddingNew event.

    Per MSDN: ( Based on customer and order parent-child relarionship )

    In addition to committing changes on a related child table before saving data to a database, you might also have to commit newly created parent records before adding new child records to a dataset. In other words, you might have to add the new parent record (Customer) to the dataset before foreign key constraints enable new child records (Orders) to be added to the dataset. To accomplish this, you can use the child BindingSource.AddingNew event.


    To add code to commit parent records in the dataset before adding new child records

    1. Create an event handler for the OrdersBindingSource.AddingNew event.

      • Open Form1 in design view, click OrdersBindingSource in the component tray, select Events in the Properties window, and then double-click the AddingNew event.

    2. Add to the event handler a line of code that calls the CustomersBindingSource.EndEdit method. The code in the OrdersBindingSource_AddingNew event handler should resemble the following:




    Friday, August 21, 2009 2:36 PM
  • That's pretty cool. Unfortunately I don't use BindingSources. Adding them would be  more complicated then the solution I currently use, which to select Deleted, Modified and Added rows and call Update on those rows in the right order, like Beth Massi taught me to do.
    Friday, August 21, 2009 2:41 PM
  • In that case you are doing it the best you can while implementing bulk copy.
    Friday, August 21, 2009 3:00 PM
  • Roger.
    Friday, August 21, 2009 3:07 PM