ADO.NET Core Architecture

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

ADO.NET Core Architecture

In this article, I will discuss ADO.NET Core Architecture. Please read our previous article discussing How to Perform CRUD Operations using ADO.NET Core with Stored Procedures.

What is ADO.NET Core?

ADO.NET Core, part of the .NET Core framework, is designed to enable developers to work with data from a variety of database systems in a managed way. It provides a rich set of components for creating distributed, data-sharing applications. While the core principles of ADO.NET remain consistent with its predecessor in the .NET Framework, ADO.NET in .NET Core has been optimized for cloud and cross-platform development, offering a more modular and lightweight approach.

Data Providers in ADO.NET Core

A data provider in ADO.NET Core is a set of classes that facilitate data access by providing direct access to a data source. These providers are optimized for their specific database systems, offering efficient data manipulation and retrieval. Here’s an overview of the key data providers in ADO.NET Core:

SQL Server Provider (System.Data.SqlClient)

• Purpose: Designed specifically for Microsoft SQL Server. It offers fast, robust access to SQL Server databases.
• Key Components: Includes SqlConnection, SqlCommand, SqlDataReader, and SqlDataAdapter, tailored for SQL Server’s features and data types.
• Usage: Ideal for applications that require direct access to SQL Server databases without the need for additional libraries.

OLE DB Provider (System.Data.OleDb)

• Purpose: Provides access to data sources exposed through OLE DB, including SQL Server, Microsoft Access, and other databases.
• Key Components: OleDbConnection, OleDbCommand, OleDbDataReader, and OleDbDataAdapter.
• Usage: Useful for connecting to a variety of databases and data sources that support OLE DB, offering a flexible connection strategy.

ODBC Provider (System.Data.Odbc)

• Purpose: Allows access to databases using the Open Database Connectivity (ODBC) standard, which includes a wide range of SQL and non-SQL databases.
• Key Components: OdbcConnection, OdbcCommand, OdbcDataReader, and OdbcDataAdapter.
• Usage: Ideal for applications that need to connect to various database systems on different platforms, especially when a specific .NET provider is not available.

Other Providers

Besides the standard providers, there are third-party and community-driven providers for databases such as MySQL (MySql.Data.MySqlClient), PostgreSQL (Npgsql), Oracle (Oracle.ManagedDataAccess), and others. These providers are specifically optimized for their respective databases and offer features and data types specific to those systems.

ADO.NET Core Architecture

ADO.NET Core is a part of the .NET Core framework designed for data access. It provides a rich set of components for connecting to databases, executing commands, and managing data. The ADO.NET Core Architecture is comprised of 6 important components. They are as follows:

• Connection
• Command
• DataReader
• DataAdapter
• DataSet
• DataView

Two components are compulsory from the above components. One is the command object, and the other one is the connection object. Irrespective of the operations like Insert, Update, Delete and Select, the command and connection object you always need. For a better understanding, please have a look at the following image.

Let us understand each of the components in detail.

Connection

The first important component of ADO.NET Core Architecture is the Connection Object. The Connection component in ADO.NET Core is responsible for establishing a connection to a specific data source. It’s the first step in interacting with a database. Each database provider (e.g., SQL Server, MySQL, PostgreSQL) has its own specific Connection class derived from the abstract DbConnection class. This component handles all aspects of database connection, including opening, managing, and closing the connection.

Key properties and methods include:

• ConnectionString: Specifies the server, database, and authentication details required to connect to the database.
• Open(): Opens a database connection.
• Close(): Closes the connection.

Command

The Second important component of ADO.NET Core Architecture is the Command Object. The Command component is used to execute SQL queries, stored procedures, or commands on the database. Derived from the DbCommand class, it allows you to interact with the database by sending commands and receiving results. You can use it to perform CRUD (Create, Read, Update, Delete) operations.

Key properties and methods include:

• CommandText: The SQL query or stored procedure to be executed.
• CommandType: Indicates whether the CommandText is a text command, stored procedure, or table name.
• ExecuteReader(): Executes commands that return rows.
• ExecuteNonQuery(): Executes commands such as INSERT, DELETE, UPDATE, and SET statements.
• ExecuteScalar(): Executes commands that return a single value.

DataReader

The third important component of ADO.NET Core Architecture is the DataReader Object. The DataReader component is used to read data from a database in a forward-only, read-only manner. It provides an efficient way to retrieve data using the ExecuteReader method of the Command object. DbDataReader is the abstract class from which specific DataReader implementations derive.

Key features include:

• Fast, forward-only access to query results.
• Read-only access, making it ideal for scenarios where you simply need to read data without modification.
• Efficient data retrieval, as it does not hold onto the data it retrieves.

DataAdapter

The fourth important component of ADO.NET Core Architecture is the DataAdapter Object. The DataAdapter serves as a bridge between a DataSet and a data source for retrieving and saving data. It uses Command objects to execute SQL commands at the data source to both load data into the DataSet and update the data source with changes made in the DataSet.

Key components and methods include:

• SelectCommand: Retrieves data from the database.
• InsertCommand, UpdateCommand, DeleteCommand: Used to propagate changes made in the DataSet back to the database.
• Fill(): Fills a DataSet with the results of the SelectCommand.
• Update(): Updates the data source with the changes made in the DataSet.

DataSet

The fifth important component of ADO.NET Core Architecture is the DataSet Object. The DataSet is a disconnected, in-memory representation of data. It can contain one or more Data Tables, as well as relationships between them, constraints, and data views. The DataSet allows you to work with data locally in your application, making batch updates and manipulating data without needing a constant connection to the database.

