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.

Connected vs Disconnected Architecture in ADO.NET Core

In ADO.NET, the terms “Connected Architecture” and “Disconnected Architecture” refer to different ways of interacting with a database. These approaches define how data is accessed and manipulated within your application.

ADO.NET (ActiveX Data Objects for .NET) Core provides a set of classes that can be used to access data sources such as databases in a managed way. When working with ADO.NET Core, you can choose between two main architectures for data access: Connected and Disconnected. These architectures are designed to handle data retrieval and manipulation in different ways, each with its own advantages and use cases.

ADO.NET Core Connected Architecture

The Connected Architecture in ADO.NET Core is designed for situations where you need to maintain a continuous connection to the data source. This approach involves directly interacting with the database using a connection object (such as SqlConnection for SQL Server databases). Operations like executing commands, reading data with a DataReader, or performing transactions are done while the connection to the database is open. In Connection Oriented Architecture, the .NET Core Application is directly linked with the corresponding Database. For a better understanding, please have a look at the following diagram.

ADO.NET Core Connected Architecture

Key Components:
  • Connection: Manages the connection to the database.
  • Command: Executes SQL queries, stored procedures, or commands on the database.
  • DataReader: Reads data from the database in a forward-only, read-only manner.
Advantages:
  • Real-time access to data.
  • Efficient for scenarios where data is read, updated, or inserted in a single round trip, i.e., Immediate execution of commands.
Disadvantages:
  • Resource-intensive, as the connection remains open throughout the operation.
  • It is not ideal for situations where the connection needs to be maintained for an extended period or when working with large volumes of data that require batch processing.
Use Cases:
  • Real-time applications where data is constantly changing.
  • Scenarios requiring immediate feedback from the database (e.g., transaction systems).
  • It is suitable for scenarios where real-time data access is required, and the operations performed are straightforward, such as executing SQL commands directly.

Example to Understand Connected Architecture in ADO.NET Core

The Connected Architecture in ADO.NET is used when an application interacts with a database using a direct connection. In this model, the connection to the database must be open while performing operations like insert, update, delete, or select. This approach is suitable for scenarios where you need real-time access to the database with immediate updates. The SqlConnection, SqlCommand, and SqlDataReader are commonly used classes in this architecture.

Let us understand the Connected Architecture in ADO.NET Core with an example. First, we create the EmployeeDB database and the Employee table with dummy data by executing the following script in SQL Server.

CREATE DATABASE EmployeeDB;
GO

USE EmployeeDB;
GO

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Department NVARCHAR(50)
);
GO

INSERT INTO Employees (FirstName, LastName, Email, Department)
VALUES 
('John', 'Doe', 'john.doe@example.com', 'IT'),
('Jane', 'Doe', 'jane.doe@example.com', 'HR'),
('Jim', 'Beam', 'jim.beam@example.com', 'Finance'),
('Jack', 'Daniels', 'jack.daniels@example.com', 'Marketing');
GO

Now, we will create a .NET Core console application and use ADO.NET to connect to the SQL Server database and query the employee table using Connected Architecture. So, create a console application and then modify the Program class as follows:

using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Connection String Pointing to EmployeeDB
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string sql = "SELECT * FROM Employees";

                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            //SqlDataReader Requires an Active and Open Connection to the Database while reading the data
                            while (reader.Read())
                            {
                                Console.WriteLine($"ID: {reader["EmployeeID"]}, Name: {reader["FirstName"]} {reader["LastName"]}, Email: {reader["Email"]}, Department: {reader["Department"]}");
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Exception Occurred, Message: {ex.Message}");
            }

            Console.ReadKey();
        }
    }
}
Output:

Example to Understand Connected Architecture in ADO.NET Core

Now, let us do one thing. After reading the first row from the database, let us close the connection and see what happens. In the example below, you can see that after reading the first row, we are closing the database connection by using the Close method within the while loop.

using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Connection String Pointing to EmployeeDB
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string sql = "SELECT * FROM Employees";

                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            //SqlDataReader Requires an Active and Open Connection to the Database while reading the data
                            while (reader.Read())
                            {
                                Console.WriteLine($"ID: {reader["EmployeeID"]}, Name: {reader["FirstName"]} {reader["LastName"]}, Email: {reader["Email"]}, Department: {reader["Department"]}");

                                //Here, the connection is closed
                                connection.Close();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Exception Occurred, Message: {ex.Message}");
            }

            Console.ReadKey();
        }
    }
}
Output:

Connected vs Disconnected Architecture in ADO.NET Core with Examples

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.

ADO.NET Core Disconnected Architecture

