Microsoft Docs

DataAdapter Overview

The DataAdapter is a key component in ADO.NET that provides a bridge between a DataSet and a data source for retrieving and saving data. It acts as a mediator, abstracting the details of data manipulation operations.

What is a DataAdapter?

A DataAdapter is an object that, in conjunction with a DataSet, provides access to data in a data source. It is used to fill a DataSet and persist changes made to the data in the DataSet back to the data source. The DataAdapter uses commands and connections to a data source to retrieve data and send updates.

The DataAdapter object abstracts the data access operations. You can use it to:

• Populate a DataSet with data from a data source.
• Detect changes made to data within a DataSet.
• Resolve conflicts when changes made to data in a DataSet are persisted back to the data source.
• Generate and execute SQL commands against the data source to resolve conflicts.

Key Properties and Methods

The DataAdapter class provides several important properties and methods:

• SelectCommand: Retrieves data from the data source.
• InsertCommand: Executes a command to insert records into the data source.
• UpdateCommand: Executes a command to update existing records in the data source.
• DeleteCommand: Executes a command to delete records from the data source.
• Fill(DataSet): Populates a DataSet with the results of executing the SelectCommand.
• Update(DataSet): Persists the changes made to the data in the DataSet back to the data source.

Common DataAdapter Implementations

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

• SqlDataAdapter: For SQL Server.
• OleDbDataAdapter: For OLE DB-compliant data sources (e.g., Access, Excel).
• OdbcDataAdapter: For ODBC data sources.
• OracleDataAdapter: For Oracle databases.

Example Usage (Conceptual)

Here's a simplified conceptual example of how you might use a SqlDataAdapter to retrieve data:

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

// ...

string connectionString = "Server=myServer;Database=myDatabase;User ID=myUser;Password=myPassword;";
string sqlQuery = "SELECT CustomerID, CompanyName, ContactName FROM Customers";

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

    try
    {
        connection.Open();
        adapter.Fill(dataSet, "Customers"); // Fill the DataSet with data into a table named "Customers"

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

DataAdapter vs. DataReader

It's important to understand the difference between a DataAdapter and a DataReader:

• DataReader: Provides a forward-only, read-only stream of data. It's efficient for scenarios where you only need to read data and don't need to cache it or make modifications.
• DataAdapter: Works with a DataSet, which is an in-memory cache of data. This allows for disconnected data access, making modifications, and then persisting those changes back to the data source. The DataAdapter is responsible for the communication between the DataSet and the data source.

Choose the appropriate component based on your application's requirements for data access and manipulation.