Microsoft Learn

Connected vs. Disconnected Data Access in ADO.NET

ADO.NET provides two primary models for interacting with data sources: the connected and disconnected data access models. Understanding the differences and when to use each is crucial for efficient and effective data management in your .NET applications.

The Connected Data Access Model

In the connected data access model, your application maintains an active connection to the data source throughout the duration of data retrieval and manipulation. This model is characterized by the use of objects like Connection and Command, often in conjunction with a DataReader.

Characteristics:

• Real-time Data: You are always working with the most current data from the source.
• Efficient for Reading: Generally more efficient for read-only operations or when you need to process data row by row as it's fetched.
• Resource Intensive: The connection remains open, which consumes database resources and network bandwidth.
• Limited Data Scope: Data is not typically cached locally in the application.

Common Usage:

• Streaming large datasets.
• Performing quick lookups.
• Executing stored procedures that return single values or simple result sets.
• Situations where data integrity requires an active transaction.

Example (Conceptual C#):

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string query = "SELECT CustomerID, CompanyName FROM Customers";
    SqlCommand command = new SqlCommand(query, connection);

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
        }
    }
    // Connection is automatically closed here by the 'using' statement
}
            

The Disconnected Data Access Model

The disconnected data access model allows your application to retrieve data from a data source, close the connection, and then work with the data locally. This is achieved using DataSet and DataTable objects, often populated and updated via DataAdapter objects.

Characteristics:

• Data Caching: Data is held in memory within the application.
• Reduced Resource Usage: The database connection is typically open only during data loading and saving, reducing load on the server.
• Flexibility: Allows for complex data manipulation, filtering, sorting, and navigation without maintaining an active connection.
• Potential for Stale Data: If the data on the server changes while it's in your application's cache, you might be working with stale data. Reconciliation is needed when updating.

Common Usage:

• Windows Forms or WPF applications that present data in grids.
• Web applications where you want to process data without holding a connection for the entire request duration.
• Scenarios requiring local data manipulation, such as client-side validation or reporting.

Example (Conceptual C#):

string connectionString = "your_connection_string";
string query = "SELECT OrderID, OrderDate FROM Orders";

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

    // Connection opens, data is filled, then connection closes
    adapter.Fill(dataSet, "Orders");

    // Work with dataSet.Tables["Orders"] disconnected from the database
    DataTable ordersTable = dataSet.Tables["Orders"];
    foreach (DataRow row in ordersTable.Rows)
    {
        Console.WriteLine($"Order ID: {row["OrderID"]}, Date: {row["OrderDate"]}");
    }

    // To update the database, you would typically use an SqlCommandBuilder
    // or explicitly define UPDATE, INSERT, DELETE commands on the DataAdapter.
}
            

Choosing the Right Model

The choice between connected and disconnected data access depends on your specific application requirements:

• Use the connected model for performance-critical read operations where real-time data is essential and the dataset is manageable.
• Use the disconnected model when you need to manipulate data locally, reduce server load, or build complex UIs that interact with data over time.

Understanding these two models allows you to design more robust, scalable, and performant data-driven applications with ADO.NET.