Microsoft Learn

Disconnected Data Access with ADO.NET

Disconnected data access in ADO.NET refers to a pattern where an application retrieves data from a data source, closes the connection, and then works with the data in memory. This contrasts with connected data access, where the connection to the data source remains open while data is being manipulated.

Core Components

The primary components that enable disconnected data access are:

• DataSet: An in-memory representation of data composed of one or more DataTable objects. It can hold tables, relationships, and constraints.
• DataTable: Represents a single table of data in memory, similar to a database table. It contains DataColumn objects (schema) and DataRow objects (data).
• DataAdapter: Acts as a bridge between a DataSet and a data source. It's responsible for filling a DataSet with data and reconciling changes made in the DataSet back to the data source. Common implementations include SqlDataAdapter, OleDbDataAdapter, and OdbcDataAdapter.

The Workflow

The typical workflow for disconnected data access involves the following steps:

1. Create a DataSet: Instantiate a new DataSet object to hold the retrieved data.
2. Create a DataAdapter: Instantiate a data adapter (e.g., SqlDataAdapter) and configure its `SelectCommand` property to retrieve the desired data.
3. Fill the DataSet: Use the `Fill` method of the DataAdapter to populate the DataSet with data from the data source. The connection is opened, data is fetched, and then the connection is closed.
4. Work with Data in Memory: The application can now interact with the data within the DataSet (or its DataTable objects) without an active connection to the database. This includes filtering, sorting, editing, adding, and deleting data.
5. Update the Data Source: When ready, use the `Update` method of the DataAdapter. The adapter automatically generates and executes the necessary SQL `INSERT`, `UPDATE`, or `DELETE` commands to synchronize the changes in the DataSet with the data source.

Example Scenario (Conceptual)

Imagine a desktop application that needs to display customer information. The application can fetch all customer data into a DataSet when it starts. The user can then browse, search, and modify this data offline. When the user saves their changes, the application uses the DataAdapter to update the database.

Code Snippet (C# - Illustrative)

Here's a simplified illustration of how you might use a SqlDataAdapter and DataSet:

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

public class DisconnectedDataAccess
{
    public static void Example()
    {
        string connectionString = "YourConnectionStringHere";
        string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers";

        DataSet customerDataSet = new DataSet();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);

            try
            {
                // 1. Open connection (implicitly by adapter.Fill) and fill the DataSet
                adapter.Fill(customerDataSet, "Customers");
                Console.WriteLine("Data loaded into DataSet.");

                // Connection is closed after Fill

                // 2. Work with data in memory (e.g., iterate through rows)
                Console.WriteLine("\nCustomer Data:");
                foreach (DataRow row in customerDataSet.Tables["Customers"].Rows)
                {
                    Console.WriteLine($"- {row["CompanyName"]} ({row["ContactName"]})");
                }

                // Imagine making changes here:
                // DataRow newRow = customerDataSet.Tables["Customers"].NewRow();
                // newRow["CompanyName"] = "New Company";
                // newRow["ContactName"] = "New Contact";
                // customerDataSet.Tables["Customers"].Rows.Add(newRow);

                // 3. Update the data source (requires SqlCommandBuilder or explicit commands)
                // For simplicity, Update command is not shown here but would involve
                // SqlCommandBuilder or manually defining Insert/Update/Delete commands.
                // adapter.UpdateCommand = ...
                // adapter.InsertCommand = ...
                // adapter.DeleteCommand = ...
                // int rowsAffected = adapter.Update(customerDataSet, "Customers");
                // Console.WriteLine($"{rowsAffected} rows updated.");
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"Database error: {ex.Message}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }
    }
}
                

Advantages of Disconnected Data Access

• Scalability: Reduces database server load and connection pooling strain.
• Responsiveness: Applications can remain responsive even during long data operations as the connection is closed.
• Flexibility: Allows for complex in-memory data manipulation, including merging data from multiple sources.
• Offline Scenarios: Enables applications to function and modify data even when network connectivity is intermittent or unavailable.

Considerations

• Data Staleness: Data in the DataSet can become stale if the underlying data source changes. Strategies like periodic re-fetching or optimistic concurrency control might be needed.
• Memory Usage: Large datasets can consume significant memory.
• Complexity: Handling updates, especially with complex scenarios involving multiple tables and relationships, can be more intricate.

Understanding and effectively utilizing disconnected data access is crucial for building robust and scalable .NET applications that interact with databases.