---
title: "Connected vs Disconnected Architecture in ADO.NET"  
description: "In ADO.NET, there are two types of architectures for accessing and managing data:"  
author: "ICSM Computer"  
published: 2025-02-12  
updated: 2025-02-12  
canonical: https://www.mindstick.com/articles/338512/connected-vs-disconnected-architecture-in-ado-dot-net  
category: "ado.net"  
tags: ["c#", "ado.net"]  
reading_time: 4 minutes  

---

# Connected vs Disconnected Architecture in ADO.NET

#### Connected vs. Disconnected Architecture in ADO.NET

In ADO.NET, there are **two types of architectures** for accessing and managing data:

1. **Connected Architecture** (Uses `DataReader`)
2. **Disconnected Architecture** (Uses `DataSet`)

#### Connected Architecture (Using `DataReader`)

**Best for:** Fast, read-only access to data with an **active [database connection](https://answers.mindstick.com/qa/93693/what-is-connection-string-in-database-connection)**.

#### How it Works?

- Establishes a connection to the database.
- Uses `SqlCommand` and `SqlDataReader` to [retrieve data](https://answers.mindstick.com/qa/102391/how-does-a-computer-hard-drive-store-and-retrieve-data).
- Reads data **row-by-row** (forward-only).
- The connection remains **open** [while reading](https://www.mindstick.com/interview/34122/how-do-you-prevent-a-file-from-being-modified-while-reading-it) data.
- After reading, the connection **must be closed manually**.

###

#### Example: Using `DataReader` (Connected Mode)

```cs
using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connStr = "Server=your_server;Database=your_db;Integrated Security=True;";

        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open(); // Must keep connection open
            SqlCommand cmd = new SqlCommand("SELECT ID, Name FROM Employees", conn);
            SqlDataReader reader = cmd.ExecuteReader(); // Executes query

            while (reader.Read()) // Reads row by row (Forward-only)
            {
                Console.WriteLine($"ID: {reader["ID"]}, Name: {reader["Name"]}");
            }

            reader.Close(); // Close reader
        } // Connection automatically closes here due to 'using'
    }
}
```

## Pros:

1. Fast and efficient for [large datasets](https://www.mindstick.com/forum/161063/how-to-optimize-entity-framework-core-queries-for-large-datasets).
2. Uses less memory (does not store data in memory).
3. Best for read-only operations.

## Cons:

1. Requires an **active connection** to read data.
2. Cannot modify or store data in memory.
3. Cannot navigate backward (forward-only).

#### Disconnected Architecture (Using `DataSet`)

**Best for:** Working with data **offline** without an active database connection.

![Connected vs Disconnected Architecture in ADO.NET](https://www.mindstick.com/mindstickarticle/b3cf18a5-9d11-4c11-a268-048cc908e3a4/images/72785303-ca95-4514-acd6-8e5de4064050.jpg)

#### How it Works?

- Uses `SqlDataAdapter` to [fetch data](https://www.mindstick.com/forum/156768/how-to-fetch-data-using-union-method-in-linq) **without keeping the connection open**.
- Stores data in a **DataSet** (in-memory storage).
- Allows sorting, filtering, and modifying data.
- Can update changes back to the database using `SqlDataAdapter.Update()`.

#### Example: Using `DataSet` (Disconnected Mode)

```cs
using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connStr = "Server=your_server;Database=your_db;Integrated Security=True;";
        DataSet ds = new DataSet(); // Stores data in-memory

        using (SqlConnection conn = new SqlConnection(connStr))
        {
            string query = "SELECT ID, Name FROM Employees";
            SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
            adapter.Fill(ds, "Employees"); // Loads data into DataSet
        }

        // No active connection required after fetching data
        foreach (DataRow row in ds.Tables["Employees"].Rows)
        {
            Console.WriteLine($"ID: {row["ID"]}, Name: {row["Name"]}");
        }
    }
}
```

## Pros:

1. No need to keep the connection open.
2. Can work with **[multiple tables](https://www.mindstick.com/forum/160909/how-to-join-multiple-tables-and-retrieve-specific-columns-in-sql-server)** (like an in-memory database).
3. Supports **modifications, filtering, and sorting**.
4. Can [update data](https://www.mindstick.com/forum/34639/how-to-create-a-stored-procedure-for-insert-and-update-data) back to the database.

## Cons:

1. **Slower** than `DataReader` (stores data in memory).
2. Uses **more memory** for large datasets.

#### Key Differences: Connected vs. Disconnected Architecture

| Feature | **Connected (**`DataReader`**)** | **Disconnected (**`DataSet`**)** |
| --- | --- | --- |
| **Connection Type** | Always **open** | Works **without** an active connection |
| **[Data Storage](https://answers.mindstick.com/qa/102476/how-does-a-computer-s-raid-configuration-enhance-data-storage)** | Reads **row-by-row** | Stores **entire data in memory** |
| **Navigation** | **Forward-only** | **Can navigate, filter, and sort** |
| **Performance** | **Faster**, low [memory usage](https://www.mindstick.com/forum/158210/what-are-some-common-techniques-for-optimizing-memory-usage-in-computer-systems) | **Slower**, higher memory usage |
| **Data Modification** | No (Read-only) | Yes (Can modify and update) |
| **Best for** | **Fast reading, large datasets** | **Disconnected apps, multiple tables** |

##

![Connected vs Disconnected Architecture in ADO.NET](https://www.mindstick.com/mindstickarticle/b3cf18a5-9d11-4c11-a268-048cc908e3a4/images/d665be72-fe9d-4362-9a56-1aab1b1e891b.png)

#### When to Use Which?

| Scenario | Use `DataReader` (Connected) | Use `DataSet` (Disconnected) |
| --- | --- | --- |
| Large datasets (fast, read-only) | Yes | No |
| Need to modify data before saving | No | Yes |
| Need sorting/filtering in-memory | No | Yes |
| Need to store multiple tables | No | Yes |
| Requires real-time data | Yes | No |

##

#### Summary

| **Architecture** | **Use When...** |
| --- | --- |
| **Connected (**`DataReader`**)** | You need **fast, read-only, forward-only access** to data. |
| **Disconnected (**`DataSet`**)** | You need **offline access**, store multiple tables, or modify data. |

---

Original Source: https://www.mindstick.com/articles/338512/connected-vs-disconnected-architecture-in-ado-dot-net

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.