ADO.NET Overview

ADO.NET is a set of classes that expose data access services to the .NET Framework developer. ADO.NET is part of the .NET Framework, and it provides a mechanism for developers to connect to data sources, retrieve data, and store that data back into the data source, all while maintaining data integrity.

Key Components of ADO.NET

ADO.NET consists of a set of .NET Framework classes that expose data access functionality. The most important of these classes are found in the System.Data namespace. ADO.NET can be used to connect to many different data sources, including relational databases (like SQL Server, Oracle, MySQL), NoSQL databases, and even XML files.

1. Connection Objects

Connection objects represent a unique session to a data source. They are used to establish a connection to the database and are responsible for managing the connection lifecycle. Examples include:

SqlConnection (for SQL Server)
OracleConnection (for Oracle)
MySqlConnection (for MySQL)

A connection object must be opened before it can be used and closed when it is no longer needed.

2. Command Objects

Command objects are used to execute SQL statements or stored procedures against a data source. They are associated with a connection object.

SqlCommand
OracleCommand
MySqlCommand

Commands can be executed to return data (using ExecuteReader or ExecuteScalar) or to perform data manipulation operations (using ExecuteNonQuery).

3. DataReader Objects

DataReader objects provide a forward-only, read-only stream of data from the data source. They are the most efficient way to retrieve a large amount of data from a database when you do not need to manipulate the data in memory.

SqlDataReader
OracleDataReader

DataReader objects are created by calling the ExecuteReader method on a Command object.

4. DataSet and DataTable Objects

DataSet and DataTable objects are central to ADO.NET for working with data in memory.

A DataSet can contain multiple DataTable objects, representing the tables retrieved from a database, along with their relationships.
A DataTable represents a single table of data in memory, with rows and columns.

These objects are useful when you need to retrieve data, manipulate it (filter, sort, update), and then send the changes back to the data source. They support caching of changes and conflict resolution.

5. DataAdapter Objects

DataAdapter objects act as a bridge between a DataSet and a data source. They are used to fill a DataSet with data from a data source and to reconcile changes made to the DataSet back to the data source.

SqlDataAdapter
OracleDataAdapter

A DataAdapter uses four commands to retrieve data and update data:

SelectCommand: Retrieves data.
InsertCommand: Inserts new records.
UpdateCommand: Updates existing records.
DeleteCommand: Deletes records.

Key Features and Benefits

Disconnected Data Access: ADO.NET allows applications to operate on data without maintaining an open connection to the data source, improving scalability and performance.
Performance: DataReaders offer highly performant, forward-only access to data.
Flexibility: Supports a wide range of data sources through various .NET data providers.
Data Manipulation: DataSet and DataTable provide robust in-memory data management capabilities.
XML Integration: Seamless integration with XML for data exchange.