Connected vs Disconnected Architecture in ADO.NET Core

Back to: ADO.NET Core Tutorial For Beginners and Professionals

Connected vs Disconnected Architecture in ADO.NET Core

In this article, I will discuss Connected vs Disconnected Architecture in ADO.NET Core with Examples. Please read our previous article discussing how to Implement Pagination using ADO.NET Core and Stored Procedure with Examples.

ADO.NET Core Connected vs Disconnected Architecture

ADO.NET Core is a fundamental part of the .NET ecosystem that provides a set of classes for accessing and manipulating data in relational databases. It supports two basic approaches for interacting with relational databases: Connected-Oriented Architecture and Disconnected-Oriented Architecture. Let’s understand both architectures in detail and compare them to know when to use one over the other.

SQL Server Database Setup:

To demonstrate how both architectures work, we will create a simple SQL Server database with tables and dummy data. So, please run the following SQL Script to create a SQL Server database named OrdersDB, Customers, and Orders tables with some dummy data for testing purposes:

-- Create OrdersDB Database
CREATE DATABASE OrdersDB;
GO

-- Switch to OrdersDB Database
USE OrdersDB;
GO

-- Create Customers Table
CREATE TABLE Customers (
    CustomerId INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100) NOT NULL
);
GO

-- Create Orders Table
CREATE TABLE Orders (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    OrderDate DATETIME2 NOT NULL,
    CustomerId INT NOT NULL FOREIGN KEY REFERENCES Customers(CustomerId),
    Amount DECIMAL(10,2) NOT NULL
);
GO

-- Insert Dummy Data into Customers table
INSERT INTO Customers (Name, Email) VALUES
('Alice Smith', 'alice@example.com'),
('Bob Johnson', 'bob@example.com'),
('Sara Taylor', 'bob@example.com');
GO

-- Insert Dummy Data into Orders table
INSERT INTO Orders (OrderDate, CustomerId, Amount) VALUES
(GETDATE(), 1, 250.00),
(DATEADD(DAY, -1, GETDATE()), 2, 125.50),
(DATEADD(DAY, -7, GETDATE()), 1, 300.75);
GO

What is Connected Oriented Architecture?

The Connected Oriented Architecture refers to a design approach where the application maintains an open and active connection to the database while performing operations. In this model, select queries, inserts, updates, and deletes are executed in real time, ensuring immediate data consistency. This architecture is used when the data needs to be constantly synchronized with the database, and real-time interaction is required.

How Does Connected Architecture Work in ADO.NET Core?

In this architecture, the connection to the database is kept open for the duration of the transaction. The data is fetched from the database, processed, and can be updated directly to the database without closing the connection in between. The connection is established using the SqlConnection object, and operations such as ExecuteReader, ExecuteNonQuery, and ExecuteScalar are performed while the connection remains open. For a clearer understanding, please refer to the following diagram.

The following are the key Components of ADO.NET Core Connected Oriented Architecture:

• SqlConnection: Manages the connection between the application and the database.
• SqlCommand: Used to execute commands against the database, such as SELECT, INSERT, UPDATE, DELETE, and also stored procedures.
• SqlDataReader: Provides a way to read data from the database in a forward-only, read-only manner.
• SqlTransaction (optional): Ensures atomic operations across multiple commands.

Example to Understand Connected Architecture in ADO.NET Core

The following example demonstrates the Connected Architecture in ADO.NET Core. Here, using SqlConnection, SqlCommand, and SqlDataReader, we read the Customer data from the database. The example code is self-explained, so please read the comment lines for a better understanding.

using Microsoft.Data.SqlClient;

