MSDN Documentation

ADO.NET DataAdapter

The DataAdapter in ADO.NET acts as a bridge between a data source and a DataSet. It manages the retrieval of data from the data source and the synchronization of changes made within the DataSet back to the data source. This is crucial for disconnected data scenarios where the application doesn't maintain a constant connection to the database.

Core Functionality

The DataAdapter provides the following key functionalities:

• Fill: Populates a DataSet with data by executing a data source query.
• Update: Propagates changes made to the rows in a DataSet back to the data source.
• SelectCommand: Retrieves data from the data source.
• InsertCommand: Executes a command to insert new rows into the data source.
• UpdateCommand: Executes a command to modify existing rows in the data source.
• DeleteCommand: Executes a command to delete rows from the data source.

Common DataAdapter Implementations

ADO.NET provides specific implementations of the DataAdapter for various data providers:

Working with DataAdapter

The typical workflow involves:

1. Creating a Connection to the data source.
2. Creating a Command object to retrieve data (e.g., a SELECT statement).
3. Creating a DataAdapter instance (e.g., SqlDataAdapter) and associating it with the Connection and Command.
4. Creating a DataSet.
5. Using the DataAdapter.Fill() method to populate the DataSet.
6. Performing operations on the data within the DataSet.
7. If changes are made, configuring INSERT, UPDATE, and DELETE commands on the DataAdapter.
8. Using the DataAdapter.Update() method to persist changes back to the data source.

Example: Filling a DataSet

The following C# code snippet demonstrates how to use a SqlDataAdapter to fill a DataSet:

using System;
using System.Data;
using System.Data.SqlClient;

public class DataAdapterExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Your_Connection_String_Here";
        string query = "SELECT CustomerID, CompanyName FROM Customers";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
            DataSet dataSet = new DataSet();

            try
            {
                connection.Open();
                adapter.Fill(dataSet, "Customers"); // Fills the DataSet with data

                // Now you can access the data in dataSet.Tables["Customers"]
                foreach (DataRow row in dataSet.Tables["Customers"].Rows)
                {
                    Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }
    }
}
                

Example: Updating a DataSet

Updating requires configuring the InsertCommand, UpdateCommand, and DeleteCommand. This often involves using stored procedures or carefully crafted SQL statements that include parameter mapping to handle specific values for each row operation.

Key Concepts

• Disconnected Data: The ability to work with data without maintaining an open connection to the data source.
• Row State: Rows in a DataTable within a DataSet have states like Added, Modified, and Deleted, which the DataAdapter uses to determine how to update the data source.
• Conflict Resolution: Strategies for handling potential conflicts when multiple users try to update the same data simultaneously.

Understanding the DataAdapter is fundamental to building efficient and robust data-driven applications with ADO.NET.