Delete mechanism.

• Download source code - 45.7 Kb

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 a certain record.
2. Retrieve all the related records (IDs and foreign keys) from the database into a DataSet. In the example mentioned above there will be a record from Employees table to be deleted and all the related records (IDs and foreign keys) from the Defects table as well.
3. Evaluate retrieved DataSet if there are any related records. In the simplest scenario, the application may inform the user about related records and restrict deletion. Or it may ask to approve deletion of related records as well or to change the foreign key of these records.
4. Save changes to the database.

Details

User asked to delete certain records

It is preferable to lock up the record for editing, the way in which 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.

Retrieve all the related records

Retrieve all the related records (IDs and primary keys) in order to decide and perform specific action on those records. For this purpose, there is a 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 - The table name to which the record belongs.
• strFieldName – Primary field name.
• iID – Record ID for the which you want to retrieve related records.

Tables are arranged in such a way that first comes the least related table (leafs). Last goes the main table. These tables are arranged as shown below:

Therefore, if you want to delete an employee with ID 12, you call the function:

DataSet _ds=GetRelatedTables(con, "employees","ID",12);

Evaluate retrieved DataSet

After you retrieve the DataSet you can check if there are any related records. For example, you can check if there are any related defects.

If (_ds.Tables["Defects"].Rows.Count>0 )

{

…

}

If there are any related records, these 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:
  C#

  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:
  C#

  int _iID;
  _iID = ChooseNewResposibleEmploee();
  
  foreach (System.Data.DataRow _row in _ds.Tables["Defects"].Rows)
  {
      _row["EmployeeID"]= _iID; //Another Employee id
  }

Saving changes to the database

If deletion was not canceled, the updated DataSet should be sent for updates:

DataAdapter.Update(_ds);