Hierarchical updates with SQL Compact Edition (or fetching the primary key on dataset.update?) RRS feed

  • Question

  • I'm having issues very similar to the one described here, . The only difference is that I'm using the SQL Server Compact Edition. On CE, I can't go and enable the 'Refresh Data Table' checkbox in the DataSet editor, it's grayed out apparently since the compact edition doesn't support batched queries. What am I supposed to do then (!)? It seems that this shortcoming pretty much breaks anything I do, for example:

    1. I create a Customer row with customerTable.AddRow(); This causes a row to be created with CustomerID=-1 (I have -1 as autoincrement and seed for the primary key).
    2. I update the Customer table with customerTableAdapter.Update(); The primary key doesn't change from -1 to 1, but if I go under the hood with an external program to check the .sdf -file, the CustomerID row is now 1. I've tried to call .AcceptChanges(), but it doesn't do anything.
    3. I call customerRow.Delete(); on the customer row that has the erroneous PrimaryKey.
    4. I call customerTableAdapter.Update(); again. I get an exception stating that zero rows were affected, but one was expected to. If I somehow could update the proper CustomerID to the row, this wouldn't occur I guess. But I can't fix it using the wizard because of the reasons stated above. What should I do?

    Another codepath that causes errors>
    1. I create a Customer row with customerTable.AddRow(); This causes a customer row to be created with CustomerID=-1.
    2. I create an Order row with orderTable.AddRow(customerID); This causes an orders row to be created with OrderID=-1, OrderCustomerID=-1. I have foreign key references between these two, and both the Update and Delete rules are set to Cascade.
    3. I instantiate a TableAdapterManager that the wizard has created for me, and pass it the CustomerTableAdapter and OrderTableAdapter objects.
    4. I call tableAdapterManager.UpdateAll(); I get an exception from the update process stating that the foreign key references are violated! Debugging shows that the Order is being submitted using the OrderCustomerID=-1 and not OrderCustomerID=1 as I would expect. Apparently due to the same shortcoming as in the codepath above, TableAdapterManager fails because it can't get the ID that the SQL backend associated to the Customer.

    What should I do to get TableAdapterManager to work as advertised? Now I'm limited to not being able to mix insertions and deletions in the same DataSet, I always have to flush in between. Also, I can't create parent and child rows without having to recreate the whole DataSet in between.

    Is there some way I could simulate the sequence of 'UPDATE xx; SELECT xx;' in Compact Edition? Or is there some other way I can fetch the IDs of newly created rows so that the usability of DataSet and TableAdapterManager is not crippled?

    Thanks ,
    Sunday, August 16, 2009 5:52 PM


  • Hi,

    If I'm not misunderstood, your basic need is to get the primary key values upon calling adapter.update()? I had similar issues with master/detail tables too and have figured out a way to solve it. Unlike you, I was using Oracle and was able to achieve the primary key by way of using an output parameter. (I did use Oracle's Keyword "Returning" to achieve this, but I guess it could be achieved this on SQL Server as well in another way). For more insight on this, check on my post @

    Hope this helps you.

    Thanks & Regards,

    Hifni Shahzard Nazeer M.
    • Marked as answer by Yichun_Feng Monday, August 24, 2009 12:32 AM
    Wednesday, August 19, 2009 3:36 PM