The Disconnected architecture, on the other hand, is designed for scenarios where maintaining a continuous connection to the data source is not feasible or necessary. This architecture uses a DataSet or DataTable to fetch data in bulk, manipulate it in memory, and perform batch updates to the database using the Data Adapter object. The connection to the database is only open during the initial data fetch and the final update, minimizing the time the connection is open. The DataAdapter object will internally manage the connection, i.e., when to establish and terminate the connection. For a better understanding, please have a look at the following diagram.

ADO.NET Core Disconnected Architecture

Key Components:
  • DataAdapter: Acts as a bridge between the DataSet and the database for retrieving and saving data.
  • DataSet/DataTable: In-memory representations of data that can be manipulated without a continuous connection to the database.
  • Connection: Used briefly to open and close the connection for data operations.
Advantages:
  • Reduces the need for an open connection to the database, saving resources.
  • Allows for complex data manipulation and batch updates in memory before committing changes to the database.
Disadvantages:
  • It is slightly more complex to implement due to the need for additional logic to manage local data states and synchronize with the database.
  • Potentially out-of-date data if changes are made to the database by other sources before the local data is synchronized.
Use Cases:
  • Applications that work with data in a batch manner.
  • Ideal for applications that require working with data sets locally, such as when the data needs to be accessed or manipulated by the user before being saved back to the database.

Example to Understand Disconnected Architecture in ADO.NET Core

The Disconnected Architecture is designed to work with data locally and does not require a constant connection to the database. This model is useful when you need to work with data offline or when the application requires minimizing the time connected to the database to free up resources. The DataSet, DataTable, and DataAdapter are key components of this architecture.

Let us understand the Connected Architecture in ADO.NET Core with an example. Please modify the Program class as follows. The following code demonstrates how to fetch data using ADO.NET Core disconnected architecture. Here, we will fetch data into a DataTable using SqlDataAdapter, make some changes to the data in memory, and then update those changes back to the database.:

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Connection String Pointing to EmployeeDB
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // SQL command to fetch all employees
                    string sql = "SELECT * FROM Employees";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, connection);

                    // Defining the UpdateCommand for the SqlDataAdapter
                    string updateSql = @"UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, Email = @Email, Department = @Department WHERE EmployeeID = @EmployeeID";
                    SqlCommand updateCommand = new SqlCommand(updateSql, connection);

                    // Adding parameters to the UpdateCommand
                    updateCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, "FirstName");
                    updateCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, "LastName");
                    updateCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 100, "Email");
                    updateCommand.Parameters.Add("@Department", SqlDbType.NVarChar, 50, "Department");
                    updateCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");
                   
                    dataAdapter.UpdateCommand = updateCommand;

                    // Using DataTable for disconnected architecture
                    DataTable dt = new DataTable();

                    try
                    {
                        // Fetching data and filling into DataTable
                        dataAdapter.Fill(dt);

                        // Displaying data before update
                        Console.WriteLine("Before Update:");
                        DisplayData(dt);

                        // Updating a row in memory
                        if (dt.Rows.Count > 0)
                        {
                            DataRow rowToUpdate = dt.Rows[0]; // Assuming we update the first row
                            rowToUpdate["FirstName"] = "UpdatedFirstName"; // Change last name for demonstration
                            rowToUpdate["LastName"] = "UpdatedLastName";
                            rowToUpdate["Department"] = "UpdatedDepartment";
                        }

                        // Updating the database with changes made to DataTable
                        dataAdapter.Update(dt);

                        // Refetching updated data to display
                        dt.Clear();
                        dataAdapter.Fill(dt);

                        // Displaying data after update
                        Console.WriteLine("\nAfter Update:");
                        DisplayData(dt);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"An error occurred: {ex.Message}");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Exception Occurred, Message: {ex.Message}");
            }

            Console.ReadKey();
        }

        static void DisplayData(DataTable dt)
        {
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine($"ID: {row["EmployeeID"]}, Name: {row["FirstName"]} {row["LastName"]}, Email: {row["Email"]}, Department: {row["Department"]}");
            }
        }
    }
}
Output:

Choosing Between Connected and Disconnected Architecture

The choice between connected and disconnected architectures in ADO.NET Core depends on the specific requirements of your application. The connected approach is more suitable for applications that require real-time data access and interactions with the database. On the other hand, if your application needs to work with data sets to minimize the need for a continuous database connection, if it operates in an environment with limited connectivity, or if you want to work with data locally, the Disconnected Architecture is better suited.

In the next article, I will discuss ADO.NET Core SqlCommandBuilder with Examples. In this article, I explain Connected vs Disconnected Architecture in ADO.NET Core with examples. I hope you enjoy this Connected vs Disconnected Architecture in ADO.NET Core with Examples article.

Leave a Reply

Your email address will not be published. Required fields are marked *