Connected and Disconnected Architecture in ADO.NET

Back to: ADO.NET Tutorial For Beginners and Professionals

Connected and Disconnected Architecture in ADO.NET

In this article, I will discuss Connected and Disconnected Architecture in ADO.NET with Examples. Please read our previous article discussing How to Perform Batch Operations in C# Using ADO.NET Data Adapters with Examples.

Connected vs Disconnected Architecture in ADO.NET

The ADO.NET is one of the Microsoft data access technologies that is used to establish a connection between the .NET Application (Console, WCF, WPF, Windows, MVC, Web Form, etc.) and different data sources such as SQL Server, Oracle, MySQL, XML, etc. The ADO.NET framework accesses data from data sources in two ways: Connection-Oriented Data Access and Disconnected-Oriented Data Access. In this article, I will explain these architectures in detail with Examples.

Types of Architecture to Access the Data using ADO.NET:

The Architecture supported by ADO.NET for communicating with data sources is categorized into two models. They are as follows:

1. Connected Oriented Architecture
2. Disconnected Oriented Architecture

Depending upon the functionality or business requirement of your application, you can either use Connection-Oriented or Disconnection-Oriented. Using both architectures in a single .NET application can also be possible to allow communication with different data sources.

ADO.NET Connection-Oriented Data Access Architecture:

In the case of Connection-Oriented Data Access Architecture, an open and active connection is always required between the .NET Application and the database. An example is Data Reader, and when we access data from the database, the Data Reader object requires an active and open connection to access the data. If the connection is closed, we cannot access the data from the database; in that case, we will get the runtime error.

The Connection Oriented Data Access Architecture is forward only. That means we can only access the data in the forward direction using this architecture mode. Once we read a row, it will move to the next data row, and there is no chance to move back to the previous row.

The Connection Oriented Data Access Architecture is read-only. This means that by using this architecture, we can only read the data. We cannot modify the data, i.e., we cannot update and delete the data row.

For Connection Oriented Architecture, we generally use the object of the ADO.NET DataReader class. The DataReader object is used to retrieve the data from the database, and it also ensures that an open and active connection is maintained while accessing the data from the database. In Connection Oriented Architecture, the .NET Application is directly linked with the corresponding Database.

ADO.NET DataReader in Connected-Oriented Architecture

The ADO.NET DataReader object reads the data from the database using Connected Oriented Architecture. It works in forward only and only mode. It requires an active and open connection while reading the data from the database.

Example to Understand Connection-Oriented Architecture:

We will use the following Employee tables to understand Connection-Oriented Architecture and Disconnection-Oriented Architecture using ADO.NET.

Please use the following SQL Script to create the EmployeeDB and populate the Employee table with the required sample data.

CREATE DATABASE EmployeeDB;
GO

USE EmployeeDB;
GO

CREATE TABLE Employee(
 Id INT IDENTITY(100, 1) PRIMARY KEY,
 Name VARCHAR(100),
 Email VARCHAR(50),
 Mobile VARCHAR(50),
)
GO

INSERT INTO Employee VALUES ('Anurag','Anurag@dotnettutorial.net','1234567890')
INSERT INTO Employee VALUES ('Priyanka','Priyanka@dotnettutorial.net','2233445566')
INSERT INTO Employee VALUES ('Preety','Preety@dotnettutorial.net','6655443322')
INSERT INTO Employee VALUES ('Sambit','Sambit@dotnettutorial.net','9876543210')
GO

Using ADO.NET Data Reader to Fetch the Data from the Database:

In the example below, I am using the ADO.NET Data Reader object to fetch the data from the database. As the Data Reader in ADO.NET works on Connection-Oriented Architecture, an active and open connection is always required to access the data from the database.

using System;
using System.Data.SqlClient;
namespace ConnectionOrientedArchitecture
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConString))
                {
                    // Creating the command object
                    SqlCommand cmd = new SqlCommand("select Name, Email, Mobile from Employee", connection);

                    // Opening Connection  
                    connection.Open();

                    // Executing the SQL query  
                    SqlDataReader sdr = cmd.ExecuteReader();

                    //Looping through each record
                    //SqlDataReader works in Connection Oriented Architecture
                    //So, it requires an active and open connection while reading the data
                    //from the database
                    while (sdr.Read())
                    {
                        Console.WriteLine(sdr["Name"] + ",  " + sdr["Email"] + ",  " + sdr["Mobile"]);
                    }
                }//Here, the connection is going to be closed automatically
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Exception Occurred: {ex.Message}");
            }

            Console.ReadKey();
        }
    }
}

