# ADONET
ADO.NET is a core data access technology within the Microsoft .NET Framework. It provides a set of classes and tools for developers to connect to and interact with databases and other data sources. It supports both connected (via DataReader) and disconnected (via DataSet) data access models, offering a versatile bridge between applications and data stores like SQL Server, Oracle, and XML.
**Pronunciation:** ay-dee-oh dot net
**Difficulty:** Intermediate
**Synonyms:** ActiveX Data Objects .NET
**Categories:** Data Access Technology, Microsoft Technologies
**Tags:** data access, .net, database, microsoft, framework
Canonical: https://scaleengineer.com/glossaries/adonet
---
## Definitions

- **Core Data Access Framework:** **ADO.NET** is a set of classes in the .NET Framework that exposes data access services for .NET developers. It provides a rich, object-oriented interface for creating robust, scalable, and data-driven applications. Its architecture is fundamentally divided into two key parts:

***Data Providers***
A Data Provider is a set of components used to connect to a specific database, execute commands, and retrieve results. Each provider is tailored for a specific data source. The core objects of a Data Provider are:
*   ***Connection:*** Establishes a connection to a data source (e.g., `SqlConnection`).
*   ***Command:*** Represents a SQL statement or stored procedure to be executed against the data source (e.g., `SqlCommand`).
*   ***DataReader:*** Provides a high-performance, forward-only, read-only stream of data from the database (e.g., `SqlDataReader`). It is highly efficient for reading data in a connected state.
*   ***DataAdapter:*** Acts as a bridge between the `DataSet` and the data source. It uses `Command` objects to fill the `DataSet` and to update the data source with changes made to the `DataSet` (e.g., `SqlDataAdapter`).

***DataSet***
The `DataSet` is the cornerstone of the disconnected architecture of **ADO.NET**. It is an in-memory cache of data retrieved from a data source. Key characteristics include:
*   ***Disconnected:*** Once populated, the `DataSet` does not require an active connection to the data source.
*   ***Rich Object Model:*** It contains a collection of one or more `DataTable` objects. Each `DataTable` contains `DataRow` and `DataColumn` objects, and you can define `DataRelation` objects to relate tables to each other.
*   ***Data Source Agnostic:*** A `DataSet` can hold data from different sources (e.g., a SQL database, an XML file, or programmatically created data) simultaneously.

While modern applications often use higher-level Object-Relational Mappers (ORMs) like Entity Framework, **ADO.NET** remains a crucial technology. These ORMs are built on top of **ADO.NET**, and it is still the preferred choice for scenarios requiring maximum performance, bulk data operations, or precise control over the executed SQL.

## Etymology

The name ADO.NET is an evolution of its predecessor, ActiveX Data Objects (ADO). The '.NET' suffix was added to signify its native integration with the .NET Framework and its shift from a COM-based technology to a managed code framework.

## First used

2002

## Historical context

The history of **ADO.NET** is rooted in Microsoft's continuous effort to simplify data access for developers. Before its inception, developers navigated a series of increasingly abstract technologies. This began with Open Database Connectivity (ODBC), a low-level C-style API.

To simplify this, Microsoft introduced Data Access Objects (DAO) for its Jet/Access databases, followed by Remote Data Objects (RDO) for client-server databases like SQL Server. The next major step was ActiveX Data Objects (ADO), a COM-based technology that provided a more unified and simpler object model for accessing various data sources.

With the launch of the .NET Framework in 2002, Microsoft introduced **ADO.NET** as a complete redesign and the successor to ADO. It was built from the ground up for the new managed code environment. Its most significant innovation was the emphasis on a disconnected architecture, centered around the `DataSet`. This was a direct response to the needs of scalable web applications, which couldn't afford to maintain persistent database connections for every user. **ADO.NET** provided a robust provider model, allowing seamless connection to different databases like SQL Server, Oracle, and any source with an OLE DB or ODBC driver. It laid the foundational data access layer upon which later, more abstract technologies like LINQ to SQL and Entity Framework were built.

## Q&A

- **What is the primary difference between the DataSet and DataReader objects in ADO.NET?:** The primary difference lies in their connection model and usage.

***DataReader*** provides a connected, forward-only, read-only stream of data from a database. It is highly efficient and has a low memory footprint because it only keeps one row in memory at a time. It's ideal for quickly reading and processing large amounts of data without needing to cache it.

***DataSet***, on the other hand, represents a disconnected, in-memory cache of data. It can contain multiple tables, relationships, and constraints. It fetches all the data from the database, disconnects, and allows the application to work with the data locally. This is useful for web applications and scenarios where data needs to be manipulated and then updated back to the source in a batch.
- **What are the two main components of the ADO.NET architecture?:** The two main components of the ADO.NET architecture are:

***1. Data Provider:*** This component consists of classes that provide direct access to a data source. It is specific to the database being used (e.g., `SqlClient` for SQL Server, `OracleClient` for Oracle). It includes core objects like:
*   `Connection`: To establish a connection to the database.
*   `Command`: To execute SQL queries or stored procedures.
*   `DataReader`: To read a stream of data from the database.
*   `DataAdapter`: To bridge the gap between the data source and the `DataSet`.

***2. DataSet:*** This is the disconnected component of ADO.NET. It is an in-memory representation of data, independent of any data source. It can be populated from a database, an XML file, or programmatically, and it can be manipulated by the application without an active connection to the original source.

## Usage examples

- The legacy application was built using raw **ADO.NET** to execute stored procedures, giving the developers fine-grained control over database interactions.
- For our data migration tool, we used an **ADO.NET** `DataReader` to efficiently stream millions of records from the source database without overwhelming the server's memory.
- Before Entity Framework became the standard, developers would use the **ADO.NET** `DataAdapter` to populate a `DataSet`, bind it to a UI grid, and then persist any changes back to the database.

## Related terms

- Entity Framework
- LINQ
- ODBC
- OLE DB
- ADO

## Popular related terms

- Entity Framework
- LINQ
- SQL Server