ADO.Net is the latest in a series of technologies from Microsoft which focus on the connection of applications to databases of one sort or another. From the DAO which (and is) was the native mode of connection for MSAccess, through the short-lived RDO, and the now comparatively long-in-the-tooth ADO, this is the next generation of technology. And, although it is not likely that there will not be some future add-ons, enhancements, and upgrades, it appears that this structure of database connectivity is a keeper.

It is not a COM technology, so it can be used on other platforms in addition to Windows, and agnostic when it comes to the brand of database it facilitates connection to. In addition, it allows more extensive support to the XML paradigm.

The .Net platform will continue to allow you to use the older ADO connection technology, but, under most circumstances, this is a poor choice because of the performance penalty , which comes from using the unmanaged code in the COM object.

ADO.Net requires some new methods of accomplishing some of the simple tasks of interacting with data. For example, server-side cursors and are not supported any more because of the increased overhead and the potentially large number of lacks required on the server. Accordingly, the only connection s allowed are forward only, read- read-only result sets, and disconnected result sets. There are rumors of server side cursors being planned for future releases, probably due to the loud complaint from the developer community. However, there are a number of techniques and tools provided which greatly lessen the need for server side cursors, so by the time of the next release, there may be less need for them.

To gain access to the ADO.Net class libraries, you must add the following statements to the top of your source files:

Imports System.Data.OleDb* or, if you are connecting to SQLServer

There is also support for the ODBC connections through Imports System.Data.ODBC

These commands expose the objects needed to connect to the data source.

Like ADO, ADO.Net uses a connection object to point to external data. Under the .Net model, a connection is opened, data is retrieved, and then the connection is closed. The closing of the connection is necessary to free up resources. The connection string (the part of the comment which identifies the source of the data, as well as access to it through username and password) is identical to the connection string grammar under the old model ADO.

The first way to access data is after you have defined and opened the connection, invoke the command object providing it with a SELECT statement, or storedprocedure name with parameters. The Data Reader will allow the application to gain access to the returned resultset. An ExecuteReader method will allow a line by line reading of the data retrieved. However, be aware that this is a forward only dataset – once a line is read, unless you save its contents somewhere, somewhere the data can be lost. The only way to make it available again is to re-establish the connection and read it again.

The second method opens a connection, retrieves a recordset, then stores that recordset in an object called a DataSet. The DataSet acts and functions like a local database, storing the data retrieved – even from multiple sources. It can even link and establish relationships between multiple tables. At the conclusion of the data retrieval, the connection is closed, so that in processing the DataSet is completely disconnected from the data source(s).

The mapping of data between the DataSet and the outside data sources is handled by the DataAdapter object. In addition to keeping track of the connections to the data sources, the DataAdapter also facilitates the updating, deleting, and insertion of data back to the source.

XML is the native format for ADO.Net. It is so tightly integrated that you can define and read schemas, and can seamlessly exchange data in the XLM format, both reading and writing with any application on any platform.

Source: http://www.paladn.com/articles/ado.dotnet.htm