As previously mentioned, the System.Data.SqlClient and System.Data.OleDb namespaces work with data sources. System.Data.SqlClient uses a managed provider to interact directly with Microsoft SQL Server version 7.0 and higher. System.Data.OleDb interacts with any valid OLE DB source. Though the namespaces are separate, the base objects function in nearly the same exact manner. Both namespaces contain Connection , Command , DataAdapter , and DataReader objects. Indeed, the namespaces almost exactly mirror one another.
As you might have guessed, the connection object opens a connection to your data source. All of the configurable aspects of a database connection are represented in the Connection object, including ConnectionString and ConnectionTimeout . Also, database transactions are still dependent upon the Connection object.
The Command object performs actions on the data source. You can use the Command object to execute stored procedures, or any valid T-SQL command understood by your datasource. This is the object that performs the standard SELECT , INSERT , UPDATE , and DELETE T-SQL operations.
The DataAdapter object is brand-new in ADO.NET. The DataAdapter takes the results of a database query from a Command object and pushes them into a DataSet using the DataAdapter.Fill() method. Additionally the DataAdapter.Update() method will negotiate any changes to a DataSet back to the original data source. Unlike ADO, updating the original data source with modified data works reliably well.
The DataReader object is also brand-new in ADO.NET. The DataReader provides a very fast, forward-only view of the data returned from a data source. In most instances, to display a set of data in a Web or Windows form, this is the object you'll use, because there is very little overhead. No DataSet is created; in fact, no more than one row of information from the data source is in memory at a time. This makes the DataReader quite efficient at returning large amounts of data. You can think of the DataReader as a firehose that goes directly from the data source to the final destination. However, if you need to manipulate schema or use some advanced display features such as automatic data paging, you must use a DataAdapter and DataSet .