Delete Record Mechanism When There Are Related Records
25. Jun, 2007 by admin
0 Comments
It is quite an often situation that duringdeletion of a record from the database there is a need to perform someaction with all related records: delete related records, reassign themto another record (change foreign key), or remove information aboutrelation (null foreign key). The mechanism described below will allowyou to deal with this issue.
Delete record mechanism when there are related records.
Introduction
It is quite an often situation that duringdeletion of a record from the database there is a need to perform someaction with all related records: delete related records, reassign themto another record (change foreign key), or remove information aboutrelation (null foreign key).
For example, consider you have an Employeestable and a defects table and one employee�s record from employeestable is going to be deleted. There is a dilemma what to do with allthe Defects assigned to that employee: delete them, leave themunassigned to any employee or assign them to another employee? Theproblem becomes even more difficult if there are many multilevelrelations. The mechanism described below will allow you to deal withthis issue, and the source provided will help you to retrieve theserelated 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 notdiscussed in this article. As well as the other solutions which do notallow users to modify or add records during this operation.
2. Retrieve all related records.
Retrieveall related records (IDs and primary keys) in order to decide andperform specific action on those records. For this purpose, there isutility 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.
Tables are arranged in such a way that first comes the least related tables (leafs).Last one goes the main table.
These tables are arranged as shown below.
Therefore, if you want to delete employee with id 12 you call function:
DataSet _ds=GetRelatedTables(con, �employees�,�ID�,12);
3. Evaluate retrieved DataSet
After you retrieve dataset you can check if there are any related records at all.For example, you can check if there are any related defects.
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
}
4. Saving changes to the DataBase.
If deletion was not canceled, updated dataset should be sent for updates.DataAdapter.Update(_ds);
Download Source
Most Commented Articles :
No comments yet... Be the first to leave a reply!