Output:

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 System;
using System.Data.SqlClient;
namespace ConnectionOrientedArchitecture
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConString))
                {
                    // Creating the command object
                    SqlCommand cmd = new SqlCommand("select Name, Email, Mobile from Employee", connection);

                    // Opening Connection  
                    connection.Open();

                    // Executing the SQL query  
                    SqlDataReader sdr = cmd.ExecuteReader();

                    //Looping through each record
                    //SqlDataReader works in Connection Oriented Architecture
                    //So, it requires an active and open connection while reading the data
                    //from the database
                    while (sdr.Read())
                    {
                        //Read-only, you cannot modify the data
                        //sdr["Name"] = "PKR";
                        Console.WriteLine(sdr["Name"] + ",  " + sdr["Email"] + ",  " + sdr["Mobile"]);
                        connection.Close();//Here, the connection is closed
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Exception Occurred: {ex.Message}");
            }

            Console.ReadKey();
        }
    }
}

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.

ADO.NET Disconnection-Oriented Data Access Architecture:

In the case of Disconnection Oriented Data Access Architecture, an open and active connection is not required between the .NET Application and the database. In this architecture, connectivity is required only to read the data from the database and update the data within the database.

An example is DataAdapter and DataSet or DataTable classes. Here, using the DataAdapter object and an active and open connection, we can retrieve the data from the database and store the data in a DataSet or DataTable. The DataSets or DataTables are in-memory objects, or you can say they store the data temporarily within the .NET Application. Then, whenever required in our .NET Application, we can fetch the data from the dataset or data table and process the data. Here, we can modify the data, insert new data, and delete the data from within the dataset or data tables. So, we do not require an active and open connection while processing the data within the .NET Application using DataSet or Datatable.

Finally, when we process the data in our .NET Application, we need to establish the connection again if we want to update the modified data stored inside the dataset or Datatable in the database. This is how Disconnection Oriented Data Access Architecture works.

ADO.NET DataAdapter in Disconnection-Oriented Architecture

The ADO.NET DataAdapter object acts as an interface between the .NET application and the database. The Data Adapter object fills the Dataset or DataTable, which helps the user perform operations on the data. Once we modify the DataSet or DataTable, we must pass the modified DataSet or DataTable to the DataAdapter, which will update the modified data in the database. The DataAdapter object will internally manage the connection, i.e., when to establish and terminate the connection.

The ADO.NET DataAdapter establishes a connection with the corresponding database, retrieves the data from the database, and fills the retrieved data into the Dataset or DataTable. Finally, when the task is completed, the application processes the data, modifies the data, and stores the modified data in the DataSet or DataTable. Then, the DataAdapter takes the modified data from the DataSet or DataTable and updates it into the database by again establishing the connection.

So, we can say that DataAdapter acts as a mediator between the Application and database, allowing the interaction in disconnection-oriented architecture.

Example to Understand Disconnected-Oriented Architecture in ADO.NET:

In the example below, I am using the ADO.NET Data Adapter object to fetch the data from the database and fill out the data table. The data table stores the data in memory, and then we modify the data table data. Finally, we provided the modified data table with the Data Adapter object to update the modified data within the database.

