Another common task required when working with a set of data is deleting records within the database. To accomplish this, ORM.NET generates a set of separate [Object].Delete() methods for each object in the hierarchy. After one or more QueryCriteria methods are invoked and a DataSet is returned you can mark one or more data rows to be deleted. Once you are finished marking the objects to be deleted the DataManager.CommitAll() method is called which attempts to perform the delete operations against the database.
Like other data operations, deletes, inserts and updates can be combined within one CommitAll() call and all operations will be performed at one time as a single transaction.
The following example deletes a single record from the Student object where FirstName = “Tim” and LastName = “Brown”.
C#
DataManager dm = new DataManager(Config.Dsn);
dm.QueryCriteria.Clear();
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,”Tim”)
.And(JoinPath.Student.Columns.LastName,”Brown”);
Student s = dm.GetStudent(FetchPath.Student);
s.Delete(); // marks the returned DataRow to be deleted
dm.CommitAll(); // performs the necessary insert,update and delete operations
Visual Basic.NET
Dim dm As New DataManager(Config.Dsn)
dm.QueryCriteria.Clear()
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,”Tim”)
.And(JoinPath.Student.Columns.LastName,”Brown”)
Dim s As Student = dm.GetStudent(FetchPath.Student)
s.Delete() ' marks the returned DataRow to be deleted
dm.CommitAll() ' performs the necessary insert,update and delete operations
If a query returns more than one row of data you can delete all or some of the rows within the DataSet by using the [ObjectName]Collection object as shown below.
C#
DataManager dm = new DataManager(Config.Dsn);
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,"Tim")
.And(JoinPath.Student.Columns.LastName,"Brown");
StudentCollection students = dm.GetStudentCollection();
// check to ensure that there are record(s) to delete
if (students != null)
{
foreach (Student s in students)
s.Delete(); // loop through and mark for deletion
dm.CommitAll(); // Delete all datarows marked for deletion transitionally
}
Visual Basic.NET
DataManager dm = new DataManager(Config.Dsn);
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,"Tim")
.And(JoinPath.Student.Columns.LastName,"Brown");
StudentCollection students = dm.GetStudentCollection();
// check to ensure that there are record(s) to delete
If Not students Is Nothing Then
Dim s As Student
For Each s in students
s.Delete() ' loop through and mark for deletion
Next
dm.CommitAll(); // Delete all datarows marked for deletion as a single transaction
End If
The following example deletes the Parent Contact data related to the Student object. Note that the FetchPath object directs the GetStudent method to retrieve Contact information when assigning the returned DataSet to the local object reference.
dm.QueryCriteria.Clear();
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,"Brown");
// Get[Object] will retrieve Student and related Contact records
Student student = dm.GetStudent(FetchPath.Student.Contact);
student.Contact.Delete(); // mark the Parent Contact record to be deleted
student.Delete(); // mark Student record Root object to be deleted
dm.CommitAll();
NOTE: When deleting related records ensure that you mark the Parent object for deletion before the Root object. Once the root object - in this case Student - is marked for deletion you can no longer reference the object.
For more information on how to use the FetchPath object refer to the Display Data section.
One area to be aware of is how deleting a Parent record will affect the related Child records and vice versa. ORM.NET generates the appropriate SQL statements based on how the relationship constraints have been defined within the SQL schema. You can view how these settings have been applied within the ORM.NET object browser under the Children Node settings page. The following shows that the relationship defined for the Enrollment object – Child of Student is set so that SQL will “Enforce relationship for INSERTS and UPDATEs” and will “Cascade Delete Related Records”.
Please refer to Microsoft's SQL Server Online documentation for more information about how Cascade Updates and Cascade Deletes operate and whether they should be used within your database schema.
NOTE: These properties are read-only and retrieved from the database schema definition.Therefore, to change these constraints you need to first make them within SQL Enterprise manager and then click the Get DB Schema Changes for the ORM.NET Object Browser to correctly display and generate the data layer.