MSDN Documentation

Handling Data Changes with ADO.NET

When working with data retrieved from a data source using ADO.NET, you often need to make modifications to that data, such as inserting new records, updating existing ones, or deleting obsolete entries. ADO.NET provides robust mechanisms to manage these data changes efficiently and accurately. This section delves into how to handle data modifications, focusing on the role of `DataAdapters` and `DataTables`.

The Role of `DataTable` and Rows

A `DataTable` object in ADO.NET acts as an in-memory representation of a single data table. Each row within the `DataTable` has a state that indicates its relationship with the underlying data source. These states are crucial for tracking changes:

• Added: The row is new and has not yet been submitted to the data source.
• Modified: The row's data has been changed since it was loaded or last accepted.
• Deleted: The row has been deleted from the data source.
• Unchanged: The row has not been modified since it was loaded or last accepted.

You can access the original and current values of a row using the `DataRowVersion` enumeration:

• `DataRowVersion.Current`: The current values in the row.
• `DataRowVersion.Original`: The values in the row when it was last loaded or accepted.

Using `DataAdapter` to Propagate Changes

The `DataAdapter` is the primary tool for synchronizing changes made in a `DataTable` back to the data source. It achieves this through its `Update` method. The `Update` method iterates through the rows in the `DataTable`, checks their current `RowState`, and executes the appropriate SQL commands (INSERT, UPDATE, DELETE) based on the `InsertCommand`, `UpdateCommand`, and `DeleteCommand` properties of the `DataAdapter`.

Steps to Handle Data Changes:

1. Retrieve Data: Use a `DataAdapter` and `DataTable` to load data from the data source.
2. Modify Data: Make changes to the `DataTable` (add, edit, delete rows).
3. Configure `DataAdapter` Commands: Set the `InsertCommand`, `UpdateCommand`, and `DeleteCommand` properties of the `DataAdapter` to define how these operations should be performed on the data source.
4. Update Data Source: Call the `DataAdapter.Update()` method, passing the `DataTable` as an argument.

Example: Updating Records

Consider a scenario where you load customer data into a `DataTable` and want to update a customer's email address.

using System;
using System.Data;
using System.Data.SqlClient; // Or your specific data provider

// Assuming 'connectionString' and 'selectSql' are already defined
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter dataAdapter = new SqlDataAdapter();

    // Configure SELECT command
    dataAdapter.SelectCommand = new SqlCommand(selectSql, connection);

    // Configure UPDATE command (example for SQL Server)
    SqlCommand updateCommand = new SqlCommand(
        "UPDATE Customers SET Email = @Email WHERE CustomerID = @CustomerID", connection);
    updateCommand.Parameters.Add("@Email", SqlDbType.VarChar, 100, "Email");
    SqlParameter param = updateCommand.Parameters.Add("@CustomerID", SqlDbType.Int, 0, "CustomerID");
    param.SourceVersion = DataRowVersion.Original; // Important for WHERE clause
    dataAdapter.UpdateCommand = updateCommand;

    DataTable customerTable = new DataTable();
    connection.Open();
    dataAdapter.Fill(customerTable);

    // Find the row to update
    DataRow customerRow = customerTable.Rows.Find(1); // Assuming CustomerID 1 exists
    if (customerRow != null)
    {
        // Modify the data
        customerRow["Email"] = "new.email@example.com";
        Console.WriteLine($"Row state after modification: {customerRow.RowState}"); // Should be Modified

        // Update the data source
        int rowsAffected = dataAdapter.Update(customerTable);
        Console.WriteLine($"{rowsAffected} row(s) updated.");
    }
}

Handling Insert and Delete Operations

Similar to updates, you can configure `InsertCommand` and `DeleteCommand` properties on the `DataAdapter`. When `DataAdapter.Update()` is called:

• Rows with `RowState` set to `Added` will have their `InsertCommand` executed.
• Rows with `RowState` set to `Deleted` will have their `DeleteCommand` executed.

For inserts, ensure your `InsertCommand` correctly handles the values for the new row, often using parameters to pass the data.

For deletes, the `DeleteCommand` typically uses parameters to identify the row to be removed, often based on a primary key value.

Concurrency Control

When multiple users or processes might be modifying the same data, concurrency issues can arise. ADO.NET provides basic concurrency handling. By default, when you update a row, the `DataAdapter` checks if the row in the database has changed since it was loaded. If it has, an `DBConcurrencyException` is thrown. You can customize this behavior by adding optimistic concurrency checks to your `UpdateCommand` (e.g., by including original values in the `WHERE` clause of the `UPDATE` statement).

Mastering data change handling with ADO.NET is essential for building dynamic and interactive data-driven applications. By leveraging `DataAdapters`, `DataTables`, and understanding row states, you can effectively manage data modifications and ensure data integrity.