none
DataSet Constraints RRS feed

  • Question

  • I have been searching trying to get a better understanding why a Cascade delete is not propogating the delete nor am I recieiving any constrain viloation error when deleting a parent which still has a child. 

    Here is a short snippet of some test logic:

                companyTableAdapter.Fill(ds.Company);
                companySettingsTableAdapter.Fill(ds.CompanySettings);

                var companyRow = ds.Company.FindByCompanyID(companyID);

                companyRow.Delete();

                updateCount = Convert.ToInt32(companyTableAdapter.Update(ds)); 

    CompanySettings is a child of Company. I have established a relationship between the two using the DataSet designer with the following settings:

    Both Relationship and Foreign Key Constraint

    Update Rule = Cascade

    Delete Rule = Cascade

    Accept/Reject Rule = Cascade

    Nest Relations = checked

    The underlying data source is SQL Server 2008 and there is not a relationship defined between the two tables in the database. Now when the above code runs I would expect either :

    1) An error indicating a constraint violation

    or

    2) For the update to propagate the delete to the Child table (CompanySettings)

    Neither occurs!? However if I orphan a record in the child table and subsequently call the Fill method on the adapter for the child table, I get an "Cannot enable contraint ... " error as I would expect. Can anyone provide any insight as to what I might be missing? I'm using VS 2008

    Thanks,

    -Ed

    Thursday, May 27, 2010 3:47 AM

Answers

  • Hello Ed,

     

    Welcome to ADO.NET DataSet forum!

     

    I think your settings are fine on the typed dataset even there is no foreign key between the Company and CompanySettings tables at the database side.   Only one place to mention, please also call companySettingsTableAdapter.Update(ds);  to update the CompanySettings table.  J    The DataSet does not raise any exceptions or delete the certain CompanySettings data rows because the rows are marked as Deleted in the dataset, but we haven’t call the method to update them to the database.  J  

     

    Besides, we can also use TableAdaterManager.UpdateAll() method to update the entire dataset.  Before that, we need to set the certain TableAdapter properties of the TableAdapterManager.

    ==============================================================================

                TableAdapterManager manager = new TableAdapterManager();

                manager.CompanyTableAdapter = companyTableAdapter;

                manager.CompanySettingTableAdapter = companySettingsTableAdapter;

                manager.UpdateAll(ds);

    ==============================================================================

    Additional references about TableAdapter, http://msdn.microsoft.com/en-us/library/bb384426.aspx. 

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, May 27, 2010 9:00 AM
    Moderator

All replies

  • Hello Ed,

     

    Welcome to ADO.NET DataSet forum!

     

    I think your settings are fine on the typed dataset even there is no foreign key between the Company and CompanySettings tables at the database side.   Only one place to mention, please also call companySettingsTableAdapter.Update(ds);  to update the CompanySettings table.  J    The DataSet does not raise any exceptions or delete the certain CompanySettings data rows because the rows are marked as Deleted in the dataset, but we haven’t call the method to update them to the database.  J  

     

    Besides, we can also use TableAdaterManager.UpdateAll() method to update the entire dataset.  Before that, we need to set the certain TableAdapter properties of the TableAdapterManager.

    ==============================================================================

                TableAdapterManager manager = new TableAdapterManager();

                manager.CompanyTableAdapter = companyTableAdapter;

                manager.CompanySettingTableAdapter = companySettingsTableAdapter;

                manager.UpdateAll(ds);

    ==============================================================================

    Additional references about TableAdapter, http://msdn.microsoft.com/en-us/library/bb384426.aspx. 

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, May 27, 2010 9:00 AM
    Moderator
  • Hi Ed,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, June 1, 2010 1:18 AM
    Moderator
  • Hi Lingzhi,

    Thanks for the welcome and the replies. I'd missed the original reply. I had a chance to test this. The following are my results:

    1) Use of the manager.UpdateAll applies the deletions across the related tables just as you indicated.

    2) Using the update method on the individual table adapters (company and settings) works so long as the calls occur in the right order. If I call the update method on the parent (Company) before I call it on the Child (CompanySettings) the child records are orphaned with no error. If I reverse the order, the records in both tables are removed as expected. Any thoughts as to why this might be the case?

    I've already moved forward with an alternative model where I delete the records from their respective tables as part of a transaction, so this is now more of an academic discussion for the benefit of the community :).

    Thanks,

    -Ed

    Thursday, June 3, 2010 4:37 PM
  • Hi Ed,

     

    Sorry for my delay.  :-)   I am very glad to hear the problem has been solved. 

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, June 8, 2010 7:28 AM
    Moderator