none
ADVANCE How to Delete selected row in data grid view and database by C# WITH Child References

    Question

  • Hello

    I have read the below: 

    How to Delete selected row in data grid view and database by C#

    @

    http://social.msdn.microsoft.com/Forums/en-US/650f8671-266d-4faa-9f23-3f0a81ed22f9/how-to-delete-selected-row-in-data-grid-view-and-database-by-c?forum=winformsdatacontrols

    and I have followed the answer using and am now using a BindingSource for my DataGridView.

    What I want :

    I have 4 Datagridviews, one parent and 3 child. When I click a delete button and dlete a record in the parent, I expected any record in the child Datagridviews that is related to the parent to be automatically deleted as well.

    Currently what happens is I use:

     foreach (DataGridViewRow item in ParentDataGridView.SelectedRows)
                {
                   ParentGridView.Rows.RemoveAt(item.Index);
                }

    The record disappear from the DataGridview but then I try to DBContext.SaveChanges(); I get the following error:

    Additional information: The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

    I did some research and found out that after I delete the Parent record, any child that use the Parent's PK as their FK will become orphaned and I can't tell EF to delete those Child Objects too. I've read about Identifying Relationships, I've read to use ObjectDelete instead of RemoveAt because  ObjectContext is better than DBContext (But I struggle to understand creating the ObjectContext, I also read that this isn't used for Code-First Approach). 

    Steps that I have already taken:

    Using DataAnnotations I have created Composite Keys, setting the 3 Child Table's PK to [Key, Column(Order = 2), and the FK linking to the Parent to [Key, ForeignKey("Parent"), Column(Order = 1)] (I don't know if that is enough to create the Identifying Relationships or is there more I need to do?)

    I've also go to my SQL database and for each Table I set the ON DELETE to Cascade. 

    I'm not sure how to get around this. Thanks!




    Thursday, February 27, 2014 6:34 AM

Answers

  • HI

    Try this

     foreach (DataGridViewRow item in ParentDataGridView.SelectedRows)
                {
                   ParentGridView.Rows.RemoveAt(item.Index);

    DeleteRecords(parentID)

                }

    public void DeleteRecords(_parentID int)

    {

    //Delete records from sql database 

    }

    Mark as Answer if you find it useful


    Shridhar J Joshi Thanks a lot

    Thursday, February 27, 2014 9:02 AM
  • Hello,

    Let's use a master-detail model which you can expand on. In this case a DataSet will hold customers table and orders table (using NorthWind database). Once the tables are loaded into the DataSet we set a relationship between the two tables on the primary key in customers, CustomerId which is also in the details table. Now we will set a ForeignKeyConstraint to the master table i.e. MyDataSet.Relations(0).ChildKeyConstraint where DeleteRule = Cascade. The master-detail tables are setup to two BindingSources which are linked up to a DataGridView, one for master, one for details.

    Now we can issue bsMaster.RemoveCurrent() where bsMaster is the BindingSource for the master DataTable. This will remove the details rows for the current master row.

    So right before bsMaster.RemoveCurrent is invoked we can cast our BindingSources to DataTables, do a select on the details table to get an array of DataRows and then we now have the primary key each row and now can execute a DELETE SQL statement to remove these rows.

    Now an easier method would be to get the primary key of the master table and execute a SQL delete statement on the backend table. This will remove child rows if the backend table has a cascading delete rule setup.

    Please note there is no code because all the above was done in VB.NET and don't have time to convert to C# right now but if by chance you want the full project done in VB.NET I will uploaded it. In the mean time the project is based off the following article Basics of manually creating a Master-Detail view of a backend database.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Thursday, February 27, 2014 5:17 PM
    Moderator

All replies

  • HI

    Try this

     foreach (DataGridViewRow item in ParentDataGridView.SelectedRows)
                {
                   ParentGridView.Rows.RemoveAt(item.Index);

    DeleteRecords(parentID)

                }

    public void DeleteRecords(_parentID int)

    {

    //Delete records from sql database 

    }

    Mark as Answer if you find it useful


    Shridhar J Joshi Thanks a lot

    Thursday, February 27, 2014 9:02 AM
  • Hello,

    Let's use a master-detail model which you can expand on. In this case a DataSet will hold customers table and orders table (using NorthWind database). Once the tables are loaded into the DataSet we set a relationship between the two tables on the primary key in customers, CustomerId which is also in the details table. Now we will set a ForeignKeyConstraint to the master table i.e. MyDataSet.Relations(0).ChildKeyConstraint where DeleteRule = Cascade. The master-detail tables are setup to two BindingSources which are linked up to a DataGridView, one for master, one for details.

    Now we can issue bsMaster.RemoveCurrent() where bsMaster is the BindingSource for the master DataTable. This will remove the details rows for the current master row.

    So right before bsMaster.RemoveCurrent is invoked we can cast our BindingSources to DataTables, do a select on the details table to get an array of DataRows and then we now have the primary key each row and now can execute a DELETE SQL statement to remove these rows.

    Now an easier method would be to get the primary key of the master table and execute a SQL delete statement on the backend table. This will remove child rows if the backend table has a cascading delete rule setup.

    Please note there is no code because all the above was done in VB.NET and don't have time to convert to C# right now but if by chance you want the full project done in VB.NET I will uploaded it. In the mean time the project is based off the following article Basics of manually creating a Master-Detail view of a backend database.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Thursday, February 27, 2014 5:17 PM
    Moderator
  • Your question seems to be 100% Linq to SQL, therefore ask it in the forum for that. 

    Currently your question and replies will only confuse those searching for answers when they find the current replies.

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=linqtosql


    Success
    Cor

    Monday, March 03, 2014 9:23 AM