MSDN Documentation

DataAdapters

DataAdapters serve as a bridge between a Dataset and a data source for retrieving and saving data. They encapsulate the logic for retrieving data from a database, populating a Dataset, and then propagating changes made in the Dataset back to the database.

A DataAdapter object provides a connection between the Dataset and the data source. It is used to fill a Dataset and persist changes in the Dataset back to the data source. A DataAdapter uses command objects to execute SQL statements or stored procedures against the data source.

Core Components of a DataAdapter

A DataAdapter object typically exposes the following core command properties:

• SelectCommand: This command retrieves data from the data source and fills the Dataset.
• InsertCommand: This command is executed when rows are added to the DataTable in the Dataset.
• UpdateCommand: This command is executed when rows in the DataTable are modified.
• DeleteCommand: This command is executed when rows are removed from the DataTable.

Common DataAdapter Classes

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

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

Using a DataAdapter

The primary methods of a DataAdapter are:

• Fill(DataTable): Populates a specified DataTable with data from the data source.
• Fill(DataSet): Populates a Dataset with data from the data source. If the Dataset already contains tables, Fill adds rows to existing tables or creates new tables based on the schema and query results.
• Update(DataTable): Persists all changes (inserts, updates, deletes) in a specified DataTable back to the data source.
• Update(DataSet): Persists all changes in a specified Dataset back to the data source.

Example: Retrieving Data with SqlDataAdapter

Here's a C# example demonstrating how to use SqlDataAdapter to retrieve data from a SQL Server database:

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

public class DataAdapterExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        string queryString = "SELECT ProductID, ProductName, UnitPrice FROM Production.Products;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand(queryString, connection);

            DataSet dataSet = new DataSet();

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

                // Process the data in the DataSet
                foreach (DataRow row in dataSet.Tables["Products"].Rows)
                {
                    Console.WriteLine($"ID: {row["ProductID"]}, Name: {row["ProductName"]}, Price: {row["UnitPrice"]}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }
    }
}
                

Example: Updating Data with SqlDataAdapter

After modifying data in the Dataset, you can use the Update method to save those changes back to the database:

// Assuming 'dataSet' is populated and modified as shown previously
// ... modify dataSet.Tables["Products"].Rows[0]["UnitPrice"] = 25.00m; ...

// Configure the UpdateCommand (requires INSERT, UPDATE, DELETE commands to be set up)
using (SqlConnection connection = new SqlConnection(connectionString))
{
    // You would typically set up InsertCommand, UpdateCommand, DeleteCommand here
    // For brevity, only demonstrating UpdateCommand setup.
    SqlCommand updateCommand = new SqlCommand(
        "UPDATE Production.Products SET ProductName = @ProductName, UnitPrice = @UnitPrice WHERE ProductID = @ProductID",
        connection);

    updateCommand.Parameters.Add("@ProductName", SqlDbType.VarChar, 50, "ProductName");
    updateCommand.Parameters.Add("@UnitPrice", SqlDbType.Money, 0, "UnitPrice");
    SqlParameter parameter = updateCommand.Parameters.Add("@ProductID", SqlDbType.Int, 0, "ProductID");
    parameter.SourceVersion = DataRowVersion.Original; // Important for WHERE clause

    adapter.UpdateCommand = updateCommand; // Assign the update command

    try
    {
        connection.Open();
        int rowsAffected = adapter.Update(dataSet, "Products"); // Persist changes
        Console.WriteLine($"{rowsAffected} row(s) updated.");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"An error occurred during update: {ex.Message}");
    }
}