MSDN Documentation

Overview

A DataAdapter abstracts the details of data retrieval and persistence. It uses four key components to interact with the data source:

• SelectCommand: A Command object that retrieves records from the data source.
• InsertCommand: A Command object that inserts new records into the data source.
• UpdateCommand: A Command object that updates existing records in the data source.
• DeleteCommand: A Command object that deletes records from the data source.

When you call methods like Fill() on a DataAdapter, it uses the SelectCommand to populate a DataSet. When you call Update(), it examines the RowState of the rows in a DataTable within the DataSet and executes the appropriate command (InsertCommand, UpdateCommand, or DeleteCommand) for each modified row.

Key DataAdapter Classes

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

• SqlDataAdapter: Used with SQL Server.
• OracleDataAdapter: Used with Oracle databases.
• OleDbDataAdapter: Used with OLE DB data sources (which can include various databases like Access, Excel, or even SQL Server via OLE DB providers).
• OdbcDataAdapter: Used with ODBC data sources.

Using the DataAdapter

Here's a typical workflow for using a DataAdapter:

1. Create a Connection object to connect to the data source.
2. Create a Command object for selecting data, defining the SQL query.
3. Create a DataAdapter object (e.g., SqlDataAdapter) and set its SelectCommand property.
4. Create a DataSet object to hold the data.
5. Call the Fill() method of the DataAdapter, passing the DataSet and the name of the table to populate.
6. Optionally, modify the data in the DataSet.
7. If changes are made, create Command objects for inserting, updating, and deleting data, and assign them to the InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter, respectively.
8. Call the Update() method of the DataAdapter, passing the DataSet.

Example:

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 selectSql = "SELECT CustomerID, CompanyName FROM Customers";
        string insertSql = "INSERT INTO Customers (CompanyName) VALUES (@CompanyName); SELECT CAST(SCOPE_IDENTITY() AS INT);";
        string updateSql = "UPDATE Customers SET CompanyName = @CompanyName WHERE CustomerID = @CustomerID";
        string deleteSql = "DELETE FROM Customers WHERE CustomerID = @CustomerID";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter();

            // Select Command
            adapter.SelectCommand = new SqlCommand(selectSql, connection);

            // Insert Command
            adapter.InsertCommand = new SqlCommand(insertSql, connection);
            adapter.InsertCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 100, "CompanyName");
            // For InsertCommand, we often need to capture the generated ID.
            // This example assumes SQL Server's SCOPE_IDENTITY() for auto-generated keys.
            adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;


            // Update Command
            adapter.UpdateCommand = new SqlCommand(updateSql, connection);
            adapter.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 100, "CompanyName");
            adapter.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Int, 0, "CustomerID");

            // Delete Command
            adapter.DeleteCommand = new SqlCommand(deleteSql, connection);
            adapter.DeleteCommand.Parameters.Add("@CustomerID", SqlDbType.Int, 0, "CustomerID");

            DataSet dataSet = new DataSet();

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

                Console.WriteLine("--- Original Data ---");
                foreach (DataRow row in dataSet.Tables["Customers"].Rows)
                {
                    Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}");
                }

                // --- Example of modifying data ---
                Console.WriteLine("\n--- Modifying Data ---");
                // Add a new row
                DataRow newRow = dataSet.Tables["Customers"].NewRow();
                newRow["CompanyName"] = "New Tech Solutions";
                dataSet.Tables["Customers"].Rows.Add(newRow);
                Console.WriteLine("Added new row.");

                // Update an existing row
                if (dataSet.Tables["Customers"].Rows.Count > 0)
                {
                    DataRow firstRow = dataSet.Tables["Customers"].Rows[0];
                    firstRow["CompanyName"] = firstRow["CompanyName"] + " (Updated)";
                    Console.WriteLine($"Updated row with ID: {firstRow["CustomerID"]}");
                }

                // --- Update the data source ---
                Console.WriteLine("\n--- Updating Data Source ---");
                int rowsAffected = adapter.Update(dataSet, "Customers");
                Console.WriteLine($"Rows affected by update: {rowsAffected}");

                // --- Refresh data to show changes ---
                Console.WriteLine("\n--- Data After Update ---");
                dataSet.Clear(); // Clear the existing dataset to re-fetch
                adapter.Fill(dataSet, "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}");
            }
        }
    }
}
                

Handling Concurrency

When multiple users might be modifying the same data, concurrency issues can arise. The DataAdapter can be configured to detect and handle these situations. By default, it performs an optimistic concurrency check:

• Before an update or delete operation, the DataAdapter checks if the original values of the row in the database match the original values stored in the DataSet.
• If they don't match, it means another user has modified the row, and an exception (typically DBConcurrencyException) is thrown.

You can customize how concurrency conflicts are handled by implementing event handlers for the RowUpdating and RowUpdating events of the DataAdapter.

Key Methods and Properties

Commonly used members of the DataAdapter class:

• Fill(DataSet): Populates a DataSet with the results of executing the SelectCommand.
• Fill(DataTable): Populates a specific DataTable within a DataSet.
• Fill(DataSet, string): Populates a DataSet and names the resulting table.
• Update(DataSet): Sends changes made to a DataSet back to the data source.
• Update(DataTable): Sends changes made to a specific DataTable back to the data source.
• SelectCommand: Gets or sets the Command used to retrieve records from the data source.
• InsertCommand: Gets or sets the Command used to insert new records into the data source.
• UpdateCommand: Gets or sets the Command used to update existing records in the data source.
• DeleteCommand: Gets or sets the Command used to delete records from the data source.

Related Concepts

• DataSet Objects
• Command Objects
• Connection Objects
• DataReader Objects