using System;
using System.Data;
using System.Data.SqlClient;
namespace BatchOperationUsingSqlDataAdapter
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                // Connection string.  
                string connectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";

                // Connect to the EmployeeDB database.  
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // Create a SqlDataAdapter  
                    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM EMPLOYEE", connection);
                    //Fetch the Employee Data and Store it in the DataTable
                    DataTable dataTable = new DataTable();

                    //The Fill method will open the connection, fetch the data, fill the data in
                    //the data table and close the connection automatically
                    adapter.Fill(dataTable); 

                    // Set the UPDATE command and parameters.
                    string UpdateQuery = "UPDATE Employee SET Name=@Name, Email=@Email, Mobile=@Mobile WHERE ID=@EmployeeID;";
                    adapter.UpdateCommand = new SqlCommand(UpdateQuery, connection);
                    adapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name");
                    adapter.UpdateCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 50, "Email");
                    adapter.UpdateCommand.Parameters.Add("@Mobile", SqlDbType.NVarChar, 50, "Mobile");
                    adapter.UpdateCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 4, "ID");
                    //Set UpdatedRowSource value as None
                    //Any Returned parameters or rows are Ignored.
                    adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

                    //Change the Column Values of Few Rows
                    DataRow Row1 = dataTable.Rows[0];
                    Row1["Name"] = "Name Changed";
                    DataRow Row2 = dataTable.Rows[1];
                    Row2["Email"] = "Email Changed";
                    DataRow Row3 = dataTable.Rows[2];
                    Row2["Mobile"] = "Mobile Changed";

                    // Execute the update.  
                    //The Update method will open the connection, execute the Update command by takking
                    //the data table data and then close the connection automatically
                    adapter.Update(dataTable);

                    Console.WriteLine($"Updated Data Saved into the DataBase");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Exception Occurred: {ex.Message}");
            }

            Console.ReadKey();
        }
    }
}

This is how connection-oriented architecture works. Now, you can verify the employee data, and you should see the updated data in the image below.

Connected vs Disconnected Architecture in ADO.NET

ADO.NET, a part of the .NET Framework, offers two distinct modes of interaction with a database: the connected and disconnected architectures. These architectures represent different ways of working with data sources.

Connected Architecture in ADO.NET

In the connected architecture, an application interacts directly with the database using a connection. This means that the connection remains open as long as the application needs to interact with the database, and it’s closed when the operations are completed. The connected architecture is primarily used for direct, real-time access to the database.

Components of Connected Architecture in ADP.NET:

• Connection: Establishes a link between the application and the database (e.g., SqlConnection for SQL Server).
• Command: Executes SQL commands or stored procedures (e.g., SqlCommand).
• DataReader: Provides a way to read a forward-only stream of data rows from a SQL Server database (SqlDataReader).

Features of Connected Architecture:

• Direct Connection: The application connects directly to the database for the duration of the data operation.
• Real-Time Access: Data is accessed and updated in real-time, making it suitable for scenarios where up-to-date data is critical.
• Command Execution: Executes SQL commands directly, such as SELECT, INSERT, UPDATE, and DELETE.

Use Cases of Connected Architecture:

• Real-time operations where immediate database interaction is required.
• Situations where data doesn’t need to be persisted in the application for long-term manipulation.

Disconnected Architecture in ADO.NET

On the other hand, the disconnected architecture allows the application to interact with the database without maintaining a constant connection. Data is retrieved from the database and stored in an in-memory representation. Any changes made to this in-memory data can later be reconciled with the database.

Components of Disconnected Architecture in ADP.NET:

• DataSet: An in-memory Data set consisting of one or more DataTable objects.
• DataAdapter: Acts as a bridge between the DataSet and the data source for retrieving and saving data. It executes SQL commands and stores the results in the DataSet.
• Connection: Used for brief periods to fetch data or update the database.

Features of Disconnected Architecture

• Disconnected Mode: Connects to the database only to fetch and update data; the connection is closed otherwise.
• In-Memory Data Representation: Data is stored in memory in DataSet or DataTable objects.
• Batch Updates: Allows multiple changes to be made to the data in memory and then updated in the database in a single transaction.

Use Cases of Disconnected Architecture:

• Applications where data can be fetched, manipulated, and updated in the database later.
• Scenarios with intermittent or limited database connectivity.
• When bandwidth is a concern, data is fetched once and then worked on locally.

Comparison Between Connected and Disconnected Architecture in ADO.NET

• Performance: The connected architecture is generally faster for immediate data operations but can be resource-intensive due to keeping the connection open. The disconnected architecture is more efficient regarding connection usage, as the connection is only open while reading or updating data.
• Scalability: Disconnected architecture is more scalable since it doesn’t require a continuous connection to the database.
• Concurrency: Since data is manipulated offline in disconnected architecture, handling concurrency and potential conflicts is crucial when updating the database.
• Complexity: Connected architecture is often simpler to implement for straightforward database operations, while disconnected architecture requires additional steps to manage the local data cache and update the database.

In the next article, I will discuss How to Load XML Data into a Data Table using ADO.NET with examples. In this article, I try to explain Connected and Disconnected Architecture in ADO.NET with examples. I hope you enjoy this article on Connected and Disconnected Architecture in ADO.NET.