MSDN Documentation

Managing Data Changes with ADO.NET

When working with data in ADO.NET, especially when using `DataSet` objects, you often need to track and manage changes made to the data before sending them back to the data source. ADO.NET provides robust mechanisms to handle insertions, updates, and deletions efficiently.

The Role of the `DataRow` State

Each row within a `DataTable` has an internal state that tracks its current status. This state is crucial for understanding how a row has been modified. The primary states are:

• Added: The row was newly inserted into the `DataTable`.
• Modified: The row's values have been changed since it was loaded or last accepted.
• Deleted: The row was marked for deletion.
• Unchanged: The row has not been modified since it was loaded or last accepted.

You can access the `RowState` property of a `DataRow` to determine its current state:

DataRow row = dataTable.Rows[0];
if (row.RowState == DataRowState.Modified)
{
    // Handle modified row
}
            

Tracking Changes with `AcceptChanges()` and `RejectChanges()`

Once changes have been made to a `DataTable`, ADO.NET maintains a "current" version and an "original" version of the data. This allows you to compare versions and manage updates.

The AcceptChanges() method is used to commit all pending changes in a `DataTable` or `DataSet`. When called, it:

• Sets the `RowState` of all `Added` rows to `Unchanged`.
• Removes `Deleted` rows from the `DataTable`.
• Sets the current version of `Modified` rows as their original version, and their `RowState` becomes `Unchanged`.

Conversely, RejectChanges() reverts all pending changes to the last accepted state. This is useful for rolling back modifications if an error occurs during the update process.

Using `GetChanges()`

The GetChanges() method is a powerful tool for isolating changes made to a `DataTable` or `DataSet`. It returns a new `DataTable` or `DataSet` containing only the rows that have been added, modified, or deleted. You can specify which row states to include in the returned collection:

DataTable changedRows = dataTable.GetChanges(DataRowState.Modified);
if (changedRows != null)
{
    // Process only the modified rows
    foreach (DataRow row in changedRows.Rows)
    {
        Console.WriteLine($"Row with ID {row["ID"]} was modified.");
    }
}
            

This is particularly useful when you need to send only the changed data back to the database, improving performance by reducing the amount of data transferred.

Updating the Data Source

To persist the changes made in a `DataSet` back to the database, you typically use the Update() method of a `DataAdapter`. The `DataAdapter` uses its associated `UpdateCommand`, `InsertCommand`, and `DeleteCommand` to execute the appropriate SQL statements for each changed row. ADO.NET intelligently determines which command to execute based on the `RowState` of each row.

Best Practice: Always use parameterized queries when constructing your `InsertCommand`, `UpdateCommand`, and `DeleteCommand` to prevent SQL injection vulnerabilities.

Handling Concurrency Conflicts

When multiple users or processes might be modifying the same data simultaneously, concurrency conflicts can arise. ADO.NET provides mechanisms to detect and resolve these conflicts. You can configure the `DataAdapter` to handle optimistic concurrency by including version columns in your `WHERE` clauses or by using timestamps.

The RowUpdating and RowUpdated events of the `DataAdapter` can be used to intervene and handle conflicts manually.

Summary

Managing data changes in ADO.NET is a fundamental aspect of data manipulation. By understanding `RowState`, utilizing `AcceptChanges()`, `RejectChanges()`, and `GetChanges()`, and correctly configuring your `DataAdapter`, you can efficiently and reliably update your data sources.