Connected and Disconnected Architecture in ADO.NET
Posted by Superadmin on November 15 2023 11:27:35

Connected and Disconnected Architecture in ADO.NET

In this article, I am going to discuss What is Connected and Disconnected Architecture in ADO.NET with Examples. Please read our previous article where we discussed 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 which 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 access the data from data sources in two different ways. The models are Connection Oriented Data Access Architecture and Disconnected Data Access Architecture. In this article, I will explain both 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

So, ADO.NET supports both Connection-Oriented Architectures as well as Disconnection-Oriented Architecture. Depending upon the functionality or business requirement of an application, we can make it either Connection-Oriented or Disconnection-Oriented. Even, it is also possible to use both Architectures together in a single .NET application to communicate with different data sources.

 

ADO.NET Connection-Oriented Data Access Architecture:

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

  

The Connection Oriented Data Access Architecture is always forward only. That means using this architecture mode, we can only access the data in the forward direction. Once we read a row, then 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 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 should be there while accessing the data from the database. In Connection Oriented Architecture, the .NET Application is directly linked with the corresponding Database.

ADO.NET Connection-Oriented Data Access Architecture

ADO.NET DataReader in Connected-Oriented Architecture

The ADO.NET DataReader object is used to read 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 are going to use the following Employee tables to understand Connection-Oriented Architecture as well as Disconnection-Oriented Architecture using ADO.NET.

 

Example to Understand Connection-Oriented Architecture

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 below example, I am using the ADO.NET Data Reader object to Fetch the Data from the Database. As Data Reader in ADO.NET works on Connection-Oriented Architecture, so it always requires an active and open connection 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:

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

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 below example, you can see, within the while loop, after reading the first row, we are closing the database connection by calling the Close method.

 

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:

What are Connected and Disconnected Architecture in ADO.NET with Examples

As you can see in the above output, after reading the first row, the data reader throws an exception, and the reason the database connection is closed. So, 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, always an open and active connection is not required in between the .NET Application and the database. In this architecture, Connectivity is required only to read the data from the database and to update the data within the database.

An example is DataAdapter and DataSet or DataTable classes. Here, using the DataAdapter object and using 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 .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, we can insert new data, can delete the data from within the dataset or data tables. So, while processing the data within the .NET Application using DataSet or Datatable, we do not require an active and open connection.

 

And finally, when we processed the data in our .NET Application, then if we want to update the modified data which is stored inside the dataset or Datatable into the database, then we need to establish the connection again and we need to update the data 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 to perform the operations on the data. And once we modify the DataSet or DataTable, then we need to pass the modified DataSet or DataTable to the DataAdapter which will update the modified data into the database. The DataAdapter object will internally manage the connection i.e. when to establish the connection and when to terminate the connection.

ADO.NET Disconnection-Oriented Data Access Architecture

The ADO.NET DataAdapter establishes a connection with the corresponding database and then retrieves the data from the database and fills the retrieved data into the Dataset or DataTable. And finally, when the task is completed i.e. the Data is processed by the application i.e. the data is modified by the application, and modified data is stored 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 which allows the interaction in disconnection-oriented architecture.

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

In the below example, I am using the ADO.NET Data Adapter object to Fetch the Data from the Database and fill the DataTable. Then data table stores the data in memory and then we modified the data table data, and finally, we provided the modified data table with the Data Adapter object which will 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 as shown in the below image.

Differences Between Connected-Oriented Architecture and Disconnected-Oriented Architecture

Differences Between Connected-Oriented Architecture and Disconnected-Oriented Architecture:

Let us see the Differences Between ADO.NET Connected Oriented Architecture and Disconnected Oriented Architecture.

 

ADO.NET Connected Oriented Architecture:
  1. It is connection-oriented data access architecture. It means always an active and open connection is required.
  2. Using the DataReader object we can implement the Connected Oriented Architecture.
  3. Connected Oriented Architecture gives a faster performance.
  4. Connected Oriented Architecture can hold the data of a single table only.
  5. You can access the data in a forward-only and read-only manner.
  6. Using Data Reader, we cannot persist the data in the database.
ADO.NET Disconnected Oriented Architecture:
  1. It is disconnection-oriented data access architecture. It means always an active and open connection is not required.
  2. Using DataAdapter and DataSet or Datatable we can implement Dis-Connected Oriented Architecture.
  3. Disconnected Oriented Architecture gives a lower performance.
  4. Disconnected Oriented Architecture can hold the data of multiple tables using dataset and single table data using Datatable.
  5. You can access the data in forward and backward directions and you can also modify the data.
  6. Using Data Adapter, we can persist the DataSet or DataTable data into the database.

In the next article, I am going to discuss How to Load XML data to SQL Server Database Table using ADO.NET DataTable with Examples. Here, in this article, I try to explain Connected and Disconnected Architecture in ADO.NET with Examples. I hope you enjoy this Connected and Disconnected Architecture in ADO.NET article.