ADO.NET Data Architecture

ADO .NET

Most applications need data access at one point of time making it a crucial component when working with applications. Data access is making the application interact with a database, where all the data is stored. Different applications have different requirements for database access. ASP.NET uses ADO .NET (Active X Data Object) as it's data access and manipulation protocol which also enables us to work with data on the Internet. 

ADO.NET Data Architecture

Data Access in ADO.NET relies on two components: Data Set and Data Provider.

Data Set

The dataset is a disconnected, in-memory representation of data. It can be considered as a local copy of the relevant portions of the database. The Data Set is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this Data Set is finished, changes can be made back to the central database for updating. The data in Data Set can be loaded from any valid data source like Microsoft SQL server database, an Oracle database or from a Microsoft Access database.

Data Provider

The Data Provider is responsible for providing and maintaining the connection to the database. A Data Provider is a set of related components that work together to provide data in an efficient and performance driven manner. The .NET Framework currently comes with two Data Providers: the SQL Data Provider which is designed only to work with Microsoft's SQL Server 7.0 or later and the OleDb Data Provider which allows us to connect to other types of databases like Access and Oracle. Each Data Provider consists of the following component classes:

The Connection object which provides a connection to the database
The Command object which is used to execute a command
The Data Reader object which provides a forward-only, read only, connected record set
The Data Adapter object which populates a disconnected Data Set with data and performs update

Data access with ADO.NET can be summarized as follows:

A connection object establishes the connection for the application with the database. The command object provides direct execution of the command to the database. If the command returns more than a single value, the command object returns a DataReader to provide the data. Alternatively, the DataAdapter can be used to fill the Dataset object. The database can be updated using the command object or the DataAdapter. 

Component classes that make up the Data Providers

The Connection Object

The Connection object creates the connection to the database. Microsoft Visual Studio .NET provides two types of Connection classes: the Sql Connection object, which is designed specifically to connect to Microsoft SQL Server 7.0 or later, and the OleDb Connection object, which can provide connections to a wide range of database types like Microsoft Access and Oracle. The Connection object contains all of the information required to open a connection to the database.

The Command Object

The Command object is represented by two corresponding classes: Sql Command and OleDb Command. Command objects are used to execute commands to a database across a data connection.  
The Common objects can be used to execute stored procedures on the database, SQL commands, or return complete tables directly. Command objects provide three methods that are used to execute commands on the database:

Execute Non Query: Executes commands that have no return values such as INSERT, UPDATE or DELETE
Execute Scalar: Returns a single value from a database query
Execute Reader: Returns a result set by way of a Data Reader object

The Data Reader Object

The Data Reader object provides a forward-only, read-only, connected stream record set from a database. Unlike other components of the Data Provider, Data Reader objects cannot be directly instantiated. Rather, the Data Reader is returned as the result of the Command object's Execute Reader method. The Sql Command. Execute Reader method returns a SqlData Reader object, and the OleDb Command. Execute Reader method returns an OleDb Data Reader object. The Data Reader can provide rows of data directly to application logic when you do not need to keep the data cached in memory. Because only one row is in memory at a time, the Data Reader provides the lowest overhead in terms of system performance but requires the exclusive use of an open Connection object for the lifetime of the Data Reader.

The Data Adapter Object

The Data Adapter is the class at the core of ADO .NET's disconnected data access. It is essentially the middleman facilitating all communication between the database and a Data Set. The Data Adapter is used either to fill a Data Table or Data Set with data from the database with it's Fill method. After the memory-resident data has been manipulated, the Data Adapter can commit the changes to the database by calling the Update method. The Data Adapter provides four properties that represent database commands:

Select Command
Insert Command
Delete Command
Update Command

When the Update method is called, changes in the Data Set are copied back to the database and the appropriate Insert Command, Delete Command, or Update Command is executed.