ADO.NET Data Architecture

[Pratyush]

Hi,

I have completed the ASP.net core part and switching over to ADO.Net but before switching over there require the knowledge of ADO.Net data architecture which will help to become the familiar with the Database connection part.

Please provide good Explanation...

Thanks for your help.

[Mecurtis]

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

DataSet

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 DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this DataSet is finished, changes can be made back to the central database for updating. The data in DataSet 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 DataProvider 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 DataProviders: the SQL Data Provider which is designed only to work with Microsoft's SQL Server 7.0 or later and the OleDb DataProvider which allows us to connect to other types of databases like Access and Oracle. Each DataProvider 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 DataReader object which provides a forward-only, read only, connected recordset
The DataAdapter object which populates a disconnected DataSet 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.

[pancham]

ADO.NET Components

The two main components of ADO.NET 3.0 for accessing and manipulating data are the .NET Framework data providers and the DataSet.

.NET Framework Data Providers
The .NET Framework Data Providers are components that have been explicitly designed for data manipulation and fast, forward-only, read-only access to data. The Connection object provides connectivity to a data source. The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. The DataReader provides a high-performance stream of data from the data source. Finally, the DataAdapter provides the bridge between the DataSet object and the data source. The DataAdapter uses Command objects to execute SQL commands at the data source to both load the DataSet with data and reconcile changes that were made to the data in the DataSet back to the data source. For more information, see .NET Framework Data Providers (ADO.NET) and Retrieving and Modifying Data in ADO.NET.

The DataSet
The ADO.NET DataSet is explicitly designed for data access independent of any data source. As a result, it can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application. The DataSet contains a collection of one or more DataTable objects consisting of rows and columns of data, and also primary key, foreign key, constraint, and relation information about the data in the DataTable objects. For more information, see DataSets, DataTables, and DataViews (ADO.NET).

The following diagram illustrates the relationship between a .NET Framework data provider and a DataSet.

ADO.NET architecture

Choosing a DataReader or a DataSet
When you decide whether your application should use a DataReader (see Retrieving Data Using a DataReader (ADO.NET)) or a DataSet (see DataSets, DataTables, and DataViews (ADO.NET)), consider the type of functionality that your application requires. Use a DataSet to do the following:

Cache data locally in your application so that you can manipulate it. If you only need to read the results of a query, the DataReader is the better choice.

Remote data between tiers or from an XML Web service.

Interact with data dynamically such as binding to a Windows Forms control or combining and relating data from multiple sources.

Perform extensive processing on data without requiring an open connection to the data source, which frees the connection to be used by other clients.

If you do not require the functionality provided by the DataSet, you can improve the performance of your application by using the DataReader to return your data in a forward-only, read-only manner. Although the DataAdapter uses the DataReader to fill the contents of a DataSet (see Populating a DataSet from a DataAdapter (ADO.NET)), by using the DataReader, you can boost performance because you will save memory that would be consumed by the DataSet, and avoid the processing that is required to create and fill the contents of the DataSet.

[Arley]

The ADO.NET 2.0 Data Providers offer a common programming model for accessing disparate data sources. Whether you are programming against a Microsoft SQL Server database or an Oracle database, you use the same fundamental programming abstractions: a connection object, a command object, and a data reader object.

The ADO.NET Entity Framework introduces a new data provider called EntityClient; this is the first layer above the ADO.NET 2.0 data providers. EntityClient offers the same programming abstractions as the other data providers-connections, commands, and data readers-but adds mapping capabilities that translate queries expressed in terms of the model into the equivalent queries in terms of the tables in the databases. To complement these capabilities, the ADO.NET Entity Framework introduces an extended query language called Entity SQL (E-SQL), which augments traditional SQL with constructs necessary for querying in terms of the higher-level modeling concepts in Entity Data Models (inheritance, complex types, and explicit relationships).