Framework includes mainly three Data Providers for ADO.NET. They are the Microsoft SQL Server Data Provider , OLEDB Data Provider and ODBC Data 

Provider . SQL Server uses the SqlConnection object , OLEDB uses the OleDbConnection Object and ODBC uses OdbcConnection Object respectively.

The four Objects from the .Net Framework provides the functionality of Data Providers in the ADO.NET. Theyare Connection Object, Command Object, DataReader Object and DataAdapter Object. The Connection Object provides physical connection to the Data Source. The Command Object uses to perform SQL statement or stored procedure to be executed at the Data Source. The DataReader Object is a stream-based, forward-only, read-only retrieval of query results from the Data Source, which do not update the data. Finally, the DataAdapter Object, which populate a Dataset Object with results of Data Source.

DataSet provides a disconnected representation of result sets from the Data Source, and it is completely independent from the Data Source. DataSet provides much greater flexibility when dealing with related Result Sets.

DataSet consists of a collection of DataTable objects that can relate to each other with DataRelation objects. The DataTable contains a collection of DataRow and DataCoulumn Object which contains Data. The DataAdapter

Object provides a bridge between the DataSet and the Data Source.

The ADO.NET Framework supports two models of Data Access Architecture, Connection Oriented Data Access Architecture and Disconnected Data Access Architecture.

In Connection Oriented Data Access Architecture the application makes a connection to the Data Source and then interacts with it through SQL requests using the same connection. In these cases the application stays connected to the database system even when it is not using any Database Operations.

ADO.Net solves this problem by introducing a new component called Dataset. The DataSet is the central component in the ADO.NET Disconnected Data Access Architecture. A DataSet is an in-memory data store that can hold multiple tables at the same time. DataSets only hold data and do not interact with a Data Source. One of the key characteristics of the DataSet is that it has no knowledge of the underlying Data Source that might have been used to populate it.     

In Connection Oriented Data Access, when we read data from a database by using a DataReader object, an open connection must be maintained between our application and the Data Source. Unlike the DataReader, the DataSet is not connected directly to a Data Source through a Connection object when we populate it. It is the DataAdapter that manages connections between Data Source and Dataset by fill the data from Data Source to the Dataset and giving a disconnected behavior to the Dataset. The DataAdapter acts as a bridge between the Connected and Disconnected Objects.

            SqlDataAdapter adapter = new SqlDataAdapter("sql","connection");
     DataSet ds = new DataSet();
     adapter.Fill(ds,"Src Table");