ADO.NET

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 of 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 providers being introduced later. Many vendors have also written providers for their databases since.Figure below shows the connection options available with ADO.NET.

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 its child objects, is what really makes ADO.NET unique. It provides a storage 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 thedata 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.

Advantages

• 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 from 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 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 convert the data to other formats, as well as easily persist it to another data store and restore it again.

ActiveX Data Objects (ADO)

Microsoft introduced ActiveX Data Objects (ADO) primarily to provide a higher-level API for working with OLE-DB. With this release, Microsoft took many of the lessons from the past to build a lighter,more efficient, and more universal data access API. Unlike RDO, ADO was initially promoted as a replacement for both DAO and RDO. At the time of its release, it (along with OLE-DB) was widely believed to be a universal solution for accessing any type of data—from databases to e-mail, flat text files, and spreadsheets.

ADO represented a major shift from previous methods of data access. With DAO and RDO, developers were expected to navigate a tree of objects in order to build and execute queries. For example, to execute a simple insert query in RDO, developers couldn’t just create an rdoQuery object and execute it. Instead,they first needed to create the rdoEngine object, then the rdoEnvironment as a child of it, then an rdoConnection, and finally the rdoQuery. It was a very similar situation with DAO. With ADO,however, this sequence was much simpler. Developers could just create a command object directly, passing in the connection information and executing it. For simplicity and best practice, most developers would still create a separate command object, but for the first time the object could stand alone.

As stated before, ADO was primarily released to complement OLE-DB; however, ADO was not limited to just communicating with OLE-DB data sources. ADO introduced the provider model, which enabled software vendors to create their own providers relatively easily, which could then be used by ADO to communicate with a given vendor’s data source and implement many of the optimizations specific to that data source. The ODBC provider that shipped with ADO was one example of this. When a developer connected to an ODBC data source, ADO would communicate through the ODBC provider instead of through OLE-DB. More direct communication to the data source resulted in better performance and an easily extensible framework. Figure above shows this relationship.

In addition to being a cleaner object model, ADO also offered a wider feature set to help lure developers
away from DAO and RDO. These included the following:

• Batch Updating—For the first time, users enjoyed the capability to make changes to an entire recordset in memory and then persist these changes back to the database by using the UpdateBatch command.

• Disconnected Data Access—Although this wasn’t available in the original release, subsequent releases offered the capability to work with data in a disconnected state, which greatly reduced the load placed on database servers.

• Multiple Recordsets—ADO provided the capability to execute a query that returns multiple recordsets and work with all of them in memory. This feature wasn’t even available in ADO.NET until this release, now known as Multiple Active Result Sets (MARS).

Remote Data Objects (RDO)

Remote Data Objects (RDO) was Microsoft’s solution to the slow performance created by DAO. For talking to databases other than Microsoft Access, RDO did not use the JET engine like DAO; instead, it communicated directly with the ODBC layer. Figure below shows this relationship.

Removing the JET engine from the call stack greatly improved performance to ODBC data sources. The JET engine was only used when accessing a Microsoft Access Database. In addition, RDO had the capability to use client-side cursors to navigate the records, as opposed to the server-side cursor requirements of DAO.This greatly reduced the load on the database server, enabling not only the application to perform better,but also the databases on which that application was dependant.

RDO was primarily targeted toward larger, commercial customers, many of whom avoided DAO due to the performance issues. Instead of RDO replacing DAO, they largely co-existed. This resulted for several reasons:

1. First, users who developed smaller applications, where performance wasn’t as critical, didn’t want to take the time to switch over to the new API.
2. Second, RDO was originally only released with the Enterprise Edition of Visual Basic, so some developers didn’t have a choice.
3. Third, with the release of ODBCDirect, a DAO add-on that routed the ODBC requests through RDO instead of the JET engine, the performance gap between the two became much smaller.
4. Finally, it wasn’t long after the release of RDO that Microsoft’s next universal access API was released.
   

Data Access Objects (DAO)

With the release of Visual Basic 2.0, developers were introduced to a new method for accessing data,known as Data Access Objects (DAO). This was Microsoft’s first attempt to create a data consumer API.Although it had very humble beginnings, and when first released only supported forward-only operations against ODBC data sources, it was the beginning of a series of libraries that would lead developerscloser to the ideal of Universal Data Access. It also helped developers using higher-level languages such as Visual Basic to take advantage of the power of ODBC that developers using lower-level languagessuch as C were beginning to take for granted.

DAO was based on the JET engine, which was largely designed to help developers take advantage of the desktop database application Microsoft was about to release, Microsoft Access. It served to provide another layer of abstraction between the application and data access, making the developer’s task simpler.
Although the initial, unnamed release with Visual Basic 2.0 only supported ODBC connections, the release of Microsoft Access 1.0 marked the official release of DAO 1.0, which supported direct communication with Microsoft Access databases without using ODBC. Figure below shows this relationship.

DAO 2.0 was expanded to support OLE-DB connections and the advantages that come along with it. It also provided a much more robust set of functionality for accessing ODBC data stores through the JET engine. Later, versions 2.5 and 3.0 were released to provide support for ODBC 2.0 and the 32-bit OS introduced with Windows 95.

Drawback

The main problem with DAO is that it can only talk to the JET engine. The JET engine then communicates with ODBC to retrieve the data. Going through this extra translation layer adds unnecessary overhead and makes accessing data through DAO slow.