MSDN Documentation

DataTable Adapters in ADO.NET

ADO.NET's DataTable Adapters, specifically the DataAdapter class and its derived classes like SqlDataAdapter and OleDbDataAdapter, are crucial components for bridging the gap between a data source and a DataSet or DataTable. They abstract the details of retrieving data from and writing data back to a data provider.

Core Functionality

DataTable Adapters serve two primary functions:

• Populating DataTables: They execute commands against a data source and fill a DataTable object with the results. This is typically done using the Fill() method.
• Synchronizing Data: They manage the process of writing changes made to a DataTable (inserts, updates, deletes) back to the original data source. This is accomplished using the Update() method.

Key Components of a DataAdapter

A typical DataTable Adapter has the following key components:

• SelectCommand: A DbCommand object that retrieves records from the data source. This command is executed when the Fill() method is called.
• InsertCommand: A DbCommand object that inserts new records into the data source. This command is executed when the Update() method encounters new rows in the DataTable.
• UpdateCommand: A DbCommand object that modifies existing records in the data source. This command is executed when the Update() method encounters modified rows.
• DeleteCommand: A DbCommand object that deletes records from the data source. This command is executed when the Update() method encounters deleted rows.
• SelectCommand.Connection: A DbConnection object that represents the connection to the data source.
• FillLoadOption: Specifies how data should be loaded into the DataTable.
• AcceptChangesDuringFill: A boolean value that determines whether AcceptChanges() is called on the DataTable after the Fill() operation.
• AcceptChangesDuringUpdate: A boolean value that determines whether AcceptChanges() is called on the DataTable after the Update() operation.

Using a SqlDataAdapter (Example)

Here's a simple example of how to use SqlDataAdapter to retrieve data and populate a DataTable:

            
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 CustomerID, CompanyName FROM dbo.Customers;";

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

            DataTable customersTable = new DataTable("Customers");

            try
            {
                connection.Open();
                adapter.Fill(customersTable);

                // Process the DataTable
                foreach (DataRow row in customersTable.Rows)
                {
                    Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}
            
        

Updating the Data Source

To update the data source, you first need to configure the InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter. ADO.NET can automatically generate these commands for SQL Server using the SqlCommandBuilder class.

            
// Assuming 'adapter' and 'customersTable' are already set up from the previous example
using (SqlCommandBuilder builder = new SqlCommandBuilder(adapter))
{
    try
    {
        // Make changes to customersTable
        // For example: customersTable.Rows[0]["CompanyName"] = "Updated Company Name";
        // customersTable.Rows.Add(new object[] { "NEWID", "New Company" });
        // customersTable.Rows[1].Delete();

        int rowsAffected = adapter.Update(customersTable);
        Console.WriteLine($"Rows updated: {rowsAffected}");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Error updating: {ex.Message}");
    }
}
            
        

DataTable vs. DataSet

While DataAdapter is often used with DataSet, it can also directly fill and update a single DataTable. A DataSet is a collection of one or more DataTable objects, allowing you to represent complex relational data.

Provider-Specific DataAdapters

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

• SqlDataAdapter: For SQL Server.
• OleDbDataAdapter: For OLE DB providers (e.g., Access, older ODBC drivers).
• OdbcDataAdapter: For ODBC data sources.
• OracleDataAdapter: For Oracle databases.

DataTable Adapters are a fundamental part of ADO.NET for managing disconnected data. They offer a robust mechanism for fetching and persisting data, simplifying data access in .NET applications.