Key features include:

• Disconnected access: Work with data without an ongoing connection to the database.
• Supports multiple data tables: Can mimic the structure of a database.
• Data manipulation: Filter, search, and navigate through the DataSet.

DataView

The sixth important component of ADO.NET Core Architecture is the DataView Object. The DataView provides a dynamic view of a DataTable in a DataSet. It can be used to sort, filter, and search data within a Data Table. DataView allows you to create different views of the data stored in a DataTable, which can be bound to UI components for displaying and editing.

Key properties include:

• Table: The DataTable to which the DataView is connected.
• Sort: Specifies the sort order.
• RowFilter: Defines criteria to filter rows.

Transaction Management in ADO.NET Core

In ADO.NET Core, the transaction management component is designed to ensure data integrity and consistency across multiple operations performed on a database. This component is essential for executing operations that need to be completed as a single unit of work, where either all operations succeed, or none are applied, maintaining the database’s state consistency. The primary classes and interfaces involved in transaction management in ADO.NET Core include DbConnection, DbTransaction, and DbCommand.

Key Components for Transaction Management

• DbConnection: Represents a connection to a database. This class is crucial for initiating transactions using the BeginTransaction method, which starts a database transaction.
• DbTransaction: Represents a transaction to be performed at a data source. Instances of DbTransaction are obtained from the DbConnection object. This class provides the Commit and Rollback methods to either complete or undo the transaction, respectively.
• DbCommand: Represents an SQL statement or stored procedure to execute against a database. When performing operations within a transaction, the DbCommand object’s Transaction property must be set to the DbTransaction instance that the command is part of.

Asynchronous Programming in ADO.NET Core

The Asynchronous Programming model in ADO.NET Core is a critical component of its architecture, designed to enhance application performance and responsiveness, especially in data-intensive applications. The model leverages the Task-based Asynchronous Pattern (TAP) to facilitate non-blocking I/O operations, allowing applications to remain responsive to user input while performing database operations in the background. Core Components of Asynchronous Programming in ADO.NET Core Includes:

Task-based Asynchronous Pattern (TAP)

• Task and Task<TResult>: At the heart of ADO.NET Core’s asynchronous model are the Task and Task<Tresult> classes. These represent asynchronous operations and are used by all asynchronous methods in ADO.NET Core.
• Async and Await Keywords: C#’s async and await keywords are used to define and consume asynchronous methods, respectively. These keywords simplify the process of working with tasks, making asynchronous code more readable and maintainable.

Asynchronous Database Operations

• Connection Operations: Classes such as SqlConnection provide asynchronous methods like OpenAsync for opening database connections without blocking the calling thread.
• Command Execution: SqlCommand and similar command objects offer asynchronous execution methods like ExecuteNonQueryAsync, ExecuteScalarAsync, and ExecuteReaderAsync, enabling non-blocking data manipulation and retrieval.

Cancellation Support

• CancellationToken: Asynchronous methods in ADO.NET Core accept CancellationToken parameters, allowing operations to be canceled programmatically. This feature is essential for building responsive applications that can handle long-running database operations gracefully.

Connection Pooling in ADO.NET Core

Connection Pooling in ADO.NET Core is managed through a combination of the ADO.NET Core infrastructure and the specific database providers (e.g., SQL Server, MySQL, PostgreSQL) that implement the ADO.NET Core interfaces. The architecture of connection pooling in ADO.NET Core is designed to provide efficient management and reuse of database connections, minimizing the overhead associated with opening and closing connections to the database server. Key Components of Connection Pooling Architecture Includes:

• Connection Pool Manager: At the heart of the connection pooling mechanism is the connection pool manager. This component is responsible for creating, managing, and supplying the connections from the pools. Each pool is associated with a unique connection string. The connection pool manager ensures that applications receive connections from the appropriate pool based on the connection string they provide.
• Connection Pool: A connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Pools are separated based on the connection string parameters; different parameters will result in different pools.
• Database Provider: The database provider (e.g., Microsoft.Data.SqlClient for SQL Server) is responsible for implementing the actual logic for opening, closing, and managing the connections to the database. The provider works with the connection pool manager to ensure that connections are efficiently reused and managed.
• Connection Objects: These are the actual instances of DbConnection (or its derivatives like SqlConnection, MySqlConnection, etc.) that applications interact with. When an application requests a connection, it actually provides a connection object from the pool (if available), or a new one is created and added to the pool.
• Configuration Settings: Connection pooling behavior can be customized through configuration settings specified in the connection string. Essential settings include Max Pool Size, Min Pool Size, Connection Lifetime, and Connection Timeout. These settings allow developers to tailor the pooling behavior to the specific needs and performance characteristics of their applications.

Connectivity and Manipulation

• Disconnected Model: ADO.NET Core emphasizes a disconnected data architecture. Data is retrieved from a data source into the application’s memory, manipulated, and then persisted back to the data source if needed. This approach minimizes the need for open connections to the data source, enhancing application scalability and performance.
• Connected Model: For operations that require real-time data access and updates, ADO.NET Core also supports a connected model primarily through the use of the DataReader object for efficient and fast data retrieval.

Cross-Platform Support

ADO.NET Core, being part of .NET Core, is cross-platform, allowing applications to run on Windows, Linux, and macOS. This makes it a versatile choice for developing database applications that need to operate in diverse environments.

In the next article, I will discuss Transactions in ADO.NET Core with Examples. In this article, I explain ADO.NET Core Architecture. I would like to have your feedback. Please post your feedback, questions, or comments about this ADO.NET Core Architecture article.