- Building a DataSet with Code
- Loading a DataSet from a Database
- Saving Changes
- Saving Changes Redux
- Constraints
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Saving Changes Redux
The code in Listing 3 illustrates one other point that is important enough to deserve its own section. That code doesn't actually work in all cases because sometimes update operations must be performed in a particular order to satisfy database constraints.
For example, in the database, the TestScores table's StudentId field is related to the Students table's StudentId field as a foreign key. In other words, the database will not allow the TestScores table to contain a StudentId value that is not already present in the Students table.
Now, suppose you use program FromDB to delete all of the Students and TestScores data for the student Amy Anderson. The code in Listing 3 applies all of the changes made in the Students table first and then applies the changes to the TestScores table. When it updates the Students table, it tries to remove the Students record for Amy Anderson. If it removes the Students record, the TestScores records for that student would contain a StudentId value that is not present in the Students table. That violates the TestScores table's foreign key constraint, so the database disallows the change, and the program crashes.
Listing 4 shows one way to fix this problem. This code uses the DataSet's GetChanges method to make a new DataSet containing records that have been deleted. If that DataSet is not empty, it uses the data adapters' Update methods to save those changes. It deletes the TestScores records first so no TestScores records are left without corresponding Students records.
The code then uses GetChanges again to make a DataSet containing any modified or inserted records. If that DataSet is not empty, the program uses the data adapters' Update methods. This time, the code updates the Students table first, so any new StudentId values are present in the Students table before the program tries to create corresponding TestScores records.
Note also that the code deletes any removed records before it inserts any new ones. That prevents problems when the user deletes a record and then adds a new record with the same primary key value. If the program tried to insert the new record first, the database would complain about the duplicate primary key value. Because the program makes the deletions first, the primary key value is unused by the time the new record is created.
This code works, more or less. One odd situation that it still doesn't handle occurs when the user changes a StudentId value in either table. When the database tries to update the StudentId value, the foreign key relationship between the two tables breaks, so the database raises an error and the program crashes. The easiest way to handle this situation is to not allow your program to change StudentId values. This field is a primary key, so it probably doesn't need to change anyway. That is particularly true in this case, in which the value is a relatively arbitrary number. It's hard to imagine a real need to change these values.
Listing 4—This code saves changes correctly...more or less
' Save changes to the data.
Private Sub Form1_Closing(ByVal sender As Object, _
ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
' Make the command builders.
Dim cb_students As New SqlCommandBuilder(m_daStudents)
Dim cb_testscores As New SqlCommandBuilder(m_daTestScores)
' Update deleted records.
Dim deleted_students As DataSet = m_DataSet.GetChanges(DataRowState.Deleted)
If Not (deleted_students Is Nothing) Then
m_daTestScores.Update(deleted_students, "TestScores")
m_daStudents.Update(deleted_students)
End If
' Update modified and inserted records.
Dim modified_students As DataSet = _
m_DataSet.GetChanges( _
DataRowState.Modified Or DataRowState.Added)
If Not (modified_students Is Nothing) Then
m_daStudents.Update(modified_students)
m_daTestScores.Update(modified_students, "TestScores")
End If
End Sub