namespace ConnectedArchitecture
{
    public class Program
    {
        static async Task Main(string[] args)
        {
            // Define the connection string to your SQL Server OrdersDB database.
            string connString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrdersDB;Trusted_Connection=True;TrustServerCertificate=True;";

            // Use 'using' to ensure proper disposal
            using (SqlConnection conn = new SqlConnection(connString))
            {
                try
                {
                    // Open the connection asynchronously
                    await conn.OpenAsync();

                    // Define command to read customers
                    string sql = "SELECT CustomerId, Name, Email FROM Customers";
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        // Execute reader asynchronously
                        using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
                        {
                            Console.WriteLine("Customer List:");
                            // Iterate rows asynchronously
                            while (await reader.ReadAsync())
                            {
                                int id = reader.GetInt32(0);        // index-based
                                string name = reader.GetString(1);
                                string email = reader.GetString(2);

                                // Displaying the Customer Details
                                Console.WriteLine($"  ID={id}, Name={name}, Email={email}");
                            }
                        }
                    }
                }
                catch (SqlException ex)
                {
                    // Handle database errors
                    Console.Error.WriteLine($"ERROR SQL Error: {ex.Message}");
                }
                catch (Exception ex)
                {
                    // Handle general errors
                    Console.Error.WriteLine($"ERROR Unexpected: {ex.Message}");
                }
            }
        }
    }
}

Code Explanation:

• The SqlConnection is opened and closed within a using block for resource safety.
• SqlCommand executes a simple SELECT.
• SqlDataReader streams results in a forward-only, read-only manner.
• Each row is read and printed immediately, minimizing memory usage.

Output:

What happens if the Connection is closed between operations?

Let us understand this with an example. After reading the first row from the database, let us close the connection. In the example below, you can see that after reading the first row, we close the database connection using the Close method within the while loop.

using Microsoft.Data.SqlClient;

namespace ConnectedArchitecture
{
    public class Program
    {
        static async Task Main(string[] args)
        {
            // Define the connection string to your SQL Server OrdersDB database.
            string connString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrdersDB;Trusted_Connection=True;TrustServerCertificate=True;";

            // Use 'using' to ensure proper disposal
            using (SqlConnection conn = new SqlConnection(connString))
            {
                try
                {
                    // Open the connection asynchronously
                    await conn.OpenAsync();

                    // Define command to read customers
                    string sql = "SELECT CustomerId, Name, Email FROM Customers";
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        // Execute reader asynchronously
                        using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
                        {
                            Console.WriteLine("Customer List:");
                            // Iterate rows asynchronously
                            while (await reader.ReadAsync())
                            {
                                int id = reader.GetInt32(0);        // index-based
                                string name = reader.GetString(1);
                                string email = reader.GetString(2);

                                // Displaying the Customer Details
                                Console.WriteLine($"  ID={id}, Name={name}, Email={email}");

                                //Here, the connection is closed
                                conn.Close();
                            }
                        }
                    }
                }
                catch (SqlException ex)
                {
                    // Handle database errors
                    Console.Error.WriteLine($"ERROR SQL Error: {ex.Message}");
                }
                catch (Exception ex)
                {
                    // Handle general errors
                    Console.Error.WriteLine($"ERROR Unexpected: {ex.Message}");
                }
            }
        }
    }
}

Output:

As you can see in the above output, the data reader throws an exception after reading the first row because the database connection is closed. This proves that connection-oriented architecture always requires an active and open connection to the database while performing the database operations.

What is Disconnected Oriented Architecture in ADO.NET Core?

Disconnected Architecture allows data manipulation without maintaining a continuous connection to the database. That means it involves working with data in a local, in-memory store rather than maintaining a constant connection to the database.

In the Disconnected Oriented Architecture, the application connects to the database only temporarily to fetch or update data. Once the data is retrieved, the connection is closed, and the data is maintained locally. Updates to the database are made after the connection is re-established, often through batch operations. It uses in-memory structures like DataSet and DataTable.

How Does Disconnected Oriented Architecture Work?

In this architecture, data is stored in objects such as a DataSet or a DataTable. These objects can hold data in memory and allow users to manipulate it offline. When it’s time to save the changes, a connection is re-established, and the SqlDataAdapter is used to push changes back to the database. After completing the operation, the connection is closed again. For a better understanding, please have a look at the following diagram.

The following are the Key Components of ADO.NET Core Disconnected Oriented Architecture

