What is ADO.NET?

ADO.NET is an object – oriented set of libraries that allows us to interact with data sources. Commonly, the data source is a database, but it could also be a text file, an Excel spreadsheet, or an XML file.

With the release of the .NET Framework, Microsoft introduced a new data access model, called ADO.NET. The ActiveX Data Object acronym was no longer relevant, as ADO.NET was not ActiveX, but Microsoft kept the acronym due to the huge success of ADO. In reality, it’s an entirely new data access model written in the .NET Framework.

ADO.NET supports communication to data sources through both ODBC and OLE-DB, but it also offers another option to using database-specific data providers. These data providers offer greater performance by being able to take advantage of data – source – specific optimizations. By using custom code for the data source instead of the generic ODBC and OLE-DB code, some of the overhead is also avoided. The original release of ADO.NET included a SQL provider and an OLE-DB provider, with the ODBC and Oracle. Many vendors have also written providers for their database since. Figure shows the connection options available with ADO.NET.
                                             Application
                                                  
                                           
                                              ADO.NET

                                              OLE DB

                                              Data Store




 With ADO.NET, the days of the recordset and cursor are gone. The model is entirely new, and consists of five basic objects:
Connection – The Connection object is responsible for establishing and maintaining the connection to the data source, along with any connection – specific information.
Command – The Command object stores the query that is to be sent to the data source, and any applicable parameters.
DataReader – The DataReader object provides fast, forward – only reading capability to quickly loop through the records.
DataSet – The DataSet object, along with the child objects, is what really makes ADO.NET unique. It provides a strong mechanism for disconnected data. The DataSet never communicates with any data source and is totally unaware of the source of the data used to populate it. The best way to think of it is as an in-memory repository to store data that has been retrieved.
DataAdapter – The DataAdapter object is what bridges the gap between the DataSet and the data source. The DataAdapter is responsible for retrieving the data from the Command object and populating the DataSet with the data returned. The DataAdapter is also responsible for persisting changes to the DataSet back to the data source.

    ADO.NET made several huge leaps forward. Arguably, the greatest was the introduction of truly disconnected data access. Maintaining a connection to a database server such as MS SQL Server is an expensive operation. The server allocates resources to each connection, so it’s important to limit the number of simultaneous connections. By disconnecting form the server as soon as the data is retrieved, instead of when the code is done working with that data, that connection becomes available for another process, making the application much more scalable.

Another feature of ADO.NET that greatly improved performance was the introduction of connection pooling. Not only is maintaining a connection to the database to the database an expensive operation, but creating and destroying that connection is also very expensive. Connection pooling cuts down on this. When a connection is destroyed in code, The Framework keeps it open in a pool. When the next process comes around that needs a connection with the same credentials, it retrieves it from the pool, instead of creating a new one.
  
Several other advantages are made possible by the DataSet object. The DataSet object stores the data as XML, which makes it easy to filter and sort the data in memory. It also makes it easy to comvert the data to other formats, as well as easily persist it to another data store and restore it again.