ADO - Connected and Disconnected Architecture

ADO.NET Connection-Oriented Data Access Architecture:

Connected data access architecture in ADO.NET involves establishing and maintaining a continuous connection between the application and the database server throughout the duration of data access operations. It uses the SqlConnection class to establish a connection and the SqlCommand class to execute SQL statements or stored procedures. The SqlDataReader class is used to fetch and read data in a forward-only, read-only manner.

Example to Understand Connection-Oriented Architecture:

using System;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "Data Source=Server;Initial Catalog=Database;Integrated Security=True";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT * FROM Customers";
            SqlCommand command = new SqlCommand(query, connection);
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string customerId = reader["CustomerID"].ToString();
                string companyName = reader["CompanyName"].ToString();
                Console.WriteLine($"Customer ID: {customerId}, Company Name: {companyName}");
            }
            reader.Close();
        }
    }
}

Explanation:

The example demonstrates the connection-oriented architecture in ADO.NET.

The SqlConnection class is used to establish a connection to the database server.

The SQL query to select all rows from the Customers table is defined in the query variable.

A SqlCommand object is created with the query and the connection.

The Open() method of the SqlConnection is called to open the connection to the database.

The ExecuteReader() method of the SqlCommand is called to execute the query and retrieve a SqlDataReader.

The SqlDataReader allows reading data in a forward-only manner using the Read() method. Each call to Read() advances to the next row.

Inside the while loop, data from the reader is extracted using the column names or indices.

Finally, the reader is closed and the connection is closed automatically when exiting the using block.

ADO.NET Disconnection-Oriented Data Access Architecture:

Disconnected data access architecture in ADO.NET involves retrieving data from the database and disconnecting from the database server. It uses the SqlDataAdapter class to fetch data into a DataSet or DataTable. The data can then be manipulated, displayed, or updated in the disconnected state without being connected to the database server.

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

using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "Data Source=Server;Initial Catalog=Database;Integrated Security=True";
        
        string query = "SELECT * FROM Customers";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
            
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, "Customers");
            DataTable dataTable = dataSet.Tables["Customers"];
            foreach (DataRow row in dataTable.Rows)
            {
                string customerId = row["CustomerID"].ToString();
                string companyName = row["CompanyName"].ToString();
                Console.WriteLine($"Customer ID: {customerId}, Company Name: {companyName}");
            }
        }
    }
}

Explanation:

The example demonstrates the disconnected-oriented architecture in ADO.NET.

The SqlConnection class is used to establish a connection to the database server.

The SQL query to select all rows from the Customers table is defined in the query variable.

A SqlDataAdapter object is created with the query and the connection.

A DataSet object is created to hold the retrieved data.

The Fill() method of the adapter is called to populate the DataSet with data from the database.

The DataSet contains one or more DataTable objects, which represent the result of the query. In this example, we access the "Customers" table using the Tables property of the DataSet.

Inside the foreach loop, we iterate over the rows in the DataTable and extract data from each row using the column names or indices.

The data can be manipulated or displayed without being connected to the database server.

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

Connected-Oriented Architecture:

Maintains a continuous connection to the database server.

Executes SQL statements directly against the database server.

Uses SqlConnection, SqlCommand, and SqlDataReader classes.

Suitable for scenarios where real-time access and data streaming are required.

Provides a forward-only, read-only data retrieval approach.

Disconnected-Oriented Architecture:

Retrieves data from the database and disconnects from the server.

Uses SqlDataAdapter, DataSet, and DataTable classes.

Suitable for scenarios where data manipulation and offline data access are required.

Provides a disconnected data manipulation approach.

Allows data to be cached, manipulated, and updated locally without a live database connection.