ADO.NET Data Adapters

Bridging Datasets and DataSources in .NET

Understanding Data Adapters

Data Adapters in ADO.NET serve as a crucial bridge between a DataSet (an in-memory cache of data) and a data source, such as a SQL Server database. They provide the functionality to retrieve data from the source and populate a DataSet, and conversely, to persist changes made in the DataSet back to the data source.

Think of a Data Adapter as a mediator. It knows how to execute SQL commands or stored procedures to fetch data, and it also understands how to translate changes (insertions, updates, deletions) in your application's data into commands that the database can understand.

Key Components

A Data Adapter is typically composed of several key command objects:

These commands are usually represented by specific implementations like SqlDataAdapter (for SQL Server) or OleDbDataAdapter (for OLE DB compliant data sources).

Basic Usage: Retrieving and Updating Data

The primary methods of a Data Adapter are Fill() and Update().

1. Retrieving Data (Fill())

The Fill() method takes a DataSet and a table name (or DataTable) as arguments and populates it with data retrieved by the SelectCommand.

2. Updating Data (Update())

The Update() method takes a DataSet and a table name (or DataTable) and applies the changes recorded in the DataSet back to the data source. It intelligently determines whether to perform an INSERT, UPDATE, or DELETE operation for each modified row.

Data Adapters work in conjunction with Connection objects to establish communication with the database and Command objects to define the SQL statements or stored procedures to be executed.

Advanced Scenarios

Code Examples

Example 1: Basic Data Retrieval

This example demonstrates how to retrieve data from a SQL Server table into a DataSet.


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

// Assume you have a connection string
string connectionString = "Your_Connection_String_Here";
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"); // Fills the DataSet with data into a table named "Customers"

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

Example 2: Updating Data

This example shows how to update a record in the database based on changes in the DataSet.


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

// Assume you have a connection string and existing DataSet
string connectionString = "Your_Connection_String_Here";
string sqlSelectQuery = "SELECT OrderID, OrderDate, ShipName FROM Orders WHERE OrderID = 10248";
string sqlUpdateQuery = "UPDATE Orders SET OrderDate = @OrderDate, ShipName = @ShipName WHERE OrderID = @OrderID";

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

    // Configure the UpdateCommand
    SqlCommand updateCommand = new SqlCommand(sqlUpdateQuery, connection);
    updateCommand.Parameters.Add("@OrderDate", SqlDbType.DateTime, 0, "OrderDate");
    updateCommand.Parameters.Add("@ShipName", SqlDbType.NVarChar, 200, "ShipName");
    updateCommand.Parameters.Add("@OrderID", SqlDbType.Int, 4, "OrderID");
    adapter.UpdateCommand = updateCommand;

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

        if (dataSet.Tables["Orders"].Rows.Count > 0)
        {
            DataRow orderRow = dataSet.Tables["Orders"].Rows[0];
            orderRow["OrderDate"] = DateTime.Now; // Modify the data
            orderRow["ShipName"] = "New Ship Name";

            // Apply the changes back to the data source
            int rowsAffected = adapter.Update(dataSet, "Orders");
            Console.WriteLine($"Successfully updated {rowsAffected} row(s).");
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"An error occurred: {ex.Message}");
    }
}

Related Resources