• SqlConnection: Opens a connection temporarily to fetch or update data.
• DataSet and DataTable: Store data in memory, supporting disconnected scenarios.
• SqlDataAdapter: Fills DataSet or DataTable and updates the database based on local changes.
• SqlCommandBuilder: Automatically generates INSERT, UPDATE, and DELETE commands.

Example to Understand Disconnected Architecture in ADO.NET Core

The following example demonstrates how to fetch data using ADO.NET Core disconnected architecture. Here, we fetch data into a DataTable using SqlDataAdapter, make some changes to the data in memory, and then update those changes back to the database. The example code is self-explained, so please read the comment lines for a better understanding.

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

namespace ConnectedArchitecture
{
    public class Program
    {
        static void Main(string[] args)
        {
            // Define the connection string to your SQL Server OrdersDB database.
            string connString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrdersDB;Trusted_Connection=True;TrustServerCertificate=True;";

            // Create connection
            using (SqlConnection conn = new SqlConnection(connString))
            {
                // Create adapter
                using (SqlDataAdapter adapter = new SqlDataAdapter())
                {
                    try
                    {
                        // Configure SELECT command
                        adapter.SelectCommand = new SqlCommand("SELECT OrderId, OrderDate, CustomerId, Amount FROM Orders", conn);

                        // Auto-generate INSERT/UPDATE/DELETE commands
                        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

                        // Fill DataTable (in-memory)
                        DataTable ordersTable = new DataTable("Orders");
                        adapter.Fill(ordersTable);

                        Console.WriteLine("Original Orders:");
                        foreach (DataRow row in ordersTable.Rows)
                        {
                            Console.WriteLine($"  [{row[0]}] Date={row[1]}, Cust={row[2]}, Amt={row[3]}");
                        }

                        // Add a new row
                        DataRow newRow = ordersTable.NewRow();
                        newRow["OrderDate"] = DateTime.Now;
                        newRow["CustomerId"] = 2;
                        newRow["Amount"] = 199.99m;
                        ordersTable.Rows.Add(newRow);

                        // Update database in batch
                        int changes = adapter.Update(ordersTable);
                        Console.WriteLine($"{changes} row(s) synchronized to database.");
                    }
                    catch (SqlException ex)
                    {
                        Console.Error.WriteLine($"[ERROR] SQL Error: {ex.Message}");
                    }
                    catch (Exception ex)
                    {
                        Console.Error.WriteLine($"[ERROR] Unexpected: {ex.Message}");
                    }
                }
            }
        }
    }
}

Code Explanation:

• SqlDataAdapter fetches data into a DataTable.
• SqlCommandBuilder auto-generates commands for updates.
• The in-memory table is manipulated by adding a new order.
• Update() batches of changes back to the server in one go.

Output:

When Should We Use ADO.NET Core Connected vs Disconnected Architecture?

Use Connected Architecture when:

• Real-time data access is required.
• Data needs to be instantly consistent between the application and the database.
• Applications such as live dashboards or transaction systems require immediate feedback.

Use Disconnected Architecture when:

• The application needs to work offline or in batch processing scenarios.
• Performance and scalability are essential, and maintaining open connections isn’t feasible.
• The application needs to work with large data sets and perform local processing before pushing changes to the database.

For a better understanding, please look at the following diagram:

Both connected and disconnected architectures in ADO.NET Core have their strengths and weaknesses. By choosing the right architecture based on the application’s needs, you can optimize performance, scalability, and data integrity.

• Choose Connected Architecture for fast, real-time, read-only access with a minimal memory footprint.
• Choose Disconnected Architecture when working with complex UIs, offline scenarios, or when you need to manage data in-memory before saving to the DB.

In the next article, I will discuss SQL Injection and Prevention using ADO.NET Core with Examples. In this article, I explain the differences between Connected and Disconnected Architecture in ADO.NET Core with examples. I hope you enjoy this article on Connected vs. Disconnected Architecture in ADO.NET Core with Examples.