Delete Record Mechanism When There Are Related Records
By Tadas Budvytis

It is quite an often situation that during deletion of a record from the database there is a need to perform some action with all related records: delete related records, reassign them to another record (change foreign key), or remove information about relation (null foreign key). The mechanism described below will allow you to deal with this issue.

Delete record mechanism when there are related records.

Introduction
It is quite an often situation that during deletion of a record from the database there is a need to perform some action with all related records: delete related records, reassign them to another record (change foreign key), or remove information about relation (null foreign key).

For example, consider you have an Employees table and a defects table and one employee’s record from employees table is going to be deleted. There is a dilemma what to do with all the Defects assigned to that employee: delete them, leave them unassigned to any employee or assign them to another employee? The problem becomes even more difficult if there are many multilevel relations. The mechanism described below will allow you to deal with this issue, and the source provided will help you to retrieve these related records.

Proposed scheme-plan to deal with the issue.

1. User asked to delete certain record.
2. Retrieve all the related records (ID’S and foreign keys) from the database into DataSet. In the example mentioned before there will be a record from employees table to be deleted and all related records (ID’s and foreign keys) from defects table as well.
3. Evaluate retrieved DataSet if there are any related records. In the simplest scenario application may inform user about related records and restrict deletion. Or it may ask to approve deletion of related records as well or to change foreign key of these records.
4 Save changes to the DataBase.

1. User asked to delete certain record.
It is preferable to lock up the record for editing, the way that only one user may work with it.

Note: The locking record topic is not discussed in this article. As well as the other solutions which do not allow users to modify or add records during this operation.

2. Retrieve all related records.
Retrieve all related records (IDs and primary keys) in order to decide and perform specific action on those records. For this purpose, there is utility function.
It returns related records from different tables related to given table - record.

You can find it in the class of DBDeleteHelper, under the namespace MohawkIDEAS.Utils

public DataSet GetRelatedTables(SqlConnection con, string strTableName, string strFieldName ,int iID);

con  	     - Active connection.
strTableName - Table name to which record belongs.
strFieldName – Primary field name.
iID          – Record id for the which you want to retrieve related records.

If (_ds.Tables[“Defects”].Rows.Count>0 )
{
 …
}

If there are any related records, there are the following options:
- User may be informed that the selected record could not be deleted as 
  there are related records, and cancel deletion.
- 	All related records could be marked to be deleted as well.
   	foreach (System.Data.DataRow _row in _ds.Tables["Defects"].Rows)
        {
           _row.Delete();
     }
 -	Related records may be updated (assigned to another employee) by   
   changing foreign key value. 
	int _iID;
      _iID = ChooseNewResposibleEmploee();
                
	foreach (System.Data.DataRow _row in _ds.Tables["Defects"].Rows)
        {
           _row[“EmployeeID”]= _iID; //Another Employee id
     }