Solution

ADO worked fairly well on a Microsoft-only Intranet and on the middle tier of a three-tier system accessed by Web clients. But ADO doesn’t scale well to the open Internet. It uses DCOM to cross machine boundaries, which means that it can’t easily work with non-Microsoft systems and has trouble get-ting through firewalls at Microsoft-only systems.

Microsoft .NET includes ADO.NET, Microsoft’s architecture for transferring the ideas of universal data access and easy programming from the COM-based world of ADO into the .NET world.  ADO.NET is conceptually similar to ADO in the sense that it is a data abstraction layer that smoothes over differences between data providers and includes prefabricated objects and functions for easy access to data. A data provider that wants to make its data available to .NET clients via ADO.NET implements a standard set of .NET objects that connect the data provider to interested clients. These objects are Connection, DataAdapter, Command, and DataReader.
In addition, the .NET CLR provides an implementation of these objects that works with any OLE DB provider, so any current data provider that speaks OLE DB speaks ADO.NET automatically as well.

ADO.NET provides two different CLR classes that we can use for our database connection.  The class System.Data.SqlClient.SqlConnection is optimized to work only with Microsoft SQL Server.  ADO.NET also provides the class System.Data.OleDb. OleDbConnection, which is a generic Connection object that works with any OLE DB data provider, including SQL Server.

Having created the Connection object, we now need to create the DataAdapter object, which mediates between the Connection object and the client application. Your client  program issues commands to the DataAdapter object, which transmits them to the database through the Connection object and then accepts the results from the Connection object and returns them to your client program. ADO.NET provides two DataAdapter classes. They are System.Data.OleDb.OleDbDataAdapter and System.Data.SqlClient.SqlDataAdapter. As was the case with Connection objects, the former is a generic class that works with any OLE DB–compliant data source, and the latter is optimized to work with SQL Server.